数据库原理实验任务书S6多表连接和嵌套查询.docx
《数据库原理实验任务书S6多表连接和嵌套查询.docx》由会员分享,可在线阅读,更多相关《数据库原理实验任务书S6多表连接和嵌套查询.docx(18页珍藏版)》请在冰豆网上搜索。
数据库原理实验任务书S6多表连接和嵌套查询
实验6SQL多表连接和嵌套查询
实验日期和时间:
2014.10.20
实验室:
信工实验室
班级:
12计科3
学号:
20124122
姓名:
张翔
实验环境:
硬件:
内存4.00GB处理器2.50Hz
软件:
Windows7旗舰版MicrosoftSQLServer2005
实验主要任务:
多表连接和嵌套查询
任务多表连接和嵌套查询
在“学生选课”数据库(实验2中建立的)中,用SQL语句完成下列查询任务。
(数据自备)数据库ER图如下:
下附创建库表的参考代码:
--创建数据库
createdatabase学生选课管理;
--打开库
use学生选课管理;
--逐个建表
createtable系(
系编号char
(2)PRIMARYKEY,
系名称char(20)notnuLLunique,
系主任char(10));
createtable学生(
学号char(10)primarykey,
姓名char(10)notnuLL,
性别char
(2)default'男'check(性别='男'OR性别='女'),
出生日期datetimecheck(出生日期<=getdate()),
系编号char
(2),
foreignkey(系编号)references系(系编号)
);
createtable教师(
教师号char(10)primarykey,
姓名char(10)notnuLL,
性别char
(2)default'男'check(性别='男'OR性别='女'),
出生日期datetimecheck(出生日期<=getdate()),
职称char(10),
系编号char
(2),
foreignkey(系编号)references系(系编号)
);
createtable课程(
课程号char
(2)primarykey,
课程名char(20),
先行课char
(2),
学分intdefault2check(学分>0and学分<=10),
系编号char
(2),
foreignkey(系编号)references系(系编号),
foreignkey(先行课)references课程(课程号));
createtable选课(
学号char(10),
课程号char
(2),
成绩intdefault0check(成绩>=0and成绩<=100),
primarykey(学号,课程号),
foreignkey(学号)references学生(学号),
foreignkey(课程号)references课程(课程号)
);
createtable授课(
教师号char(10),
课程号char
(2),
教室char(10),
上课时间char(15),
选课人数intdefault0check(选课人数>=0),
总学时intdefault36check(总学时>=0),
primarykey(教师号,课程号),
foreignkey(教师号)references教师(教师号),
foreignkey(课程号)references课程(课程号)
);
以上命令运行后可建数据库和表,表中数据请根据题意自行设计填入,注意先填主表后填子表。
1.查询“会计系”和“金融系”的女生的信息,列出:
系名、学号、姓名、性别、出生日期,结果按系名、学号排列。
代码:
select系名称,学号,姓名,性别,出生日期
from系,学生
where系.系编号=学生.系编号and系名称in('金融系','会计系')and性别='女'
orderby系名称,学号
结果贴图:
2.查询所有学生的成绩详单,列出:
学号、姓名、课程号、课程名、成绩、学分,结果按学号排序。
代码:
select学生.学号,学生.姓名,课程.课程号,课程.课程名,选课.成绩,课程.学分
from学生,选课,课程
where学生.学号=选课.学号and课程.课程号=选课.课程号
orderby学生.学号
结果贴图:
3.查询所有2011级的不及格的学生成绩单,列出:
学号、姓名、课程号、课程名、成绩,结果按学号排序。
代码:
select学生.学号,学生.姓名,课程.课程号,课程.课程名,选课.成绩
from学生,选课,课程
where学生.学号like'2011%'and学生.学号=选课.学号and课程.课程号=选课.课程号and选课.成绩<60
orderby学生.学号
结果贴图:
4.查询选修了“数据库原理”这门课的所有学生的学号、姓名、成绩,结果按成绩降序排列。
代码:
select学生.学号,学生.姓名,选课.成绩
from学生,选课,课程
where学生.学号=选课.学号and课程.课程号=选课.课程号and课程.课程名='数据库原理'
orderby选课.成绩desc
结果贴图:
5.查询属于计算机系的但没有选修“数据库原理”的2009级学生的学号、姓名。
代码:
select学生.学号
from系,学生
where系.系编号=学生.系编号
and系名称='计算机系'and学生.学号like'2009%'
except
select学生.学号
from选课,课程,学生
where课程.课程号=选课.课程号and学生.学号=选课.学号and课程名='数据库原理'
结果贴图:
6.查询学校中与老师同名的学生的学号、姓名。
代码:
select学生.学号,学生.姓名
from学生,教师
where学生.姓名=教师.姓名
结果贴图:
7.查询所有以“高等数学”为先行课的课程信息,列出:
课程号、课程名、学分、开课系(给出开课系的编号或系名称都行)。
代码:
select课程.课程号,课程.课程名,课程.学分,系.系名称
from课程,系,选课
where课程.系编号=系.系编号and选课.课程号=课程.课程号and先行课=04;
结果贴图:
8.查询每个学生及其选修课程的情况,包括没有选修任何课程的学生,列出学号、姓名、课程号、课程名、成绩、学分,没有选修课程的学生只列出学号、姓名。
(提示:
用外连接)。
代码:
select学生.学号
from系,学生
where系.系编号=学生.系编号
and系名称='计算机系'and学生.学号like'2009%'
except
select学生.学号
from选课,课程,学生
where课程.课程号=选课.课程号and学生.学号=选课.学号
and系名称='计算机系'and课程名='数据库原理'
结果贴图:
9.统计所有学生的选课门数,但只列出选课少于4门的学生的学号、选课数量。
代码:
select学号,count(*)选课数量
from选课
groupby学号
havingcount(*)<4
结果贴图:
10.统计出每个同学的个人总分(其所选修的所有课程的成绩总和),列出其学号、总分。
(在选课表中按学号分组求和)
代码:
select学生.学号,sum(成绩)
from学生,课程,选课
where选课.学号=学生.学号and选课.课程号=课程.课程号
groupby学生.学号
结果贴图:
11.统计出各门课的平均分、最高分、最低分,列出课程号、平均分、最高分、最低分。
代码:
select课程.课程号,avg(成绩),max(成绩)as最高分,min(成绩)as最低分
from课程,选课
where选课.课程号=课程.课程号
groupby课程.课程号
结果贴图:
12.统计各门课的选课人数,列出课程名称、选课人数。
代码:
select课程.课程名,count(*)选课人数
from课程,选课
where选课.课程号=课程.课程号
groupby课程.课程名
结果贴图:
13.(选做)查询同时选修了“数据库原理”和“编译原理”的学生记录,列出学号、姓名。
代码:
select学生.学号,姓名
from选课,学生,课程
where选课.课程号=课程.课程号and选课.学号=学生.学号and课程名='数据库原理'
INTERSECT
select学生.学号,姓名
from选课,学生,课程
where选课.课程号=课程.课程号and选课.学号=学生.学号and课程名='编译原理'
结果贴图:
14.(选做)查询其他系中比计算机系所有学生年龄都小的学生姓名及年龄。
代码:
select姓名,year(getdate())-year(出生日期)年龄
from学生,系
where学生.系编号=系.系编号and系名称<>'计算机系'andyear(getdate())-year(出生日期)<
(selectyear(getdate())-year(max(出生日期))
from学生,系
where学生.系编号=系.系编号and系名称='计算机系')
结果贴图:
15.(选做)查询由“计算机系”的开课的课程信息(提示:
即课程表中系编号是计算机系编号的那些课程),列出课程名称、选课人数。
代码:
select课程.课程名,count(*)选课人数
from学生,系,课程
where学生.系编号=系.系编号and课程.系编号=03
groupby课程名
结果贴图:
16.(选做)先查询“操作系统”这门课的平均分,再列出选修了该课程且成绩低于该平均分的所有学生的学生信息,列出:
学号、姓名、成绩。
代码:
select学生.学号,姓名,成绩
from选课,课程,学生
where选课.课程号=课程.课程号and学生.学号=选课.学号and课程名='操作系统'
and成绩<
(selectavg(成绩)
from选课,课程
where选课.课程号=课程.课程号and课程名='操作系统')
结果贴图:
总结:
要求:
1.报告格式和内容要求:
贴图时请剪裁到适当大小,要保证打印时可以看清,但也不要太大以免“越界”。
不要在报告中写与实验无关的话,内容要有条理、完整、并能突出重点,要将遇到的主要问题说明。
2.文件格式要求:
将实验成果(包括:
本实验报告、数据库、查询代码)放入一个文件夹中,文件夹以“学号姓名_S5”为文件名。
其中,S3表示这是实验3。
注意:
你的学号放在姓名前,所有文件保存后关闭,然后再打包成RAR文件,以免提交的内容丢失或打不开。
提交方式:
可以将文件包发到我邮箱dah1225@。
3.主动查阅资料,坚持自己亲手完成实验,弄清每个步骤和相关原理。