数据库实验-4

sterne 发布于

一转眼又是好久没post

这次之后完全换成jetbrains家的datagrip写了，不要太香

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
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
--一、对xsgl数据库完成下列操作要求：
--1.将被全部学生都选修了的课程的总学分改为4学分。
CREATE VIEW all_set(课程号)
AS
SELECT 课程号
FROM kc X
WHERE NOT EXISTS(
	SELECT *
	FROM xs Y
	WHERE NOT EXISTS(
		SELECT *
		FROM cj Z
		WHERE X.课程号=Z.课程号 AND Y.学号=Z.学号));
GO
UPDATE kc
SET 学分=4
WHERE kc.课程号=(
	SELECT 课程号
	FROM all_set
	WHERE kc.课程号=all_set.课程号);

--2.从学生表删除没有选课的学生。
DELETE
FROM xs
WHERE NOT EXISTS(
	SELECT *
	FROM cj
	WHERE xs.学号=cj.学号);
GO

--3.将每个学生的平均分，总分和选课门数插入到数据库中（学号，姓名，平均分，总分，选课门数）。
--插入数据库是？
是建table然后insert。。。
CREATE VIEW stu_data
AS
SELECT xs.学号,姓名,AVG(成绩) AS '平均分',SUM(成绩) AS '总分',COUNT(课程号) AS '选课门数'
FROM xs LEFT JOIN cj ON(xs.学号=cj.学号)
GROUP BY xs.学号,姓名;
GO

--4.创建每门课程的平均分和选课人数的视图（课程号，课程名，平均分，人数）。
CREATE VIEW course_data
AS
SELECT kc.课程号,课程名,AVG(成绩) AS '平均分',COUNT(学号) AS '人数'
FROM kc LEFT JOIN cj ON(kc.课程号=cj.课程号)
GROUP BY kc.课程号,课程名;
GO

--5.将李强同学从学生表删除（提示应该先删除李强同学的选课记录）。
DECLARE @sno INT
SELECT @sno=(SELECT 学号 FROM xs WHERE 姓名='李强');

DELETE
FROM cj
WHERE 学号=@sno;

DELETE
FROM xs
WHERE 学号=@sno;

GO
--6.插入一条选课记录（具体内容自己选）。
INSERT
INTO cj
VALUES ('2006030101','J005',NULL);
GO

--7.创建网络工程专业的学生的选课信息的视图，要求视图包含，学号，姓名，专业，课程号，课程名，成绩。
CREATE VIEW sdept_is_NW
AS
SELECT X.学号,姓名,专业,Z.课程号,课程名,成绩
FROM xs X JOIN cj Y ON(X.学号=Y.学号) JOIN kc Z ON(Y.课程号=Z.课程号)
WHERE X.专业='网络工程';
GO

--8.查询网络工程专业的各科的平均成绩，要求使用第7题创建的视图进行查询。
SELECT Z.课程号,Z.课程名,AVG(X.成绩)
FROM sdept_is_NW X JOIN kc Z ON(Z.课程号=X.课程号)
GROUP BY Z.课程号,Z.课程名;
GO

--9.查询被信息管理专业的学生都选修了的课程的课程号，课程名。
SELECT X.课程号,X.课程名
FROM kc X
WHERE NOT EXISTS(
	SELECT *
	FROM xs Y
	WHERE 专业='信息管理' AND NOT EXISTS(
		SELECT *
		FROM cj Z
		WHERE X.课程号=Z.课程号 AND Y.学号=Z.学号));
GO

--10.显示选修课程数最多的学号及选修课程数最少的学号，姓名（使用派生表实现）。
SELECT X.学号,姓名
FROM (SELECT TOP 1 学号 FROM cj GROUP BY 学号 ORDER BY COUNT(*) DESC) AS maxsno,
	 (SELECT TOP 1 学号 FROM cj GROUP BY 学号 ORDER BY COUNT(*) ASC) AS minsno,xs X
WHERE X.学号=maxsno.学号 or X.学号=minsno.学号;

--11.查询每个学生成绩高于自己的平均成绩的学号，姓名，课程号和成绩（使用派生表实现）。
SELECT xs.学号,姓名,课程号,成绩
FROM (SELECT 学号,AVG(成绩) AS score FROM cj GROUP BY 学号) AS avgscore,xs JOIN cj ON(xs.学号=cj.学号)
WHERE xs.学号=avgscore.学号 AND cj.成绩>avgscore.score;
GO
--12.自己验证with check option的作用。
CREATE VIEW CS_stu
AS
SELECT *
FROM xs
WHERE 专业='计算机'
WITH CHECK OPTION;
GO
INSERT
INTO CS_stu
VALUES('100','123','女','1987-05-20','信息管理',10,NULL,NULL);
--试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION
--而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。

