实验七 SQL数据更新和视图

题目

（1）向商品类别表category中插入一条记录（801，‘座椅套’，‘各种品牌的汽车座套’）

（2）向商品表product中插入一条记录：商品编号80101，商品名称“四季通用汽车座套”，库存数量100，单价308，成本价288，商品类别编号801，上架时间“2018-01-01”）

将商品表product中对每个班，求学生的平均年龄，并把结果存入数据库。

（3）查询订单日期为“2021-11-11”的每种商品的销售总数量，并将查询结果录入到表sale中

（4）将供应商名称为“顺京通讯有限公司”的联系电话修改为“010-4358779”

（5）将商品类别名称为“激光打印机”的所有商品的单价降低10%

（6）商品80101已经下架，从商品表中删除其详细记录信息

（7）将会员周晔的所有订单中付款日期paydate为空的订单信息全部删除

（8） 建立会员订购商品视图view_buy，要求视图属性包括商品编号prono、商品名称proname、单价price、订单编号orderno、购买数量qty、优惠discount、实付总金额totalmoney，并且基于该视图修改属性totalmoney的值为：单价*购买数量-优惠

（9）基于商品表和供应商表创建一个名为view_renbaocomputer的视图，要求能够通过该视图查看供应商名称为“仁宝电脑有限公司”的所有商品信息：包括商品编号pronno、商品名称proname、库存stock、单价price、成本价、供应商名称supname、联系电话telephone信息

（10）基于商品表product创建一个视图view_product，要求视图总包括prono、proname、stock、price、cost、catno信息。然后通过该视图完成以下操作：

通过视图view_product添加一条记录：（60102，‘海信37英寸激光电视机’，15，1600，1300，601）

通过视图view_product修改商品编号为60201的价格为2499

通过视图view_product删除商品编号为60201的商品

步骤

1. 向商品类别表category中插入一条记录（801，‘座椅套’，‘各种品牌的汽车座套’）

INSERT INTO  dbo.category(catno, catname, describe)

VALUES (801, '座椅套', '各种品牌的汽车座套');

（2）向商品表product中插入一条记录：商品编号80101，商品名称“四季通用汽车座套”，库存数量100，单价308，成本价288，商品类别编号801，上架时间“2018-01-01”）

将商品表product中对每个班，求学生的平均年龄，并把结果存入数据库。

INSERT INTO  dbo.product(prono, proname, stock, price, cost, catno, ontime)

VALUES (80101, '四季通用汽车座套', 100, 308, 288, 801, '2018-01-01');

（3）查询订单日期为“2021-11-11”的每种商品的销售总数量，并将查询结果录入到表sale中

create table sale(prono int,total_quantity int)

INSERT INTO sale (prono, total_quantity)

SELECT prono, SUM(memno) AS total_quantity

FROM dbo.orders

WHERE  orderdate= '2021-11-11'

GROUP BY prono;

（4）将供应商名称为“顺京通讯有限公司”的联系电话修改为“010-4358779”

UPDATE dbo.supplier

SET telephone = 010-4358779

WHERE supname = '顺京通讯有限公司';

1. 将商品类别名称为“激光打印机”的所有商品的单价降低10%

UPDATE dbo.product

SET price = price * 0.9

WHERE catno = (

    SELECT catno

    FROM dbo.category

    WHERE catname = '激光打印机'

);

（6）商品80101已经下架，从商品表中删除其详细记录信息

delete from dbo.product where prono=80101

1. 将会员周晔的所有订单中付款日期paydate为空的订单信息全部删除

DELETE FROM dbo.orders

WHERE memno = (SELECT memno FROM dbo.member WHERE memname = '周晔') AND paydate IS NULL;

（8） 建立会员订购商品视图view_buy，要求视图属性包括商品编号prono、商品名称proname、单价price、订单编号orderno、购买数量qty、优惠discount、实付总金额totalmoney，并且基于该视图修改属性totalmoney的值为：单价*购买数量-优惠
CREATE VIEW  view_buy AS 

SELECT 

    p.prono,

    p.proname,

    p.price,

    o.orderno,

    o.qty,

    o.discount,

    (p.price * o.qty - o.discount) AS totalmoney

FROM 

     dbo.orders o

JOIN 

dbo.product p ON o.prono = p.prono;

（9）基于商品表和供应商表创建一个名为view_renbaocomputer的视图，要求能够通过该视图查看供应商名称为“仁宝电脑有限公司”的所有商品信息：包括商品编号pronno、商品名称proname、库存stock、单价price、成本价、供应商名称supname、联系电话telephone信息
CREATE VIEW view_renbaocomputer AS

SELECT 

    p.prono,

    p.proname,

    p.stock ,

    p.price,

    p.cost,

    s.supname,

    s.telephone

FROM 

    dbo.product p

JOIN 

    dbo.supplier s ON p.supno = s.supno

WHERE 

s.supname = '仁宝电脑有限公司';

（10）基于商品表product创建一个视图view_product，要求视图总包括prono、proname、stock、price、cost、catno信息。然后通过该视图完成以下操作：

通过视图view_product添加一条记录：（60102，‘海信37英寸激光电视机’，15，1600，1300，601）

通过视图view_product修改商品编号为60201的价格为2499

通过视图view_product删除商品编号为60201的商品

 CREATE VIEW view_product AS

SELECT prono, proname, stock AS stock, price, cost, catno

FROM dbo.product;

 INSERT INTO view_product (prono, proname, stock, price, cost, catno)

VALUES (60102, '海信英寸激光电视机', 15, 1600, 1300, 601);

UPDATE view_product

SET price = 2499

WHERE prono = 60201;

DELETE FROM view_product

WHERE prono = 60201;