### 在数据库supermarket上完成以下实验内容。
1、完成教材的例3-77~例3-88的操作。#
建立咖啡类商品的视图#
1
2
3
4
5
| create view Coffee
as
select GoodsNO,GoodsName,InPrice,SalePrice,ProductTime
from Goods G join Category C on G.CategoryNO = C.CategoryNO
where CategoryName = '咖啡'
|
建立MIS专业学生的视图，并要求通过试图完成修改与插入操作时视图仍只有MIS专业学生#
1
2
3
4
5
| create view MIS_student
as
select * from Student
where Major = 'MIS'
with check option
|
建立购买了咖啡类商品的学生视图#
1
2
3
4
5
6
| create view Buy_coffee
as
select * from Student where exists(
select * from SaleBill SA join Coffee C
on SA.GoodsNO=C.GoodsNO
where SA.SNO=Student.SNO)
|
建立保存商品编号与销售额的视图#
1
2
3
4
5
6
| create view SumSale(GoodsNO,SumSale)
as
select G.GoodsNO,SUM(SalePrice * S.Number) SumSale
from SaleBill S join Goods G
on S.GoodsNO = G.GoodsNO
group by G.GoodsNO
|
建立销售额前5的商品视图#
1
2
3
4
5
6
7
| create view Top5SumSale(GoodsNO,SumSale)
as
select top 5 G.GoodsNO,SUM(SalePrice * S.Number) SumSale
from SaleBill S join Goods G
on S.GoodsNO = G.GoodsNO
group by G.GoodsNO
order by SumSale desc
|
删除视图Coffee#
查询MIS专业购买了咖啡类商品的学生信息#
1
2
3
4
5
| select * from Student where Major = 'MIS'
and exists(
select * from SaleBill S join Coffee C
on C.GoodsNO = S.GoodsNO
where S.SNO = Student.SNO)
|
查询销售额前5商品的供应商编号#
1
2
| select top 5 SupplierNO from SumSale T
join Goods G on T.GoodsNO = G.GoodsNO
|
查询销售额大于100的供应商编号#
1
2
3
4
5
| select G.GoodsNO,SUM(SalePrice * S.Number) SumSale
from SaleBill S join Goods G
on S.GoodsNO = G.GoodsNO
group by G.GoodsNO
having SUM(SalePrice * S.Number)>100
|
在Buy_coffee视图中插入一个新的学生信息，其中学号为S09，姓名为程伟，出生年份为1993，其余为空#
1
| insert into Buy_coffee(SNO,SName,BirthYear) values('S09','程伟',1993)
|
将视图MIS_student中姓名为“李明”的学生微信更改为“LiMing”#
1
| update MIS_student set WeiXin = 'LiMing' where SName = '李明'
|
将视图MIS_student中姓名为“闵红”的学生元组删除#
1
| delete from MIS_student where SName = '闵红'
|
2、写出创建满足下还要求的视图的SQL语句。#
(1)统计每个学生的消费金额。
1
2
3
4
5
6
7
8
9
10
11
| create view ExpenseOfStudent as
(
select S.SNO,SName,SUM(SalePrice * SA.Number) 消费
from Student S join SaleBill SA
on S.SNO = SA.SNO
join Goods G on SA.GoodsNO = G.GoodsNO
group by S.SNO,SName
);
go
select * from ExpenseOfStudent
|
(2)统计每个供货商提供的商品种类(一个商品编号代表一种)。
1
2
3
4
5
6
7
8
9
| create view Goods_type as
(
select S.SupplierNO,SupplierName,count(S.SupplierNO) 商品种类数量
from Supplier S
join Goods G on S.SupplierNO = G.SupplierNO
group by S.SupplierNO,SupplierName
);
go
select * from Goods_type
|
(3)统计各商品种类的销售数量及平均售价。
1
2
3
4
5
6
7
8
9
10
| create view Goods_Sale as
(
select C.CategoryNO,C.CategoryName,SUM(G.Number) 销售数量,AVG(G.SalePrice) 平均售价
from Goods G
join Category C on G.CategoryNO = C.CategoryNO
group by C.CategoryNO,C.CategoryName
);
go
select * from Goods_Sale
|
(4)建立Sup001供货商的商品信息视图，并要求通过视图完成修改与插入操作时视图仍只有Sup001供货商的商品。
1
2
3
4
5
6
7
8
9
10
11
| create view Sup001_Supplier
as
select S.SupplierNO,S.SupplierName,G.GoodsNO,G.GoodsName,G.InPrice,G.SalePrice,G.ProductTime,SA.Number ,SA.SNO
from Goods G
join Supplier S on G.SupplierNO = S.SupplierNO
join SaleBill SA on G.GoodsNO = SA.GoodsNO
where S.SupplierNO = 'Sup001'
with check option
go
select * from Sup001_Supplier
|
3、利用上述视图，完成如下任务。#
(1)统计每个MIS专业学生的消费金额。
1
2
3
4
| select S.SName,S.Major,SUM(ep.消费) 消费总额 from ExpenseOfStudent ep
join Student S on ep.SNO = S.SNO
where S.Major = 'MIS'
group by S.SName,S.Major
|
(2)查询售价低于该商品种类售价平均价的商品名和售价。
1
2
3
| select G.GoodsName,G.SalePrice from Goods G
join Goods_Sale GS on G.CategoryNO = GS.CategoryNO
where G.SalePrice < GS.平均售价
|
(3)利用第4题(4)中的视图插入供货商Sup002的商品信息，结果如何?为什么?
1
2
| 插入失败，原因是因为前面创建视图的时候规定了该视图在修改与插入的操作时，
视图仍只有Sup001供货商的商品
|
(4)利用第4题(4)中的视图删除GN0004的商品信息，结果如何?为什么?
1
2
| 删除成功，因为前面创建视图的时候规定了该视图在修改与插入的操作时，视图仍只有Sup001供货商的商品，
但是对删除的权限没有规定，而且视图内有GN0004的商品信息
|
(5)查询供货种类大于等于2的供货商的名称及数量。
1
| select SupplierName,GT.商品种类数量 from Goods_type GT where GT.商品种类数量 >= 2
|
