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. 主动查阅资料,坚持自己亲手完成实验,弄清每个步骤和相关原理。
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1