Catalog
数据库实验三
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
325
326
327
328
329
330
331
332
333
334
335
336
337
--一.对xsgl数据库完成以下操作
--1.查询没有选修英语的学生的学号,姓名和课程号,课程名,成绩
select xs.学号,xs.姓名,kc.课程号,kc.课程名,cj.成绩
from xs,kc,cj
where xs.学号 = cj.学号 and kc.课程号 = cj.课程号 and kc.课程名 <> '英语'

--2.查询英语成绩高于英语的平均成绩的学生的学号,姓名,成绩
select xs.学号,xs.姓名,cj.成绩
from xs,cj,kc
where xs.学号 = cj.学号 and cj.课程号 = kc.课程号 and kc.课程名 = '英语' and cj.成绩 >
(select avg(cj.成绩)
from kc,cj
where cj.课程号 = kc.课程号 and kc.课程名 = '英语')

--3.查询选修了英语和高数(数学)的学生的学号和姓名(要求使用两种方法实现)
select xs.学号,xs.姓名
from xs,cj,kc
where xs.学号 = cj.学号 and cj.课程号 = kc.课程号 and kc.课程名 = '英语' and cj.学号 in
(select cj.学号
from cj,xs,kc
where xs.学号 = cj.学号 and cj.课程号 = kc.课程号 and kc.课程名 = '数学')

select xs.学号,xs.姓名
from xs,cj,kc
where xs.学号 = cj.学号 and cj.课程号 = kc.课程号 and 课程名 = '英语'
and xs.学号 in
(select 学号
from cj
where 课程号 =
(select 课程号
from kc
where 课程名 = '数学'))

--4.查询没有选修程明所选修的全部课程的学生的姓名
select xs.姓名
from xs,cj
where xs.学号 = cj.学号 and cj.课程号 not in
(select cj.课程号
from xs,cj
where xs.学号 = cj.学号 and xs.姓名 = '程明')

--5.查询每个专业年龄超过该专业平均年龄的学生的姓名和专业
select xs1.姓名,xs1.专业
from xs xs1
where datediff(yy,xs1.出生时间,getdate()) >
(select avg(datediff(yy,xs2.出生时间,getdate()))
from xs xs2
where xs1.专业 = xs2.专业)

--6.查询每个专业每门课程的专业,课程号,课程名,选课人数,平均分和最高分
select xs.专业,kc.课程号,kc.课程名,count(*) 选课人数,avg(cj.成绩) 平均分,max(cj.成绩) 最高分
from xs,kc,cj
where xs.学号 = cj.学号 and kc.课程号 = cj.课程号
group by kc.课程号,kc.课程名,xs.专业

--7.查询每个学生取得最高分的课程的课程号,课程名和成绩
select cj.学号,kc.课程号,kc.课程名,cj.成绩
from kc,cj
where kc.课程号 = cj.课程号 and cj.成绩 >= all
(select cj2.成绩
from cj cj2
where cj2.学号 = cj.学号)

--8.查询每个专业年龄最高的学生的学号,姓名,专业和年龄
select xs.学号,xs.姓名,xs.专业,datediff(yy,xs.出生时间,getdate()) 年龄
from xs
where datediff(yy,xs.出生时间,getdate()) >= all
(select datediff(yy,xs2.出生时间,getdate())
from xs xs2
where xs2.专业 = xs.专业)

--9.查询没有选修数据结构和操作系统的学生的学号和姓名
select xs.学号,xs.姓名
from xs
where xs.学号 not in
(select cj.学号
from cj
where cj.课程号 in
(select kc.课程号
from kc
where kc.课程名 = '数据结构' or kc.课程名 = '操作系统'))

--10.查询网络工程专业年龄最小的学生的学号和姓名
select xs.学号,xs.姓名
from xs
where xs.专业 = '网络工程' and datediff(yy,xs.出生时间,getdate()) <= all
(select datediff(yy,xs2.出生时间,getdate())
from xs xs2
where xs2.专业 = '网络工程')

--11.查询选课人数超过5人的课程的课程号,课程名和成绩
select kc.课程号,kc.课程名,cj.成绩
from kc,cj
where kc.课程号 = cj.课程号 and cj.课程号 in
(select cj2.课程号
from cj cj2
group by cj2.课程号
having count(cj2.学号) > 5)

--12.查询选修了全部课程的学生的学号和姓名(用两种方法实现)
select xs.学号,xs.姓名
from xs
where not exists
(select *
from kc
where not exists
(select *
from cj
where cj.学号 = xs.学号 and cj.课程号 = kc.课程号))

