《数据库系统概论》实验报告书 安徽工业大学计算机科学与技术学院
此处要选择的是SAGE ，但分组却是SNO，意思是把学生按学号分组，输出年龄，不对号。选择的是SAGE 就以SAGE分组，所以改成：SELECT SAGE FROM STUDENT GROUP BY SAGE;
例3-27：(分组查询) 求学生人数不足3人的系及其相应的学生数。
select SDEPT,count(SNO) from Student group by SDEPT having count(SNO)<3;
例3-28：(分组查询) 求各系中除01班之外的各班的学生人数。
select SDEPT,SCLASS,count(SNO) from Student group by SDEPT,SCLASS having SCLASS not in('01');
例3-29：(涉及空值的查询) 分别观察各组函数、行的显示顺序以及分组查询与空值的关系。
例3-30：(连接查询) 求选修了课程001且成绩在70分以下或成绩在90分以上的学生的姓名、课程名称和成绩。
select Student.SNAME,Course.CNAME,Score.SCORE from Student,Course,Score where Student.SNO=Score.SNO and Course.CNO=Score.CNO and Score.CNO='001' and Score.SCORE not between 70 and 90;
例3-31：(连接查询与表的别名) 求选修了课程的学生的学生姓名、课程号和成绩。
select st.SNAME,sc.CNO,sc.SCORE from Student st,Score sc where st.SNO=sc.SNO;
例3-32：(自身连接查询) 求年龄大于 ’李丽’ 的所有学生的姓名、系和年龄。
select s1.SNAME,s1.SDEPT,s1.SAGE from Student s1,Student s2 where s1.SAGE>s2.SAGE and
20/19
《数据库系统概论》实验报告书 安徽工业大学计算机科学与技术学院
s2.SNAME='李丽';
例3-33：(外部连接查询) 求选修了课程002或003的学生的学号、课程号、课程名和成绩，要求必须将002和003课程的相关信息显示出来。
select SNO,Course.*,SCORE from Course,Score where Course.CNO=Score.CNO(+) and Score.CNO in('002','003');
例3-34：(子查询) 求与 ‘黎明’ 年龄相同的学生的姓名和系。
select SNAME,SDEPT from Student where SAGE in(select SAGE from Student where SNAME='黎明');
例3-35：(子查询) 求选修了课程名为 ’数据结构’ 的学生的学号和姓名。
select SNO,SNAME from Student where SNO in(select SNO from Score,Course where Course.CNO=Score.CNO and Course.CNAME='数据结构');
例3-36：(子查询ANY) 求比数学系中某一学生年龄大的学生的姓名和系。
select SNAME,SDEPT from Student where SAGE >any(select SAGE from Student where Sdept='MA') and SDEPT<>'MA';
例3-37：(子查询ALL) 求比数学系中全体学生年龄大的学生的姓名和系。
select SNAME,SDEPT from Student where SAGE>all(select SAGE from Student where SDEPT='MA');
21/19
《数据库系统概论》实验报告书 安徽工业大学计算机科学与技术学院
例3-38：(子查询EXISTS) 求选修了课程004的学生的姓名和系。
select SNAME,SDEPT from Student st where exists(select * from Score sc where CNO='004' and st.SNO=sc.SNO);
例3-39：(返回多列的子查询) 求与 ‘李丽’ 同系且同龄的学生的姓名和系。
select SNAME,SDEPT from Student where SDEPT=(select SDEPT from Student where SNAME='李丽') and SAGE=(select SAGE from Student where SNAME='李丽') and SNAME<>'李丽';
例3-40：(多个子查询) 求与 ‘‘黎明’ 同系，且年龄大于 ‘李丽’ 的学生的信息。
select * from Student where SAGE>(select SAGE from Student where SNAME='李丽') and SDEPT in(select SDEPT from Student where SNAME='黎明');
例3-41：(子查询中使用表连接) 求数学系中年龄相同的学生的姓名和年龄。
SELECT SNAME,SAGE FROM STUDENT WHERE SDEPT='MA' AND SAGE IN(SELECT SAGE FROM STUDENT GROUP BY SAGE HAVING COUNT(SAGE)>1);
select s1.SNAME,s1.SAGE from Student s1 where s1.SNO in(select s2.SNO from Student s2,Student s3 where s2.SNO<>s3.SNO and s2.SAGE=s3.SAGE and s2.SDEPT='MA' and s3.SDEPT='MA');
例3-42：(连接或嵌套查询) 检索至少选修王成刚老师所授课程中一门课程的女学生姓名。
（1）select s1.SNAME from Student s1 where SSEX='女' and exists(select * from Score sc1,Teach t1 where sc1.CNO=t1.CNO and t1.TNAME='王成刚' and sc1.SNO=s1.SNO);
（2）select s1.SNAME from Student s1 where SSEX='女' and SNO in(select distinct(SNO)from Score sc1,Teach t1 where sc1.CNO=t1.CNO and t1.TNAME='王成刚');
例3-43：(嵌套与分组查询) 检索选修某课程的学生人数多于3人的教师姓名。
select distinct TNAME from Teach t1 where t1.CNO in(select distinct CNO from Score sc1 group by sc1.CNO having count(sc1.SNO)>3);
22/19
《数据库系统概论》实验报告书 安徽工业大学计算机科学与技术学院
例3-44：(集合查询) 列出所有教师和同学的姓名和性别。
select TNAME,TSEX from Teach union select SNAME,SSEX from Student;
例3-45：(相关子查询) 求未选修课程004的学生的姓名。
select SNAME from Student s1 where not exists(select * from Score sc where sc.SNO=s1.SNO and sc.CNO='004');
例3-46：(相关子查询) 求选修了全部课程的学生的姓名。
select SNAME from Student s1 where not exists(select * from Course c1 where not exists(select * from Score sc1 where sc1.CNO=c1.CNO and sc1.SNO=s1.SNO));
例3-47：(相关子查询) 求至少选修了学生 ‘96002’ 所选修的全部课程的学生的学号。
select distinct(SNO) from Score sc1 where not exists(select * from Score sc2 where sc2.SNO='96002' and not exists(select * from Score sc3 where sc3.CNO=sc2.CNO and sc3.SNO=sc1.SNO));
例3-48：(相关子查询) 求成绩比所选修课程平均成绩高的学生的学号、课程号、和成绩。
select * from Score sc1 where exists(select * from Score sc2 group by sc2.CNO having sc1.SCORE>avg(sc2.SCORE) and sc1.CNO=sc2.CNO);
23/19
《数据库系统概论》实验报告书 安徽工业大学计算机科学与技术学院
例3-49：(相关子查询) 查询被一个以上的学生选修的课程号。 select distinct(CNO) from Score sc1 where exists(select * from Score sc2 where sc1.CNO=sc2.CNO group by sc2.CNO having count(sc2.SNO )>1);
例3-50：(相关子查询) 查询所有未选课程的学生姓名和所在系。
select SNAME,SDEPT from Student st1 where not exists(select * from Course c1 where exists(select * from Score sc1 where sc1.CNO=c1.CNO and sc1.SNO=st1.SNO));
② 对重点实验结果进行分析；
在使用group by 的时候，select出的内容为分组数据，或集函数对分组计算的结果。having子句可以筛选分组后的各组数据，where子句在集合前先筛选记录，也就是说where应该作用在group by 子句和having子句前.
相关查询中，exists 的功能是，依次选取父查询的结果，送入子查询中进行检查判断，结果为真，父查询的该条结果就输出，否则不输出；not exists 的作用于此正好相反。
24/19