数据库原理实验二

1.实验目的

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

计查询和连接查询、嵌套查询和集合查询。

2.实验内容和要求

针对自建数据库 test 设计各种单表查询、分组统计查询和连接查询、嵌套查询合和集

合查询，理解和掌握 SQL 查询语句各个子句的特点和作用，按照 SQL 程序设计规范写出具体

的 SQL 查询语句，并调试通过。

要求每个查询语句都要有结果，并把查询结果截屏作为实验报告的内容，如果查询结

果为空，需要自行修改所给表的数据，并把修改过程在实验报告里说明。

tips: 1、数据库中的orders表中，日期需要修改，执行下面语句：update orders SET orderdate=DATE_ADD(orderdate, INTERVAL 3 year);

2、执行这一句：update lineitem SET partkey=65483,suppkey=20368 WHERE orderkey=6084 and linenumber=2;

3.实验步骤

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

SELECT regionkey, `name`
FROM region

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

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

（3）查询 2024年 9 月份订单金额超过 1 万元的订单编号、顾客姓名、顾客所在国家名称

和订单日期。

SELECT orders.orderkey,customer.`name`,nation.`name`,orders.orderdate
FROM orders,customer,nation
WHERE orders.orderdate BETWEEN '2024-09-01' AND '2024-09-30'
&& orders.totalprice > 10000 && orders.custkey = customer.custkey && customer.nationkey = nation.nationkey

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

SELECT customer.custkey,customer.`name`,SUM(orders.totalprice) AS Sum_totalprice
FROM customer,orders
WHERE orders.custkey = customer.custkey && orders.orderdate like '2024-%'
GROUP BY customer.custkey
ORDER BY customer.custkey

（5）查询零件的销售情况，统计在 2024年（按照下订单时间）的销售总金额大于 100

万的零件信息，显示零件编号、零件名称、品牌、制造厂、销售总金额。

SELECT part.partkey,part.`name`,part.brand,part.mfgr,SUM(totalprice) AS Sum_extendedprice
FROM part,lineitem,orders
WHERE orders.orderdate LIKE '2024-%' && part.partkey = lineitem.partkey && orders.orderkey = lineitem.orderkey
GROUP BY partkey
HAVING SUM(totalprice) > 100000

（6）查询每个供应商供应的产品销售情况，统计在 2023 年（按照下订单时间）的销售总

金额大于 100 万的供应商信息，显示供应商编号、供应商名称、销售总金额

SELECT supplier.suppkey,`name`,SUM(totalprice) AS Sum_extendedprice
FROM supplier,orders,lineitem
WHERE orders.orderdate LIKE '2023-%' && supplier.suppkey = lineitem.suppkey && orders.orderkey = lineitem.orderkey
GROUP BY suppkey
HAVING SUM(totalprice) > 100000

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

SELECT customer.custkey,customer.`name`,nation.`name`,AVG(totalprice)AS Avg_extendedprice
FROM customer,nation,orders
WHERE customer.nationkey = nation.nationkey && orders.custkey = customer.custkey
GROUP BY custkey
HAVING AVG(totalprice) > 50000

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

SELECT A.suppkey,A.name,A.address
FROM supplier A,supplier B
WHERE A.nationkey = B.nationkey && B.name = '金石印刷有限公司';

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

应商名称和供应价格。

SELECT part.partkey,part.`name`,part.mfgr,part.brand,part.retailprice,supplier.`name` AS Supplier_name,partsupp.supplycost
FROM part,supplier,partsupp
WHERE partsupp.suppkey = supplier.suppkey && part.partkey = partsupp.partkey && partsupp.supplycost < part.retailprice

（10）查询顾客“曹玉书”订购的订单编号、订单金额及其订购的零件编号、数量和订单

明细价格。

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

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

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

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

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

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

SELECT *
FROM part
WHERE partkey IN (
SELECT part.partkey
FROM part,customer,orders,lineitem
WHERE customer.`name` = '刘玉龙' 
&& customer.custkey = orders.custkey
&& orders.orderkey = lineitem.orderkey 
&& lineitem.partkey = part.partkey) 
and partkey IN(
SELECT part.partkey
FROM part,customer,orders,lineitem
WHERE customer.`name` = '钱岚' 
&& customer.custkey = orders.custkey
&& orders.orderkey = lineitem.orderkey 
&& lineitem.partkey = part.partkey)

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

SELECT *
FROM part
WHERE partkey IN (
SELECT part.partkey
FROM part,customer,orders,lineitem
WHERE customer.`name` = '刘玉龙' 
&& customer.custkey = orders.custkey
&& orders.orderkey = lineitem.orderkey 
&& lineitem.partkey = part.partkey) 
or partkey IN(
SELECT part.partkey
FROM part,customer,orders,lineitem
WHERE customer.`name` = '钱岚' 
&& customer.custkey = orders.custkey
&& orders.orderkey = lineitem.orderkey 
&& lineitem.partkey = part.partkey)

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

SELECT *
FROM part
WHERE partkey IN (
SELECT part.partkey
FROM part,customer,orders,lineitem
WHERE customer.`name` = '刘玉龙' 
&& customer.custkey = orders.custkey
&& orders.orderkey = lineitem.orderkey 
&& lineitem.partkey = part.partkey) 
and partkey not IN(
SELECT part.partkey
FROM part,customer,orders,lineitem
WHERE customer.`name` = '钱岚' 
&& customer.custkey = orders.custkey
&& orders.orderkey = lineitem.orderkey 
&& lineitem.partkey = part.partkey)