【数据库系统概论】实验三 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=(SELECT Top 1 cno FROM T.SC GROUP BY cno ORDER BY COUNT(*) DESC);
1

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

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

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

SELECT * FROM T.Book WHERE Author LIKE '%数据结构%' ORDER BY Price DESC;
SELECT top 1 Press FROM T.Book WHERE Author LIKE '%数据结构%' ORDER BY Price DESC;
1
2

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

SELECT Press 出版社,COUNT(*) 出版数量 FROM T.Book GROUP BY Press ORDER BY COUNT(*) DESC;
1

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

SELECT Cno, Cname FROM T.Course C WHERE NOT EXISTS
(SELECT * FROM T.Student S WHERE NOT EXISTS
(SELECT * FROM T.SC WHERE SC.Cno = C.Cno and SC.Sno = S.Sno));
1
2
3

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

SELECT DISTINCT Sno 学号 FROM T.SC scx 
WHERE NOT EXISTS(SELECT * FROM T.SC scy WHERE scy.Sno = '202110810' AND NOT EXISTS(SELECT * FROM T.SC scz WHERE scz.Sno = scx.Sno AND scz.Cno = scy.Cno));
1
2

2．完整的txt代码

-- 创建数据库 ST_2022611788
CREATE DATABASE ST_2022611788;
-- 创建模式T
CREATE SCHEMA T;
-- 如果表存在先执行删除操作
DROP TABLE IF EXISTS T.SC;
DROP TABLE IF EXISTS T.Course;
DROP TABLE IF EXISTS T.Student;
DROP TABLE IF EXISTS T.Book;
-- 在模式T中创建表Student
CREATE TABLE T.Student(	
 Sno CHAR(9) PRIMARY KEY,            
 Sname CHAR(20) UNIQUE, 
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20)
 ); 
 -- 在模式T中创建表Course
CREATE TABLE T.Course(	
 Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40),            
 Cpno CHAR(4),               	                      
 Ccredit SMALLINT,
 FOREIGN KEY (Cpno) REFERENCES  T.Course(Cno) 
 ); 
  -- 在模式T中创建表SC
CREATE TABLE T.SC(
 Sno CHAR(9), 
 Cno CHAR(4),  
 Grade SMALLINT,
 PRIMARY KEY (Sno,Cno),                      
 FOREIGN KEY (Sno) REFERENCES T.Student(Sno), 
 FOREIGN KEY (Cno) REFERENCES T.Course(Cno) 
 ); 
 -- 增加一个关系T.Book
CREATE TABLE T.Book(
Bno CHAR(10),
Author VARCHAR(30),
Price FLOAT,
Press CHAR(30),
Site VARCHAR(30)
);
 -- 在表中插入T.Student的数据
