1. The experiment purpose

Familiar with database data update operation , Able to use  SQL  Statement to add data to the database 、 Delete 、 Modify the operating .

2. The contents and requirements of the experiment

in the light of  tpch  Database or self built database  test  Design single line insert 、 Batch insert 、 Modify and delete data , Understand and master insert、delete  and update  The various components of the grammatical structure , Combined nesting SQL  Subquery , Design several different forms of statements , And debugging passed .

3. The experimental steps

（1） Insert a record into the supplier table , The value of each column should be given reasonably .

Source code ：

INSERT INTO supplier (
			suppkey,
			name,
			address,
			nationkey,
			phone,
			acctbal,
			comment
			)
		VALUES(
		131209,
		' Tencent holdings ',
		' Shenzhen, Guangdong Province ',
		227,
		'020-2456741',
		1300098203.20,
		' nothing '
		);

Results screenshots ：（ The following figure shows the results filtered using visualization , You can also use SELECT Statement to find the result ）

（2） Insert all the information of all Chinese customers into a new customer table （customernew） in .

  Source code ：

CREATE TABLE customernew AS SELECT
			*
		FROM
			customer
		WHERE
			1 = 0;

		INSERT INTO customernew SELECT
			customer.*
		FROM
			customer,
			nation
		WHERE
			customer.nationkey = nation.nationkey
		AND nation. NAME = ' China ';

Results screenshots ：

（3） Insert the information of each customer and its total shopping quantity and total shopping price into the customer statistics table （cusshopping）

Source code ：

CREATE TABLE cusshopping AS SELECT
			customer.custkey,
			customer. NAME,
			COUNT(*) AS shopping_num,
			SUM(totalprice) AS price_sum
		FROM
			customer,
			orders
		WHERE
			customer.custkey = orders.custkey
		GROUP BY
			orders.custkey
		ORDER BY
			orders.custkey;

  Results screenshots ：

（4） hold “ Shenzhen Hongyun Trading Co., Ltd ” The supply price of all parts supplied is reduced   20%.

Source code ：

UPDATE partsupp
SET supplycost = supplycost * 0.8
WHERE
	suppkey IN (
		SELECT
			suppkey
		FROM
			supplier
		WHERE
			NAME = ' Shenzhen Hongyun Trading Co., Ltd '
	);

Results screenshots ：（ The following figure shows the results filtered using visualization , You can also use SELECT Statement to find the result ）

Before updating

After the update

 （5） Update the order price in the order details table , Order details price （extendedprice）= wholesale price   (part.retailprice)* Number of parts （quantity）.

Source code ：

UPDATE lineitem,
 part
SET lineitem.extendedprice = lineitem.quantity * part.retailprice
WHERE
	lineitem.partkey = part.partkey;

Results screenshots ：

Before updating

After the update

 （6） Delete customer “ Tong Shuai ” All order detail records and order records .

Source code ：

DELETE
FROM
	lineitem
WHERE
	orderkey IN (
		SELECT
			orderkey
		FROM
			orders,
			customer
		WHERE
			customer. NAME = ' Tong Shuai '
		AND orders.custkey = customer.custkey
	);
DELETE
FROM
	orders
WHERE
	custkey IN (
		SELECT
			custkey
		FROM
			customer
		WHERE
			NAME = ' Tong Shuai '
	);

Results screenshots ：（ The following figure shows the results filtered using visualization , You can also use SELECT Statement to find the result ）

Before deleting

   After deleting

 （7） Delete “ Australia ” All supplier information for .

Source code ：

DELETE
FROM
	lineitem
WHERE
	suppkey IN (
		SELECT
			suppkey
		FROM
			supplier
		WHERE
			nationkey IN
		(SELECT nationkey
FROM nation
WHERE NAME = ' Australia ')
	);

DELETE
FROM
	partsupp
WHERE
	suppkey IN (
		SELECT
			suppkey
		FROM
			supplier
		WHERE
			nationkey IN
		(SELECT nationkey
FROM nation
WHERE NAME = ' Australia ')
	);

DELETE
FROM
	supplier
WHERE
	nationkey IN (
		SELECT
			nationkey
		FROM
			nation
		WHERE
			NAME = ' Australia '
	);

Results screenshots ：（ The following figure shows the results filtered using visualization , You can also use SELECT Statement to find the result ）

Before deleting

  After deleting