实验数据库综合查询.docx
- 文档编号:6054772
- 上传时间:2023-01-03
- 格式:DOCX
- 页数:16
- 大小:291KB
实验数据库综合查询.docx
《实验数据库综合查询.docx》由会员分享,可在线阅读,更多相关《实验数据库综合查询.docx(16页珍藏版)》请在冰豆网上搜索。
实验数据库综合查询
实验六:
数据库综合查询
一、实验目的
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 17.使用ANY、ALL查询,列出其他院系中比CS系所有学生年龄小的学生; USEstudent SELECT* FROMstudent WHERESdept! ='CS'ANDSage 18.分别使用连接查询和嵌套查询,列出与‘张力’在一个院系的学生的信息; 连接: 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 六、出现问题及解决办法 如: 某些查询操作无法执行,如何解决?
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 实验 数据库 综合 查询