博客

数据库结构

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

SELECT regionkey,name FROM region;

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

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

wait 几分钟

原数据CSV数据表格如下：除去表头一行，则数据共30810行，与实验最终跑出来的数据数量吻合

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

1

SELECT ord.orderkey,cus.name,nat.name,ord.orderdate
 FROM orders ord,customer cus,nation nat
  WHERE ord.orderdate between '2021-09-01' and '2021-09-30'
   && ord.totalprice>10000 && ord.custkey=cus.custkey && cus.nationkey=nat.nationkey;

2

SELECT ord.orderkey,cus.name,nat.name,ord.orderdate
 FROM orders ord,customer cus,nation nat
  WHERE ord.orderdate LIKE '2021-09%' 
   && ord.totalprice>10000 && ord.custkey=cus.custkey && cus.nationkey=nat.nationkey;

WAIT 几分钟

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

SELECT cus.custkey,cus.name,SUM(ord.totalprice) AS sum_totalprice
 FROM customer cus,orders ord
  WHERE ord.custkey=cus.custkey && ord.orderdate like '2020-%'
   GROUP BY cus.custkey
    ORDER BY cus.custkey;

wait 几分钟

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

SELECT cus.custkey,cus.name,nat.name,AVG(ord.totalprice) AS avg_price
 FROM customer cus,orders ord,nation nat
  WHERE nat.nationkey=cus.nationkey && cus.custkey=ord.custkey
   GROUP BY cus.custkey
    HAVING avg_price>50000
     ORDER BY cus.custkey;

wait 几分钟

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

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

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

SELECT par.partkey,par.name,par.mfgr,par.brand,par.retailprice,sup.name,parx.supplycost
 FROM partsupp parx,part par,supplier sup
  WHERE par.partkey=parx.partkey && parx.supplycost<par.retailprice && sup.suppkey=parx.suppkey ORDER BY par.partkey \G;

wait 几分钟

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

SELECT lin.orderkey,ord.totalprice,lin.partkey,lin.quantity,lin.extendedprice 
 FROM lineitem lin,orders ord,customer cus
  WHERE cus.name='曹玉书' && cus.custkey=ord.custkey && lin.orderkey=ord.orderkey
   ORDER BY lin.orderkey;

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

1

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

2

SELECT cus.custkey,cus.name,par.name,par.mfgr 
 FROM customer cus,part par 
  WHERE  par.mfgr='南昌矿山机械厂' && par.name='缝盘机' &&
   cus.custkey IN 
   (SELECT ord.custkey 
     FROM orders ord,lineitem lin  
      WHERE par.partkey=lin.partkey && ord.orderkey=lin.orderkey)
      ORDER BY cus.custkey;

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

SELECT cus.custkey,cus.name,AVG(ord.totalprice) AS avg_price,SUM(ord.totalprice) AS sum_price
 FROM customer cus,orders ord,nation nat 
  WHERE ord.custkey=cus.custkey &&  nat.name='中国' && cus.nationkey=nat.nationkey 
   GROUP BY cus.custkey
    HAVING avg_price>10000
     ORDER BY avg_price;

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

SELECT * FROM part par 
 WHERE 
 par.partkey IN
 (SELECT lin.partkey 
    FROM lineitem lin 
     WHERE lin.orderkey IN 
      (SELECT ord.orderkey 
        FROM orders ord 
         WHERE ord.custkey IN 
          (SELECT cus.custkey 
            FROM customer cus 
             WHERE cus.name='钱岚'
             )
        )
   )
 AND
  par.partkey IN 
  (SELECT lin.partkey 
    FROM lineitem lin 
     WHERE lin.orderkey IN 
      (SELECT ord.orderkey 
        FROM orders ord 
         WHERE ord.custkey IN 
          (SELECT cus.custkey 
            FROM customer cus 
             WHERE cus.name='刘玉龙'
           )
       )
    )
   ;

是我写错了？

SELECT par.partkey FROM part par 
 WHERE 
 par.partkey IN
 (SELECT lin.partkey 
    FROM lineitem lin 
     WHERE lin.orderkey IN 
      (SELECT ord.orderkey 
        FROM orders ord 
         WHERE ord.custkey IN 
          (SELECT cus.custkey 
            FROM customer cus 
             WHERE cus.name='钱岚'
             )
        )
   )
   order by par.partkey;


 SELECT par.partkey FROM part par 
 WHERE 
 par.partkey IN
 (SELECT lin.partkey 
    FROM lineitem lin 
     WHERE lin.orderkey IN 
      (SELECT ord.orderkey 
        FROM orders ord 
         WHERE ord.custkey IN 
          (SELECT cus.custkey 
            FROM customer cus 
             WHERE cus.name='刘玉龙'
             )
        )
   )
   order by par.partkey;

还真没有两个人都订购过的零件

12、查询顾客刘玉龙或钱岚订购过得全部零件的信息

先只查零件编号

SELECT par.partkey FROM part par 
 WHERE 
 par.partkey IN
 (SELECT lin.partkey 
    FROM lineitem lin 
     WHERE lin.orderkey IN 
      (SELECT ord.orderkey 
        FROM orders ord 
         WHERE ord.custkey IN 
          (SELECT cus.custkey 
            FROM customer cus 
             WHERE cus.name='钱岚' OR cus.name='刘玉龙'
             )
        )
   )
   order by par.partkey;

查零件全部信息

SELECT * FROM part par 
 WHERE 
 par.partkey IN
 (SELECT lin.partkey 
    FROM lineitem lin 
     WHERE lin.orderkey IN 
      (SELECT ord.orderkey 
        FROM orders ord 
         WHERE ord.custkey IN 
          (SELECT cus.custkey 
            FROM customer cus 
             WHERE cus.name='钱岚' OR cus.name='刘玉龙'
             )
        )
   )
   order by par.partkey \G;

13、查询刘玉龙订购过而钱岚没订购过的零件信息

SELECT * FROM part par 
 WHERE 
 par.partkey IN
 (SELECT lin.partkey 
    FROM lineitem lin 
     WHERE lin.orderkey IN 
      (SELECT ord.orderkey 
        FROM orders ord 
         WHERE ord.custkey IN 
          (SELECT cus.custkey 
            FROM customer cus 
             WHERE cus.name='刘玉龙'
             )
        )
   )
   AND 
   par.partkey NOT IN 
    (SELECT lin.partkey 
    FROM lineitem lin 
     WHERE lin.orderkey IN 
      (SELECT ord.orderkey 
        FROM orders ord 
         WHERE ord.custkey IN 
          (SELECT cus.custkey 
            FROM customer cus 
             WHERE cus.name='钱岚'
             )
        )
   )
   order by par.partkey \G;