5.1.1 SQL综合练习

一、创建表

  • 创建表student，特征包括学生编号、姓名、性别、出生年月及班级。
  1. create table student
  2. (
  3. SNO VARCHAR(3) NOT NULL, 
  4. SNAME VARCHAR(4) NOT NULL,
  5. SSEX VARCHAR(2) NOT NULL,
  6. SBIRTHDAY DATETIME,
  7. CLASS VARCHAR(5)
  8. );
  • 创建表course，特征包括课程编号、课程名称和教师编号。
  1. create table course
  2. (
  3. CNO VARCHAR(5) NOT NULL, 
  4. CNAME VARCHAR(10) NOT NULL,
  5. TNO VARCHAR(10) NOT NULL
  6. );
  • 创建表score，特征包括学生编号、课程编号与分数。
  1. create table score
  2. ( 
  3. SNO VARCHAR(3) NOT NULL, 
  4. CNO VARCHAR(5) NOT NULL, 
  5. DEGREE NUMERIC(10, 1) NOT NULL
  6. );
  • 创建表teacher，特征包括教师编号、姓名、性别、出生年月、职称及所属院系。
  1. create table teacher
  2. ( 
  3. TNO VARCHAR(3) NOT NULL, 
  4. TNAME VARCHAR(4) NOT NULL, 
  5. TSEX VARCHAR(2) NOT NULL, 
  6. TBIRTHDAY DATETIME NOT NULL, 
  7. PROF VARCHAR(6), 
  8. DEPART VARCHAR(10) NOT NULL
  9. );

二、插入表数据

