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
--一、对xsgl数据库完成下列操作要求:
use xsgl
--1.将被全部学生都选修了的课程的总学分改为4学分
update kc set kc.学分 = 4
where kc.课程号 in
(select kc.课程号
from kc
where not exists
(select *
from xs
where not exists
(select *
from cj
where cj.课程号 = kc.课程号 and cj.学号 = xs.学号)))

--2.从学生表删除没有选课的学生
delete from xs
where xs.学号 not in
(select cj.学号
from cj)

--3.将每个学生的平均分,总分和选课门数插入到数据库中(学号,姓名,平均分,总分,选课门数)
create table new(
学号 char(10),
姓名 nchar(10),
平均分 numeric(10,2),
总分 numeric(10,2),
选课门数 smallint)
insert into new
select xs.学号,xs.姓名,avg(cj.成绩),sum(cj.成绩),count(cj.课程号)
from xs left join cj on xs.学号=cj.学号
group by xs.学号,xs.姓名

--4.创建每门课程的平均分和选课人数的视图(课程号,课程名,平均分,人数)
create view Course(课程号,课程名,平均分,人数)
as
select kc.课程号,kc.课程名,avg(cj.成绩) 平均分,count(cj.学号) 人数
from kc left join cj on (kc.课程号 = cj.课程号)
group by kc.课程号,kc.课程名

--5.将李强同学从学生表删除(提示应该先删除李强同学的选课记录)
delete from cj
where cj.学号 =
(select xs.学号
from xs
where xs.姓名 = '李强')
delete from xs
where xs.姓名 = '李强'

--6.插入一条选课记录(具体内容自己选)
insert
into xs
values('2018008003','彭于晏','男',null,null,null,null,null)
insert
into cj
values('2018008003','A001',null)

--7.创建网络工程专业的学生的选课信息的视图,要求视图包含,学号,姓名,专业,课程号,课程名,成绩
create view IT(学号,姓名,专业,课程号,课程名,成绩)
as
select xs.学号,xs.姓名,xs.专业,kc.课程号,kc.课程名,cj.成绩
from xs left join cj on xs.学号 = cj.学号
right join kc on cj.课程号 = kc.课程号
where xs.专业 = '网络工程'

--8.查询网络工程专业的各科的平均成绩,要求使用第7题创建的视图进行查询
select IT.课程号,avg(IT.成绩) 平均成绩
from IT
group by IT.课程号

--9.查询被信息管理专业的学生都选修了的课程的课程号,课程名
select kc.课程号,kc.课程名
from kc
where not exists
(select *
from xs
where xs.专业 = '信息管理' and not exists
(select *
from cj
where cj.学号 = xs.学号 and cj.课程号 = kc.课程号))

--10.显示选修课程数最多的学号及选修课程数最少的学号,姓名(使用派生表实现)
select xs.学号,xs.姓名,count(*) 门数
from xs,cj,(select top 1 count(*)
from cj
group by cj.学号
order by count(*) desc) Maxx(门数),
(select top 1 count(*)
from cj
group by cj.学号
order by count(*)) Minn(门数)
where cj.学号 = xs.学号
group by xs.学号,xs.姓名,Maxx.门数,Minn.门数
having count(*) = Maxx.门数 or count(*) = Minn.门数

--11.查询每个学生成绩高于自己的平均成绩的学号,姓名,课程号和成绩(使用派生表实现)
select xs.学号,xs.姓名,cj.课程号,cj.成绩
from xs,cj,(select cj1.学号,avg(cj1.成绩) 平均成绩
from cj cj1
group by cj1.学号) avg_grade
where cj.学号 = avg_grade.学号 and xs.学号 = cj.学号 and cj.成绩 > avg_grade.平均成绩

--12.自己验证with check option的作用
create view IE
as
select xs.学号,xs.姓名,xs.性别,xs.专业
from xs
where xs.专业 = '网络工程'
with check option

insert into IE(学号,姓名)
values('2018008002','吴彦祖')

--13.创建一个网络工程系的学生基本信息的视图MA_STUDENT,在此视图的基础上,再定义一个该专业女生信息的视图,然后再删除MA_STUDENT,观察执行情况。
create view MA_STUDENT
as
select *
from xs
where xs.专业 = '网络工程'
with check option

