子查询（二）子查询

1 找出住在纽约的代理商的aid值和名字

select  aid aid值,aname 姓名 
        from agents
             where city = 'New York'

2 检索订货记录中所有零件的pid值

select distinct pid 
     from orders 
         order by pid

3 找出至少被两个顾客订购的产品的pid值

select pid 
     from (select pid , count(aid) ca 
               from orders
                    group by pid  
          ) a
          where a.ca >= 2
                order by pid

4 求出有顾客居住的城市的数目

select count(*) 
       from(
           select distinct city 
                from customers 
       )  

5 查询那些订购了某个被代理商a06订购过的产品的顾客的cid值

select distinct cid from orders 
       where pid in(
             select distinct pid from orders where aid = 'a06'
       )order by cid 

6 求出通过住在Duluth和Dallas的代理商订了货的顾客的cid值

select distinct cid from orders 
       where aid in(
             select aid 代理商编号
                    from agents
                         where city = 'Duluth' or city = 'Dallas'
       )order by cid 

7 检索有关住在Duluth或Dallas的代理商的所有信息

select ag.aid 代理商编号,ag.aname 姓名,ag.city 城市,ag.PERCENTS 佣金百分比,
       ord.ORDNO 订单号,ord.CID 客户编号,ord.PID 商品编号,ord.QTY 数量,ord.DOLLARS 总金额
        from agents ag inner join orders ord
             on ag.aid = ord.aid
                where ag.city = 'Duluth' or ag.city = 'Dallas'

8 求出通过住在Duluth或Dallas的代理商订货的所有顾客的姓名和折扣

select cname 姓名,discnt 折扣 
        from customers
             where cid in (
                select distinct ord.CID 客户编号
                        from agents ag inner join orders ord
                             on ag.aid = ord.aid
                                where ag.city = 'Duluth' or ag.city = 'Dallas'
             ) 

9 找出订购了产品p05的顾客的名字

select cname 姓名 
       from customers 
            where cid in(
                  select distinct cid from orders
                         where pid = 'p05'
            )

10 分别查询有和没有购买过产品的顾客名称

select distinct cname 姓名 
       from customers 
            where cid in (
                  select distinct cid from orders
            )

11 得到从代理商a03处订购了产品p07的顾客的名称 

select * from customers 
       where cid =(
             select cid from orders 
                    where aid = 'a03' and pid = 'p07'
       )

12 检索由住在Duluth的顾客和住在New York 的代理商组成的所有订货记录的ordno值（）

select  ordno from orders 
       where cid in (select cid from customers 
                 where city = 'Duluth') and
             aid in (select aid from agents 
                 where city = 'New York')
                 order by ordno

13 找出佣金百分率最小的代理商的名称

select aname 姓名 
       from agents 
       where percents = (
             select min(PERCENTS) 佣金百分率 
                    from agents
       )

14 检索通过代理商a05订货的所有顾客的名字

select cname 姓名 
       from customers 
            where cid in (
                  select distinct cid 
                         from orders 
                              where aid = 'a05'
            )

15 求出住在New York 或Duluth 并订购了价格超过一美元的所有产品的代理商的aid值

select aid from orders 
       where aid in ( 
            select distinct aid from orders 
                   where aid in (
                         select aid from agents where city = 'New York' or 
                                city = 'Duluth'
                   )
       ) and DOLLARS/ QTY >= 1

16 找出具有以下性质的顾客的cid 值：如果顾客c006订购了某种产品，那要检索的顾客也订购了该产品

select distinct cid  from orders 
       where pid in (
             select distinct pid 
                    from orders 
                         where cid = 'c006'
       ) order by cid

17 检索对同一产品至少订购了两次的所有顾客的名字

select cname from customers where cid in  
(select cid from orders group by cid,pid having count(pid)>=2)

18 求出既订购了产品p01又订购了产品p07的顾客的cid值 

select distinct cid from orders x  
       where pid='p01' and 
             exists (select * from orders where cid=x.cid and pid='p07')