select xs.学号,xs.姓名
from xs
where xs.学号 in
(select cj.学号
from cj
group by cj.学号
having count(*) =
(select count(*)
from kc))

--13.查询选课人数最多的课程号和课程名(包含并列)
select cj.课程号,kc.课程名
from kc,cj
where kc.课程号 = cj.课程号
group by cj.课程号,kc.课程名
having count(*) >= all
(select count(*)
from cj cj2
group by cj2.课程号)

--14.查询选修了程明所选修的全部课程的学生的姓名
select distinct xs.姓名
from xs,cj
where xs.学号 = cj.学号 and not exists
(select *
from cj cj2,xs xs2
where xs2.学号 = cj2.学号 and xs2.姓名 = '程明' and not exists
(select *
from cj cj3
where cj3.学号 = cj.学号 and cj 3.课程号 = cj2.课程号))


--二.对罗斯文数据库完成一下查询
--15.查询每个订单购买产品的数量和总金额,显示订单号,数量,总金额
select [Order Details].OrderID,sum([Order Details].Quantity) 数量,sum([Order Details].UnitPrice*[Order Details].Quantity*(1-[Order Details].Discount)) 总金额
from [Order Details]
group by [Order Details].OrderID

--16.查询每个员工在7月份处理订单的数量
select count(Orders.OrderID) 订单数量
from Orders right join Employees on (Employees.EmployeeID = Orders.EmployeeID)
where month(Orders.OrderDate) = '07'
group by Employees.EmployeeID

--17.查询每个顾客的订单总数,显示顾客ID,订单总数
select Customers.CustomerID,count(Orders.OrderID) 订单总数
from Orders right join Customers on (Customers.CustomerID = Orders.CustomerID)
group by Customers.CustomerID

--18.查询每个顾客的订单总数和订单总金额
select Customers.CustomerID,count(distinct Orders.OrderID) 订单总数,sum([Order Details].UnitPrice*[Order Details].Quantity*(1-[Order Details].Discount)) 总金额
from Orders right join Customers on Orders.CustomerID=Customers.CustomerID
left join [Order Details] on Orders.OrderID=[Order Details].OrderID
group by Customers.CustomerID

--19.查询每种产品的卖出总数和总金额
select sum([Order Details].Quantity) 卖出总数,sum([Order Details].UnitPrice*[Order Details].Quantity*(1-[Order Details].Discount)) 总金额
from [Order Details] right join Products on ([Order Details].ProductID = Products.ProductID)
group by Products.ProductID

--20.查询购买过全部商品的顾客的ID和姓名
select Customers.CustomerID,Customers.ContactName
from Customers
where not exists
(select *
from Products
where not exists
(select *
from Orders,[Order Details]
where Orders.OrderID = [Order Details].OrderID and Products.ProductID = [Order Details].ProductID and Customers.CustomerID = Orders.CustomerID))

--三.对books数据库完成以下操作
--21.查询各种类别的图书的类别和数量(包含目前没有图书的类别)
select BookType.TypeName,count(BookInfo.TypeID) 数量
from BookType left join BookInfo on (BookType.TypeID = BookInfo.TypeID)
group by BookType.TypeName

--22.查询借阅了‘数据库基础’的读者的卡编号和姓名
select BorrowInfo.CardNo,CardInfo.Reader
from BorrowInfo,CardInfo,BookInfo
where BorrowInfo.BookNo = BookInfo.BookNo and BorrowInfo.CardNo = CardInfo.CardNo and BookInfo.BookName = '数据库基础'

--23.查询各个出版社的图书价格超过这个出版社图书的平均价格的图书的编号和名称
select BookInfo.BookNo,BookInfo.BookName
from BookInfo
where BookInfo.Price >
(select avg(b1.Price)
from BookInfo b1
where BookInfo.Publisher = b1.Publisher)

--24.查询没有借过图书的读者的编号和姓名
select CardInfo.CardNo,CardInfo.Reader
from CardInfo
where CardInfo.CardNo not in
(select BorrowInfo.CardNo
from BorrowInfo)

--25.查询借阅次数超过2次的读者的编号和姓名
select CardInfo.CardNo,CardInfo.Reader
from CardInfo,BorrowInfo
where CardInfo.CardNo = BorrowInfo.CardNo
group by CardInfo.CardNo,CardInfo.Reader
having count(BorrowInfo.CardNo) > 2

--26.查询借阅卡的类型为老师和研究生的读者人数
select count(CardInfo.CardNo) 读者人数
from CardType,CardInfo
where CardInfo.CTypeID = CardType.CTypeID and (CardType.TypeName = '教师' or CardType.TypeName = '研究生')

