1. The experiment purpose

be familiar with SQL  Language about view operation , Be able to use SQL  Statement to create the desired view , Define the schema outside the database , And can use the created view to realize data management .

2. The contents and requirements of the experiment

in the light of tpch  Database or self built database test  Create views and bands with check option  The view of , And verify  with check option Validity of options . Understand and master the implementation principle of view resolution , Master the difference between updatable view and non updatable view .

3. The experimental steps

（1） Create a “ Hebei Huaxin group ” Supplier supplied parts view Viewpart1, Supplier number is required 、 Name of supplier 、 Part number 、 Part name 、 Quantity available 、 Retail price and supply price .

Source code ：

CREATE VIEW Viewpart1 (
			suppkey,
			sname,
			partkey,
			pname,
			availqty,
			retailprice,
			supplycost
			) AS SELECT
			supplier.suppkey,
			supplier. NAME,
			part.partkey,
			part. NAME,
			partsupp.availqty,
			part.retailprice,
			partsupp.supplycost
		FROM
			supplier,
			part,
			partsupp
		WHERE
			supplier. NAME = ' Hebei Huaxin group ' WITH CHECK OPTION;

Results screenshots ：

（2） Create a view  ViewCust1, According to customer Statistics  2020  Total purchase amount of annual orders and total quantity of parts , The customer number is required to be output 、 full name 、 Total purchase amount and total quantity of purchased parts .

Source code ：

CREATE VIEW viewcust1 (
	custkey,
	NAME,
	price_sum,
	num_sum
) AS SELECT
	customer.custkey,
	customer. NAME,
	SUM(totalprice),
	SUM(quantity)
FROM
	customer,
	lineitem,
	orders
WHERE
	YEAR (orderdate) = '2020'
AND orders.orderkey = lineitem.orderkey
AND orders.custkey = customer.custkey
GROUP BY
	Custkey;

Results screenshots ：

 1） For the view just created , Insert a record , See if it can pass the verification , And explain why .

Source code ：

INSERT INTO viewcust1
VALUES
	(
		'131209',
		' The blue sky ',
		201312.09,
		1111
	);

result ： Can't pass validation , Part of the reason for this function to come from the view , Can't update

2） For the view just created , Query that the total purchase amount exceeds 10  Million customer number 、 full name 、 Total purchase amount and total quantity of purchased parts .

Source code ：

SELECT *
		FROM viewcust1
		WHERE price_sum > 100000;

Query results ：

3） Create a “ Hebei Iron and Steel Group Co., Ltd ” Supplier supplied parts view Viewpart2, It is required to list the number of supplied parts 、 Supplier No 、 Available quantity and supply price .

Source code ：

CREATE VIEW viewpart2 AS SELECT
	partkey,
	suppkey,
	availqty,
	supplycost
FROM
	partsupp
WHERE
	suppkey IN (
		SELECT
			suppkey
		FROM
			supplier
		WHERE
			NAME = ' Hebei Iron and Steel Group Co., Ltd '
	);

  Results screenshots ：

Then add... Respectively through this view 、 Delete and modify a “ Hebei Iron and Steel Group Co., Ltd ” Parts supply record .

Add source code ：

INSERT INTO viewpart2
VALUES(1209,24706,129,1209);

  Results screenshots ：

Modify the source code ：

UPDATE viewpart2
SET supplycost = 1312.09
WHERE partkey = 1209;

  Results screenshots ：

Remove source code ：

DELETE
FROM viewpart2
WHERE partkey = 1209;

  Results screenshots ：

（4） Use with check option  Create a “ Hebei Iron and Steel Group Co., Ltd ” Supplier supplied parts view Viewpart3, It is required to list the number of supplied parts 、 Supplier No 、 Available quantity and supply price .

Then add... Respectively through this view 、 Delete and modify a “ Hebei Iron and Steel Group Co., Ltd ” Parts supply record , Verify that the view is updatable , And compare with “（3）” Are there any similarities and differences in the experimental results .

Source code ：
 

CREATE VIEW viewpart3 AS SELECT
	partkey,
	suppkey,
	availqty,
	supplycost
FROM
	partsupp
WHERE
	suppkey IN (
		SELECT
			suppkey
		FROM
			supplier
		WHERE
			NAME = ' Hebei Iron and Steel Group Co., Ltd '
	) WITH CHECK OPTION;

  Results screenshots ：

  Add source code ：

INSERT INTO viewpart2
VALUES(1209,24706,129,1209);

Results screenshots ：

Modify the source code ：

UPDATE viewpart2
SET supplycost = 1312.09
WHERE partkey = 1209;

  Results screenshots ：

Remove source code ：

DELETE
FROM viewpart2
WHERE partkey = 1209;

  Results screenshots ：

（5） Create customer ordered parts detail view  Viewcust2, Customer number is required 、 full name 、 Number of parts purchased 、 amount of money ;

Source code ：

CREATE VIEW viewcust2 (
	custkey,
	NAME,
	price_sum,
	num_sum
) AS SELECT
	customer.custkey,
	customer. NAME,
	SUM(totalprice),
	SUM(quantity)
FROM
	customer,
	lineitem,
	orders
WHERE
orders.orderkey = lineitem.orderkey
AND orders.custkey = customer.custkey
GROUP BY
	custkey;

Results screenshots ：

1） Then create a view based on the view  Viewcust3, List the average number of parts purchased by each customer and the average amount , Display customer number 、 full name 、 Average number of parts purchased 、 Average amount ;

Source code ：

CREATE VIEW viewcust3 (
	custkey,
	NAME,
	price_avg,
	num_avg
) AS SELECT
	viewcust2.custkey,
	viewcust2. NAME,
	(
		viewcust2.price_sum / COUNT(orders.orderkey)
	),
	(
		viewcust2.num_sum / COUNT(lineitem.orderkey)
	)
FROM
	viewcust2,
	orders,
	lineitem
WHERE
	viewcust2.custkey = orders.custkey
AND orders.orderkey = lineitem.orderkey
GROUP BY
	viewcust2.custkey;

Results screenshots ：

 2） Delete view  Viewcust2.

Source code ：

DROP VIEW viewcust2;

Results screenshots ：