高级SQL语句查询(含答案和截图)

C顾客cidcnamecity discnt

c001 李广天津10.00

c002 王开基北京12.00

c003 安利德北京8.00

c004 曹士雄天津8.00

c006 曹士雄广州0.00

P商品pidpname city quantity price

p01 梳子天津111400 0.50

p02 刷子成都203000 0.50

p03 刀片西安150600 1.00

p04 钢笔西安125300 1.00

p05 铅笔天津221400 1.00

p06 文件夹天津123100 2.00

p07 盒子成都100500 1.00

A代理aidanamecity percent

a01 史可然北京 6

a02 韩利利上海 6

a03 杜不朗成都7

a04 甘瑞北京 6

a05 敖斯群武汉 5

a06 史可然天津 5

O订单ordnomonthcidaid pidqtydollars

1011 01 c001 a01 p01 1000 450.00 1012 01 c001 a01 p01 1000 450.00 1019 02 c001 a02 p02 400 180.00 1017 02 c001 a06 p03 600 540.00 1018 02 c001 a03 p04 600 540.00 1023 03 c001 a04 p05 500 450.00 1022 03 c001 a05 p06 400 720.00 1025 04 c001 a05 p07 800 720.00

1013 01 c002 a03 p03 1000 880.00

1026 05 c002 a05 p03 800 704.00

1 查询所有定购了至少一个价值为0.50的商品的顾客的名字。

ame

from client

wherecid in (select cid from order1 where pid in(select pid from product where price='0.5'))

2 找出全部没有在代理商a03处订购商品的顾客cid值。

selectclient.cid

from client

wherecid not in (select cid from order1 where aid in(select aid from agend where aid='a03'))

3 找出只在代理商a03处订购商品的顾客。

selectclient.cid

from client

wherecid not in (select cid from order1 where aid in(select aid from agend where aid!='a03') )AND cid in (select cid from order1 where aid in(select aid from agend where aid='a03'))

4 找出没有被任何一个在北京的顾客通过在上海的代理商订购的所有商品。

select *from product

wherepid not in (select pid from client,agend,order1

where client.cid=order1.cid and agend.aid = order1.aid and client.city='北京' and agend.city = '上海')

5 找出订购了所有价格为0.50的商品的顾客名字。

selectcname

from client

wherecid in(select cid from order1 ,product where order1.pid=product.pid AND product.price='0.50' )

6 找出订购所有被任何人订购过一次的商品的顾客cid。

除法

selectcid

from client

where not exists(select pid from order1 where pid not in(select pid from order1 where order1.cid=client.cid))

7 找出所有接到至少顾客c004订购的商品集合的订单的代理商的aid。

select aid

from order1

wherecid='c004'

8 找出订购了p01和p07这两种商品的顾客的cid。

错误：

select cid

from order1

wherepid='p01' AND pid='p07'

正确：

select distinct cid

from order1

wherepid='p01' AND exists(select pid from order1 where pid='p07')

9 找出从至少一个接受订购商品p03订单的代理商处订购过商品的顾客cid。

select distinct cid

from order1

where aid in (select aid from order1 where pid='p03')

10 找出所有具有和在北京和天津的顾客相同的折扣率的顾客的cid。

selectcid

from client

where discnt in (select discnt from client where city='北京' )AND discnt in (select discnt from client where city='天津' )

11 列出通过以下代理商订购的商品的pid：代理商从以下的顾客处接受订单，而这些顾客从一个接受过顾客c001订单的代理商处订购了至少一个商品。

select distinctpid

from order1

where aid in(select aid from order1 where cid in(select cid from order1 where aid in (select aid from order1 where cid='c001')))