【数据库系统概论】实验三 SQL数据定义及更新

一、实验目的

1．掌握SQL Server数据库系统基本操作
2．掌握SQL Server数据库系统代码更新方法

二、实验内容

1. 用数据定义语言创建数据库，数据库名为ST_，为本人学号。在数据库中创建schema T，在架构T下创建Student、Course、SC三张数据表（参见教材）并建立相关主码和索引。
2. 增加一个关系Book(BNo，AUTHOR，PRICE，PRESS，SITE)，主关键字是BNO。其中BNO为书号（char(10)），AUTHOR为书名(varchar(30)，PRICE为价格(float)，PRESS为出版社(varchar(30)，SITE为出版地(varchar(30)。
3. 在以上四张表中用SQL语言插入若干数据。涉及到完整性约束要求的，用update修改数据。要求学生数据不少于20，课程数据不小于6，选课数据不少于40。书籍信息不少于15。
4. 保存完整的代码txt文件。存放于U盘中。
5. 检索选修人数最多的课程
6. 检索每门课程的最高分
7. 查询“数据结构”价格最低的出版社
8. 统计每个出版社图书的数量。
9. 查询全部同学都选修了的课程。
10. 查询至少选修了学号为“”同学选修全部课程的学生。

三、问题和要求

1．写出你操作并正确执行的代码及结果。

（5）检索选修人数最多的课程

SELECT*FROM T.Course WHERE Cno=(SELECTTop1 cno FROM T.SC GROUPBY cno ORDERBYCOUNT(*)DESC);

（6）检索每门课程的最高分

SELECT Cname 课程名,C.Cno 课程ID,MAX(Grade) 最高分 FROM T.SC SC,T.Course C WHERE SC.Cno=C.Cno GROUPBY C.Cno,Cname ORDERBYMAX(Grade)DESC;

（7）查询“数据结构”价格最低的出版社

SELECT*FROM T.Book WHERE Author LIKE'%数据结构%'ORDERBY Price DESC;SELECTtop1 Press FROM T.Book WHERE Author LIKE'%数据结构%'ORDERBY Price DESC;

（8）统计每个出版社图书的数量

SELECT Press 出版社,COUNT(*) 出版数量 FROM T.Book GROUPBY Press ORDERBYCOUNT(*)DESC;

（9）查询全部同学都选修了的课程

SELECT Cno, Cname FROM T.Course C WHERENOTEXISTS(SELECT*FROM T.Student S WHERENOTEXISTS(SELECT*FROM T.SC WHERE SC.Cno = C.Cno and SC.Sno = S.Sno));

（10）查询至少选修了学号为 “ 201215122 ” 同学选修全部课程的学生。

SELECTDISTINCT Sno 学号 FROM T.SC scx 
WHERENOTEXISTS(SELECT*FROM T.SC scy WHERE scy.Sno ='202110810'ANDNOTEXISTS(SELECT*FROM T.SC scz WHERE scz.Sno = scx.Sno AND scz.Cno = scy.Cno));

2．完整的txt代码

-- 创建数据库 ST_2022611788CREATEDATABASE ST_2022611788;-- 创建模式TCREATESCHEMA T;-- 如果表存在先执行删除操作DROPTABLEIFEXISTS T.SC;DROPTABLEIFEXISTS T.Course;DROPTABLEIFEXISTS T.Student;DROPTABLEIFEXISTS T.Book;-- 在模式T中创建表StudentCREATETABLE T.Student(    
 Sno CHAR(9)PRIMARYKEY,            
 Sname CHAR(20)UNIQUE, 
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20));-- 在模式T中创建表CourseCREATETABLE T.Course(    
 Cno CHAR(4)PRIMARYKEY,
 Cname CHAR(40),            
 Cpno CHAR(4),                                         
 Ccredit SMALLINT,FOREIGNKEY(Cpno)REFERENCES  T.Course(Cno));-- 在模式T中创建表SCCREATETABLE T.SC(
 Sno CHAR(9), 
 Cno CHAR(4),  
 Grade SMALLINT,PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCES T.Student(Sno),FOREIGNKEY(Cno)REFERENCES T.Course(Cno));-- 增加一个关系T.BookCREATETABLE T.Book(
Bno CHAR(10),
Author VARCHAR(30),
Price FLOAT,
Press CHAR(30),
Site VARCHAR(30));-- 在表中插入T.Student的数据INSERTINTO T.Student(Sno,Sname,Ssex,Sage,Sdept)VALUES('202110767','杨润玲','女',21,'大数据学院');INSERTINTO T.Student VALUES('202110768','朱娅玲','女',21,'大数据学院');INSERTINTO T.Student VALUES('202110769','徐江','男',22,'大数据学院');INSERTINTO T.Student VALUES('202110770','周双凤','女',22,'大数据学院');INSERTINTO T.Student VALUES('202110771','张秋玲','女',20,'大数据学院');INSERTINTO T.Student VALUES('202110772','卢成思','男',20,'大数据学院');INSERTINTO T.Student VALUES('202110775','胡梁蕊','女',21,'大数据学院');INSERTINTO T.Student VALUES('202110777','李银娇','女',22,'大数据学院');INSERTINTO T.Student VALUES('202110780','滕佳美','女',22,'大数据学院');INSERTINTO T.Student VALUES('202110794','颜蓉','女',22,'大数据学院');INSERTINTO T.Student VALUES('202110806','杨瑜','男',21,'大数据学院');INSERTINTO T.Student VALUES('202110810','杨登洪','女',23,'大数据学院');INSERTINTO T.Student VALUES('202110817','刘宇洁','男',19,'大数据学院');INSERTINTO T.Student VALUES('202110818','张钰雪','女',21,'大数据学院');INSERTINTO T.Student VALUES('202110820','王庆','男',20,'大数据学院');INSERTINTO T.Student VALUES('202110822','陈天浪','男',23,'大数据学院');INSERTINTO T.Student VALUES('202110824','贺铄清','男',21,'大数据学院');INSERTINTO T.Student VALUES('202110828','魏列镜','男',22,'大数据学院');INSERTINTO T.Student VALUES('202110830','撒月星','男',22,'大数据学院');INSERTINTO T.Student VALUES('202110831','余艳红','女',22,'大数据学院');INSERTINTO T.Student VALUES('202110838','方绍玉','女',21,'大数据学院');-- 在表中插入T.Course的数据INSERTINTO T.Course(Cno,Cname,Cpno,Ccredit)VALUES('1','数据结构',NULL,3);INSERTINTO T.Course VALUES('2','操作系统',NULL,3);INSERTINTO T.Course VALUES('3','计算机组成原理',NULL,3);INSERTINTO T.Course VALUES('4','计算机网络',NULL,3);INSERTINTO T.Course VALUES('5','数据库系统原理及应用','1',3);INSERTINTO T.Course VALUES('6','C语言程序设计',NULL,3);INSERTINTO T.Course VALUES('7','Python程序设计',NULL,2);INSERTINTO T.Course VALUES('8','大数据应用技术',NULL,2);-- 涉及到完整性约束要求，先插入T.Course的数据再更新关联的列UPDATE T.Course SET Cpno ='5'WHERE Cno ='1'UPDATE T.Course SET Cpno ='3'WHERE Cno ='2'UPDATE T.Course SET Cpno ='6'WHERE Cno ='4'UPDATE T.Course SET Cpno ='7'WHERE Cno ='5'UPDATE T.Course SET Cpno ='6'WHERE Cno ='7'-- 在表中插入T.SC的数据INSERTINTO T.SC(Sno,Cno,Grade)VALUES('202110767','1',91);INSERTINTO T.SC VALUES('202110767','5',66);INSERTINTO T.SC VALUES('202110767','6',94);INSERTINTO T.SC VALUES('202110767','7',55);INSERTINTO T.SC VALUES('202110768','1',50);INSERTINTO T.SC VALUES('202110768','2',13);INSERTINTO T.SC VALUES('202110768','8',69);INSERTINTO T.SC VALUES('202110769','1',80);INSERTINTO T.SC VALUES('202110769','3',80);INSERTINTO T.SC VALUES('202110769','5',72);INSERTINTO T.SC VALUES('202110769','6',14);INSERTINTO T.SC VALUES('202110770','1',63);INSERTINTO T.SC VALUES('202110770','2',40);INSERTINTO T.SC VALUES('202110770','3',43);INSERTINTO T.SC VALUES('202110770','4',73);INSERTINTO T.SC VALUES('202110771','1',38);INSERTINTO T.SC VALUES('202110771','2',38);INSERTINTO T.SC VALUES('202110771','3',62);INSERTINTO T.SC VALUES('202110771','5',90);INSERTINTO T.SC VALUES('202110772','1',46);INSERTINTO T.SC VALUES('202110772','6',46);INSERTINTO T.SC VALUES('202110775','1',89);INSERTINTO T.SC VALUES('202110775','7',89);INSERTINTO T.SC VALUES('202110777','1',41);INSERTINTO T.SC VALUES('202110777','8',41);INSERTINTO T.SC VALUES('202110780','1',19);INSERTINTO T.SC VALUES('202110780','7',19);INSERTINTO T.SC VALUES('202110794','1',1);INSERTINTO T.SC VALUES('202110794','6',1);INSERTINTO T.SC VALUES('202110806','1',92);INSERTINTO T.SC VALUES('202110806','5',92);INSERTINTO T.SC VALUES('202110810','1',15);INSERTINTO T.SC VALUES('202110810','4',15);INSERTINTO T.SC VALUES('202110817','1',2);INSERTINTO T.SC VALUES('202110817','3',2);INSERTINTO T.SC VALUES('202110818','1',56);INSERTINTO T.SC VALUES('202110818','2',56);INSERTINTO T.SC VALUES('202110820','1',33);INSERTINTO T.SC VALUES('202110822','1',85);INSERTINTO T.SC VALUES('202110822','5',85);INSERTINTO T.SC VALUES('202110824','1',57);INSERTINTO T.SC VALUES('202110824','3',26);INSERTINTO T.SC VALUES('202110828','1',53);INSERTINTO T.SC VALUES('202110828','2',53);INSERTINTO T.SC VALUES('202110828','5',36);INSERTINTO T.SC VALUES('202110830','1',77);INSERTINTO T.SC VALUES('202110830','5',70);INSERTINTO T.SC VALUES('202110830','6',76);INSERTINTO T.SC VALUES('202110830','7',49);INSERTINTO T.SC VALUES('202110831','1',30);INSERTINTO T.SC VALUES('202110831','2',81);INSERTINTO T.SC VALUES('202110838','1',4);INSERTINTO T.SC VALUES('202110838','5',97);INSERTINTO T.SC VALUES('202110838','6',99);-- 在表中插入T.Book的数据INSERTINTO T.book(Bno,Author,Price,Press,Site)VALUES('1','计算机操作系统',49.5,'清华大学出版社','北京');INSERTINTO T.book VALUES('2','Java程序设计及实验',66,'清华大学出版社','北京');INSERTINTO T.book VALUES('3','python快速编程入门（第二版）',49.8,'人民邮电出版社','北京');INSERTINTO T.book VALUES('4','计算机组成原理',49.8,'电子工业出版社','北京');INSERTINTO T.book VALUES('5','数据结构（C语言）版',58,'人民邮电出版社','北京');INSERTINTO T.book VALUES('6','数据库系统概论（第5版）',42,'高等教育出版社','北京');INSERTINTO T.book VALUES('7','物联网安全实战',55.3,'机械工业出版社','重庆');INSERTINTO T.book VALUES('8','数据结构与算法',55.3,'机械工业出版社','重庆');INSERTINTO T.book VALUES('9','C#程序设计教程 第4版',48.3,'机械工业出版社','重庆');INSERTINTO T.book VALUES('10','深入理解计算机系统',97.3,'机械工业出版社','重庆');INSERTINTO T.book VALUES('11','数学之美 第三版',69,'人民邮电出版社','北京');INSERTINTO T.book VALUES('12','中文版Photoshop',94,'水利水电出版社','河北');INSERTINTO T.book VALUES('13','Excel完全自学教程',45.6,'天津科学技术出版社','天津');INSERTINTO T.book VALUES('14','Python GUI设计',96,'吉林大学出版社','北京');INSERTINTO T.book VALUES('15','SQL必知必会 第5版',45,'人民邮电出版社','北京');INSERTINTO T.book VALUES('16','Java数据结构和算法',36.3,'清华大学出版社','重庆');INSERTINTO T.book VALUES('17','剑指offer',56.3,'人民邮电出版社','重庆');INSERTINTO T.book VALUES('18','啊哈算法',52.63,'电子工业出版社','北京');INSERTINTO T.book VALUES('19','LeetCode常见算法',86.9,'人民邮电出版社','河北');INSERTINTO T.book VALUES('20','程序是怎样跑起来的',42.6,'高等教育出版社','天津');-- 执行查询SELECT*FROM T.Student;SELECT*FROM T.Course;SELECT*FROM T.SC;SELECT*FROM T.Book;-- 5.检索选修人数最多的课程WHERE Cno=(SELECTTop1 cno FROM T.SC GROUPBY cno ORDERBYCOUNT(*)DESC);-- 6.检索每门课程的最高分SELECT Cname 课程名,C.Cno 课程ID,MAX(Grade) 最高分 FROM T.SC SC,T.Course C WHERE SC.Cno=C.Cno GROUPBY C.Cno,Cname ORDERBYMAX(Grade)DESC;-- 7.查询“数据结构”价格最低的出版社SELECT*FROM T.Book WHERE Author LIKE'%数据结构%'ORDERBY Price DESC;SELECTtop1 Press FROM T.Book WHERE Author LIKE'%数据结构%'ORDERBY Price DESC;-- 8.统计每个出版社图书的数量SELECT Press 出版社,COUNT(*) 出版数量 FROM T.Book GROUPBY Press ORDERBYCOUNT(*)DESC;-- 9.查询全部同学都选修了的课程SELECT Cno, Cname FROM T.Course C WHERENOTEXISTS(SELECT*FROM T.Student S WHERENOTEXISTS(SELECT*FROM T.SC WHERE SC.Cno = C.Cno and SC.Sno = S.Sno));-- 10.查询至少选修了学号为 “ 201215122 ” 同学选修全部课程的学生。SELECTDISTINCT Sno 学号 FROM T.SC scx WHERENOTEXISTS(SELECT*FROM T.SC scy WHERE scy.Sno ='202110810'ANDNOTEXISTS(SELECT*FROM T.SC scz WHERE scz.Sno = scx.Sno AND scz.Cno = scy.Cno));