实验五高级查询【实验目的与要求】1、熟练掌握IN子查询2、熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替）3、熟练掌握EXISTS子查询（尤其是如何将全称量词和逻辑蕴含用EXISTS谓词代替）4、熟练掌握复杂查询的select语句【实验准备】1．准备好测试数据2．熟悉多表查询与嵌套查询的用法。
【实验内容】5.1.准备工作1.创建测试用数据库XSGL，并在其中创建三个表本实验需用到student、course和SC表，其结构和约束如下：Student表结构及其约束为：表5-1 student表结构和约束列名称类型宽度允许空值缺省值主键说明Sno char 8 否是学号Sname varchar 8 否学生姓名Sex char 2 否男性别Birth datetime 否出生年月Classno char 3 否班级号Entrance_date datetime 否入学时间Home_addr varchar 40 是家庭地址Course表结构及其约束为：表5-2 course表结构和约束列名称类型宽度允许空值缺省值主键说明cno Char 3 否是课程号Cname varchar 20 否课程名称Total_perior int 是总学时credit int 是学分SC表结构及其约束为：表5-3 SC表结构和约束列名称类型宽度允许空值缺省值主键外键说明sno Char 8 否是学号,参照student表cno char 3 否是课程号,参照course表grade int 是否成绩其中成绩为百分制。
2.对表添加、修改、删除数据向student表中插入如下数据:表5-4 student表Sno sname sex birth classno Entrance_date Home_addr sdept postcode 20050001 张虹男1984/09/011 051 2005/09/01 南京CS 200413 20050002 林红女1983/11/12 051 2005/09/01 北京CS 100010 20050003 赵青男1982/05/11 051 2005/09/01 上海MA 200013向course表中插入数据:表5-5 course表cno Cname Total_perior credit001 高数68 3002 C语言程序设计75 4003 JAVA语言程序设计68 3向SC表中插入数据:表5-6 SC表Sno Cno grade20050001 001 8920050001 002 7820050001 003 8920050002 002 6020050003 001 80为达到更好的测试效果，请自行向数据库表中添加其它数据，使表中数据量达10条以上，并使每个字段值表现出多样性。
5.2.复杂查询（1）查询比“林红”年纪大的男学生信息。
SQL语句：select *from Studentwhere birth<(select birthfrom Studentwhere Sname='林虹')and Sex='男';（2）检索所有学生的选课信息。
SQL语句：select Sno,Sname,Cno,Cnamefrom Student,Course（3）查询已选课学生的学号、姓名、课程名、成绩。
连接查询T—SQL语句：select distinct Student.Sno,Sname,Cname,gradefrom Student,Course,SCwhere SC.Sno=Student.Sno and o=o（4）查询选修了“C语言程序设计”的学生的学号和姓名。
SQL语句：select distinct Student.Sno,Snamefrom Student,Course,SCwhere ame='C语言程序设计'（5）查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。
（子查询）SQL语句：(select classnofrom Studentwhere Sname='张虹');连接查询SQL语句：select distinct Student.Sno,Sname,Home_addrfrom Student,Course,SCwhere classno=(select classnofrom Studentwhere Sname='张虹');（6）查询其他班级中比“051”班任一学生年龄大的学生的学号、姓名。
带有ANY或ALL谓词的子查询语句：select Sno,Snamefrom Studentwhere birth< any(select birthfrom Studentwhere classno='051')and classno<>051;用聚合函数实现：select Sno,Snamefrom Studentwhere birth< (select MIN(birth)from Studentwhere classno='051')and classno<>051;（7）查询选修了全部课程的学生姓名。
SQL语句：select Snamefrom Studentwhere not exists(select *from Coursewhere not exists(select *from SCwhere Student.Sno=Sno and o=Cno));（8）查询至少选修了学生“20050002”选修的全部课程的学生的学号，姓名。
SQL语句：select Sname,Snofrom Studentwhere Sno IN(select DISTINCT Snofrom SC xwhere not exists(select Sno,Cnofrom SC ywhere y.Sno='20050002' andnot exists(select Cno,Snofrom SC zwhere z.Sno=x.Snoand o=o)));（9）检索学生的学号、姓名、学习课程名及课程成绩。
SQL语句：select distinct Student.Sno,Sname,Cname,gradefrom Student,SC,Coursewhere SC.Sno=Student.Sno and o=o（10）检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到低次序排列。
SQL语句：select Sno,Cno,gradefrom SCwhere grade>(select MAX(grade)from SCwhere Cno='002') and Cno=(select cnofrom Coursewhere Cname='高数')order by grade desc;（11）检索选修3门以上课程的学生的学号、总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。
SQL语句：select Sno,SUM(grade) as 'total_grade'from SCwhere grade>=60group by Snohaving Sno in(select Snofrom SCgroup by Snohaving COUNT(Cno)>3)order by SUM(grade) desc;（12）检索出每位学生的学号、姓名、未修课程名。
SQL语句：select Sno,Sname,Cname as 'Untake_course'from Student,Coursewhere not exists(select Sno,Cnofrom SCwhere Sno=Student.Sno and Cno=o )order by Student.Sno;（13）检索多于3名学生选修的并以3结尾的课程号的平均成绩。
SQL语句：select Cno,avg(grade) as 'average_grade'from SCwhere grade in(select gradefrom SCwhere Cno in(select Cnofrom SCgroup by CnohavingCount(Cno)>=3))and o like '__3'group by Cno;（14）检索最高分与最低分之差大于5分的学生的学号、姓名、最高分、最底分。
SQL语句：select SC.Sno,Sname,max(grade) as 'Max_Grade',min(grade) as 'Min_Grade' from SC left join Student on(SC.Sno=Student.Sno)group by SC.Sno,Snamehaving SC.Sno in(select SC.Snofrom SC,Studentwhere Student.Sno=SC.Sno)and (max(grade)-min(grade))>5（15）查询选修课程‘001’的学生集合与选修课程‘002’的学生集合的交集SQL语句：select Snofrom SCwhere Cno='001'and Sno in(select Snofrom SCwhere Cno='002')（16）查询选修课程‘001’的学生集合与选修课程‘002’的学生集合的差集SQL语句：select Snofrom SCwhere Cno='001'and Sno not in(select Snofrom SCwhere Cno='002')5.3.嵌套子查询以下实验在前面实验中创建的CPXS数据库中完成，请根据前面实验创建的表结构和数据，完成如下嵌套查询：查询在2004年3月18日没有销售的产品名称（不允许重复）。
用IN子查询：写出对应SQL语句并给出查询结果：select distinct 产品名称from CPwhere 产品编号 not in(select 产品编号from CPXSBwhere 销售日期='2004-3-18');用EXISTS子查询：写出对应SQL语句并给出查询结果：select distinct 产品名称from CPwhere 产品名称!=all(select 产品名称from CPwhere exists(select 产品编号from CPXSBwhere 销售日期!='2004-03-18' andCP.产品编号=CPXSB.产品编号))查询名称为“家电市场”的客户在2004年3月18日购买的产品名称和数量。