--27.查询没有被借过的图书的编号和名称
select BookInfo.BookNo,BookInfo.BookName
from BookInfo
where BookInfo.BookNo not in
(select BorrowInfo.BookNo
from BorrowInfo)

--28.查询没有借阅过英语类型的图书的学生的编号和姓名
select CardInfo.CardNo,CardInfo.Reader
from CardInfo
where not exists
(select *
from BookInfo,BorrowInfo,BookType,CardType
where BookType.TypeName='英语' and CardType.TypeName = '学生' and CardType.CTypeID = CardInfo.CTypeID and BookType.TypeID=BookInfo.TypeID and BookInfo.BookNo=BorrowInfo.BookNo and BorrowInfo.CardNo=CardInfo.CardNo)

--29.查询借阅了‘计算机应用’类别的‘数据库基础’课程的学生的编号读者以及该读者的借阅卡的类型
select CardInfo.CardNo,CardType.TypeName
from CardInfo,CardType,BorrowInfo
where CardInfo.CTypeID = CardType.CTypeID and BorrowInfo.CardNo = CardInfo.CardNo and BorrowInfo.BookNo in
(select BookInfo.BookNo
from BookInfo
where BookInfo.BookName = '数据库基础')

--30.查询借阅过了全部图书的读者的编号和姓名
select CardInfo.CardNo,CardInfo.Reader
from CardInfo
where not exists
(select *
from BookInfo
where not exists
(select *
from BorrowInfo
where BorrowInfo.CardNo = CardInfo.CardNo and BorrowInfo.BookNo = BookInfo.BookNo))

--四.对商场数据库完成以下操作
--Market (mno, mname, city)
--Item (ino, iname, type, color)
--Sales (mno, ino, price)
--其中,market表示商场,它的属性依次为商场号、商场名和所在城市;item表示商品,它的属性依次为商品号、商品名、商品类别和颜色;sales表示销售,它的属性依次为商场号、商品号和售价。用SQL语句实现下面的查询要求:
--1.列出北京各个商场都销售,且售价均超过10000 元的商品的商品号和商品名
select item.ino,item.iname
from item
where not exists
(select *
from sales,market
where sales.mno = market.mno and market.city = '北京' and not exists
(select *
from sales s1
where s1.ino = item.ino and s1.mno = sales.mno and s1.price > 10000))

--2.列出在不同商场中最高售价和最低售价只差超过100 元的商品的商品号、最高售价和最低售价
select sales.ino,max(sales.price) 最高售价,min(sales.price) 最低售价
from sales
group by sales.ino
having max(sales.price) - min(sales.price) > 100

--3.列出售价超过该商品的平均售价的各个商品的商品号和售价
select sales.ino,sales.price
from sales
where sales.price >
(select avg(s2.price)
from sales s2
where s2.ino = sales.ino)

--4.查询每个每个城市各个商场售价最高的商品的商场名,城市,商品号和商品名
select market.mname,market.city,sales.ino,item.iname
from market,sales,item
where market.mno = sales.mno and item.ino = sales.ino and sales.price >= all
(select s2.price
from sales s2
where s2.mno = sales.mno)

--5.查询销售商品数量最多的商场的商场号,商场名和城市
select market.mno,market.mname,market.city
from market,sales
where market.mno = sales.mno
group by market.mno,market.mname,market.city
having count(sales.ino) >= all
(select count(s2.ino)
from sales s2
group by s2.mno)

--6.查询销售了冰箱和洗衣机的商场号,商场名和城市
select distinct market.mno,market.mname,market.city
from market,sales
where market.mno in
(select s1.mno
from sales s1
where s1.ino in
(select item.ino
from item
where item.iname = '冰箱')
and s1.mno in
(select s2.mno
from sales s2
where s2.ino in
(select item.ino
from item
where item.iname = '洗衣机')))

--7.查询所有商场都销售了的商品的商品号和商品名。(用两种方法实现)
select item.ino,item.iname
from item
where not exists
(select *
from market
where not exists
(select *
from sales
where sales.mno = market.mno and sales.ino = item.ino))

select item.ino,item.iname
from item,sales
where item.ino = sales.ino
group by sales.ino, item.ino,item.iname
having count(sales.mno) =
(select count(market.mno)
from market)
Author: Christopher Shen
Link: https://www.pasxsenger.com/2019/10/22/数据库实验三/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
Donate
  • 微信
  • 支付寶