数据库原理实验任务书S6多表连接和嵌套查询.docx

1、数据库原理实验任务书S6多表连接和嵌套查询实验6 SQL多表连接和嵌套查询实验日期和时间：2014.10.20实验室：信工实验室班级：12计科3学号：20124122姓名：张翔实验环境：硬件：内存 4.00GB 处理器2.50Hz软件：Windows 7旗舰版Microsoft SQL Server 2005实验主要任务：多表连接和嵌套查询任务多表连接和嵌套查询在 “学生选课”数据库（实验2中建立的）中，用SQL语句完成下列查询任务。（数据自备）数据库ER图如下：下附创建库表的参考代码：-创建数据库create database 学生选课管理;-打开库use 学生选课管理;-逐个建表creat

2、e table 系(系编号 char(2) PRIMARY KEY,系名称 char(20)not nuLL unique,系主任 char(10);create table 学生(学号 char(10) primary key ,姓名 char(10) not nuLL,性别 char(2) default 男 check(性别=男 OR 性别=女),出生日期 datetime check(出生日期=getdate(),系编号 char(2),foreign key (系编号) references 系(系编号);create table 教师(教师号 char(10) primary ke

3、y ,姓名 char(10) not nuLL,性别 char(2) default 男 check(性别=男 OR 性别=女),出生日期 datetime check(出生日期0 and 学分=0 and 成绩=0),总学时 int default 36 check(总学时=0),primary key(教师号,课程号),foreign key (教师号) references 教师(教师号),foreign key (课程号) references 课程(课程号);以上命令运行后可建数据库和表，表中数据请根据题意自行设计填入，注意先填主表后填子表。1. 查询“会计系”和“金融系”的女生的信

4、息，列出：系名、学号、姓名、性别、出生日期，结果按系名、学号排列。代码：select 系名称,学号,姓名,性别,出生日期from 系,学生where 系.系编号=学生.系编号and 系名称in(金融系,会计系) and 性别=女order by 系名称,学号结果贴图：2. 查询所有学生的成绩详单，列出：学号、姓名、课程号、课程名、成绩、学分，结果按学号排序。代码： select 学生.学号,学生.姓名,课程.课程号,课程.课程名,选课.成绩,课程.学分from 学生,选课,课程where 学生.学号=选课.学号and 课程.课程号=选课.课程号order by 学生.学号结果贴图：3. 查询所

5、有2011级的不及格的学生成绩单，列出：学号、姓名、课程号、课程名、成绩，结果按学号排序。代码：select 学生.学号,学生.姓名,课程.课程号,课程.课程名,选课.成绩from 学生,选课,课程where 学生.学号like 2011% and 学生.学号=选课.学号and 课程.课程号=选课.课程号and 选课.成绩60order by 学生.学号结果贴图：4. 查询选修了“数据库原理”这门课的所有学生的学号、姓名、成绩，结果按成绩降序排列。代码：select 学生.学号,学生.姓名,选课.成绩from 学生,选课,课程where 学生.学号=选课.学号and 课程.课程号=选课.课程号

6、and 课程.课程名=数据库原理order by 选课.成绩desc结果贴图：5. 查询属于计算机系的但没有选修“数据库原理”的2009级学生的学号、姓名。代码：select 学生.学号from 系,学生where 系.系编号=学生.系编号and 系名称=计算机系 and 学生.学号like 2009% exceptselect 学生.学号from 选课,课程,学生where 课程.课程号=选课.课程号and 学生.学号=选课.学号and 课程名=数据库原理结果贴图：6. 查询学校中与老师同名的学生的学号、姓名。代码：select 学生.学号,学生.姓名from 学生,教师where 学生.姓

7、名=教师.姓名结果贴图：7. 查询所有以“高等数学”为先行课的课程信息，列出：课程号、课程名、学分、开课系（给出开课系的编号或系名称都行）。代码：select 课程.课程号,课程.课程名,课程.学分,系.系名称from 课程,系,选课where 课程.系编号=系.系编号and 选课.课程号=课程.课程号and 先行课= 04;结果贴图：8. 查询每个学生及其选修课程的情况，包括没有选修任何课程的学生，列出学号、姓名、课程号、课程名、成绩、学分，没有选修课程的学生只列出学号、姓名。（提示：用外连接）。 代码：select 学生.学号from 系,学生where 系.系编号=学生.系编号and 系

8、名称=计算机系 and 学生.学号like 2009% exceptselect 学生.学号from 选课,课程,学生where课程.课程号=选课.课程号and 学生.学号=选课.学号and 系名称=计算机系 and 课程名=数据库原理结果贴图：9. 统计所有学生的选课门数，但只列出选课少于4门的学生的学号、选课数量。代码：select 学号, count(*) 选课数量from 选课group by 学号having count(*)4结果贴图：10. 统计出每个同学的个人总分（其所选修的所有课程的成绩总和），列出其学号、总分。（在选课表中按学号分组求和）代码：select 学生.学号,su

9、m(成绩)from 学生,课程,选课where 选课.学号=学生.学号and 选课.课程号=课程.课程号group by 学生.学号结果贴图：11. 统计出各门课的平均分、最高分、最低分，列出课程号、平均分、最高分、最低分。代码：select 课程.课程号,avg(成绩),max(成绩)as最高分,min(成绩)as最低分from 课程,选课where 选课.课程号=课程.课程号group by 课程.课程号结果贴图：12. 统计各门课的选课人数，列出课程名称、选课人数。代码：select 课程.课程名,count(*)选课人数from 课程,选课where 选课.课程号=课程.课程号grou

10、p by 课程.课程名结果贴图：13. （选做）查询同时选修了“数据库原理”和“编译原理”的学生记录，列出学号、姓名。代码：select 学生.学号,姓名from 选课,学生,课程where 选课.课程号=课程.课程号and 选课.学号=学生.学号and 课程名=数据库原理INTERSECTselect 学生.学号,姓名from 选课,学生,课程where 选课.课程号=课程.课程号and 选课.学号=学生.学号and 课程名=编译原理结果贴图：14. （选做）查询其他系中比计算机系所有学生年龄都小的学生姓名及年龄。代码：select 姓名,year(get date()-year(出生日期)

11、 年龄from 学生,系where 学生.系编号=系.系编号and 系名称计算机系and year(get date()-year(出生日期)(select year(get date()-year(max(出生日期)from 学生,系where 学生.系编号=系.系编号and 系名称=计算机系)结果贴图：15. （选做）查询由“计算机系”的开课的课程信息（提示：即课程表中系编号是计算机系编号的那些课程），列出课程名称、选课人数。代码：select 课程.课程名,count(*)选课人数from 学生,系,课程where 学生.系编号=系.系编号and 课程.系编号=03group by 课程

12、名结果贴图：16. （选做）先查询“操作系统”这门课的平均分，再列出选修了该课程且成绩低于该平均分的所有学生的学生信息，列出：学号、姓名、成绩。代码：select 学生.学号,姓名,成绩from 选课,课程,学生where 选课.课程号=课程.课程号and 学生.学号=选课.学号and 课程名=操作系统 and 成绩(select avg(成绩)from 选课,课程where 选课.课程号=课程.课程号and 课程名=操作系统)结果贴图：总结：要求：1. 报告格式和内容要求：贴图时请剪裁到适当大小，要保证打印时可以看清，但也不要太大以免“越界”。不要在报告中写与实验无关的话，内容要有条理、完整、并能突出重点，要将遇到的主要问题说明。2. 文件格式要求： 将实验成果（包括：本实验报告、数据库、查询代码）放入一个文件夹中，文件夹以“学号姓名_S5”为文件名。其中，S3表示这是实验3。注意：你的学号放在姓名前，所有文件保存后关闭，然后再打包成RAR文件，以免提交的内容丢失或打不开。提交方式：可以将文件包发到我邮箱dah1225。3. 主动查阅资料，坚持自己亲手完成实验，弄清每个步骤和相关原理。