数据库原理中SQL语句实验指导书及答案.docx
- 文档编号:12095716
- 上传时间:2023-04-17
- 格式:DOCX
- 页数:29
- 大小:20.94KB
数据库原理中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 Sage select Sage from student where Sdept='CS' ) 17. 使用 ANY、ALL 查询,列出其他院系中比 CS 系所有学生年龄小的学生; select Sname,Sdept,Sage from student where Sdept! ='CS' and Sage select 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='女'
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 原理 SQL 语句 实验 指导书 答案