SQL数据库学习之路（练习）--数据库系统教程第三章练习
目录
利用实验1创建的数据库Lesson，分别在各表中插入以下记录，要求至少有一个表为单元组、至少有一个表为多元组的方式插入：
通过select into语句将SC表的表结构复制为SC_TEMP表，然后查询所有性别非空的学生的选课记录并将之插入到SC_TEMP表。
创建视图V_M_STUDENT, 要求显示男学生的学号、姓名、课程名、教师名、成绩等列
检索每个学生的姓名和出生年份，并将出生年份列重命名为Birth_Year
统计每门课程的学生选修人数，（超过3人的课程才统计)，要求显示课程号和人数，查询结构按人数升序排序，若人数相同，按课程号降序排序
分别用INNER JOIN 和LEFT OUTER JOIN 连接S和SC表
实验要求
利用SQL语句创建数据库Lesson
create database work
on primary(
name='work', --主数据文件的逻辑名称
filename='F:\SQL\work1\work.mdf', --主数据文件的物理名称
size=10mb, --主数据文件的初始大小
filegrowth=10mb --主数据文件的增长率
)
log on(
name='work_log', --日志文件的逻辑名称
filename='F:\SQL\work1\work_log.ldf', --日志文件的物理名称
size=5mb, --日志文件的初始大小
filegrowth=10% --日志文件的增长率
)通过SQL语句创建以下基本表
教师关系 T(T#, TNAME,TITLE)
课程关系 C(C#,CNAME,T#)
学生关系 S(S#,SNAME,AGE,SEX)
选课关系SC(S#,C#,SCORE)
班级关系CLASS(CLASSID,CLASSNAME)
其中红色粗体为主键，带下划线的属性为外键。
通过SQL语句在CLASS表的CLASSID列上创建聚集索引IDX_CLASSID
通过SQL语句创建在S#和C#两个列上创建索引IDX_S#_C#，并指定索引按S#降序，C#升序有序。
create table T --创建表教师关系T
(T# char(4) not null,
TNAME char(8) not null,
TITLE char(10),
PRIMARY KEY(T#) --设置主键为T#
);
create table C --创建表课程关系T
(C# char(4),
CNAME char(10) not null,
T# char(4),
PRIMARY KEY(C#), --设置主键为C#
FOREIGN KEY(T#)REFERENCES T(T#) --设置外键为T#
);
create table S --创建表学生关系S
(S# char(4) not null,
SNAME char(8) not null,
AEG char(1),
PRIMARY KEY(S#) --设置主键为S#
);
create table SC --创建表选课关系SC
(S# char(4),
C# char(4),
SCORE SMALLINT,
PRIMARY KEY(S#,C#), --设置主键为S#,C#
FOREIGN KEY(S#)REFERENCES S(S#), ----设置外键为S#
FOREIGN KEY(C#)REFERENCES C(C#) ----设置外键为C#
);
create table class --创建表班级关系class
(
CLASSID char(4),
CLASSNAME char(8)
);通过SQL语句实现以下操作：
撤销索引IDX_CLASSID及IDX_S#_C#
create unique index IDX_CLASSID on class(CLASSID);
--在CLASS表的CLASSID列上创建聚集索引IDX_CLASSID
create unique index IDX_S#_C# on SC(S# DESC,C# ASC);
--创建在S#和C#两个列上创建索引IDX_S#_C#，并指定索引按S#降序，C#升序有序DROP INDEX IDX_CLASSID on class; --撤销索引IDX_CLASSID
DROP INDEX IDX_S#_C# on SC; --撤销索引IDX_S#_C#在学生关系中增加班级号属性列CLASSID
alter table S add CLASSID char(4); --在学生关系中增加班级号属性列CLASSID撤销学生关系中的班级号属性列CLASSID
alter table S drop column CLASSID; --撤销学生关系中的班级号属性列CLASSID撤销班级关系CLASS
drop table class ; --撤销班级关系CLASS利用实验1创建的数据库Lesson，分别在各表中插入以下记录，要求至少有一个表为单元组、至少有一个表为多元组的方式插入：
S表
C表
SC表
T表
insert S(S#,SNAME,AEG,sex)
values('s1','WANG','20','男'),
('s2','LIU','18','女'),
('s3','HU','17','男'),
('s4','XIA','19',NULL),
('s5','SUN','20','男'),
('s6','ZHAO',NULL,'男'),
('s7','DENG',NULL,'女')
insert C(C#,CNAME,T#) values ('c1','OS','t2')
insert C(C#,CNAME,T#) values ('c2','java','t1')
insert C(C#,CNAME,T#) values ('c3','C++','t1')
insert C(C#,CNAME,T#) values ('c4','C','t3')
insert SC(S#,C#,SCORE)
values('s1','c1','80'),
('s1','c2','53'),
('s1','c3',NULL),
('s1','c4','68'),
('s2','c1','85'),
('s3','c1','85'),
('s3','c3','83'),
('s3','c4','75'),
('s4','c1','79'),
('s4','c2','92'),
('s5','c1',NULL),
('s5','c2','45')
insert T(T#,TNAME,TITLE)
values('t1','LIU','教授'),
('t2','ZHANG','副教授'),
('t3','LI','教授'),
('t4','ZHAO','讲师')通过select into语句将SC表的表结构复制为SC_TEMP表，然后查询所有性别非空的学生的选课记录并将之插入到SC_TEMP表。
select *into SC_TEMP from SC where 1=2
insert into SC_TEMP select *from SC where S# in(select S# from S where sex is not NULL)删除SC_TEMP表中无成绩的元组。
delete from SC_TEMP where SCORE is NULL --删除SC_TEMP表中成绩为NULL的元组将SC_TEMP表中S3的低于S3平均成绩的成绩提高10%
--将SC_TEMP表中S3的低于S3平均成绩的成绩提高10%
update SC_TEMP set SCORE =1.1*SCORE where SCORE <(select AVG(SCORE) from SC_TEMP where S#='s3') and S#='s3'删除SC_TEMP表中S3的小于该生平均成绩的成绩元组。
--删除SC_TEMP表中S3的小于该生平均成绩的成绩元组。
delete from SC_TEMP where SCORE <(select AVG(SCORE) from SC_TEMP where S#='s3') and S#='s3'将S表中性别为空的学生的性别修改为女，并将年龄修改为25岁
update S set [sex]='女', [AEG]='25' where sex is NULL创建视图V_M_STUDENT, 要求显示男学生的学号、姓名、课程名、教师名、成绩等列
create view V_M_STUDENT(S#,SNAME,CNAME,TNAME,SCORE)
as select S.S#,SNAME,CNAME,TNAME,SCORE from S,C,T,SC
where S.S#=SC.S# AND SC.C#=C.C# AND C.T#=T.T#检索学习C3课程的所有学生姓名和年龄
--1）检索学习C3课程的所有学生姓名和年龄
select S.SNAME,S.AEG from S,SC where S.S#=SC.S# and SC.C#='c3'检索学习由教授讲授的课程的学生学号和姓名
--2）检索学习由教授讲授的课程的学生学号和姓名
Select DISTINCT S.S#,S.SNAME from S,SC,C,T where
T.TITLE='教授' AND T.T#=C.T# AND C.C#=SC.C#
AND SC.S#=S.S#检索没选修任何课程的学生学号
--3）检索没选修任何课程的学生学号
select S.S# from S where S.S# NOT IN (SELECT S# FROM SC) 检索年龄在18到20岁之间的女学生学习的课程号和课程名
--4）检索年龄在18到20岁之间的女学生学习的课程号和课程名
SELECT DISTINCT C.C#,C.CNAME FROM C,SC WHERE C.C#=SC.C# AND SC.S# IN(SELECT S# FROM S WHERE S.AEG BETWEEN 18 AND 20)检索学习了C1或C3课程的学生学号
--5）检索学习了C1或C3课程的学生学号
SELECT DISTINCT S.S# FROM S,SC WHERE S.S# LIKE SC.S# AND (SC.C#='c1' or SC.C#='c3')检索Hu同学不学的课程号
--6）检索Hu同学不学的课程号
SELECT C.C# FROM C WHERE C.C# NOT IN (SELECT C# FROM SC,S WHERE SC.S# LIKE S.S# AND S.SNAME='HU') 检索成绩为空的课程对应的教师号和教师姓名
--7）检索成绩为空的课程对应的教师号和教师姓名
SELECT T.T#,T.TNAME
FROM T
WHERE T.T# IN
(SELECT C.T# FROM C,SC WHERE C.C# LIKE SC.C# AND SC.SCORE IS NULL)检索至少有S2和S4学习的课程号
--8）检索至少有S2和S4学习的课程号
SELECT DISTINCT SC.C# FROM SC
WHERE SC.C# IN
((SELECT SC.C# FROM SC WHERE SC.S# like 's2') INTERSECT (SELECT SC.C# FROM SC WHERE SC.S# like 's4'))检索选修了S3学习的全部课程的学生学号
--9）检索选修了S3学习的全部课程的学生学号
SELECT DISTINCT SC.S# FROM SC WHERE SC.C# IN (SELECT SC.C# FROM SC WHERE SC.S# LIKE 's3')检索每个学生的姓名和出生年份，并将出生年份列重命名为Birth_Year
--10)检索每个学生的姓名和出生年份，并将出生年份列重命名为Birth_Year
SELECT S.SNAME,2018-S.AEG AS Birth_Year FROM S检索选修了课程的所有学生的学号，要求不显示重复结果
--1)检索选修了课程的所有学生的学号，要求不显示重复结果
SELECT DISTINCT SC.S# FROM SC 检索LIU老师所授课程的每门课程的人数和平均成绩
--2)检索LIU老师所授课程的每门课程的人数和平均成绩。
SELECT C.C#,COUNT(DISTINCT SC.S#),AVG(SC.SCORE)
FROM C,T,SC
WHERE C.T#=T.T# AND C.C#=SC.C# AND TNAME='LIU'
GROUP BY C.C#;统计各门课程的最高分
--3)统计各门课程的最高分。
SELECT SC.C#,MAX(SC.SCORE) FROM SC GROUP BY SC.C#统计每门课程的学生选修人数，（超过3人的课程才统计)，要求显示课程号和人数，查询结构按人数升序排序，若人数相同，按课程号降序排序
--4)统计每门课程的学生选修人数，（超过3人的课程才统计)，要求显示课程号和人数，查询结构按人数升序排序，若人数相同，按课程号降序排序。
SELECT DISTINCT C#,COUNT(DISTINCT S#) AS 人数
FROM SC
GROUP BY C#
HAVING COUNT(S#)>3
ORDER BY COUNT(DISTINCT S#) asc,C#查询每个学生超过他自己选修课程平均成绩的学号及课程号
--5)查询每个学生超过他自己选修课程平均成绩的学号及课程号。
SELECT X.S#,X.C#
FROM SC AS X
WHERE X.SCORE >(SELECT AVG(Y.SCORE) FROM SC AS Y WHERE X.S# =Y.S# )把低于所有课程总平均成绩的女同学的成绩提高3%
--6)把低于所有课程总平均成绩的女同学的成绩提高3%
--SELECT SC.SCORE FROM SC,S WHERE SC.S# LIKE S.S# AND S.sex='女' AND SC.SCORE>(SELECT AVG(SC.SCORE) FROM SC)
UPDATE SC SET SC.SCORE=SC.SCORE*1.03 WHERE SC.SCORE LIKE (SELECT SC.SCORE FROM SC,S WHERE SC.S# LIKE S.S# AND S.sex='女' AND SC.SCORE<(SELECT AVG(SC.SCORE) FROM SC))分别用INNER JOIN 和LEFT OUTER JOIN 连接S和SC表
--INNER JOIN 仅对满足连接条件的CROSS中的列 LEFT OUTER JOIN 一个表满足条件的行，和另一个表的所有行
SELECT *FROM S INNER JOIN SC on S.S#=SC.S#
SELECT *FROM S LEFT OUTER JOIN SC on S.S#=SC.S# 检索姓名中第三个字母为“N”的学生的姓名和年龄
--8)检索姓名中第三个字母为“N”的学生的姓名和年龄。
SELECT S.SNAME,S.AEG FROM S WHERE S.SNAME LIKE '__N%'选择C1课程的前3名的学生学号和成绩
--9) 选择C1课程的前3名的学生学号和成绩。
SELECT TOP(3) SC.S#,SC.SCORE FROM SC WHERE SC.C# LIKE 'c1' ORDER BY SC.SCORE DESC检索平均成绩大于85的学生学号
--10) 检索平均成绩大于85的学生学号。
SELECT DISTINCT X.S# FROM SC AS X WHERE 85<= ALL(SELECT AVG(Y.SCORE) FROM SC AS Y WHERE Y.S#=X.S# GROUP BY Y.S#)
