SQL基础知识

SQL基础知识.png

1.查询每门课程被选修的学生数

INNER JOIN course c 
on sc.cid=c.cid
group by cname;

2.查询出只选修了4门课程的全部学生的学号和姓名

INNER JOIN student s
on sc.sid=s.sid
group by s.sid
HAVING 选课数=4;

3.查询姓张的学生名单

select * from student where sname like '张%';

4.查询同名同姓学生名单，并统计同名人数

GROUP BY sname
HAVING COUNT(*)>=2;

5.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

INNER JOIN course c
on sc.cid=c.cid
group by cname
order by 平均成绩 asc,课程号 desc;

6.查询平均成绩大于80的所有学生的学号，姓名和平均成绩

INNER JOIN student s
on s.sid=sc.sid
group by sid
having 平均成绩>80;

7.查询所有课程都低于60分的学生姓名和分数

INNER JOIN student s 
on s.sid=sc.sid
group by s.sid
having 分数<60;

8.查询所有学生的选课情况，显示学生的姓名和选修的课程名

INNER JOIN sc 
on s.sid=sc.sid
INNER JOIN course c
on c.cid=sc.cid;

9.查询任何一门课程成绩在70分以上的姓名、课程名称和分数

INNER JOIN student s
on sc.sid=s.sid
INNER JOIN course c
on c.cid=sc.cid
where score>70;

10.查询所有不及格的课程，并按课程号从大到小罗列课程ID和学生ID

INNER JOIN student s
on sc.sid=s.sid
INNER JOIN course c
on c.cid=sc.cid
where score<60 
order by 课程ID desc,学生ID desc;

11.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名

INNER JOIN student s
on sc.sid=s.sid
INNER JOIN course c
on c.cid=sc.cid
where c.cid=003 and score>80;

12.求选全了课程的学生人数
(1)先查所有课程数量

select count(*) from course; 

(2)学员选课数量等于总课程数量

INNER JOIN sc
on sc.sid=s.sid
GROUP BY s.sid
having 选课数=(select count(*) from course);

13.查询选修李老师所授课程的学生中，成绩最高的学生姓名及其成绩

INNER JOIN student s
on sc.sid=s.sid
INNER JOIN course c
on c.cid=sc.cid
INNER JOIN teacher t
on t.tid=c.tid
where tname like '李%'
order by 学生成绩 DESC
limit 2;

14.查询各个课程及相应的选修人数

INNER JOIN course c
on sc.cid=c.cid
group by c.cid;

15.查询不同课程成绩相同的同学的学号、课程号、学生成绩

from sc a
INNER JOIN sc b
on a.score=b.score
where a.cid<>b.cid
order by a.score desc;

16.查询每门功课成绩最好的前两名

where(
select count(*) from sc b
where a.cid=b.cid
and a.score<=b.score
)<=2
order by a.cid;

17.检索至少选修两门课程的学生学号

INNER JOIN sc
on sc.sid=s.sid
GROUP BY s.sid
having 选课数>=2;

18.查询全部学生都选修的课程的课程号和课程名
(1)先查所有学生人数

select count(*) from student; 

(2)学员选课数量等于总人数

INNER JOIN sc
on sc.sid=s.sid
INNER JOIN course c
on c.cid=sc.cid
GROUP BY c.cid
having 选课人数=(select count(*) from student);