数据库原理中SQL语句实验指导书及答案.docx
《数据库原理中SQL语句实验指导书及答案.docx》由会员分享，可在线阅读，更多相关《数据库原理中SQL语句实验指导书及答案.docx（29页珍藏版）》请在冰豆网上搜索。
数据库原理中SQL语句实验指导书及答案
实验四：
管理 SQL Server 表数据
一、实验目的
熟悉数据表结构及使用特点；
熟悉使用 Management Stuio 界面方式管理数据表数据；
熟悉使用 T-SQL 语句管理数据表数据。
二、实验环境
已安装 SQL Server 2005 企业版的计算机（13 台）；
具有局域网环境,有固定 IP;
三、实验学时
2 学时
四、实验要求
了解 SQL Server 数据表数据的管理方法；
了解 SQL Server 数据类型；
完成实验报告（部分题只需给出关键语句）。
五、实验内容及步骤
以课本指定的数据库为例，并依据数据表的结构创建相对应的数据表（student、
course、sc），请分别使用 Management Stuio 界面方式及 T-SQL 语句实现进行
以下操作：
向各个数据表中插入如下记录：
学生信息表（student）
Sno
1
2
3
4
5
6
7
8
9
0
1
2
3
Sname
赵菁菁
李勇
张力
张衡
张向东
张向丽
王芳
王民生
王小民
李晨
张毅
杨磊
李晨
Ssex
女
男
男
男
男
女
女
男
女
女
男
女
女
Sage Sdept
23 CS
20 CS
19 CS
18 IS
20 IS
20 IS
20 CS
25 MA
18 MA
22 MA
20 WM
20 EN
19 MA
4
5
6
7
8
9
0
1
2
3
4
5
张丰毅
李蕾
刘社
刘星耀
李贵
林自许
马翔
刘峰
牛站强
李婷婷
严丽
朱小鸥
男
女
男
男
男
男
男
男
男
女
女
女
22 CS
21 EN
21 CM
18 CM
19 EN
20 WM
21
25 CS
22
18
20
30 WM
课程信息表（course）
Cno
Cname
Cpno
Ccredit
1
2
3
4
5
6
7
8
9
10
数据库
数学
信息系统
操作系统
数据结构
数据处理
PASCAL 语言
大学英语
计算机网络
人工智能
5
1
6
7
6
4
2
4
3
4
2
4
4
4
2
选课信息表（sc）
Sno
Cno
Grade
1
2
2
3
4
5
6
4
1
1
3
1
1
1
1
2
75
85
53
86
74
58
84
46
5
6
8
9
0
0
1
5
8
1
2
1
1
1
1
5
6
7
2
2
2
2
2
8
8
8
8
4
4
9
5
6
7
10
8
8
89
65
72
76
96
86
62
0
58
62
85
54
58
58
70
65
Null
Null
insert intovalues（'','1',75）
insert intovalues（'1','数据库','5',4）
insert intovalues（'1','赵菁菁','女',23,'CS'）
2.修改 CS 系姓名为“李勇”的学生姓名为“李咏”；
update student
set Sname='李咏'
where Sname='李勇'
3.修改课程“数据处理”的学分为 3 学分；
course
set Ccredit=3
where Cname ='数据处理'
4.将选修课程“1”的同学成绩加 5 分；
sc
set Grade =Grade+5
where Cno='1'
5.将选修课程“大学英语”的同学成绩加 5 分；
update sc
set Grade=Grade+5
from course,sc
where = and ='大学英语'
6.将学号为“0”的学生信息重新设置为“王丹丹、女、20、MA”；
update student
set Sname='王丹丹',Ssex='女',Sage=20,Sdept='MA'
where Sno='0'
7.修改借书证号为 2005001 的学生记录重新设置：
名字为王婧婧、专业为信息管
理、借书量增加 5 本；（因为无些相关的数据表帮无法实现）
8.删除数据表 student 中无专业的学生记录；
delete
from student
where Sdept is null
9.删除数据表 student 中计算机系年龄大于 25 的男同学的记录；
delete
from student
where Ssex='男' and Sage>25 and Sdept='CS'
10.删除数据表 course 中学分低于 1 学分的课程信息；
delete
from course
where Ccredit<1
实验五：
数据库单表查询
一、实验目的
1. 掌握 SELECT 语句的基本语法和查询条件表示方法；
2. 掌握查询条件表达式和使用方法；
3. 掌握 GROUP BY 子句的作用和使用方法；
4. 掌握 HAVING 子句的作用和使用方法；
5. 掌握 ORDER BY 子句的作用和使用方法。
二、实验环境
已安装 SQL Server 2005 企业版的计算机（13 台）；
具有局域网环境,有固定 IP;
三、实验学时
2 学时
四、实验要求
1. 了解数据库查询；
2. 了解数据库查询的实现方式；
3. 完成实验报告；
五、实验内容及步骤
以数据库原理实验 4 数据库中数据为基础，请使用 T-SQL 语句实现以下操作：
1. 列出所有不姓刘的所有学生；
*
from student
where Sname not like '刘%'
2. 列出姓“沈”且全名为 3 个汉字的学生；
select *
from student1
where Sname like'沈__'
3. 显示在 1985 年以后出生的学生的基本信息；
select *
from student
where YEAR（GETDATE（））-Sage>1985
4. 按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按
以下规定显示：
性别为男显示为男 生，性别为女显示为女 生，其他显示为
“条件不明”；
select 性别=
case
when Ssex='男' then'男生'
whenSsex='女' then'女生'
else '条件不明'
end,Sno 学号,Sname 码,Sage 年龄,Sdept 院系
from student
5. 查询出课程名含有“数据”字串的所有课程基本信息；
select *
from course
where Cname like '%数据%'
7.显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、
年龄及院系； Sno,Sname,Ssex,Sage,Sdept
from student
where Sno like '_______[1,2,3,4,9][1,2,3,4,9]%'
8.列出选修了‘1’课程的学生，按成绩的降序排列；
select student.*,sc.*
from student,sc
where=and ='1'
order by Grade DESC
9.列出同时选修“1”号课程和“2”号课程的所有学生的学号；
select Sno
from sc
where Cno='1' and Sno in（
select Sno
from sc
where Cno='2'
10.列出课程表中全部信息，按先修课的升序排列；
select *
from course
order by Cpno Asc
11.列出年龄超过平均值的所有学生名单，按年龄的降序显示；
select *
from student
where Sage>
（
select AVG（Sage）
from student
）
order by Sage DESC
12.按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在
结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
select Sno 学号,Sname 姓名,Ssex 性别,YEAR（GETDATE （））-Sage 出生年份,Sdept 所在院
系
from student
order by YEAR（GETDATE （））-Sage
13.按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，
其中院系按照以下规定显示：
院系为 CS 显示为计算机系，院系为 IS 显示为信息
系，院系为 MA 显示为数学系，院系为 EN 显示为外语系，院系为 CM 显示为中医
系，院系为 WM 显示为西医系，其他显示为院系不明；
select Sdept=
case
when Sdept='CS' then '计算机系'
when Sdept='IS' then '信息系'
when Sdept='MA' then '数学系'
when Sdept='EN' then '外语系'
when Sdept='CM' then '中医系'
when Sdept='WM' then '西医系'
else '条件不明'
end ,Sno,Sname,Ssex,Sage
from student
order by Sdept DESC
14.显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院
系规模”，其中若该院系人数>=5 则该字段值为“规模很大”，若该院系人数大于
等于 4 小于 5 则该字段值为“规模一般”， 若该院系人数大于等于 2 小于 4 则该
字段值为“规模稍小”，否则显示“规模很小”；
select Sdept ,院系规模=
case
when COUNT（Sno）>=5 then'规模很大'
when COUNT（Sno）>=4then'规模一般'
when COUNT（Sno）>=2then'规模稍小'
else '规模很小'
end
from student
where Sdept is not Null
group by Sdept
15.按照课程号、成绩降序显示课程成绩在 70-80 之间的学生的学号、课程号及
成绩；
select Sno,Cno,Grade
from sc
where Grade between 70 and 80
order by Cno,Grade DESC
16.显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为
“学生总人数，平均年龄”；
select count（*）学生总人数,AVG（Sage） 平均年龄
from student
17.显示选修的课程数大于 3 的各个学生的选修课程数；
select Sno 学号,COUNT（Sno）选修课程数
from sc
group by Sno
having COUNT（*）>=3
18.按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
use student
select Cno ' 课 程 号 ',COUNT（*）' 总 人 数 ',MAX（Grade）' 最 高 分 ',MIN（Grade）' 最 低 分
',AVG（Grade）'平均分'
from sc
group by Cno
order by Cnodesc
19.显示平均成绩大于“1”学生平均成绩的各个学生的学号、平均成绩；
use student
select Sno '学号',AVG（Grade）'平均成绩'
from sc
group by Sno
having AVG（Grade）>（
select AVG（Grade）
from sc
where Sno='1'
）
20.显示选修各个课程的及格的人数、及格比率；
use student
select Cno'课程号' ,COUNT（*）'及格人数',cast（cast（COUNT（case when Grade>=60 then 1
end）as float）/COUNT（*）AS float
（1））'及格率'
from sc
group by Cno
21.显示选修课程数最多的学号及选修课程数最少的学号；
use student
select Sno '学号',COUNT（*）'选修课程数'
from sc
group by Sno
having COUNT（Cno）>=all
（ select COUNT（*）
from sc
group by Sno
）
union
select Sno '学号',COUNT（*）'选修课程数'
from sc
group by Sno
having COUNT（Cno）<=all
（ select COUNT（*）
from sc
group by Sno
）
22.显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、
男生人数、女生人数”；
select Sdept ,COUNT（case when Ssex='女'then 1 end）'女生人数',
COUNT（case when Ssex='男'then 1 end）'男生人数'
from student
group by Sdept,Ssex
23.列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
select Sno 学号,AVG（Grade）平均成绩
from sc
group by Sno
having COUNT（case when Grade<60 then 1 end）>=2
实验六：
数据库综合查询
一、实验目的
1. 掌握 SELECT 语句的基本语法和查询条件表示方法；
2. 掌握查询条件种类和表示方法；
3. 掌握连接查询的表示及使用；
4. 掌握嵌套查询的表示及使用；
5. 了解集合查询的表示及使用。
二、实验环境
已安装 SQL Server 2005 企业版的计算机（13 台）；
具有局域网环境,有固定 IP;
三、实验学时
2 学时
四、实验要求
1. 了解 SELECT 语句的基本语法格式和执行方法；
2. 了解连接查询的表示及使用；
3. 了解嵌套查询的表示及使用；
4. 了解集合查询的表示及使用；
5. 完成实验报告；
五、实验内容及步骤
以数据库原理实验 5 数据为基础，请使用 T-SQL 语句实现进行以下操作：
1. 查询以‘数据_’开头,且倒数第 3 个字符为‘结’的课程的详细情况；
select *
from sc
where Cno in（
select Cno
from course
where Cname like '数据\_%'ESCAPE'\'
）
2. 查询名字中第 2 个字为‘阳’的学生姓名和学号及选修的课程号、课程名；
select Sname, ,,Cname
from sc,course,student
where = and = and Sname like'_阳'
3. 列出选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修
课程号及成绩；
select ,Sname,Sdept,,Grade
from student,sc,course
where = and = and Cname in（'数学','大学英语'）
4. 查询缺少成绩的所有学生的详细情况;
Select student.*
from student,sc
Where = and Grade is null
5. 查询与‘张力’（假设姓名唯一）年龄不同的所有学生的信息；
from student
where Sage !
=（
select Sage
from student
where Sname='张力'
）
6. 查询所选课程的平均成绩大于张力的平均成绩的学生学号、姓名及平均成
绩；
select ,Sname,AVG（Grade）
from student,sc
where =
group by ,Sname
having AVG（Grade）>（
select AVG（Grade）
from sc
where Sno=（
select Sno
from student
where Sname='张力'
）
）
7. 按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。
其中已修学分为考试已经及格的课程学分之和；
select ,Sname,Sdept,SUM（Ccredit） 总学分
from student,sc,course
where = and = and Grade>=60
group by ,Sname,Sdept
8. 列出只选修一门课程的学生的学号、姓名、院系及成绩；
select ,Sname,Sdept,sum（Grade） 成绩
from student,sc
where =
group by ,Sname,Sdept
having COUNT（*）=1
9. 查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；
select ,Sname,Cno
from student,sc
where = andin（
select
from student,sc
where = and Cno in（
select Cno
from sc
where Sno in （
select Sno
from student
where Sname='张力'
）
）
）
10. 只选修“数据库”和“数据结构”两门课程的学生的基本信息；
select ,Sname
from student,sc,course
where = and = and Cname='数据库' andin （
select
from sc,course
where = and Cname='数据结构'
）andnot in（
select Sno
from sc,course
where = and Cname not in（'数据库','数据结构'）
）
11. 至少选修“数据库”或“数据结构”课程的学生的基本信息；
select ,Sname
from sc,student,course
where = and = and Cname='数据库' andin（
select Sno
from sc
where Cno=（
select Cno
from course
where Cname='数据结构'
）
）
12. 列出所有课程被选修的详细情况，包括课程号、课程名、学号、姓名及成绩；
select ,Cname, ,Sname,Grade
from course left outer join sc on= ）,student
where =
13. 查询只被一名学生选修的课程的课程号、课程名；
select ,Cname
from sc,course
where =
group by ,Cname
having COUNT（*）=1
14. 检索所学课程包含学生‘张向东’所学课程的学生学号、姓名；
select Sno,Sname
from student
where exists（
select *
from sc
where Sno=（
select Sno
from student
where Sname='张向东'
）
and exists （
select *
from sc y
where = and =
）
）
15. 使用嵌套查询列出选修了“数据结构”课程的学生学号和姓名；
select Sno,Sname
from student
where Sno in（
select Sno
from sc
where Cno=（
select Cno
from course
where Cname='数据结构'
）
）
16. 使用嵌套查询查询其它系中年龄小于 CS 系的某个学生的学生姓名、年龄和
院系；
select Sname,Sdept,Sage
from student
where Sdept!
='CS' and Sageselect Sage
from student
where Sdept='CS'
）
17. 使用 ANY、ALL 查询，列出其他院系中比 CS 系所有学生年龄小的学生；
select Sname,Sdept,Sage
from student
where Sdept!
='CS' and Sageselect Sage
from student
where Sdept='CS'
）
18. 分别使用连接查询和嵌套查询，列出与‘张力’在一个院系的学生的信息；
select *
from student
where Sdept=（
select Sdept
from student
where Sname='张力'
）
19. 使用集合查询列出 CS 系的学生以及性别为女的学生名单；
select *
from student
where Sdept='CS'
union
select *
from student
where Ssex='女'
20. 使用集合查询列出 CS 系的学生与年龄不大于 19 岁的学生的交集、差集；
select student.*
from student,sc
where = and Cno='1'
intersect
select student.*
from student,sc
where = and Cno='2'
21. 使用集合查询列出选修课程 1 的学生集合与选修课程 2 的学生集合的交集；
select *
from student
where Sdept='CS'
union
select *
from student
where Ssex='女'
升级会员 