数据库5/8

(1)
CREATE VIEW V_DLMU_PARTSUPP1 AS
SELECT p.partkey,p.name,ps.availqty,p.retailprice,ps.supplycost,p.comment
FROM part p,partsupp ps,supplier s
WHERE p.partkey=ps.partkey AND s.suppkey=ps.suppkey AND s.name=‘海大汽配’;

(2)
CREATE VIEW V_CustAvgOrder(custkey,cname,avgprice,avgqusntity) AS
SELECT c.custkey,MAX(c.name),AVG(o.totalprice),AVG(l.quantity)
FROM customer c,orders o,lineitem l
WHERE c.custkey=o.custkey and l.orderkey=o.orderkey
GROUP BY c.custkey;
(3)
CREATE VIEW v_dlmu_partsupp2 AS
SELECT partkey,suppkey,availqty,supplycost
FROM partsupp
WHERE suppkey = (SELECT suppkey
FROM supplier
WHERE name = ‘海大汽配’)
WITH CHECK OPTION;

INSERT INTO v_dlmu_partsupp2
VALUES(58889,5048,704,77760);

UPDATE v_dlmu_partsupp2
set supplycost = 12
WHERE suppkey = 58889;

DELETE FROM v_dlmu_patsupp2
WHERE suppkey = 58889;

1.


（4）
1.CREATE VIEW v_dlmu_partsupp3
AS
SELECT partkey,suppkey,availqty,supplycost
FROM partsupp
WHERE suppkey = (SELECT suppkey
FROM supplier
WHERE name = ‘海大汽配’);

2.INSERT INTO v_dlmu_partsupp3
values(58889,5048,704,77760);
3.UPDATE v_dlmu_partsupp3
SET supplycost = 12
WHERE suppkey = 58889;
4.DELETE FROM v_dlmu_partsupp3
WHERE suppkey = 58889;
（5）INSERT into v_custavgorder
VALUES (100000,NULL,20,2000);
（6）CREATE VIEW v_custord(custkey,cname ,qty,extprice)
AS
SELECT c.custkey,c.name,l.quantity,l.extendedprice
FROM customer c,orders o,lineitem l
WHERE c.custkey = o.custkey AND o.orderkey = l.orderkey;

CREATE VIEW v_custavgorder(custkey,cname,avgqty,avgprice)
as
SELECT custkey,MAX(cname),AVG(qty),AVG(extprice)
FROM v_custord
GROUP BY custkey;
DROP VIEW v_custord RESTRICT;
DROP VIEW v_custord CASCADE;