create view Female
as
select *
from MA_STUDENT
where MA_STUDENT.性别 = '女'

drop view MA_STUDENT

--14.查询和程明同龄的学生的学号和姓名以及年龄
select xs.学号,xs.姓名,datediff(yy,xs.出生时间,getdate()) 年龄
from xs
where datediff(yy,xs.出生时间,getdate()) = (select datediff(yy,xs2.出生时间,getdate())
from xs xs2
where xs2.姓名 = '程明')

--15.查询没有被全部的学生都选修的课程的课程号和课程名
select kc.课程号,kc.课程名
from kc
where exists
(select *
from xs
where not exists
(select *
from cj
where cj.学号 = xs.学号 and cj.课程号 = kc.课程号))

--16.查询选课学生包含了选英语的全部学生的课程的课程号和课程名
select kc.课程号,kc.课程名
from kc
where not exists
(select *
from cj
where cj.课程号 = (select kc.课程号
from kc
where kc.课程名 = '英语')
and not exists
(select *
from cj c
where c.学号 = cj.学号 and c.课程号 = kc.课程号))

--二、使用Northwind数据库完成下列操作
use Northwind
--1. 将员工lastname是: Peacock处理的订单中购买数量超过50的商品折扣改为七折
update [Order Details]
set Discount = 0.3
where [Order Details].Quantity > 50 and [Order Details].OrderID in
(select O1.OrderID
from [Order Details] O1,Employees,Orders
where Employees.EmployeeID = Orders.EmployeeID and Orders.OrderID = O1.OrderID and Employees.LastName = 'Peacock' and O1.Quantity > 50)

--2. 删除lastname是: Peacock处理的所有订单
delete
from [Order Details]
where [Order Details].OrderID in
(select distinct O1.OrderID
from [Order Details] O1,Employees,Orders
where O1.OrderID = Orders.OrderID and Orders.EmployeeID = Employees.EmployeeID and Employees.LastName = 'Peacock')

delete
from Orders
where Orders.EmployeeID =
(select Employees.EmployeeID
from Employees
where Employees.LastName = 'Peacock')

--3. 将每个订单的订单编号,顾客编号,产品总数量,总金额插入到数据库中
create table OrderInfo(
订单编号 int,
顾客编号 nchar(5),
产品总数量 smallint,
总金额 money)

insert
into OrderInfo
select Orders.OrderID,Orders.CustomerID,sum([Order Details].Quantity) 数量,sum([Order Details].UnitPrice*[Order Details].Quantity*(1- [Order Details].Discount)) 金额
from [Order Details],Orders
where [Order Details].OrderID = Orders.OrderID
group by Orders.OrderID,Orders.CustomerID

--4. 插入一个新的订单,要求该订单购买了商品编号为5,7,9的商品。(5号商品买了10个,7号买了20个,9号买了15个,都没有折扣)
insert
into [Order Details]
values('10325','5', '50', '10', '0')
insert into [Order Details]
values('10325','7', '70', '20', '0')
insert into [Order Details]
values('10325','9', '90', '15', '0')

--5. 将每年每个员工处理订单的数量和订单的总金额创建为视图
create view Employ
as
select Employees.EmployeeID,datename(yy,Orders.OrderDate) 年份,
count(distinct Orders.OrderID) 订单数量,sum([Order Details].Quantity) 数量,
sum([Order Details].Quantity*[Order Details].UnitPrice*(1-[Order Details].Discount)) 订单总金额
from Employees left join Orders on Employees.EmployeeID=Orders.EmployeeID
left join [Order Details] on Orders.OrderID=[Order Details].OrderID
group by Employees.EmployeeID,datename(yy,Orders.OrderDate)

--6. 购买了CustomerID是‘VINET’用户所购买的全部商品的用户的CustomerID和CompanyName。
select distinct Customers.CustomerID,Customers.CompanyName
from Customers,Orders
where Customers.CustomerID = Orders.CustomerID and not exists
(select *
from Orders o2,Customers c2
where o2.CustomerID = c2.CustomerID and o2.CustomerID = 'VINET' and not exists
(select *
from Orders o3
where o3.CustomerID = Orders.CustomerID))
Author: Christopher Shen
Link: https://www.pasxsenger.com/2019/10/24/数据库实验四/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
Donate
  • 微信
  • 支付寶