还剩5页未读,继续阅读
文本内容:
《数据库原理》上机实验三实验名称表连接和SELECT的嵌套使用
一、实验目的
1.理解连接语句的操作和基本使用方法,掌握内连接、外连接、自身连接的概念和使用
2.掌握SELECT语句的嵌套使用,实现表的复杂查询,进一步理解SELECT语句的高级使用方法
二、实验内容与步骤连接查询1不同表之间的连接查询例
3.37查询每个学生及其选修课程的情况本查询实际上是涉及Students与Reports两个表的连接操作这两个表之间的联系是通过公共属性Sno实现的,因此,其操作命令为SELECTStudents.*Reports.*FROMStudentsReportsWHEREStudents.Sno=Reports.Sno;说明若在以上等值连接中把目标列中重复的属性列去掉则为自然连接XE自然连接,其命令为SELECTStudents.SnoSnameSsexSageSdeptCnoGradeFROMStudentsReportsWHEREStudents.Sno=Reports.Sno;例
3.38查询每个学生的学号Sno、姓名Sname、选修的课程名Cname及成绩Grade本查询涉及到三个表的连接操作,完成该查询的SQL语句如下SELECTStudents.SnoSnameCnameGradeFROMStudentsReportsCoursesWHEREStudents.Sno=Reports.SnoANDReports.Cno=Courses.Cno;2自身连接例
3.39查`询每一门课的间接先修课即先修课的先修课在Courses表关系中,只有每门课的直接先修课信息,而没有先修课的先修课要得到这个信息,必须先对一门课找到其先修课,再按此先修课的课程号,查找它的先修课程这就需要要将Courses表与其自身连接为方便连接运算,这里为Courses表取两个别名分别为A,B则完成该查询的SQL语句为SELECTA.CnoA.CnameB.Pre_CnoFROMCoursesACoursesBWHEREA.Pre_Cno=B.Cno;3外连接例
3.40把例
3.37中的等值连接改为左连接该左连接操作在SQLServer2000中的命令格式为SELECTStudents.SnoSnameSsexSdeptCnoGradeFROMStudentsLEFTJOINReportsONStudents.Sno=Reports.Sno;说明以上左连接操作也可以用如下的右连接操作代替,其结果完全一样SELECTStudents.SnoSnameSsexSdeptCnoGradeFROMReportsRIGHTJOINStudentsONReports.Sno=Students.Sno;嵌套查询1带谓词IN的嵌套查询例
3.41查询选修了编号为“C02”的课程的学生姓名Sname和所在系SdeptSELECTSnameSdeptFROMStudentsWHERESnoINSELECTSnoFROMReportsWHERECno=C02;例
3.42查询与“李伟”在同一个系学习的学生学号Sno、姓名Sname和系名Sdept该查询可构造嵌套查询实现,其SQL语句如下SELECTSnoSnameSdeptFROMStudentsWHERESdeptINSELECTSdeptFROMStudentsWHERESname=’李伟’;说明本例中的查询也可以用自身连接来完成,其SQL语句如下SELECTA.SnoA.SnameA.SdeptFROMStudentsAStudentsBWHEREA.Sdept=B.SdeptANDB.Sname=’李伟’;例
3.43查询选修了课程名为“数据结构”的学生学号Sno和姓名Sname本查询涉及学号、姓名和课程名Cname三个属性学号和姓名存放在Students表中,课程名的存放在Courses表中,但Students与Courses两个表之间没有公共属性,必须通过Reports表建立它们之间的联系所以本查询实际上涉及三个关系的连接操作SELECTSnoSname/*
③最后在Studenst关系中*/FROMStudents/*取出Sno和Sname*/WHERESnoINSELECTSno/*
②然后在SC关系中找出*/FROMReports/*选修了3号课程的学生学号*/WHERECnoINSELECTCno/*
①首先在Courses关系中*/FROMCourses/*找出“数据结构”的课程号,*/WHERECname=‘数据结构’;/*结果为C02号*/说明本查询同样可以用连接查询实现SELECTS.SnoSnameFROMStudentsSReportsRCoursesCWHERES.Sno=R.SnoANDR.Cno=C.CnoANDC.Cname=数据结构;2带有比较运算符的嵌套查询例
3.44将例
3.42改为带有比较运算符的嵌套查询由于一个学生只可能在一个系学习,因此子查询的结果是一个值,因此可以用=代替IN,其SQL语句如下SELECTSnoSnameSdeptFROMStudentsWHERESdept=SELECTSdeptFROMStudentsWHERESname=’李伟’;3带谓词ANY或ALL的嵌套查询例
3.45查询非自动化系的不超过自动化系所有学生的年龄的学生姓名Sname和年龄Sage其查询命令为SELECTSnameSageFROMStudentsWHERESdept自动化ANDSage=ALLSELECTSageFROMStudentsWHERESdept=自动化;说明本查询也可以用集函数来实现其SQL语句如下SELECTSnameSageFROMStudentsWHERESdept自动化ANDSage=SELECTMINSageFROMStudentsWHERESdept=自动化;4带谓词EXISTS的嵌套查询例
3.46查询所有选修了编号为“C01”课程的学生姓名Sname和所在系Sdept本查询的SQL语句是SELECTSnameSdeptFROMStudentsWHEREEXISTSSELECT*FROMReportsWHERESno=Students.SnoANDCno=C01;例
3.47将例
3.42改为带谓词EXISTS的查询,其SQL语句如下SELECTSnoSnameSdeptFROMStudentsAWHEREEXISTSSELECT*FROMStudentsBWHEREB.Sdept=A.SdeptANDB.Sname=’李伟’;例
3.48查询选修了所有课程的学生姓名Sname和所在系由于没有全称量词,可将题目的意思转换成等价的用存在量词的形式查询这样的学生,没有一门课程是他不选修的其SQL语句为SELECTSnameSdeptFROMStudentsWHERENOTEXISTSSELECT*FROMCoursesWHERENOTEXISTSSELECT*FROMReportsWHERESno=Students.SnoANDCno=Courses.Cno;集合查询例
3.49查询计算机科学系的学生或年龄不大于20岁的学生信息SELECT*FROMStudentsWHERESdept=计算机UNIONSELECT*FROMStudentsWHERESage=20;例
3.50查询数学系的学生且年龄不大于20岁的学生的交集,这实际上就是查询数学系中年龄不大于20岁的学生SELECT*FROMStudentsWHERESdept=数学ANDSage=20;例
3.51查询数学系的学生与年龄不大于20岁的学生的差集本查询的等价说法是,查询数学系中年龄大于20岁的学生SELECT*FROMStudentsWHERESdept=计算机ANDSage20;下列查询使用的基本表与上机实验一中Sale_Manage数据库中表相同
1.用T-SQL语句完成下列涉及表连接的查询操作
(1)查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址selectx.emp_namex.sexx.titlex.salaryx.addry.emp_namey.sexy.titley.salaryy.addrfromemployeeasxinnerjoinemployeeasyonx.emp_no!=y.emp_noandx.addr=y.addrandx.sex=女andx.dept=y.deptandy.sex=女
(2)检索product表和sale_item表中相同产品的产品编号、产品名称、数量、单价selectx.prod_idx.qtyx.unit_pricey.prod_namefromsale_itemasxinnerjoinproductasyonx.prod_id=y.prod_idorderbyx.prod_id
(3)检索product表和sale_item表中单价高于500元的相同产品的产品编号、产品名称、数量、单价selectx.prod_idx.qtyx.unit_pricey.prod_namefromsale_itemasxinnerjoinproductasyonx.prod_id=y.prod_idandunit_price500orderbyx.prod_id4)找出公司中姓名相同的员工,并且依据员工编号排序这些员工信息selectx.emp_nox.emp_namey.emp_noy.emp_namefromemployeeasxinnerjoinemployeeasyonx.emp_no!=y.emp_noandx.emp_name=y.emp_nameorderbyx.emp_nodesc
2.用T-SQL语句完成下列涉及SELECT语句嵌套使用的查询操作
(1)由sales表中查找出销售金额最高的订单select*fromsaleswheretot_amt=selectmaxtot_amtfromsales
(2)由sales表中查找出订单金额大于“E006业务员在2004/10/15这天所接任一张订单的金额”的所有订单,并显示承接这些订单的业务员和该条订单的金额selectsale_idtot_amtfromsaleswheretot_amtallselecttot_amtfromsaleswheresale_id=E006andorder_date=2004/10/15orderbytot_amt
(3)找出公司女业务员所接的订单selectemp_nofromemployeewheresex=女
(4)找出目前业绩未超过200000元的员工selectemp_noemp_namefromemployeewheresalary
2000003.使用数据库ST中的相关表完成查询
(1)检索选修课程名为“MS”的学生号和学生姓名(连接查询和嵌套查询两种方法)嵌套查询:selectSnoSnamefromswhereSnoinselectSnofromcwhereCname=MS连接查询:selectSnoSnamefromscwheres.Sno=C.CnoandCname=MS
(2)检索至少选修了课程号为c01和c02的学生学号selectx.Snofromscasxscasywherex.Cno=C01andy.Cno=C02andx.Sno=y.Sno
(3)检索选修了“操作系统”或“数据库”课程的学号和成绩(连接查询和嵌套查询两种方法)连接查询selectSnoGradefromsccwheresc.Sno=c.CnoandCname=操作系统orCname=数据库嵌套查询selectSnoGradefromscwhereCnoinselectCnofromcwhereCname=操作系统orCname=数据库
(4)检索选修了全部课程的学生姓名selectSnamefromswherenotexistsselect*fromcwherenotexistsselect*fromscwhereSno=s.SnoandCno=c.Cno
(5)检索至少选修了学生“S01”选修的全部课程的学生号码selectdistinctSnofromscasxwherenotexistsselect*fromscasywherey.Sno=S01andnotexistsselect*fromscaszwherez.Sno=x.Snoandz.Cno=y.Cno
(6)检索选修了三门课以上学生的姓名、年龄和所在系selectSnameSageSdeptfromswhereSnoinselectSnofromscgroupbySnohavingcount*3
三、实验报告按实验名称、实验目的、实验内容、实验结果的顺序在作业本上写出。