1. The experiment purpose

understand SQL  Basic specification of programming , Skilled use SQL  Language to achieve data query , Including single table query 、 Grouping statistics query and connection query 、 Nested query and collection query .

2. The contents and requirements of the experiment

in the light of  tpch  Database or self built database  test  Design various single table queries 、 Grouping statistics query and connection query 、 Nested query and collection query , Understand and master SQL The characteristics and functions of each clause of the query statement , according to SQL Program design specifications write specific SQL  Query statement , And debugging passed .

3. The experimental steps

（1） Query the number and name of each region .

Source code ：

SELECT regionkey,name
		FROM region;

Results screenshots ：

（2） Query the number of each supplier 、 name 、 Address 、 Telephone number and supplier's country name .

Source code ：

SELECT 
			suppkey,
			supplier.name,
			address,phone,
			nation.name
		FROM 
			nation,
			supplier
		WHERE
			supplier.nationkey = nation.nationkey;

Results screenshots ：

 （3） Inquire about 2021  year 9  The amount of each order in the month exceeds 10000  Order number of yuan 、 Customer name 、 Customer's country name 、 And order date .

Source code ：

SELECT 
			orderkey,
			customer.name,
			nation.name,
			orderdate
		FROM
			nation,
			customer,
			orders
		WHERE
			customer.nationkey = nation.nationkey
		AND customer.custkey = orders.custkey
		AND YEAR(orderdate) = '2021'
		AND MONTH(orderdate) = '9'
		AND totalprice > 10000;

There is a problem of no result in the query process , It is verified that the date of the data imported into the table is empty , The data is processed and re imported, which solves the problem of no result .

Query results ：

（4） Count each customer in  2020  Total order amount for the year , Display customer number 、 Customer name 、 Total order amount .

Source code ：

SELECT 
			customer.custkey,
			name,SUM(totalprice) AS sum_totalprice
		FROM 
			customer,
			orders
		WHERE
			customer.custkey = orders.custkey
		AND YEAR(orderdate) = '2020'
		GROUP BY customer.custkey
		ORDER BY customer.custkey;

Query results ：

（5） The average amount of the query order exceeds  5  Customer number of 10000 yuan 、 full name 、 Country name and average order amount .

Source code ：

SELECT
			customer.custkey,
			customer.name,
			nation.name,
			AVG(totalprice)
		FROM
			customer,
			orders,
			nation
		WHERE
			customer.custkey = orders.custkey
		AND customer.nationkey = nation.nationkey
		GROUP BY customer.custkey
		HAVING AVG(totalprice)>50000
		ORDER BY customer.custkey;

Query results ：

（6） Query and “ Jinshi Printing Co., Ltd ” Supplier number in the same country 、 Name and address information .

Source code ：

SELECT 
			sup.suppkey,
			sup.name,
			sup.address
		FROM
			supplier sup,
			supplier supp
		WHERE
			sup.nationkey = supp.nationkey
		AND supp.name = ' Jinshi Printing Co., Ltd ';

Use two aliases , Will a supplier Tables are used as two tables

Query results ：

（7） Query the part number where the supply price is less than the retail price 、 Part name 、 manufacturer 、 brand 、 wholesale price 、 Supplier name and supply price .

Source code ：

SELECT
			part.partkey,
			part.name,
			mfgr,
			brand,
			retailprice,
			supplier.name,
			supplycost
		FROM 
			supplier,
			part,
			partsupp
		WHERE
			partsupp.supplycost < part.retailprice
		AND partsupp.partkey = part.partkey
		AND partsupp.suppkey = supplier.suppkey;

Query results ：

（8） Enquiring customers “ Cao Yushu ” Order number of the order 、 The order amount and the part number ordered 、 Quantity and order details price .

Source code ：

SELECT
			orders.orderkey,
			totalprice,
			lineitem.partkey,
			quantity,
			extendedprice
		FROM
			lineitem,
			orders,
			customer
		WHERE
			customer. name = ' Cao Yushu '
		AND lineitem.orderkey = orders.orderkey
		AND customer.custkey = orders.custkey;

Query results ：

（9） Inquired and ordered “ Nanchang Mining Machinery Factory ” Made of “ Sewing machine ” Your customer number 、 full name .

Source code ：

SELECT
			customer.custkey,
			customer.name,
		FROM
			lineitem,
			orders,
			customer,
			part
		WHERE
			part.mfgr = ' Nanchang Mining Machinery Factory '
		AND part.name = ' Sewing machine '
		AND part.partkey = lineitem.partkey
		AND lineitem.orderkey = orders.orderkey
		AND customer.custkey = orders.custkey;

  Query results ：

（10） The average amount of the query order exceeds  1  The customer number of Chinese customers among 10000 yuan customers 、 full name .

  Source code ：

SELECT
			customer.custkey,
			customer.name
		FROM
			nation,
			orders,
			customer
		WHERE
			customer.nationkey = nation.nationkey
		AND nation.name = ' China '
		AND customer.custkey = orders.custkey
		GROUP BY customer.custkey
		HAVING AVG(totalprice) > 10000
		ORDER BY customer.custkey;

  Query results ：

（11） Enquiring customers “ Liu Yulong ” and “ Qian LAN ” Information on all parts ordered .

Source code ：

SELECT
	*
FROM
	part
WHERE
	part.partkey IN
(
	SELECT lineitem.partkey
	FROM
		lineitem
	WHERE
		lineitem.orderkey IN
	(
		SELECT orders.orderkey
		FROM
			orders
		WHERE
			orders.custkey IN
		(
			SELECT customer.custkey
			FROM
				customer
			WHERE
				customer.name = ' Liu Yulong '
		)
	)
)
AND
	part.partkey IN
(
	SELECT lineitem.partkey
	FROM
		lineitem
	WHERE
		lineitem.orderkey IN
	(
		SELECT orders.orderkey
		FROM
			orders
		WHERE
			orders.custkey IN
		(
			SELECT customer.custkey
			FROM
				customer
			WHERE
				customer.name = ' Qian LAN '
		)
	)
);

  Query results ：

（12） Enquiring customers “ Liu Yulong ” or “ Qian LAN ” Information on all parts ordered .

Source code ：

SELECT
	*
FROM
	part
WHERE
	part.partkey IN
(
	SELECT lineitem.partkey
	FROM
		lineitem
	WHERE
		lineitem.orderkey IN
	(
		SELECT orders.orderkey
		FROM
			orders
		WHERE
			orders.custkey IN
		(
			SELECT customer.custkey
			FROM
				customer
			WHERE
				customer.name = ' Liu Yulong '
				OR customer.name = ' Qian LAN '
		)
	)
);

  Query results ;

（13） Enquiring customers “ Liu Yulong ” Ordered but “ Qian LAN ” Information on parts not ordered . 

Source code ：

SELECT
	*
FROM
	part
WHERE
	part.partkey IN
(
	SELECT lineitem.partkey
	FROM
		lineitem
	WHERE
		lineitem.orderkey IN
	(
		SELECT orders.orderkey
		FROM
			orders
		WHERE
			orders.custkey IN
		(
			SELECT customer.custkey
			FROM
				customer
			WHERE
				customer.name = ' Liu Yulong '
				AND customer.name != ' Qian LAN '
		)
	)
);

Query results ：