SQL数据库学习之路（练习）--数据库系统教程第三章练习-阿里云开发者社区

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%				--日志文件的增长率
)
        
          
        
        
        
          
          AI 代码解读

通过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)
);
        
          
        
        
        
          
          AI 代码解读

通过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#升序有序
        
          
        
        
        
          
          AI 代码解读

DROP INDEX IDX_CLASSID on class; --撤销索引IDX_CLASSID
DROP INDEX IDX_S#_C# on SC;		--撤销索引IDX_S#_C#
        
          
        
        
        
          
          AI 代码解读

在学生关系中增加班级号属性列CLASSID

alter table S add CLASSID char(4); --在学生关系中增加班级号属性列CLASSID
        
          
        
        
        
          
          AI 代码解读

撤销学生关系中的班级号属性列CLASSID

alter table S drop column CLASSID;	  --撤销学生关系中的班级号属性列CLASSID
        
          
        
        
        
          
          AI 代码解读

撤销班级关系CLASS

drop table class ;		--撤销班级关系CLASS
        
          
        
        
        
          
          AI 代码解读

利用实验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','讲师')
        
          
        
        
        
          
          AI 代码解读

通过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)
        
          
        
        
        
          
          AI 代码解读

删除SC_TEMP表中无成绩的元组。

delete from SC_TEMP where SCORE is NULL  --删除SC_TEMP表中成绩为NULL的元组
        
          
        
        
        
          
          AI 代码解读

将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'
        
          
        
        
        
          
          AI 代码解读

删除SC_TEMP表中S3的小于该生平均成绩的成绩元组。

--删除SC_TEMP表中S3的小于该生平均成绩的成绩元组。
delete from SC_TEMP where SCORE <(select AVG(SCORE) from SC_TEMP where S#='s3') and S#='s3'
        
          
        
        
        
          
          AI 代码解读

将S表中性别为空的学生的性别修改为女，并将年龄修改为25岁

update S set [sex]='女', [AEG]='25' where sex is NULL
        
          
        
        
        
          
          AI 代码解读

创建视图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#
        
          
        
        
        
          
          AI 代码解读

检索学习C3课程的所有学生姓名和年龄

--1）检索学习C3课程的所有学生姓名和年龄
select S.SNAME,S.AEG from S,SC where S.S#=SC.S# and SC.C#='c3'
        
          
        
        
        
          
          AI 代码解读

检索学习由教授讲授的课程的学生学号和姓名

--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#
        
          
        
        
        
          
          AI 代码解读

检索没选修任何课程的学生学号

--3）检索没选修任何课程的学生学号
select S.S# from S where S.S# NOT IN (SELECT S# FROM SC) 
        
          
        
        
        
          
          AI 代码解读

检索年龄在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)
        
          
        
        
        
          
          AI 代码解读

检索学习了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')
        
          
        
        
        
          
          AI 代码解读

检索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') 
        
          
        
        
        
          
          AI 代码解读

检索成绩为空的课程对应的教师号和教师姓名

--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)
        
          
        
        
        
          
          AI 代码解读

检索至少有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'))
        
          
        
        
        
          
          AI 代码解读

检索选修了S3学习的全部课程的学生学号

--9）检索选修了S3学习的全部课程的学生学号
SELECT DISTINCT SC.S# FROM SC WHERE SC.C# IN (SELECT SC.C# FROM SC WHERE SC.S# LIKE 's3')
        
          
        
        
        
          
          AI 代码解读

检索每个学生的姓名和出生年份，并将出生年份列重命名为Birth_Year

--10)检索每个学生的姓名和出生年份，并将出生年份列重命名为Birth_Year
SELECT S.SNAME,2018-S.AEG AS Birth_Year FROM S
        
          
        
        
        
          
          AI 代码解读

检索选修了课程的所有学生的学号，要求不显示重复结果

--1)检索选修了课程的所有学生的学号，要求不显示重复结果
SELECT DISTINCT SC.S# FROM SC 
        
          
        
        
        
          
          AI 代码解读

检索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#;
        
          
        
        
        
          
          AI 代码解读

统计各门课程的最高分

--3)统计各门课程的最高分。
SELECT SC.C#,MAX(SC.SCORE) FROM SC GROUP BY SC.C#
        
          
        
        
        
          
          AI 代码解读

统计每门课程的学生选修人数，（超过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#
        
          
        
        
        
          
          AI 代码解读

查询每个学生超过他自己选修课程平均成绩的学号及课程号

--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# )
        
          
        
        
        
          
          AI 代码解读

把低于所有课程总平均成绩的女同学的成绩提高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))
        
          
        
        
        
          
          AI 代码解读

分别用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# 
        
          
        
        
        
          
          AI 代码解读

检索姓名中第三个字母为“N”的学生的姓名和年龄

--8)检索姓名中第三个字母为“N”的学生的姓名和年龄。
SELECT S.SNAME,S.AEG FROM S WHERE S.SNAME LIKE '__N%'
        
          
        
        
        
          
          AI 代码解读

选择C1课程的前3名的学生学号和成绩

--9) 选择C1课程的前3名的学生学号和成绩。
SELECT TOP(3) SC.S#,SC.SCORE FROM SC WHERE SC.C# LIKE 'c1' ORDER BY SC.SCORE DESC
        
          
        
        
        
          
          AI 代码解读

检索平均成绩大于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#)
        
          
        
        
        
          
          AI 代码解读

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

SQL数据库学习之路（练习）--数据库系统教程第三章练习

实验要求

利用SQL语句创建数据库Lesson