接下来依次向创建好的四张表插入数据，操作如下：

  • 向student表插入6条学生信息数据
  1. insert into student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾华' 
  2. ,'男' ,1977-09-01,95033);
  3. insert into student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' 
  4. ,'男' ,1975-10-02,95031);
  5. insert into student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王丽' 
  6. ,'女' ,1976-01-23,95033);
  7. insert into student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李军' 
  8. ,'男' ,1976-02-20,95033);
  9. insert into student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' 
  10. ,'女' ,1975-02-10,95031);
  11. insert into student (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陆君' 
  12. ,'男' ,1974-06-03,95031);
  • 向course表插入4条课程数据
  1. insert into course (CNO,CNAME,TNO)VALUES ('3-105' ,'计算机导论',825)
  2. insert into course (CNO,CNAME,TNO)VALUES ('3-245' ,'操作系统' ,804);
  3. insert into course (CNO,CNAME,TNO)VALUES ('6-166' ,'数据电路' ,856);
  4. insert into course (CNO,CNAME,TNO)VALUES ('9-888' ,'高等数学' ,100);
  • 向score表插入12条得分数据
  1. insert into score (SNO,CNO,DEGREE)VALUES (103,'3-245',86);
  2. insert into score (SNO,CNO,DEGREE)VALUES (105,'3-245',75);
  3. insert into score (SNO,CNO,DEGREE)VALUES (109,'3-245',68);
  4. insert into score (SNO,CNO,DEGREE)VALUES (103,'3-105',92);
  5. insert into score (SNO,CNO,DEGREE)VALUES (105,'3-105',88);
  6. insert into score (SNO,CNO,DEGREE)VALUES (109,'3-105',76);
  7. insert into score (SNO,CNO,DEGREE)VALUES (101,'3-105',64);
  8. insert into score (SNO,CNO,DEGREE)VALUES (107,'3-105',91);
  9. insert into score (SNO,CNO,DEGREE)VALUES (108,'3-105',78);
  10. insert into score (SNO,CNO,DEGREE)VALUES (101,'6-166',85);
  11. insert into score (SNO,CNO,DEGREE)VALUES (107,'6-106',79);
  12. insert into score (SNO,CNO,DEGREE)VALUES (108,'6-166',81);
  • 向teacher表插入4条教师信息数据
  1. insert into teacher (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
  2. VALUES (804,'李诚','男','1958-12-02','副教授','计算机系');
  3. insert into teacher (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
  4. VALUES (856,'张旭','男','1969-03-12','讲师','电子工程系');
  5. insert into teacher (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART)
  6. VALUES (825,'王萍','女','1972-05-05','助教','计算机系');
  7. insert into teacher (TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) 
  8. VALUES (831,'刘冰','女','1977-08-14','助教','电子工程系');

三、题目

\1. 查询Student表中的所有记录的sname、ssex和class列

  1. select sname,ssex,class from student;

\2. 查询教师所有的单位（即不重复的depart列）

  1. select distinct depart from teacher;

\3. 查询student表的所有记录

  1. select * from student;

\4. 查询score表中成绩在60到80之间的所有记录

  1. select * from score where degree between 60 and 80;

\5. 查询score表中成绩为85，86或88的记录

  1. select * from score where degree in (85,86,88);

\6. 查询student表中”95031”班或性别为”女”的同学记录

  1. select * from student where class = '95031' or ssex = '女';

\7. 以class降序查询student表的所有记录 \8. 以cno升序、degree降序查询score表的所有记录

  1. select * from student order by class desc;
  2. select * from score order by cno asc,degree desc;

\9. 查询”95031”班的学生人数

  1. select count(*) from student where class = '95031';

\10. 查询score表中的最高分的学生学号和课程号

  1. select sno,cno from score where degree in (
  2. select max(degree) from score
  3. );

\11. 查询”3-105”号课程的平均分

  1. select avg(degree) from score where cno = '3-105';

\12. 查询score表中至少有5名学生选修的并以3开头的课程的平均分数

  1. select avg(degree) from score 
  2. where cno like '3%' 
  3. group by cno having count(*) >= 5;

\13. 查询最低分大于70，最高分小于90的sno列

  1. select sno from score 
  2. group by sno 
  3. having min(degree) > 70 and max(degree) < 90;

\14. 查询所有学生的sname、cno和degree列 \15. 查询所有学生的sno、cname和degree列 \16. 查询所有学生的sname、cname和degree列

  1. # 14
  2. select a.sname,b.cno,b.degree from student as a 
  3. join score as b on a.sno = b.sno
  4. # 15
  5. select a.cname,b.sno,b.degree from course as a
  6. join score as b on a.cno = b.cno
  7. # 16
  8. select a.sname,b.cname,c.degree from student as a
  9. join course as b
  10. join score as c
  11. on a.sno = c.sno and b.cno = c.cno;
  12. # 第二种解法
  13. select a.sname,b.cname,c.degree from student as a
  14. join (course b,score c)
  15. on a.sno = c.sno and b.cno = c.cno;

\17. 查询”95033”班所选课程的平均分

  1. select avg(degree) from score 
  2. where sno in (
  3. select sno from student where class = '95033'
  4. );
  5. # 进阶解法
  6. select avg(a.degree) from score a
  7. join student b 
  8. on a.sno = b.sno where b.class = '95033';

\18. 假设使用如下命令建立了一个grade表，现查询所有同学的Sno、Cno和rank列，并按照rank列排序

  1. create table grade
  2. (
  3. low numeric(3,0),
  4. upp numeric(3,0),
  5. rank char(1)
  6. );
  7. insert into grade values (90,100,'A');
  8. insert into grade values (80,89,'B');
  9. insert into grade values (70,79,'C');
  10. insert into grade values (60,69,'D');
  11. insert into grade values (0,59,'E');

解法如下：

  1. select a.sno,a.cno,b.rank from score a 
  2. join grade b 
  3. where a.degree between b.low and b.upp
  4. order by rank;

\19. 查询score表中选修”3-105”课程的成绩高于”109”号同学成绩的所有同学的记录

  1. # 解法一
  2. select * from score where cno = '3-105'
  3. and degree > (
  4. select degree from score where sno = 109 and cno = '3-105'
  5. );
  6. # 解法二
  7. select a.* from score a 
  8. where a.cno = '3-105' and a.degree > all(select degree from score b 
  9. where b.sno = '109' and b.cno = '3-105');

\20. 查询score中选学一门以上课程的同学中分数为非最高分成绩的记录

  1. select A.sno from score as A where A.degree not in (
  2. select max(B.degree) from score as B group by B.sno )
  3. group by A.sno
  4. having count(A.sno) > 1;

\21. 查询和学号为107的同学同年出生的所有学生的sno、sname和sbirthday列

考察日期与时间函数的运用

  1. select sno,sname,sbirthday from student where year(sbirthday) in 
  2. (
  3. select year(sbirthday) from student wehre sno = 107
  4. );

\22. 查询”张旭”教师任课的学生成绩

  1. select degree from score where cno in (
  2. select cno from course where tno in (
  3. select tno from teacher where teacher = '张旭'));
  4. # 进阶解法
  5. select a.degree from score a 
  6. join (teacher b,course c)
  7. on a.cno = c.cno and b.tno = c.tno
  8. where b.tname = '张旭';

\23. 查询选修某课程的同学人数多于5人的教师姓名

  1. select a.tname from teacher a 
  2. join(course b,score c)
  3. on a.tno = b.tno and b.cno = c.cno
  4. group by c.cno having count(*) > 5;

\24. 查询所有表中关于”95033”班和”95031”班全体学生的信息记录

  1. select * from student a inner join score b 
  2. on a.sno = b.sno inner join course c
  3. on b.cno = c.cno inner join teacher d
  4. on c.tno = d.tno 
  5. where a.class = '95033' or a.class = '95031';

\25. 查询存在有85分以上成绩的课程cno

  1. # 解法一：
  2. select distinct cno from score where degree > 85;
  3. # 解法二：
  4. select cno from score group by cno having max(degree) >85;

\26. 查询出”计算机系”教师所教课程的成绩表

  1. select a.*,b.cname,c.tname,c.depart from score a
  2. join (course b, teacher c)
  3. on a.cno = b.cno and b.tno = c.tno
  4. where c.depart = '计算机系';

\27. 查询”计算机系”中与”电子工程系”没有相同职称的教师的tname和prof

  1. select tname,prof from teacher where depart = '计算机系' and prof not in
  2. (select prof from teacher where depart = '电子工程系');

\28. 查询选修编号为”3-105”课程且成绩高于选修编号为”3-245”的同学的cno、sno和degree,并按degree从高到低次序排序。

  1. select * from score as a,score as b
  2. where a.cno = '3-105' and b.cno = '3-245'
  3. and a.sno = b.sno
  4. and a.degree > b.degree
  5. order by a.degree desc;

\29. 查询所有教师和同学的name、sex和birthday \30. 查询所有女教师和女同学的name、sex和birthday

  1. # 29
  2. select sname as name, ssex as sex, sbirthday as birthday from student
  3. union
  4. select tname as name, tsex as sex, tbirthday as birthday from teacher;
  5. # 30
  6. select sname as name, ssex as sex, sbirthday as birthday from student
  7. where ssex = '女'
  8. union
  9. select tname as name, tsex as sex, tbirthday as birthday from teacher
  10. where tsex = '女';

\31. 查询成绩比该课程平均成绩低的同学的成绩表

  1. select a.* from score a where degree < (
  2. select avg(degree) from score b 
  3. where a.cno = b.cno );

\32. 查询所有任课教师的tname和depart \33. 查询所有未讲课的教师的tname和depart

  1. # 32
  2. # 解法一
  3. select a.tname,a.depart from teacher a 
  4. join course b 
  5. on a.tno = b.tno;
  6. # 解法二
  7. select a.tname,a.depart from teacher a
  8. where exists (
  9. select * from course b where a.tno = b.tno
  10. );
  11. # 33
  12. select a.tname,a.depart from teacher a
  13. where not exists (
  14. select * from course b where a.tno = b.tno
  15. );

\34. 查询至少有2名男生的班号

  1. select class from student where ssex = '男' 
  2. group by class 
  3. having count(ssex) >= 2;

\35. 查询Student表中不姓“王”的同学记录

  1. select * from student where sname not like '王%';

\36. 查询student表中每个学生的姓名和年龄

  1. # 解法一
  2. select sname, year(curdate())-year(sbirthday) age from student;
  3. # 解法二
  4. select sname, year(now())-year(sbirthday) age from student;

\37. 查询student表中最大和最小的sbirthday日期值

  1. select sname,max(sbirthday) birthday from student
  2. where sbirthday in (
  3. select max(sbirthday) from student )
  4. union
  5. select sname,min(sbirthday) birthday from student
  6. where sbirthday in (
  7. select min(sbirthday) from student );

\38. 以班号和年龄从大到小的顺序查询student表中的全部记录

  1. select * from student 
  2. order by class desc,
  3. year(now())-year(sbirthday()) desc;

\39. 查询”男”教师及其所上的课程

  1. select a.tname,b.cname from teacher a 
  2. join course b
  3. on a.tno = b.tno
  4. where a.tsex = '男';

\40. 查询和“李军”同性别并同班的同学sname

  1. select sname from student where ssex in (
  2. select ssex from student where sname = '李军')
  3. and class in (
  4. select class from student where sname = '李军')
  5. and sname != '李军';

\41. 查询所有选修“计算机导论”课程的“男”同学的成绩表

  1. # 解法一
  2. select a.* from score a join (course b,student c)
  3. on a.cno = b.cno and a.sno = c.sno
  4. where c.ssex = '男' and a.cno in (
  5. select cno from course where cname = '计算机导论'
  6. );
  7. # 解法二
  8. select a.* from score a join (course b,student c)
  9. using (sno,cno)
  10. where c.ssex = '男' and b.cname = '计算机导论';

References：

  1. 深入浅出MySQL (豆瓣)
  2. MySQL必知必会 (豆瓣)
  3. 经典SQL练习题 - CSDN博客

原文链接：https://zhuanlan.zhihu.com/p/29413183?utm_source=qq&utm_medium=social