文章目录
- 创建表格说明
- SQL语言实现查询
- 1. 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数
- 2. 查询张三所选修的课程编号和成绩
- 3. 查询张三选修的课程名称及学分
- 4. 查询每个学生的学号、姓名、选修的课程名及成绩
- 5. 查询所有学生的选课情况，列出学号、姓名、课程号、课程名、成绩、学分。
- 6. 查询所有低于90分的学生成绩记录，并按学号降序排列，列出学号、姓名、课程号、课程名、成绩。
- 7. 查询选修“2”号课程且成绩在90分以上的所有学生的学号、姓名、成绩。
- 8. 查询“DB_DESIGN”的先行课名称。
- 9. 查询同时选修了课程号为“1”、“2”的学生学号。
- 10. 查询计算机系（'CS'）全体学生的“DB_DESIGN”成绩，列出学号、姓名、课程名、成绩，并按成绩降序排列。
- 11. 查询其他系中比信息系（'IS'）所有学生年龄都不小的学生姓名及年龄。
- 12. 查询没有选修“1”号课程的学生姓名。
- 13. 查询有3名以上学生选修的课程号。
- 14. 查询“DB_DESIGN”课程没有成绩的学生姓名。
- 15. 查询各门课程的成绩均不低于90分的学生姓名。
- 16. 查询“DB_DESIGN”成绩不低于该门课程平均分的学生姓名。
- 17. 查询各个系男女学生的平均年龄和人数。
- 18. 查询计算机系(CS)总平均成绩最高的学生学号和姓名。
- 19. 查询总平均成绩大于85的学生人数。
- 20. 查询“3”号课成绩比“95002”号同学该门课成绩高的所有学生的学号。
- 21. 查询“2”号课成绩比“3”号课成绩高的所有学生的学号及其“2”号课和“3”号课的成绩。
- 22. 查询有二门以上（含两门）及格课程的学生姓名及其平均成绩。
- 23. 查询各科成绩最高分和最低分，以如下字段形式显示：课程号，最高分，最低分
- 24. 查询最受欢迎的课程的课程号和选课人数
- 25. 查询和“95001”号同学学习的课程完全相同的其他同学的学号和姓名
创建表格说明
学生表 （学号，姓名，性别，年龄，所在系）CREATE TABLE Student (Sno varCHAR(10) NOT NULL UNIQUE, Sname varCHAR(30), Ssex varCHAR(20) check (Ssex='男' or Ssex='女' ), Sage int, Sdept varCHAR(20) );
课程表 （课程号，课程名，先修课程，学分）create Table Course( Cno varCHAR(10), Cname varCHAR(60), Cpno varCHAR(10), Ccredit int not null, Primary key(Cno), FOREIGN KEY (Cpno) REFERENCES Course(Cno) );
选课表 （学号，课程号，成绩）CREATE TABLE SC( Sno varCHAR(10), Cno varCHAR(10), Grade int, Primary key (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );
SQL语言实现查询
1. 查询有3门以上课程是90分以上的学生的学号及（90分以上的）课程数
sc表，限定90分以上并以学号分组，count统计
SELECT sno, count(*)
FROM sc
WHERE grade>90
GROUP BY sno
HAVING count(*) >3;
- 1
- 2
- 3
- 4
- 5
2. 查询张三所选修的课程编号和成绩
可以将学生表与课程表合并，限定条件查询，对于表内相同的列一定要标注，例如选课表的学号，标注方式（表.列），一定记得标注每一个有歧义的，或者自定义命名
select cno, grade
from student, sc
where student.sno=sc.sno
and sname='张三' ;
- 1
- 2
- 3
- 4
3. 查询张三选修的课程名称及学分
合并三张表最容易，缺点就是费内存，对于嵌套查询会在下一次训练使用
select cname, ccredit
from student, course, sc
where student.sno=sc.sno
and sc.cno=course.cno
and sname= '张三' ;
- 1
- 2
- 3
- 4
- 5
4. 查询每个学生的学号、姓名、选修的课程名及成绩
想必很多也知道要合并表输出，但是这里得使用基于学生表的左连接left join（表）on（连接条件）
（或者右连接right join，此时学生表要放右边），基于学生表的左连接可以做到查找出没有选课的学生，仅仅是自然连接会统计不到未选课的，左连接效果更好。
select student.sno,sname,cname,grade
from (student left join sc on student.sno=sc.sno)
left join course on sc.cno=course.cno;
- 1
- 2
- 3
5. 查询所有学生的选课情况，列出学号、姓名、课程号、课程名、成绩、学分。
跟4很像，而这个所有学生，只能用左连接
select student.sno,sname,sc.cno,cname,grade,ccredit
from (student left join sc on student.sno=sc.sno)
left join course on sc.cno=course.cno;
- 1
- 2
- 3
6. 查询所有低于90分的学生成绩记录，并按学号降序排列，列出学号、姓名、课程号、课程名、成绩。
order by …desc降序排列
select student.sno,sname,sc.cno,cname,grade
from student,sc,course
where student.sno=sc.sno
and sc.cno=course.cno
and grade<90
order by student.sno desc;
- 1
- 2
- 3
- 4
- 5
- 6
7. 查询选修“2”号课程且成绩在90分以上的所有学生的学号、姓名、成绩。
select student.sno,sname,grade
from student,sc
where student.sno=sc.sno
and cno='2'
and grade>90;
- 1
- 2
- 3
- 4
- 5
8. 查询“DB_DESIGN”的先行课名称。
两个一个的表连接，这里需要自定义名称区别两张表，将一表先修课程号与二表课程号连接，在表一查课程名，系统会找出合并后的表二的修行课的名称
select c2.cname
from course c1, course c2
where c1.cpno=c2.cno
and c1.cname='DB_DESIGN';
- 1
- 2
- 3
- 4
使用嵌套查询，即先找出DESIGN的先修课程号，然后cno相等的即为他的先行课
嵌套查询注重逻辑性，而且在复杂的查询中可以一步步查询便于及时改正，比如这里可以先执行括号内，看看查找出来的课程号是不是我想要的先修课程号
select cname
from course
where cno IN
(select cpno
from course
where cname='DB_DESIGN');
- 1
- 2
- 3
- 4
- 5
- 6
9. 查询同时选修了课程号为“1”、“2”的学生学号。
连接两张表判断，这个思路比较简单。
select x.sno
from sc x, sc y
where x.sno=y.sno
and x.cno= '1'
and y.cno= '2';
- 1
- 2
- 3
- 4
- 5
也可以使用嵌套，先查出选修1的学生，导出学号，在这些里边再次判断有没有选修2。
这里使用了另一种方式，个人觉得它更加实用，即查找了选修1或者2的学生，并按学号分组，那么既然两个都选修，那么统计的数量就为2，这种方式更加实用在于，在查找同时选修课程数很多情况下，这种方式仅需改动很小的一部分。
select sno
from student
where sno in
(select sno
from sc
where cno in ('1','2')
group by sno
having count(cno)=2);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
10. 查询计算机系（‘CS’）全体学生的“DB_DESIGN”成绩，列出学号、姓名、课程名、成绩，并按成绩降序排列。
这里就不需要外连接了，这里限定了一定选修课程。
select student.sno,sname,cname,grade
from student,sc,course
where student.sno=sc.sno
and sc.cno=course.cno
and sdept= 'CS'
and cname='DB_DESIGN'
order by grade desc;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
11. 查询其他系中比信息系（‘IS’）所有学生年龄都不小的学生姓名及年龄。
都不小，即不小于最大值，其中限定其他系，用不等号 <>。
select sname,sage
from student
where sage>=
(
select max(sage)
from student
where sdept='IS'
)
and sdept<>'IS';
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
12. 查询没有选修“1”号课程的学生姓名。
exists的使用
select sname from student
where not exists
(
select * from sc
where cno= '1'
and sc.sno=student.sno
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
in的使用，在限定了数据下用in，比如我这里限定学号在新查找的表存在
select sname
from student
where sno not in
(
select sno
from sc
where cno='1'
);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
13. 查询有3名以上学生选修的课程号。
select cno from sc group by cno having count(*)>3
- 1
14. 查询“DB_DESIGN”课程没有成绩的学生姓名。
注意空值null的判断用is，或者is not，不要用=
select sname from student, course, sc
where student.sno=sc.sno and course.cno=sc.cno and cname='DB_DESIGN' and grade is null;
- 1
- 2
15. 查询各门课程的成绩均不低于90分的学生姓名。
对（学号，姓名）分组，判断最小课程分数>=90，这里分组加上姓名是因为select输出的内容需要在group by 里含有，为了少一次查询，将其作为两个变量进行分组，实际还是一个，因为学号相等，学生的名字一定相等。
select sname
from student, sc
where student.sno=sc.sno
group by student.sno,sname
having min(grade)>=90;
- 1
- 2
- 3
- 4
- 5
16. 查询“DB_DESIGN”成绩不低于该门课程平均分的学生姓名。
先找均分再比较
select sname from student, course, sc
where student.sno= sc.sno and course.cno=sc.cno and course.cname='DB_DESIGN'
and sc.grade>=
(select avg(sc.grade) from course, sc
where course.cno=sc.cno and course.cname='DB_DESIGN');
- 1
- 2
- 3
- 4
- 5
17. 查询各个系男女学生的平均年龄和人数。
select sdept, ssex, avg(sage), count(*) from student group by sdept, ssex;
- 1
18. 查询计算机系(CS)总平均成绩最高的学生学号和姓名。
找成绩最高，然后成绩与最高相等的学生，不要用limit，要防止最高分多人情况
SELECT sc.sno,sname
FROM sc ,student
WHERE sc.sno=student.sno and sdept='CS'
GROUP BY sc.sno,sname
HAVING avg(grade)=(SELECT max(A) from(SELECT avg(grade) A
FROM sc ,student
WHERE Sc.sno=student.sno and sdept='CS'
GROUP BY sc.sno));
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
19. 查询总平均成绩大于85的学生人数。
select count(*) from student where sno in
(select sno from sc group by sno having avg(grade)>85);
- 1
- 2
20. 查询“3”号课成绩比“95002”号同学该门课成绩高的所有学生的学号。
select x.sno from sc x where x.cno='3'
and x.grade>
(select y.grade from sc y where y.cno='3' and y.sno='95002');
- 1
- 2
- 3
21. 查询“2”号课成绩比“3”号课成绩高的所有学生的学号及其“2”号课和“3”号课的成绩。
select x.sno, x.grade, y.grade from sc x, sc y
where x.sno=y.sno and x.cno='2' and y.cno='3' and x.grade>y.grade;
- 1
- 2
22. 查询有二门以上（含两门）及格课程的学生姓名及其平均成绩。
select sname, avg(x.grade) from student, sc x
where student.sno=x.sno and student.sno in
(select y.sno from sc y where y.grade>=60 group by y.sno having count(y.cno)>=2)
group by student.sno,sname;
- 1
- 2
- 3
- 4
23. 查询各科成绩最高分和最低分，以如下字段形式显示：课程号，最高分，最低分
select cno 课程号,max(grade) 最高分,min(grade) 最低分
from sc group by cno;
- 1
- 2
24. 查询最受欢迎的课程的课程号和选课人数
不要用limit
select cno,count(sno) from sc group by cno having
count(sno)>=all(select count(sno) from sc group by cno);
- 1
- 2
25. 查询和“95001”号同学学习的课程完全相同的其他同学的学号和姓名
查95001选修的课程，再查选了95001没有选过的课程并滤除，那么剩下的就是至多选了95001选修的全部课程，统计数量确定完全相等
select sno,sname from student where sno in
(select sno from sc where sno not in
(select distinct sno from sc where cno not in (select cno from sc where sno='95001'))
group by sno
having count(*)=(select count(*) from sc where sno='95001'))
and sno!= '95001';
- 1
- 2
- 3
- 4
- 5
- 6