数据库原理实验任务书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.主动查阅资料，坚持自己亲手完成实验，弄清每个步骤和相关原理。
升级会员 