一、实验目的
1.掌握插入数据、删除数据、修改数据。
2.掌握使用子查询插入数据、更新数据。
二、实验内容
操作系统:Windows 10
数据库管理系统:SQL Server 2017
参考的是教材P84页的“实验3-4 数据更新”的内容
- 实验过程
use SuperMarket;
1.添加新品“GN0011 Sup002 CN001 乐至三合一咖啡 12.30 17.30 100 2018-11-12 18”
insert into Goods values('GN0011','Sup002','CN001','乐至三合一咖啡',12.30,17.30,100,'2018-11-12 00:00:00',18);
2.先建立一张新表,使用子查询将各月的销售额插入该表,存储月份及销售额
create table MonthSale(
月份 char(7),
销售额 decimal(18,2)
)
insert into MonthSale
select MONTH(HappenTime) 月份,sum(s.Number*g.SalePrice) 销售额 from SaleBill s
join Goods g on g.GoodsNO=s.GoodsNO
group by MONTH(HappenTime)
3.使用子查询将各学生的购买额插入新表,由系统自建新表,存储学生学号、姓名、销售额
select b. * into StudentSale from (
select st.SNO,st.SName,SUM(s.Number*g.SalePrice) sale from student st
join salebill s on st.SNO = s.SNO
join goods g on s.GoodsNO = g.GoodsNO
group by st.SNO,st.SName) b
4.将所有商品存量增加2
UPDATE Goods set Number = Number + 2;
5.将保质期还有30天的商品价格打8折
UPDATE goods set SalePrice = SalePrice*0.8 where QGPeriod <= 30;
6.分别使用子查询方式与连接方式将广州地区供货商的商品加价10%
--子查询
UPDATE Goods set InPrice = (1+0.1)*InPrice where SupplierNO in(
select SupplierNO from supplier where Address like '广州%'
)
--连接查询
update Goods set SalePrice = SalePrice * 1.1
from Supplier S join Goods G on S.SupplierNO = G.SupplierNO
where Address like '广州%'
7.将销售额后两位的商品下架
delete from Goods where GoodsNO in (
select GoodsNO from (select top 2 g.GoodsNO,g.GoodsName,SUM(g.SalePrice*s.Number) sale from Goods g
join SaleBill s on g.GoodsNO = s.GoodsNO
group by g.GoodsNO,g.GoodsName
order by sale asc) gg)
8.删除销售额最小的供应商信息
delete from Supplier where SupplierNO in(
select top 1 S.SupplierNO from Supplier S
join Goods G on S.SupplierNO = G.SupplierNO
join SaleBill SA on G.GoodsNO = SA.GoodsNO
group by S.SupplierNO
order by SUM(SalePrice * SA.Number)
)
- 实验结果
3.
4.
5.
6.