GO
--13.创建一个网络工程系的学生基本信息的视图MA_STUDENT，在此视图的基础上，再定义一个该专业女生信息的视图，然后再删除MA_STUDENT，观察执行情况。
CREATE VIEW MA_STUDENT
AS
SELECT *
FROM xs
WHERE 专业='网络工程'
WITH CHECK OPTION;
GO
CREATE VIEW MA_STUDENT_FAMALE
AS
SELECT *
FROM MA_STUDENT
WHERE 性别='女';
GO
DROP VIEW MA_STUDENT;
--对象名 'MA_STUDENT' 无效。
--Msg 4413, Level 16, State 1, Line 10
--由于绑定错误，无法使用视图或函数 'xsgl.dbo.MA_STUDENT_FAMALE'。

--MA_STUDENT_FAMALE还存在，但是不能进行查询操作。

--14.查询和程明同龄的学生的学号和姓名以及年龄。
SELECT 学号,姓名,2021-YEAR(出生时间) AS '年龄'
FROM xs Y
WHERE EXISTS(
	SELECT *
	FROM xs X
	WHERE 姓名='程明' AND YEAR(X.出生时间)=YEAR(Y.出生时间));

--15.查询没有被网络工程的全部学生都选修的课程的课程号和课程名（可以被网络工程专业的部分学生选修）。
SELECT kc.课程号,课程名
FROM kc
WHERE EXISTS(
	SELECT *
	FROM xs X
	WHERE 专业='网络工程' AND NOT EXISTS(
		SELECT *
		FROM cj Y
		WHERE X.学号=Y.学号 AND kc.课程号=Y.课程号)); 
GO
--16.查询选课包含了选英语的全部学生的课程的课程号和课程名。
--句子成分分析：查询 选课 （包含选英语的全部学生的） 的课程号和课程名
--我理解为：查询 选英语的所有学生所选的课程？
CREATE VIEW XS_ENG
AS
SELECT X.学号,Z.课程号,Z.课程名
FROM xs X JOIN cj Y ON(X.学号=Y.学号) JOIN kc Z ON(Y.课程号=Z.课程号)
WHERE EXISTS(
	SELECT *
	FROM xs JOIN cj ON(xs.学号=cj.学号) JOIN kc ON(cj.课程号=kc.课程号)
	WHERE kc.课程名='英语' AND X.学号=xs.学号);
GO
SELECT DISTINCT 课程号,课程名
FROM XS_ENG;


--17.	查询没有选修数据结构、操作系统和英语三门课的学生的学号，姓名，课程号，课程名和成绩。
GO
CREATE VIEW DS_OP_EN
AS
SELECT X.学号
FROM xs X JOIN cj Y ON(X.学号=Y.学号) JOIN kc Z ON(Y.课程号=Z.课程号)
WHERE Z.课程名='数据结构' OR Z.课程名='操作系统' OR Z.课程名='英语';
GO 

SELECT X.学号,姓名,Y.课程号,课程名,成绩
FROM xs X JOIN cj Y ON(X.学号=Y.学号) JOIN kc Z ON(Y.课程号=Z.课程号)
WHERE NOT EXISTS(
	SELECT *
	FROM DS_OP_EN A
	WHERE A.学号=X.学号);

--18.	将没有选课的学生的总学分设置为0。
UPDATE xs
SET 总学分=0
WHERE NOT EXISTS(
	SELECT *
	FROM cj
	WHERE xs.学号=cj.学号);

--二、使用Northwind数据库完成下列操作
--1. 将员工lastname是: Peacock处理的订单中购买数量超过50的商品折扣改为七折。
UPDATE [Order Details]
SET [Order Details].Discount=0.3
WHERE EXISTS(
	SELECT *
	FROM Orders JOIN Employees ON(Orders.EmployeeID=Employees.EmployeeID)
	WHERE Employees.LastName='Peacock' AND Orders.OrderID=[Order Details].OrderID)
	 and Employees.LastName='Peacock' and o1.Quantity>50) and [Order Details].Quantity>50
	 （你怎么少了50这句）