INSERT INTO T.Student(Sno,Sname,Ssex,Sage,Sdept) VALUES ('202110767', '杨润玲', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110768', '朱娅玲', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110769', '徐江', '男', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110770', '周双凤', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110771', '张秋玲', '女', 20, '大数据学院');
INSERT INTO T.Student VALUES ('202110772', '卢成思', '男', 20, '大数据学院');
INSERT INTO T.Student VALUES ('202110775', '胡梁蕊', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110777', '李银娇', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110780', '滕佳美', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110794', '颜蓉', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110806', '杨瑜', '男', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110810', '杨登洪', '女', 23, '大数据学院');
INSERT INTO T.Student VALUES ('202110817', '刘宇洁', '男', 19, '大数据学院');
INSERT INTO T.Student VALUES ('202110818', '张钰雪', '女', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110820', '王庆', '男', 20, '大数据学院');
INSERT INTO T.Student VALUES ('202110822', '陈天浪', '男', 23, '大数据学院');
INSERT INTO T.Student VALUES ('202110824', '贺铄清', '男', 21, '大数据学院');
INSERT INTO T.Student VALUES ('202110828', '魏列镜', '男', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110830', '撒月星', '男', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110831', '余艳红', '女', 22, '大数据学院');
INSERT INTO T.Student VALUES ('202110838', '方绍玉', '女', 21, '大数据学院');
  -- 在表中插入T.Course的数据
INSERT INTO T.Course(Cno,Cname,Cpno,Ccredit) VALUES ('1', '数据结构', NULL, 3);
INSERT INTO T.Course VALUES ('2', '操作系统', NULL, 3);
INSERT INTO T.Course VALUES ('3', '计算机组成原理', NULL, 3);
INSERT INTO T.Course VALUES ('4', '计算机网络', NULL, 3);
INSERT INTO T.Course VALUES ('5', '数据库系统原理及应用', '1', 3);
INSERT INTO T.Course VALUES ('6', 'C语言程序设计', NULL, 3);
INSERT INTO T.Course VALUES ('7', 'Python程序设计', NULL, 2);
INSERT INTO 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的数据
INSERT INTO T.SC(Sno,Cno,Grade) VALUES ('202110767', '1', 91);
INSERT INTO T.SC VALUES ('202110767', '5', 66);
INSERT INTO T.SC VALUES ('202110767', '6', 94);
INSERT INTO T.SC VALUES ('202110767', '7', 55);
INSERT INTO T.SC VALUES ('202110768', '1', 50);
INSERT INTO T.SC VALUES ('202110768', '2', 13);
INSERT INTO T.SC VALUES ('202110768', '8', 69);
INSERT INTO T.SC VALUES ('202110769', '1', 80);
INSERT INTO T.SC VALUES ('202110769', '3', 80);
INSERT INTO T.SC VALUES ('202110769', '5', 72);
INSERT INTO T.SC VALUES ('202110769', '6', 14);
INSERT INTO T.SC VALUES ('202110770', '1', 63);
INSERT INTO T.SC VALUES ('202110770', '2', 40);
INSERT INTO T.SC VALUES ('202110770', '3', 43);
INSERT INTO T.SC VALUES ('202110770', '4', 73);
INSERT INTO T.SC VALUES ('202110771', '1', 38);
INSERT INTO T.SC VALUES ('202110771', '2', 38);
INSERT INTO T.SC VALUES ('202110771', '3', 62);
INSERT INTO T.SC VALUES ('202110771', '5', 90);
INSERT INTO T.SC VALUES ('202110772', '1', 46);
INSERT INTO T.SC VALUES ('202110772', '6', 46);
INSERT INTO T.SC VALUES ('202110775', '1', 89);
INSERT INTO T.SC VALUES ('202110775', '7', 89);
INSERT INTO T.SC VALUES ('202110777', '1', 41);
INSERT INTO T.SC VALUES ('202110777', '8', 41);
INSERT INTO T.SC VALUES ('202110780', '1', 19);
INSERT INTO T.SC VALUES ('202110780', '7', 19);
INSERT INTO T.SC VALUES ('202110794', '1', 1);
INSERT INTO T.SC VALUES ('202110794', '6', 1);
INSERT INTO T.SC VALUES ('202110806', '1', 92);
INSERT INTO T.SC VALUES ('202110806', '5', 92);
INSERT INTO T.SC VALUES ('202110810', '1', 15);
INSERT INTO T.SC VALUES ('202110810', '4', 15);
INSERT INTO T.SC VALUES ('202110817', '1', 2);
INSERT INTO T.SC VALUES ('202110817', '3', 2);
INSERT INTO T.SC VALUES ('202110818', '1', 56);
INSERT INTO T.SC VALUES ('202110818', '2', 56);
INSERT INTO T.SC VALUES ('202110820', '1', 33);
INSERT INTO T.SC VALUES ('202110822', '1', 85);
INSERT INTO T.SC VALUES ('202110822', '5', 85);
INSERT INTO T.SC VALUES ('202110824', '1', 57);
INSERT INTO T.SC VALUES ('202110824', '3', 26);
INSERT INTO T.SC VALUES ('202110828', '1', 53);
INSERT INTO T.SC VALUES ('202110828', '2', 53);
INSERT INTO T.SC VALUES ('202110828', '5', 36);
INSERT INTO T.SC VALUES ('202110830', '1', 77);
INSERT INTO T.SC VALUES ('202110830', '5', 70);
INSERT INTO T.SC VALUES ('202110830', '6', 76);
INSERT INTO T.SC VALUES ('202110830', '7', 49);
INSERT INTO T.SC VALUES ('202110831', '1', 30);
INSERT INTO T.SC VALUES ('202110831', '2', 81);
INSERT INTO T.SC VALUES ('202110838', '1', 4);
INSERT INTO T.SC VALUES ('202110838', '5', 97);
INSERT INTO T.SC VALUES ('202110838', '6', 99);
-- 在表中插入T.Book的数据
INSERT INTO T.book(Bno,Author,Price,Press,Site) VALUES ('1', '计算机操作系统', 49.5, '清华大学出版社', '北京');
INSERT INTO T.book VALUES ('2', 'Java程序设计及实验', 66, '清华大学出版社', '北京');
INSERT INTO T.book VALUES ('3', 'python快速编程入门（第二版）', 49.8, '人民邮电出版社', '北京');
INSERT INTO T.book VALUES ('4', '计算机组成原理', 49.8, '电子工业出版社', '北京');
INSERT INTO T.book VALUES ('5', '数据结构（C语言）版', 58, '人民邮电出版社', '北京');
INSERT INTO T.book VALUES ('6', '数据库系统概论（第5版）', 42, '高等教育出版社', '北京');
INSERT INTO T.book VALUES ('7', '物联网安全实战', 55.3, '机械工业出版社', '重庆');
INSERT INTO T.book VALUES ('8', '数据结构与算法', 55.3, '机械工业出版社', '重庆');
INSERT INTO T.book VALUES ('9', 'C#程序设计教程 第4版', 48.3, '机械工业出版社', '重庆');
INSERT INTO T.book VALUES ('10', '深入理解计算机系统', 97.3, '机械工业出版社', '重庆');
INSERT INTO T.book VALUES ('11', '数学之美 第三版', 69, '人民邮电出版社', '北京');
INSERT INTO T.book VALUES ('12', '中文版Photoshop', 94, '水利水电出版社', '河北');
INSERT INTO T.book VALUES ('13', 'Excel完全自学教程', 45.6, '天津科学技术出版社', '天津');
INSERT INTO T.book VALUES ('14', 'Python GUI设计', 96, '吉林大学出版社', '北京');
INSERT INTO T.book VALUES ('15', 'SQL必知必会 第5版', 45, '人民邮电出版社', '北京');
INSERT INTO T.book VALUES ('16', 'Java数据结构和算法', 36.3, '清华大学出版社', '重庆');
INSERT INTO T.book VALUES ('17', '剑指offer', 56.3, '人民邮电出版社', '重庆');
INSERT INTO T.book VALUES ('18', '啊哈算法', 52.63, '电子工业出版社', '北京');
INSERT INTO T.book VALUES ('19', 'LeetCode常见算法', 86.9, '人民邮电出版社', '河北');
INSERT INTO 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=(SELECT Top 1 cno FROM T.SC GROUP BY cno ORDER BY COUNT(*) DESC);

-- 6.检索每门课程的最高分
SELECT Cname 课程名,C.Cno 课程ID,MAX(Grade) 最高分 FROM T.SC SC,T.Course C WHERE SC.Cno=C.Cno GROUP BY C.Cno,Cname ORDER BY MAX(Grade) DESC;

-- 7.查询“数据结构”价格最低的出版社
SELECT * FROM T.Book WHERE Author LIKE '%数据结构%' ORDER BY Price DESC;
SELECT top 1 Press FROM T.Book WHERE Author LIKE '%数据结构%' ORDER BY Price DESC;

-- 8.统计每个出版社图书的数量
SELECT Press 出版社,COUNT(*) 出版数量 FROM T.Book GROUP BY Press ORDER BY COUNT(*) DESC;

-- 9.查询全部同学都选修了的课程
SELECT Cno, Cname FROM T.Course C WHERE NOT EXISTS
(SELECT * FROM T.Student S WHERE NOT EXISTS
(SELECT * FROM T.SC WHERE SC.Cno = C.Cno and SC.Sno = S.Sno));

-- 10.查询至少选修了学号为 “ 201215122 ” 同学选修全部课程的学生。
SELECT DISTINCT Sno 学号 FROM T.SC scx WHERE NOT EXISTS(SELECT * FROM T.SC scy WHERE scy.Sno = '202110810' AND NOT EXISTS(SELECT * FROM T.SC scz WHERE scz.Sno = scx.Sno AND scz.Cno = scy.Cno));
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181