实验数据库综合查询.docx
《实验数据库综合查询.docx》由会员分享，可在线阅读，更多相关《实验数据库综合查询.docx（19页珍藏版）》请在冰豆网上搜索。
实验数据库综合查询
实验六:
数据库综合查询
一、实验目的
1.掌握SELECT语句的基本语法和查询条件表示方法;
2.掌握查询条件种类和表示方法；
3.掌握连接查询的表示及使用;
4.掌握嵌套查询的表示及使用；
5.了解集合查询的表示及使用。
二、实验环境
已安装SQLServer2005企业版的计算机（13台）；
具有局域网环境,有固定IP;
三、实验学时
2学时
四、实验要求
1.了解SELECT语句的基本语法格式和执行方法;
2.了解连接查询的表示及使用;
3.了解嵌套查询的表示及使用；
4.了解集合查询的表示及使用；
5.完成实验报告;
五、实验内容及步骤
以数据库原理实验5数据为基础，请使用T—SQL语句实现进行以下操作：
1.查询以‘DB_’开头，且倒数第3个字符为‘s’的课程的详细情况;
USEstudent
SELECT’课程号’=Cno，’课程名’=Cname,'先行课号’=Cpno，'学分'=Ccredit
FROMcourse
WHERE
SUBSTRING（Cname，1，3）=’DB_’AND
SUBSTRING（RIGHT（RTRIM（Cname），3）,1，1）=’s’
2.查询名字中第2个字为‘阳’的学生姓名和学号及选修的课程号、课程名；
USEstudent
SELECT'姓名'=student。
Sname,'学号'=student。
Sno,’课程号'=course.Cno，’课程名’=course。
Cname
FROMstudent，course,sc
WHERE
student.Sno=sc.SnoAND
course.Cno=sc.CnoAND
SUBSTRING（LTRIM（student.Sname）,2,1）=’阳’
3.列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩;
USEstudent
SELECT
’学号'=student.Sno，
’姓名'=student。
Sname,
'所在院系'=student.Sdept，
'课程号’=sc。
Cno,
’成绩’=sc.Grade
FROMstudent,course,sc
WHERE
student.Sno=sc.SnoAND
course.Cno=sc.CnoAND
（course。
Cname='数学'ORcourse。
Cname='大学英语’）
4.查询缺少成绩的所有学生的详细情况；
USEstudent
SELECTstudent。
＊
FROMstudent，sc
WHERE
student.Sno=sc.SnoAND
sc。
GradeISNULL
5.查询与‘张力’（假设姓名唯一）年龄不同的所有学生的信息；
USEstudent
SELECT＊
FROMstudent
WHERE
Sname！
='张力’AND
Sage！
=（SELECTSageFROMstudentWHERESname=’张力'）
6.查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成绩；
USEstudent
DECLARE@ZL_AVGINT
SET＠ZL_AVG=（
SELECTAVG（sc。
Grade）
FROMsc，student
WHEREsc。
Sno=student。
SnoANDstudent。
Sname=’张力'
GROUPBYsc.Sno,student。
Sno）
SELECTDISTINCT
’学号’=student.Sno,
’姓名'=student.Sname，
'平均成绩’=AVG（sc。
Grade）
FROMstudent,sc
WHEREsc。
Sno=student。
Sno
GROUPBYsc。
Sno,student.Sno，student。
Sname
HAVINGAVG（sc.Grade）〉@ZL_AVG
7.按照“学号，姓名,所在院系，已修学分"的顺序列出学生学分的获得情况.其中已修学分为考试已经及格的课程学分之和；
USEstudent
SELECTDISTINCT
'学号'=student.Sno,
’姓名’=student。
Sname，
’所在院系’=student。
Sdept，
’已修学分’=SUM（CASEWHENsc。
Grade>=60THENcourse.Ccredit＊1ELSE0END）
FROMstudent,sc，course
WHEREsc。
Sno=student.SnoANDsc。
Cno=course.Cno
GROUPBYstudent.Sno,student。
Sname,student。
Sdept
8.列出只选修一门课程的学生的学号、姓名、院系及成绩；
USEstudent
SELECT
'学号'=student。
Sno，
’姓名’=student。
Sname，
'院系’=student.Sdept,
’成绩'=sc。
Grade
FROMstudent，sc
WHEREstudent.Sno=sc.SnoAND
student。
Sno=ANY（SELECTSnoFROMscGROUPBYSnoHAVINGCOUNT（Cno）=1）
9.查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号;
USEstudent
SELECT
'学号'=student。
Sno，
’姓名'=student。
Sname，
’课程号’=sc.Cno
FROMstudent，sc
WHEREstudent。
Sno=sc。
SnoAND
student.Sname!
='张力’AND
sc。
Cno=ANY（SELECTsc。
CnoFROMsc，studentWHEREsc。
Sno=student。
SnoANDstudent。
Sname=’张力'）
10.只选修“数据库”和“数据结构”两门课程的学生的基本信息;
USEstudent
SELECT
'学号'=student.Sno,
'姓名'=student.Sname，
'学院’=student。
Sdept,
’选修课程'=course.Cname
FROMstudent，sc,course，courseb
WHEREstudent。
Sno=sc.SnoAND
course。
Cno=sc。
CnoAND
b.Cno=course.CnoAND（
（sc.Sno=ANY（SELECTSnoFROMscGROUPBYSnoHAVINGCOUNT（Cno）=1）AND
（course。
Cname='数据库'ORcourse.Cname=’数据结构’））
OR
（sc。
Sno=ANY（SELECTSnoFROMscGROUPBYSnoHAVINGCOUNT（Cno）=2）AND
b.Cname=’数据库'ANDcourse.Cname=’数据结构'））
11.至少选修“数据库”或“数据结构”课程的学生的基本信息；
USEstudent
SELECTDISTINCT
'学号’=student.Sno，
'姓名'=student。
Sname，
'学院’=student.Sdept
FROMstudent，sc
WHEREstudent.Sno=sc.SnoAND
sc。
Sno=ANY（
SELECTsc。
Sno
FROMcourse，sc
WHEREcourse.Cno=sc.CnoAND
（course。
Cname=’数据库’ORcourse。
Cname=’数据结构'））
12.列出所有课程被选修的详细情况,包括课程号、课程名、学号、姓名及成绩;
USEstudent
SELECT
’课程号’=sc.Cno，
'课程名'=course。
Cname,
’学号'=sc。
Sno,
'姓名'=student。
Sname，
’成绩’=sc.Grade
FROMsc，course,student
WHEREsc.Cno=course.CnoANDsc.Sno=student。
Sno
ORDERBYsc.CnoASC
13.查询只被一名学生选修的课程的课程号、课程名；
USEstudent
SELECT
’课程号’=sc。
Cno，
’课程名’=course.Cname
FROMsc，course
WHEREsc.Cno=course。
Cno
GROUPBYsc.Cno,course.Cname
HAVINGCOUNT（sc.Sno）=1
ORDERBYsc。
CnoASC
14.
检索所学课程包含学生‘张向东’所学课程的学生学号、姓名；
USEstudent
SELECTDISTINCT
'学号’=sc.Sno，
'姓名’=student。
Sname
FROMsc，student
WHEREsc.Sno=student.SnoAND
student。
Sname!
=’张向东’AND
sc.Cno=ANY（SELECTsc。
CnoFROMsc，student
WHEREsc。
Sno=student。
SnoANDstudent。
Sname=’张向东'）
15.使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；
USEstudent
SELECTDISTINCT
'学号’=sc.Sno，
'姓名'=student.Sname
FROMsc,student,course
WHEREsc.Sno=student。
SnoAND
sc.Cno=course.CnoAND
sc.Sno=ANY（SELECTsc.SnoFROMsc,courseWHEREsc。
Cno=course.CnoANDcourse.Cname='数据结构'）
16.使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；
USEstudent
SELECT
'姓名'=Sname,
’年龄’=Sage，
'院系’=Sdept
FROMstudent
WHERESdept!
=’CS’ANDSage〈ANY（SELECTTOP4SageFROMstudentWHERESdept=’CS'）
17.使用ANY、ALL查询，列出其他院系中比CS系所有学生年龄小的学生;
USEstudent
SELECT＊
FROMstudent
WHERESdept!
=’CS’ANDSage18.分别使用连接查询和嵌套查询,列出与‘张力’在一个院系的学生的信息;
连接：
USEstudent
SELECTa。
*
FROMstudenta,studentb
WHEREa.Sdept=b。
SdeptANDa。
Sname!
='张力'ANDb。
Sname=’张力'
嵌套：
USEstudent
SELECT＊
FROMstudent
WHERESdept=ALL（SELECTSdeptFROMstudentWHERESname=’张力'）ANDSname！
=’张力'
19.使用集合查询列出CS系的学生以及性别为女的学生名单；
USEstudent
SELECT＊
FROMstudent
WHERESdept='CS’
UNION
SELECT*
FROMstudent
WHERESsex='女'
ORDERBYSdept，SsexASC
20.使用集合查询列出CS系的学生与年龄不大于19岁的学生的交集、差集；
USEstudent
SELECT＊
FROMstudent
WHERESdept='CS'
EXCEPT
SELECT*
FROMstudent
WHERESage>19
ORDERBYSageASC
GO
SELECT＊
FROMstudent
WHERESdept='CS’
EXCEPT
SELECT*
FROMstudent
WHERESage!
>19
ORDERBYSageASC
21.使用集合查询列出选修课程1的学生集合与选修课程2的学生集合的交集；
USEstudent
SELECTstudent.*
FROMstudent，sc
WHEREsc.Sno=student.SnoANDsc。
Cno='1'
INTERSECT
SELECTstudent。
*
FROMstudent,sc
WHEREsc。
Sno=student。
SnoANDsc。
Cno=’2’
22.思考题：
按照课程名顺序显示各个学生选修的课程（如200515001数据库数据结构数学）；
一种笨拙的循环方法:
USEstudent
DECLARE@SNumberINT
DECLARE＠TmpSnoINT
SET@SNumber=0
WHILE＠SNumber<15
BEGIN
SET@TmpSno=（SELECTDISTINCTTOP（＠SNumber+1）Sno
FROMsc
EXCEPTSELECTDISTINCTTOP（@SNumber）Sno
FROMsc）
SELECT’学号’=Sno，'姓名'=Sname，
’第一门’=（SELECTTOP1course.Cname
FROMsc,course
WHEREsc。
Cno=course.CnoANDsc。
Sno=＠TmpSno）,
'第二门'=（SELECTTOP2course。
Cname
FROMsc,course
WHEREsc.Cno=course.CnoANDsc。
Sno=＠TmpSno
EXCEPTSELECTTOP1course。
Cname
FROMsc,course
WHEREsc.Cno=course.CnoANDsc。
Sno=＠TmpSno）,
’第三门'=（SELECTTOP3course。
Cname
FROMsc,course
WHEREsc.Cno=course.CnoANDsc。
Sno=@TmpSno
EXCEPTSELECTTOP2course.Cname
FROMsc，course
WHEREsc。
Cno=course.CnoANDsc.Sno=＠TmpSno）,
'第四门’=（SELECTTOP4course。
Cname
FROMsc,course
WHEREsc.Cno=course。
CnoANDsc。
Sno=＠TmpSno
EXCEPTSELECTTOP3course.Cname
FROMsc,course
WHEREsc.Cno=course.CnoANDsc。
Sno=＠TmpSno），
’第五门’=（SELECTTOP5course.Cname
FROMsc,course
WHEREsc。
Cno=course.CnoANDsc.Sno=＠TmpSno
EXCEPTSELECTTOP4course.Cname
FROMsc，course
WHEREsc。
Cno=course.CnoANDsc.Sno=@TmpSno）
FROMstudent
WHERESno=＠TmpSno
SET@SNumber=＠SNumber+1
END
六、出现问题及解决办法
如：
某些查询操作无法执行，如何解决？
升级会员 