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

本文涉及的产品
日志服务 SLS,月写入数据量 50GB 1个月
简介: 目录 实验要求利用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#)

 

相关实践学习
日志服务之使用Nginx模式采集日志
本文介绍如何通过日志服务控制台创建Nginx模式的Logtail配置快速采集Nginx日志并进行多维度分析。
目录
相关文章
|
2天前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
10 2
|
7天前
|
SQL JSON Java
没有数据库也能用 SQL
SPL(Structured Process Language)是一款开源软件,允许用户直接对CSV、XLS等文件进行SQL查询,无需将数据导入数据库。它提供了标准的JDBC驱动,支持复杂的SQL操作,如JOIN、子查询和WITH语句,还能处理非标准格式的文件和JSON数据。SPL不仅简化了数据查询,还提供了强大的计算能力和友好的IDE,适用于多种数据源的混合计算。
|
13天前
|
存储 SQL 关系型数据库
【入门级教程】MySQL:从零开始的数据库之旅
本教程面向零基础用户,采用通俗易懂的语言和丰富的示例,帮助你快速掌握MySQL的基础知识和操作技巧。内容涵盖SQL语言基础(SELECT、INSERT、UPDATE、DELETE等常用语句)、使用索引提高查询效率、存储过程等。适合学生、开发者及数据库爱好者。
27 0
【入门级教程】MySQL:从零开始的数据库之旅
|
17天前
|
SQL NoSQL 关系型数据库
数据库学习
【10月更文挑战第8天】
17 1
|
17天前
|
关系型数据库 MySQL Java
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
这篇文章是关于如何使用Django框架配置MySQL数据库,创建模型实例,并自动或手动创建数据库表,以及对这些表进行操作的详细教程。
47 0
Django学习二:配置mysql,创建model实例,自动创建数据库表,对mysql数据库表已经创建好的进行直接操作和实验。
|
18天前
|
Java 关系型数据库 MySQL
springboot学习五:springboot整合Mybatis 连接 mysql数据库
这篇文章是关于如何使用Spring Boot整合MyBatis来连接MySQL数据库,并进行基本的增删改查操作的教程。
29 0
springboot学习五:springboot整合Mybatis 连接 mysql数据库
|
18天前
|
Java 关系型数据库 MySQL
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
这篇文章是关于如何使用Spring Boot框架通过JdbcTemplate操作MySQL数据库的教程。
17 0
springboot学习四:springboot链接mysql数据库,使用JdbcTemplate 操作mysql
|
8天前
|
SQL 数据库
SQL数据库基础语法入门
[link](http://www.vvo.net.cn/post/082935.html)
|
14天前
|
SQL NoSQL MongoDB
一款基于分布式文件存储的数据库MongoDB的介绍及基本使用教程
一款基于分布式文件存储的数据库MongoDB的介绍及基本使用教程
32 0
|
15天前
|
SQL 存储 关系型数据库
mysql 数据库空间统计sql
mysql 数据库空间统计sql
32 0