4.19例1.3

#实验1.3
1.1.
SELECT custkey,NAME
FROM customer
WHERE custkey IN(SELECT o.custkey
from orders o,lineitem l,partsupp ps,part p
WHERE o.orderkey = l.orderkey AND
l.partkey = ps.partkey AND
l.suppkey = ps.suppkey AND
ps.partkey = p.partkey AND
p.mfgr = ‘海大’ AND p.name =‘船舶模拟驾驶舱’);
1.2.
3. SELECT custkey,NAME
FROM customer
WHERE custkey IN(SELECT o.custkey
from orders o,lineitem l,part p
WHERE o.orderkey = l.orderkey AND
l.partkey=p.partkey AND
p.mfgr = ‘海大’ AND p.name =‘船舶模拟驾驶舱’);
2.
SELECT custkey,NAME
FROM customer c
WHERE NOT EXISTS(SELECT o.custkey
from orders o,lineitem l,partsupp ps,part p
WHERE c.custkey=o.custkey AND
o.orderkey = l.orderkey AND
l.partkey=ps.partkey AND
l.suppkey=ps.suppkey AND
p.mfgr = ‘海大’ AND p.name =‘船舶模拟驾驶舱’);

3.
4.
SELECT c.*
FROM customer c,(SELECT custkey

FROM orders
GROUP BY custkey
HAVING AVG(totalprice)>10000) b,nation n
WHERE c.custkey=b.custkey AND
c.nationkey=n.nationkey AND n.name=‘中国’;

5.
SELECT P.*
FROM customer c,orders o,lineitem l,partsupp ps,part p
WHERE c.custkey=o.custkey AND o.orderkey =l.orderkey AND
l.suppkey=ps.suppkey AND l.partkey=ps.partkey AND
ps.partkey=p.partkey AND c.name=‘张三’;
6.
SELECT P.*
FROM customer c,orders o,lineitem l,partsupp ps,part p
WHERE c.custkey=o.custkey AND o.orderkey =l.orderkey AND
l.suppkey=ps.suppkey AND l.partkey=ps.partkey AND
ps.partkey=p.partkey AND c.name=‘张三’;
UNION
SELECT P.*
FROM customer c,orders o,lineitem l,partsupp ps,part p
WHERE c.custkey=o.custkey AND o.orderkey =l.orderkey AND
l.suppkey=ps.suppkey AND l.partkey=ps.partkey AND
ps.partkey=p.partkey AND c.name=‘李四’;