【Sql Server】实验报告五：SQL数据更新_添加新品“gn0011 sup002 cn001 乐至三合一咖啡 12. 30 17. 30 100

一、实验目的

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)
 
)

• 实验结果

1. 
2. 3.

4.

5.

6.