《实验五SQL语言之高级查询.doc》由会员分享，可在线阅读，更多相关《实验五SQL语言之高级查询.doc（10页珍藏版）》请在三一文库上搜索。
1、实验五 高级查询姓名：学号：专业：班级：同组人：实验日期：【实验目的与要求】1、 熟练掌握IN子查询2、 熟练掌握比较子查询（尤其要注意ANY、ALL谓词如何用集函数代替）3、 熟练掌握EXISTS子查询（尤其要注意如何将全称量词和逻辑蕴含用EXISTS谓词代替）4、 熟练掌握复杂查询的select语句【实验准备】1 准备好测试数据2 熟悉多表查询与嵌套查询的用法。【实验内容】5.0 准备工作1.创建测试用数据库XSGL，并在其中创建三个表，其结构和约束如下：Student表结构及其约束为： 表5-1 student表结构和约束列名称类型宽度允许空值缺省值主键说明Snochar8否是学号Sna
2、mevarchar8否学生姓名Sexchar2否男性别Birthdatetime否出生年月Classnochar3否班级号Entrance_datedatetime否入学时间Home_addrvarchar40是家庭地址Course表结构及其约束为：表5-2 course表结构和约束列名称类型宽度允许空值缺省值主键说明CnoChar3否是课程号Cnamevarchar20否课程名称Total_periorint是总学时Creditint是学分SC表结构及其约束为：表5-3 SC表结构和约束列名称类型宽度允许空值缺省值主键外键说明SnoChar8否是学号Cnochar3否是课程号Gradeint
3、是否成绩其中成绩为百分制。2.对表添加、修改、删除数据向Student表中插入如下数据:表5-4 Student表SnoSnameSexBirthClassnoEntrance_dateHome_addrSdeptPostcode20050001张虹男1984/09/0110512005/09/01南京CS20041320050002林红女1983/11/120512005/09/01北京CS10001020050003赵青男1982/05/110512005/09/01上海MA200013向Course表中插入数据:表5-5 Course表CnoCnameTotal_perior Credi
4、t 001高数683002C语言程序设计754003JAVA语言程序设计683向SC表中插入数据:表5-6 SC表SnoCnoGrade 20050001001892005000100278200500010038920050002002602005000300180为达到更好的测试效果，请自行向数据库表中添加其它数据，使表中数据量达10条以上，并使每个字段值表现出多样性。5.1 复杂查询（1）查询比“林红”年纪大的男学生信息。SQL语句：select Sno,Sname,Birth,Classno,Entrance_date,Home_addr,Sdept,Postcode from Stu
5、dentwhere Birth( select Birth from Student where Sname=林红) and Sex=男（2）检索所有学生的选课信息。SQL语句：Select Student.Sno,Sname,Sex,Classno,Sdept,Course.Cname,Total_perior,Credit,SC.Cno,Gradefrom Student,SC,Coursewhere Student.Sno=SC.Sno and SC.Cno=Course.Cno（3）查询已选课学生的学号、姓名、课程名、成绩。连接查询TSQL语句：select Student.Sno,S
6、name,Course.Cname,SC.Gradefrom Student,SC,Coursewhere Student.Sno=SC.Sno and SC.Cno=Course.Cno（4）查询选修了“C语言程序设计”的学生的学号和姓名。SQL语句：select Student.Sno,Snamefrom Student,Course,SCwhere Student.Sno=SC.Snoand SC.Cno=Course.Cnoand Course.Cname=C语言程序设计（5）查询与“张虹”在同一个班级的学生学号、姓名、家庭住址。（子查询）SQL语句：select Sno,Sname,
7、Home_addrfrom Studentwhere Classno=(select Classno from Student where Sname=张虹)and Sname张虹连接查询SQL语句：select s1.Sno,s1.Sname,s1.Home_addrfrom Student s1,Student s2where s1.Classno=s2.Classnoand s2.Sname=张虹and s1.Sname张虹（6）查询其他班级中比“051”班任一学生年龄大的学生的学号、姓名。带有ANY或ALL谓词的子查询语句：select Sno,Snamefrom Studentwhe
8、re Birthany (select Birth from Student where Classno=051)and Classno051用聚合函数实现：select Sno,Snamefrom Studentwhere Birthany (select max(Birth) from Student where Classno=051)and Classno051（7）查询选修了全部课程的学生姓名。SQL语句： select Snamefrom Studentwhere not exists (select * from Course where not exists (select *
9、 from SC where Sno=Student.Sno and Cno=Course.Cno)（8）查询至少选修了学生“20050002”选修的全部课程的学生的学号，姓名。SQL语句：select distinct Snofrom SC SCXwhere not exists (select * from SC SCY where SCY.Sno=20050002 and not exists (select * from SC SCZ where SCZ.Sno=SCX.Sno and SCZ.Cno=SCY.Cno)and Sno20050002（9）检索学生的学号、姓名、学习课程名
10、及课程成绩。SQL语句：select Student.Sno,Sname,Cname,Gradefrom Student,Course,SCwhere Student.Sno=SC.Snoand Course.Cno=SC.Cno（10）检索选修了“高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到低次序排列。SQL语句：select Sno,Cno,Gradefrom SC where Grade ( select MAX(Grade) from SC where Cno=002 )and Cno=(select Cno from Course whe
11、re Cname=高数)order by Grade desc;（11）检索选修3门以上课程的学生的学号、总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。SQL语句：use XSGLselect Sno,sum(Grade)as Total_Gradefrom SC where Grade=60group by Snohaving Sno in(select Sno from SC group by Sno having count(Cno)=3)order by sum(Grade) desc;（12）检索出每位学生的学号、姓名、未修课程名。SQL语句：select Studen
12、t.Sno,Sname,Cname as 未选修课程from Student,Coursewhere not exists(select Sno,Cno from SC where SC.Cno=Course.Cno and Student.Sno=SC.Sno)order by Student.Sno（13）检索多于3名学生选修的并以3结尾的课程号的平均成绩。SQL语句：select Sno,avg(Grade) as average_gradefrom SCwhere Grade in( select Grade from SC where Cno in( select Cno from
13、SC group by Cno having count(Cno)=3 ) and Cno5（15）查询选修课程001的学生集合与选修课程002的学生集合的交集SQL语句： select Student.Sno,Sname,Sdept,Classnofrom Student,SCwhere Student.Sno=SC.Sno and Cno=001intersectselect Student.Sno,Sname,Sdept,Classnofrom Student,SCwhere Student.Sno=SC.Sno and Cno=002（16）查询选修课程001的学生集合与选修课程002
14、的学生集合的差集SQL语句：select Student.Sno,Sname,Sdept,Classnofrom Student,SCwhere Student.Sno=SC.Sno and Cno=001exceptselect Student.Sno,Sname,Sdept,Classnofrom Student,SCwhere Student.Sno=SC.Sno and Cno=0025.2嵌套子查询以下实验在前面实验中创建的CPXS数据库中完成，请根据前面实验创建的表结构和数据，完成如下嵌套查询：n 查询在2004年3月18日没有销售的品名称（不允许重复）。用IN子查询：写出对应SQ
15、L语句并给出查询结果：select distinct 产品名称from cpxsb,cpwhere cp.产品编号 in (select distinct 产品编号 from cpxsb where 销售日期2004-3-18)用EXISTS子查询：写出对应SQL语句并给出查询结果： select distinct 产品名称from cpxsb,cpwhere exists (select distinct 产品编号 from cpxsb where 销售日期2004-3-18)n 查询名称为“家电市场”的客户在2004年3月18日购买的产品名称和数量。用IN子查询：写出对应SQL语句并给出查
16、询结果：select 产品名称,数量from cpxsb,cpwhere cpxsb.客户编号 in (select 客户编号 from xss where 客户名称=家电市场 )and cpxsb.销售日期=2004-3-18and cpxsb.产品编号=cp.产品编号用EXISTS子查询：写出对应SQL语句并给出查询结果：select 产品名称,数量from cpxsb left join cp on(cpxsb.产品编号=cp.产品编号)where exists (select 产品名称 from cp where 销售日期=2004-3-18)and cpxsb.客户编号= (sele
17、ct 客户编号 from xss where 客户名称=家电市场)all(select 数量 from CPXSB where 数量 in (select 数量 from CPXSB where 销售日期=2004-03-18)用集函数：写出对应SQL语句并给出查询结果：select 产品编号from CPXSBgroup by 产品编号,数量having 数量(select max(数量) from CPXSB where 数量 in(select 数量 from CPXSB where 销售日期=2004-03-18) 查询购买了所有产品的客户的名称。写出对应SQL语句并给出查询结果：se
18、lect 客户名称from xsswhere not exists(select 产品编号 from cp where not exists (select 客户编号 from cpxsb where cp.产品编号=cpxsb.产品编号 and cpxsb.客户编号=xss.客户编号) 查询购买了客户编号为“000001”的客户购买的所有产品的客户的名称。写出对应SQL语句并给出查询结果： select 客户名称from xsswhere 客户编号 in( select distinct 客户编号 from cpxsb x where not exists(select 客户编号 from cpxsb y where y.客户编号=000001 and not exists( select 客户编号 from cpxsb z where z.客户编号=x.客户编号 and z.产品编号=y.产品编号) ) and 客户编号!=000001第 10 页 共 10 页