数据库实验二 数据查询

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

2.实验内容和要求
针对自建数据库 test 设计各种单表查询、分组统计查询和连接查询、嵌套查询合和集合查询，理解和掌握 SQL 查询语句各个子句的特点和作用，按照 SQL 程序设计规范写出具体的 SQL 查询语句，并调试通过。要求每个查询语句都要有结果，并把查询结果截屏作为实验告的内容，如果查询结果为空，需要自行修改所给表的数据，并把修改过程在实验报告里说明。

3.实验步骤
（1）查询各个地区的编号和名称。

select regionkey,name from region;

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

select suppkey,supplier.name suppliersname,address,phone,nation.name nationsname
from supplier,nation 
where supplier.nationkey = nation.nationkey;

 

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

      查询2023年的不存在，所以先把orderdate年份+2

update orders 
set orderdate = DATE_ADD( orderdate, interval 2 year );

      然后再查询

select orderkey,customer.name customersname,nation.name nationsname,orderdate 
from orders,customer,nation 
where customer.custkey = orders.custkey 
	and customer.nationkey = nation.nationkey 
	and year(orderdate) = '2023' 
	and month(orderdate) = '9' 
	and totalprice > 10000;

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

select customer.custkey,customer.name,sum(totalprice) sumprice
from customer,orders
where orders.custkey=customer.custkey and year(orderdate)='2023'
group by custkey

（5） 查询零件的销售情况，统计在 2023 年（按照下订单时间）的销售总金额大于 100万的零件信息，显示零件编号、零件名称、品牌、制造厂、销售总金额.

      查询总金额大于100万的不存在，所以修改为查询总金额大于10万的

select part.partkey,part.name,part.brand,mfgr,sum(totalprice) sum_price 
from part,orders,lineitem 
where year(orderdate) = '2023' 
	and part.partkey = lineitem.partkey 
	and orders.orderkey = lineitem.orderkey 
group by
	part.partkey 
having
	sum(totalprice) > 100000;

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

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

      查询总金额大于100万的不存在，所以修改查询总金额大于10万的

select supplier.suppkey,supplier.name,sum(totalprice)
from supplier,lineitem,orders
where year(orderdate)='2023'
	and supplier.suppkey=lineitem.suppkey
	and lineitem.orderkey=orders.orderkey
group by suppkey
having sum(totalprice)>100000;

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

select orders.custkey,customer.name ordersname,nation.name nationsname,avg(totalprice)
from orders,nation,customer
where orders.custkey = customer.custkey
	and customer.nationkey = nation.nationkey
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
	and B.name='金石印刷有限公司';

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

select part.partkey,part.name partsname,part.mfgr,part.brand,retailprice,supplier.name suppliersname,partsupp.supplycost
from supplier,partsupp,part
where supplier.suppkey = partsupp.suppkey
	and partsupp.partkey = part.partkey
	and retailprice>supplycost;

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

select orders.orderkey,orders.totalprice,lineitem.partkey,lineitem.quantity,extendedprice
from orders,lineitem,customer
where lineitem.orderkey=orders.orderkey
	and orders.custkey=customer.custkey
	and customer.name='曹玉书';

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

select customer.custkey,customer.name
from lineitem,customer,part,orders
where lineitem.partkey = part.partkey
	and lineitem.orderkey = orders.orderkey
	and orders.custkey = customer.custkey
	and mfgr = '南昌矿山机械厂'
	and part.name = '缝盘机';

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

select customer.custkey,customer.name
from nation,customer,orders
where nation.nationkey=customer.nationkey
    and customer.custkey=orders.custkey
	and nation.name='中国'
	group by customer.custkey
having avg(orders.totalprice)>10000;

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

select *
from part
where partkey in(
  	  select part.partkey
			from part,lineitem,orders,customer
			where part.partkey=lineitem.partkey
		    and orders.orderkey=lineitem.orderkey
				and orders.custkey=customer.custkey
				and customer.name='刘玉龙'
	)and partkey in(
 	   select part.partkey
			from part,lineitem,orders,customer
			where part.partkey=lineitem.partkey
		 	   	and orders.orderkey=lineitem.orderkey
				and orders.custkey=customer.custkey
				and customer.name='钱岚'
);

查询结果为空，表示没有“刘玉龙”和“钱岚”都订购过的零件

所以修改为只查询“刘玉龙”订购过的零件

select * from part
where partkey in(
   	select part.partkey
	from part,lineitem,orders,customer
	where part.partkey=lineitem.partkey
		    and orders.orderkey=lineitem.orderkey
	        and orders.custkey=customer.custkey
            and customer.name='刘玉龙'
);

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

select *
from part
where partkey in(
   	 select part.partkey
	 from part,lineitem,orders,customer
		where part.partkey=lineitem.partkey
		    and orders.orderkey=lineitem.orderkey
		    and orders.custkey=customer.custkey
		    and customer.name='刘玉龙'	
	)or partkey in(
    	select part.partkey
		from part,lineitem,orders,customer
		where part.partkey=lineitem.partkey
		    and orders.orderkey=lineitem.orderkey
		    and orders.custkey=customer.custkey
		    and customer.name='钱岚'
);

 

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

select *
	from part
	where partkey in(
   		select part.partkey
		from part,lineitem,orders,customer
			where part.partkey=lineitem.partkey
		    	and orders.orderkey=lineitem.orderkey
				and orders.custkey=customer.custkey
				and customer.name='刘玉龙'
	)and partkey not in(
    		select part.partkey
			from part,lineitem,orders,customer
			where part.partkey=lineitem.partkey
		 	    and orders.orderkey=lineitem.orderkey
				and orders.custkey=customer.custkey
				and customer.name='钱岚'
)