2021年数据库实验3答案
实验三：交互式SQL语句的使用
欧阳光明（2021.03.07）
1、实验目的
（1）掌握数据库对象的操作过程，包括创建、修改、删除（2）熟悉表的各种操作，包括插入、修改、删除、查询
（3）熟练掌握常用SQL语句的基本语法
2、实验平台
使用SQL Server提供的Microsoft SQL Server Management Studio工具，交互式使用SQL语句。
3 实验内容及要求
选择如下一个应用背景之一：
●学生选课系统
●习题3、4、和5中使用的数据库
●其它你熟悉的应用
（1）建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。
（2）要求认真进行实验，记录各实验用例及执行结果。
（3）深入了解各个操作的功能。
实验要求包括如下方面的内容：3.1 数据定义
1．基本表的创建、修改及删除 2．索引的创建
3．视图的创建
3.2 数据操作
完成各类更新操作包括：
1．插入数据
2．修改数据
3. 删除数据
3.3 数据查询操作
完成各类查询操作
1．单表查询
2．分组统计
3. 连接查询
4. 嵌套查询
5. 集合查询
3.4 数据操作
1．创建视图
2．视图查询
参考示例：
建立一个学生选课数据库，练习对表、视图和索引等数据库对象的各种操作。
一、数据定义
创建学生选课数据库ST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：
表1. Student表结构
表2. Course表结构
表3. SC表结构
1．创建、修改及删除基本表
（1）创建Student表
CREATETABLE Student
(Sno CHAR(8)PRIMARYKEY,
Sname CHAR(8),
Ssex CHAR(2)NOTNULL,
Sage INT,
Sdept CHAR(20)
);
（2）创建Course表
CREATETABLE Course
(Cno CHAR(4)PRIMARYKEY,
Cname CHAR(40)NOTNULL,
Cpno CHAR(4),
Ccredit SMALLINT,
);
（3）创建SC表
CREATETABLE SC
(Sno CHAR(8)FOREIGNKEY (Sno)REFERENCES Student(Sno), Cno CHAR(4),
Grade SMALLINT,
);
（4）创建员工表Employee
CREATETABLE Employee
(
编号CHAR(8)PRIMARYKEY,
姓名VARCHAR(8)notnull
部门CHR（40）,
工资numeric(8,2),
生日datetime，
职称char(20),
);
指出该语句中的错误并改正后执行。
（5）检查表是否创建成功
SELECT*FROM Student
SELECT*FROM Course
SELECT*FROM SC
SELECT*FROM Employee
（6）修改表结构及约束
●增加班级列
ALTERTABLE Student ADD Sclass char(4)
●修改年龄列
ALTERTABLE Student ALTERCOLUMN Sage smallint
●增加约束
ALTERTABLE Course ADDUNIQUE(Cname)
（7）删除表
DROPTABLE Employee
2．创建索引
（1）为Course表按课程名称创建索引
CREATEINDEX iCname On Course(Cname)
（2）为Student表按学生姓名创建唯一索引
CREATEUNIQUEINDEX iSname ON Student(Sname)（3）为SC表按学号和课程号创建聚集索引
CREATECLUSTEREDINDEX iSnoCno On SC(Sno,Cno desc)（4）为Course表按课程号创建唯一索引
请自己完成该操作
3．创建视图
建立信息系学生的视图：
CREATEVIEW IS_Student
AS
SELECT Sno,Sname,Sage FROM Student
WHERE Sdept='IS';
3.2 数据操作
1．插入数据
将如下表格中的数据分别插入到数据库相应的表中：
表4.学生基本信息表
表5.课程信息表
表6.学生选课信息表
（1）插入到Student表
INSERTINTO Student VALUES('20100001','李勇','男',20,'CS','1001')
INSERTINTO Student VALUES('20100002','刘晨','女
',19,'CS','1001')
INSERTINTO Student(Sno,Sname,Ssex,Sage,Sdept,Sclass)VALUE S('20100021','王敏','女',18,'MA','1002')
INSERTINTO Student(Sno,Sname,Ssex,Sage,Sdept,sclass)VALUE S('20100031','张立','男',19,'IS','1003')
INSERTINTO Student(Sno,Sname,Ssex,sclass)VALUES('20100003 ','刘洋','女','1001')
检查下列语句中的错误，并改正：
INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept,sclass) VALUES('20100010',赵斌,'男','19','IS','1005')
INSERT INTO Student VALUES('20100022','张明明',19,'男
','CS','1002')
（2）插入到Course表
INSERTINTO Course(Cno,Cname,Cpno,Ccredit)VALUES('1','数据库系统原理','5',4)
INSERTINTO Course(Cno,Cname,Cpno,Ccredit)VALUES('2','高等数学',null,2)
INSERTINTO Course(Cno,Cname,Cpno,Ccredit)VALUES('3','管理信息系统','1',4)
请写出插入其余行的插入语句，并插入数据。
（3）插入到SC表
INSERTINTO SC VALUES('20100001','1',92)
INSERTINTO SC VALUES('20100002','2',80)
INSERTINTO SC(Sno,Cno)VALUES('20100003','1') INSERTINTO SC(Sno,Cno,Grade)VALUES('20100010','3',null)请写出插入其余行的插入语句，并运行。
（4）多行插入到表中
创建存一个表，保存学生的学号、姓名和年龄：CREATETABLE cs_Student
(
学号char(8),
姓名char(8),
年龄smallint
);
插入数据行：
INSERTINTO cs_Student
SELECT Sno,Sname,Sage
FROM student Where Sdept='CS';
（5）检查插入到表中的数据
SELECT*FROM Student
SELECT*FROM Course
SELECT*FROM SC
2．修改数据
（1）将学生20100001的年龄改为22岁。
UPDATE student SET Sage= 22 WHERE Sno='20100001';
（2）将所有学生的年龄增加一岁。
UPDATE Student SET Sage=Sage+1
（3）填写赵斌同学的管理信息系统课程的成绩
UPDATE SC SET Grade= 85
WHERE Sno='20100010'AND Cno='3'
（4）将计算机科学系全体学生的成绩加5分
UPDATE sc SET Grade=Grade+ 5
WHERE'CS'=(select Sdept from student where student.Sno=sc.Sno);
（5）请自己完成如下操作
●将刘晨同学的2号课程成绩修改为80
●将“20100021”同学的学号修改为“20100025”
（6）检查数据是否修改
3. 删除数据
（1）删除学号为201000022的学生记录
DELETEFROM Student WHERE Sno='20100022'
（2）删除学号20100001学生的1号课程选课记录
将选课信息复制到一个临时表tmpSC中：
SELECT*INTO tmpSC FROM SC
在tmpSC中执行删除操作：
DELETEFROM tmpSC WHERE Sno='20100001'and Cno='1'
（3）删除临时表中20100002学生的全部选课记录
请自己完成该操作。
（4）删除计算机科学系所有学生的选课记录
DELETEFROM tmpSC WHERE'CS'=(select Sdept from student where s tudent.Sno=tmpSC.Sno);
（5）删除全部选课记录
DELETEFROM tmpSC
（6）检查数据是否删除
3.3 数据查询操作
完成如下查询操作：
1．单表查询
（1）按指定目标列查询
●查询学生的详细记录：
SELECT*FROM Student;
●查询学生的学号、姓名和年龄
SELECT Sno,Sname,Sage FROM Student;
（2）目标列包含表达式的查询
●查询全体学生的姓名、出生年份和所有系，要求用小写字母
表示所有系名。
SELECT Sname,'Year of Birth: ',2004-
Sage,LOWER(Sdept)FROM Student;
（3）查询结果集中修改列名称
●查询全体学生的姓名、出生年份和所有系，要求用小写字母
表示所有系名。
SELECT Sname,'Year of Birth:'as BIRTH, 2000-SageBIRTHDAY,DEPARTMENT=LOWER(Sdept)
FROM Student;
（4）取消重复行
●查询选修了课程的学生学号：比较ALL和DISTINCT的区别SELECT Sno FROM SC;
SELECTDISTINCT Sno FROM SC;
（5）简单条件查询
●查询计算机科学系全体学生的名单
SELECT Sname FROM Student WHERE Sdept='CS';
（6）按范围查询
●查询年龄在20~23岁之间的学生的姓名、系别和年龄SELECT Sname,Sdept,Sage FROM Student WHERE Sage BETWEEN 20 AND 23
（7）查询属性值属于指定集合的行
●查询信息系（IS）、数学系（MA）和计算机科学系（CS）
学生的姓名和性别
SELECT Sname,Ssex FROM Student WHERE Sdept IN('IS','MA','CS');（8）模糊查询
●查询所有姓刘学生的姓名、学号和性别
SELECT Sname,Sno,Ssex FROM Student WHERE Sname LIKE'刘%'（9）查询空值
●查询缺少成绩的学生的学号和相应的课程号
SELECT Sno,Cno FROM sc WHERE Grade isnull;
（10）多重条件查询
●查询计算机科学系年龄在岁以下的学生姓名
SELECT Sname FROM student WHERE Sdept='CS'and Sage<20;（11）结果集排序
●查询全体学生情况，查询结果按所在系的系号升序排列，同
一系中的学生按年龄降序排列
SELECT*FROM Student ORDERBY Sdept,Sage DESC;
（12）完成下列查询
●查询学生基本信息，结果集属性名使用汉字
●查询信息系且年龄大于23岁同学的学号和姓名
●查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所
在系
●查询年龄不在21~24岁之间的学生的姓名、系别和年龄2．分组统计
（1）聚集函数的使用
●查询学生总人数
SELECT COUNT(*)FROM Student;
●查询选修了课程的学生人数
SELECT COUNT(DISTINCT Sno)FROM SC
●查询最高分
SELECT MAX(Grade)FROM SC
（2）聚集函数作用于部分行
●统计2号课程的总分、均分和最高分
SELECT SUM(grade)总分,AVG(grade)均分,MAX(grade)最高分FROM sc WHERE Cno='2'
（3）分组统计
●统计各门课程的选课人数、均分和最高分
select cno课程号,count(*)人数,AVG(grade)均分,MAX(grade)最高分
from sc groupby Cno
●统计均分大于90的课程
select cno课程号,count(*)人数,AVG(grade)均分,MAX(grade)最高分
from sc groupby Cno
having AVG(grade)> 90
（4）完成下面的查询
●统计每个同学的学号、选课数、平均成绩和最高成绩
●统计每个班的每门课的选课人数、平均成绩和最高成绩
3. 连接查询
（1）在WHERE中指定连接条件
●查询每个参加选课的学生信息及其选修课程的情况
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student,SC
WHERE Student.Sno=SC.Sno
●查询每一门课的间接先修课
SELECT*FROM course first,course second
WHEREfirst.Cpno=https://www.360docs.net/doc/956302987.html,o;
https://www.360docs.net/doc/956302987.html,o,second.Cpno FROM course first,course second WHEREfirst.Cpno=https://www.360docs.net/doc/956302987.html,o;
（2）在FROM中指定连接条件
●查询每个参加选课的学生信息及其选修课程的情况
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student JOIN SC ON (Student.Sno=SC.Sno)
（3）使用外连接查询
●查询每个学生信息及其选修课程的情况
SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade FROM Student LEFTOUTERJOIN SC ON (Student.Sno=SC.Sno)（4）复合条件连接查询
●查询选修号课程且成绩在分以上的所有学生
SELECT Student.Sno,Sname
FROM Student join SC ON (Student.Sno=SC.Sno)/* 连接条件*/
WHERE https://www.360docs.net/doc/956302987.html,o='2'AND SC.Grade> 90;/* 过滤条件*/
（5）多表查询
●查询每个学生的学号、姓名、选修的课程名及成绩SELECT Student.Sno,Sname,Cname,Grade
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND https://www.360docs.net/doc/956302987.html,o=https://www.360docs.net/doc/956302987.html,o;
（6）完成下列查询
●查询选修了2号课程的同学的学号和姓名
●查询各门课程的课程号、课程名称以及选课学生的学号
●查询选修了数据库系统原理课程的同学的学号和姓名和成绩
4. 嵌套查询
（1）由In引出的子查询
●查询与“刘晨”在同一个系学习的学生
SELECT Sno,Sname,Sdept FROM Student
WHERE Sdept IN(SELECT Sdept FROM Student WHERE Sname='刘晨');
（2）由比较运算符引出的子查询
●找出每个学生超过他选修课程平均成绩的课程号。
SELECT Sno,Cno FROM SCx
WHERE Grade>=(SELECT AVG(Grade)FROM SCy
WHERE y.Sno=x.Sno);
（3）带修饰符的比较运算符引出的子查询
●查询其他系中比计算机科学系所有学生年龄都小的学生姓名
及年龄。
SELECT Sname,Sage FROM Student
WHERE Sage AND Sdept<>'CS'; （4）由EXISTS引出的子查询 ●查询所有选修了1号课程的学生姓名 SELECT Sname FROM Student WHERE EXISTS(SELECT*FROM SC WHERE Sno=Student.Sno AN D Cno='1') 5. 集合查询 （1）集合并 ●查询计算机科学系的学生及年龄不大于19岁的学生 SELECT*FROM Student WHERE Sdept='CS' UNION SELECT*FROM Student WHERE Sage<=19 （2）集合交 ●查询计算机科学系且年龄不大于19岁的学生 SELECT*FROM Student WHERE Sdept='CS' INTERSECT SELECT*FROM Student WHERE Sage<=19 （3）集合差 查询计算机科学系且年龄大于19岁的学生 SELECT*FROM Student WHERE Sdept='CS' EXCEPT SELECT*FROM Student WHERE Sage<=19; 3.4 视图操作 建立视图并基于视图进行查询： 1. 创建视图 （1）建立学生基本信息视图 CREATEVIEW Student_VIEW(学号,姓名,性别,年龄,系,班级) AS SELECT Sno,Sname,Ssex,Sage,Sdept,Sclass FROM Student; （2）建立学生均分视图 CREATEVIEW S_G(Sno,Gavg) AS SELECT Sno,avg(Grade) FROM SC GROUPBY Sno; （3）建立选课信息视图 CREATEVIEW XK_VIEW AS SELECT Student.*,Course.*,Grade FROM Student,SC,Course WHERE Student.Sno=SC.Sno AND https://www.360docs.net/doc/956302987.html,o=https://www.360docs.net/doc/956302987.html,o; 2. 视图查询 （1）查询学生基本信息 SELECT*FROM Student_VIEW （2）找出每个学生超过他选修课程平均成绩的课程号 SELECT SC.Sno,Cno,grade FROM SC,S_G WHERE SC.Sno=S_G.Sno and Grade>=S_G.Gavg （3）查询每个学生的学号、姓名、选修的课程名及成绩SELECT Sno,Sname,Cname,Grade FROM XK_VIEW （4）比较使用视图查询和直接从基表查询的优点