数据库实验——SQL查询综合.doc

1、数据库原理与应用课程实验1数据库原理与应用实验报告题目: SQL 查询综合学号:_姓名:_教师:_数据库原理与应用课程实验2实验题目SQL 查询综合1、 实验内容以数据库更新实验中的数据为基础，使用 SQL 语句实现以下操作：1、 列出所有不姓刘的所有的学生；2、 列出姓“沈”且全名为 3 个汉字的学生；3、 显示在 1985 年以后出生的学生的基本信息；（函数 Year(GETDATE()可取得时间的年份） ；4、 按照“性别、学号、姓名、院系”的顺序列出学生信息；5、 查询课程名含有“数据”字串的所有课程基本信息；6、 显示学号第八位和第九位是 9 的学生的学号、姓名、性别、年龄、院系；7

2、、 列出选修了1课程的学生，按成绩降序排列；8、 列出同时选修1号课程和2号课程的所有学生的学号；9、 列出课程表中全部信息，按先修课的升序排列；10、 列出年龄超过平均值的所有学生的名单，按年龄的降序显示；11、 按照出生年份升序显示所有学生的学号、性别、出生年份及院系，在结果集中列标题分别指定为“学号、姓名、性别、出生年份、院系” ；12、 按课程号、成绩降序显示课程成绩在 70-80 之间的学生的学号、课程号及成绩；13、 显示学生信息表中的学生总人数及平均年龄、在结果集中列报题分别指定为“学生总人数，平均年龄” ；14、 显示选修的课程数大于 3 的各个学生的选修课程数；15、 按课程

3、号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；16、 显示平均成绩大于200515001学生平均成绩的各个学生的学号、平均成绩；17、 列出有两门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；18、 查询以数据_开头，且倒数第 3 个字符为结的课程的详细情况；19、 查询名字中第 2 个字为阳的学生姓名和学生及选修的课程号、课程名；20、 列出选修了“数学”或者“大学英语“的学生学号、姓名、所在院系、选修课程号及成绩；21、 查询缺少成绩的所有学生的详细情况；22、 查询与“张力“（假设姓名唯一）年龄不同的所有的学生的信息；23、 查询所选课程的平均成绩大于张力的平均

4、成绩的学生学号、姓名及其平均成绩；24、 查询只选修一门课程的学生的学号、姓名、院系及成绩；25、 至少选修数据库和数据结构课程的学生的基本信息；26、 查询只被一名学生选修的课程的课程号、课程名；27、 选修了数据结构课程的学生学号和姓名；28、 其他系年龄小于 CS 系某个学生的学生姓名、年龄和院系；29、 列出和张力在一个院系的学生的信息。2、 实验目的与要求掌握 SQL 查询语句3、 实验环境数据库原理与应用课程实验3SQL Server 或 MySQL4、 实验结果与分析1、 列出所有不姓刘的所有的学生；select *from studentwhere Sname not like

5、 刘%2、 列出姓“沈”且全名为 3 个汉字的学生；select *from studentwhere Sname like 沈_3、 显示在 1985 年以后出生的学生的基本信息；（函数 Year(GETDATE()可取得时间的年份） ；select *from studentwhere (Year(GETDATE()-Sage)1985数据库原理与应用课程实验44、 按照“性别、学号、姓名、院系”的顺序列出学生信息；select Ssex 性别,Sno 学号,Sname 姓名,Sdept 院系from student数据库原理与应用课程实验55、 查询课程名含有“数据”字串的所有课程基本信

6、息；select *from coursewhere Cname like %数据%6、 显示学号第八位和第九位是 9 的学生的学号、姓名、性别、年龄、院系；select *from studentwhere Sno like _99数据库原理与应用课程实验67、 列出选修了1课程的学生，按成绩降序排列；select Sno,Gradefrom scwhere Cno=1order by Grade desc8、 列出同时选修1号课程和2号课程的所有学生的学号；select x.Snofrom sc x,sc ywhere x.Sno=y.Sno and x.Cno=1 and y.Cno=2

7、9、 列出课程表中全部信息，按先修课的升序排列；select *from courseorder by Cpno10、 列出年龄超过平均值的所有学生的名单，按年龄的降序显示；select *from studentwhere Sage(select avg(Sage)数据库原理与应用课程实验7from student)order by Sage desc11、 按照出生年份升序显示所有学生的学号、性别、出生年份及院系，在结果集中列标题分别指定为“学号、姓名、性别、出生年份、院系” ；select Sno 学号,Sname 姓名 ,Ssex 性别,2013- Sage 出生年份,Sdept 院系

8、from studentorder by 2013-Sage数据库原理与应用课程实验812、 按课程号、成绩降序显示课程成绩在 70-80 之间的学生的学号、课程号及成绩；select Sno,Cno,Gradefrom scwhere Grade between 70 and 80order by Cno desc,Grade desc13、 显示学生信息表中的学生总人数及平均年龄、在结果集中列报题分别指定为“学生总人数，平均年龄” ；select count(Sno) 学生总人数,avg (Sage) 平均年龄from student14、 显示选修的课程数大于 3 的各个学生的选修课程数

9、；select Sno,count(Sno)from scgroup by Snohaving count(Sno)315、 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；select Cno,count(Sno) 总人数,max (Grade) 最高成绩 ,min(Grade) 最低成绩,avg(Grade) 平均成绩from scwhere Sno is not NULL and Grade is not nullgroup by Cnoorder by cast(Cno as int) desc数据库原理与应用课程实验916、 显示平均成绩大于200515001学生

10、平均成绩的各个学生的学号、平均成绩；select Sno,avg(Grade) 平均成绩from scgroup by Snohaving avg(Grade)(select avg(Grade) from sc where Sno=200515001)17、 列出有两门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；select Sno,avg(Grade) 平均成绩from scgroup by Snohaving sum(case when Grade=218、 查询以数据_开头，且倒数第 3 个字符为结 的课程的详细情况；select *from coursewhere Cna

11、me like 数据_% 结_escape数据库原理与应用课程实验1019、 查询名字中第 2 个字为阳的学生姓名和学生及选修的课程号、课程名；select student.Sname,student.Sno,course.Cno,course.Cnamefrom student,course,scwhere student.Sno=sc.Sno and course.Cno=sc.Cno and student.Sname like _阳%20、 列出选修了“数学”或者“大学英语“的学生学号、姓名、所在院系、选修课程号及成绩；select student.Sno,student.Sname,

12、student.Sdept,Cno,sc.Gradefrom student,scwhere student.Sno=sc.Sno and Cno in(select Cno from course where Cname=数学 or Cname=大学英语 )21、 查询缺少成绩的所有学生的详细情况；select *from studentwhere Sno in(select Sno from sc where Grade is NULL)数据库原理与应用课程实验1122、 查询与“张力“（假设姓名唯一）年龄不同的所有的学生的信息；select *from studentwhere Sage

13、 not in(select Sage from student where Sname=张力)23、 查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及其平均成绩；select student.Sno,student.Sname,avg(sc.Grade) 平均成绩from student,scwhere student.Sno=sc.SnoGroup by student.Sno,student.Snamehaving(avg(Grade)(select avg(Grade) from sc,student where Sname=张力 and student.Sno=sc.Sno

14、)数据库原理与应用课程实验1224、 查询只选修一门课程的学生的学号、姓名、院系及成绩；select student.Sno,student.Sname,student.Sdept,sc.Gradefrom student,scwhere student.Sno=sc.Sno and sc.Sno in(select Sno from sc group by Sno having count(Sno)=1)25、 至少选修数据库和数据结构课程的学生的基本信息；select *from studentwhere Sno in(select Sno from sc where Cno in (se

15、lect Cno from course where Cname in (数据库 , 数据结构 )group by Sno having count(*) = 2)26、 查询只被一名学生选修的课程的课程号、课程名；select Cno,Cnamefrom coursewhere Cno in(select Cno from scgroup by Cno having count(*)=1)27、 选修了数据结构课程的学生学号和姓名；select Sno,Sname数据库原理与应用课程实验13from studentwhere Sno in(select Sno from sc where Cno in(select Cno from course where Cname=数据结构)28、 其他系年龄小于 CS 系某个学生的学生姓名、年龄和院系；select Sname,Sage,Sdeptfrom studentwhere Sageany(select Sage from student where Sdept=CS)and Sdept!=CS29、 列出和张力在一个院系的学生的信息。select *from studentwhere Sdept in(select Sdept from student where Sname=张力 )数据库原理与应用课程实验14