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);
网友评论