数据库实验二 数据查询

1.实验目的

理解 SQL 程序设计基本规范，熟练运用 SQL 语言实现数据查询，包括单表查询、分组统计查询和连接查询、嵌套查询合和集合查询。

2.实验内容和要求

针对 tpch 数据库或自建数据库 test 设计各种单表查询、分组统计查询和连接查询、嵌套查询合和集合查询，理解和掌握 SQL 查询语句各个子句的特点和作用，按照 SQL 程序设计规范写出具体的 SQL 查询语句，并调试通过。

3.实验步骤

（1）查询各个地区的编号和名称。

源代码：

SELECT regionkey,name
		FROM region;

结果截图：

（2）查询各个供应商的编号、名称、地址、电话和供应商所在国家名称。

源代码：

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

结果截图：

 （3）查询 2021 年 9 月份每个订单金额超过 10000 元的订单编号、顾客姓名、顾客所在国家名称、和订单日期。

源代码：

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;

查询过程中出现了无结果的问题，经检验是导入到表中的数据日期乱码均为空，处理数据后重新导入从而解决了无结果的问题。

查询结果：

（4）统计每个顾客在 2020 年的订购总金额，显示顾客编号、顾客姓名、订购总金额。

源代码：

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;

查询结果：

（5）查询订单平均金额超过 5 万元的顾客编号、姓名、所在国家名称和订单平均金额。

源代码：

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;

查询结果：

（6）查询与“金石印刷有限公司”在同一个国家的供应商编号、名称和地址信息。

源代码：

SELECT 
			sup.suppkey,
			sup.name,
			sup.address
		FROM
			supplier sup,
			supplier supp
		WHERE
			sup.nationkey = supp.nationkey
		AND supp.name = '金石印刷有限公司';

使用两个别名，将一个supplier表当做两个表使用

查询结果：

（7）查询供应价格小于零售价格的零件编号、零件名称、制造厂、品牌、零售价格、供应商名称和供应价格。

源代码：

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;

查询结果：

（8）查询顾客“曹玉书”订购的订单编号、订单金额及其订购的零件编号、数量和订单明细价格。

源代码：

SELECT
			orders.orderkey,
			totalprice,
			lineitem.partkey,
			quantity,
			extendedprice
		FROM
			lineitem,
			orders,
			customer
		WHERE
			customer. name = '曹玉书'
		AND lineitem.orderkey = orders.orderkey
		AND customer.custkey = orders.custkey;

查询结果：

（9）查询订购了“南昌矿山机械厂”制造的“缝盘机”的顾客编号、姓名。

源代码：

SELECT
			customer.custkey,
			customer.name,
		FROM
			lineitem,
			orders,
			customer,
			part
		WHERE
			part.mfgr = '南昌矿山机械厂'
		AND part.name = '缝盘机'
		AND part.partkey = lineitem.partkey
		AND lineitem.orderkey = orders.orderkey
		AND customer.custkey = orders.custkey;

 查询结果：

（10）查询订单平均金额超过 1 万元的顾客中的中国籍顾客的顾客编号、姓名。

 源代码：

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

 查询结果：

（11）查询顾客“刘玉龙”和“钱岚”都订购过的全部零件的信息。

源代码：

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 = '刘玉龙'
		)
	)
)
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 = '钱岚'
		)
	)
);

 查询结果：

（12）查询顾客“刘玉龙”或“钱岚”订购的全部零件的信息。

源代码：

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 = '刘玉龙'
				OR customer.name = '钱岚'
		)
	)
);

 查询结果;

（13）查询顾客“刘玉龙”订购过而“钱岚”没订购过的零件的信息。 

源代码：

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 = '刘玉龙'
				AND customer.name != '钱岚'
		)
	)
);

查询结果：