开发者社区> 空城机> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: 目录   实验要求 利用SQL语句创建数据库Lesson 通过SQL语句创建以下基本表 通过SQL语句实现以下操作: 撤销索引IDX_CLASSID及IDX_S#_C# 在学生关系中增加班级号属性列CLASS...
+关注继续查看

目录

 

实验要求

利用SQL语句创建数据库Lesson

通过SQL语句创建以下基本表

通过SQL语句实现以下操作:

撤销索引IDX_CLASSID及IDX_S#_C#

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

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

撤销班级关系CLASS

利用实验1创建的数据库Lesson,分别在各表中插入以下记录,要求至少有一个表为单元组、至少有一个表为多元组的方式插入:

通过select into语句将SC表的表结构复制为SC_TEMP表,然后查询所有性别非空的学生的选课记录并将之插入到SC_TEMP表。

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

将SC_TEMP表中S3的低于S3平均成绩的成绩提高10%

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

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

创建视图V_M_STUDENT, 要求显示男学生的学号、姓名、课程名、教师名、成绩等列

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

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

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

检索年龄在18到20岁之间的女学生学习的课程号和课程名

​检索学习了C1或C3课程的学生学号

​检索Hu同学不学的课程号

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

检索至少有S2和S4学习的课程号

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

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

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

检索LIU老师所授课程的每门课程的人数和平均成绩

统计各门课程的最高分

统计每门课程的学生选修人数,(超过3人的课程才统计),要求显示课程号和人数,查询结构按人数升序排序,若人数相同,按课程号降序排序

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

把低于所有课程总平均成绩的女同学的成绩提高3%

分别用INNER JOIN 和LEFT OUTER JOIN 连接S和SC表

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

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

 检索平均成绩大于85的学生学号


实验要求

利用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#)

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
SQL数据库学习之路(九)
目录   数据库创建补充: 为数据库用户赋予权限 在数据库表中使用视图进行添加修改 数据库创建补充: 以文本格式显示自己创建的数据库表                                          以网格格式显示自己创建的数据库表 可以右键数据库--属性---选项,在选项中可以设置数据库的兼容性,还有是否可以让多人同时访问或者单人访问。
850 0
SQL数据库学习之路(练习)---C#登录界面连数据库
目录 C#登录界面连数据库 一、在数据库中先创建一个数据库。 二、在VS中创建C#的windows窗体应用程序 三、在VS中连接到数据库 四、设计登录界面 五、设置注册界面 六、将form1的按钮和form2进行关联 七、设置form2界面的注册按钮 八、form1的登录按钮设置 九、运行成果 参考文章: C#登录界面连数据库 一、在数据库中先创建一个数据库。
2706 0
04.数据库系统
脑图如下所示:
638 0
SQL脚本修改数据库名称
USE master; GO DECLARE @SQL VARCHAR(MAX); SET @SQL='' SELECT @SQL=@SQL+'; KILL '+RTRIM(SPID) FROM master.
805 0
sql给数据库加锁问题
加锁是在操作数据时进行了,不能事后加锁。 例: begin   tran           insert   表   with(TABLOCKX)     --加锁           (字段列表)   values(值列表)           commit   tran 其他锁的示...
1029 0
数据库常用SQL
版权声明:欢迎评论和转载,转载请注明来源。 https://blog.csdn.net/zy332719794/article/details/8951552 以前零零散散地学习和使用了不少的SQL语句,感觉都了解一些,但实际用的时候也要常常上网再查询和确认。
657 0
+关注
空城机
新手上路
35
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载