--2. 删除lastname是: Peacock处理的所有订单。
GO 
CREATE VIEW Peacock_Orders
AS
SELECT Orders.OrderID
FROM Orders JOIN Employees ON(Orders.EmployeeID=Employees.EmployeeID)
WHERE Employees.LastName='Peacock';

DELETE
FROM [Order Details]
WHERE EXISTS(
	SELECT *
	FROM Peacock_Orders
	WHERE [Order Details].OrderID=Peacock_Orders.OrderID);

DELETE
FROM Orders
WHERE EXISTS(
	SELECT *
	FROM Peacock_Orders
	WHERE Orders.OrderID=Peacock_Orders.OrderID);


--3. 将每个订单的订单编号，顾客编号，产品总数量，总金额插入到数据库中。
GO
CREATE VIEW Order_data
AS
SELECT Orders.OrderID,CustomerID,SUM(Quantity) AS '产品总数量',SUM(Quantity*UnitPrice*(1-Discount)) AS '总金额'
FROM Orders JOIN [Order Details] ON(Orders.OrderID=[Order Details].OrderID)
GROUP BY Orders.OrderID,CustomerID;
GO
--4. 插入一个新的订单，要求该订单购买了商品编号为5,7,9的商品。（5号商品买了10个，7号买了20个，9号买了15个，都没有折扣）。

SET IDENTITY_INSERT Orders ON
INSERT
INTO Orders(OrderID,EmployeeID)
VALUES (10001,4);


INSERT
INTO [Order Details](OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (10001,'5',20,10,0);
INSERT
INTO [Order Details](OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (10001,'7',20,20,0);
INSERT
INTO [Order Details](OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (10001,'9',20,15,0);



--5. 将每年每个员工处理订单的数量和订单的总金额创建为视图。
GO
CREATE VIEW Yearly_Orders
AS
SELECT YEAR(OrderDate) AS 'year',EmployeeID,COUNT(Orders.OrderID) AS '订单数量',SUM(UnitPrice*Quantity*(1-discount)) AS '总金额'
FROM Orders JOIN [Order Details] ON([Order Details].OrderID=Orders.OrderID)
GROUP BY EmployeeID,YEAR(OrderDate);


--6. 求购买了CustomerID是‘VINET’用户所购买的全部商品的用户的CustomerID和CompanyName。
GO
CREATE VIEW VINET_Products
AS
SELECT [Order Details].ProductID
FROM [Order Details] JOIN Orders ON([Order Details].OrderID=Orders.OrderID)
WHERE Orders.CustomerID='VINET';
GO

SELECT X.CustomerID,CompanyName
FROM Customers X
WHERE NOT EXISTS(
	SELECT *
	FROM VINET_Products A
	WHERE NOT EXISTS(
		SELECT *
		FROM [Order Details] B JOIN Orders C ON(B.OrderID=C.OrderID)
		WHERE A.ProductID=B.ProductID AND X.CustomerID=C.CustomerID));

--7. 将被全部顾客都购买过的商品的商品编号和商品名和单价创建为一个视图proa。
GO
CREATE VIEW proa
AS
SELECT X.ProductID,X.ProductName,X.UnitPrice
FROM Products X
WHERE NOT EXISTS(
	SELECT *
	FROM Customers Y
	WHERE NOT EXISTS(
		SELECT *
		FROM [Order Details] B JOIN Orders C ON(B.OrderID=C.OrderID)
		WHERE X.ProductID=B.ProductID AND Y.CustomerID=C.CustomerID));
GO

--8. 将被全部顾客都购买过的商品的单价加上5元。
UPDATE Products
SET UnitPrice=UnitPrice+5
WHERE EXISTS(
	SELECT *
	FROM proa
	WHERE proa.ProductID=Products.ProductID);

--9. 删除订单总金额少于50元的订单明细。
DELETE
FROM [Order Details]
WHERE EXISTS(
	SELECT *
	FROM [Order Details] A
	WHERE [Order Details].OrderID=A.OrderID
	GROUP BY OrderID
	HAVING SUM(UnitPrice*Quantity*(1-discount))<50);


STERNE

STERNE

SDUer，大一入门，大二入土，大三老东西！

63
1
20
NOTICE

这里还什么都没有呢喵~

CATEGORYS
TAGS
ACM Arc CCPC HPC bot c cpp deeplearning games hexo java maths pyqt python qt recommends review sql srtp test