查询作业部份题目参考答案精选全文

可编辑修改精选全文完整版
1找出至少被两个顾客订购的产品的pid值
select pid,count(cid) as 订购产品的顾客数量from orders group by pid having count(cid)>=2
select distinct x1.pid from orders x1, orders x2 where x1.pid=x2.pid and x1.cid <x2.cid
2求出通过住在Duluth或Dallas的代理商订货的所有顾客的姓名和折扣select cname,discnt from customers where cid in (select cid from orders where aid in ( select aid from agents where city='Duluth' or city='Dallas') )
3检索由住在Duluth的顾客和住在New York 的代理商组成的所有订货记录的ordno值（）
select ordno from orders where cid in (select cid from customers where city='Dul uth') and aid in (select aid from agents where city='New York')
4求出住在New York 或Duluth 并订购了价格超过一美元的所有产品的代理商的aid值
select aid from agents where aid in (select aid from orde rs where dollars/qty>1) and city='New York' or city='Dulu th'
5找出具有以下性质的顾客的cid 值：如果顾客c006订购了某种产品，那要检索的顾客也订购了该产品
select cname,cid from customers where cid in (select cid from orders where pid in (sel ect pid from orders where cid='c006'))
6检索对同一产品至少订购了两次的所有顾客的名字
select cname from customers where cid in (select cid fro m orders group by cid,pid having count(pid)>=2)
7求出既订购了产品p01又订购了产品p07的顾客的cid值
select distinct cid from orders x
where pid='p01' and exists (select * from orders where ci d=x.cid and pid='p07')
8求出住在同一城市的顾客对
select c1.cid,c2.cid from customers c1,customers c2 where c1.city=c2.city and c1.cid <c2.cid
9、检索所有满足以下条件的顾客-代理商姓名对(cname,aname),其中的cname 通过aname订了货
select distinct ame,AGENTS.aname from CUSTOMERS,AGE NTS,ORDERS where CUSTOMERS.cid=ORDERS.cid and AGENTS.aid=ORD ERS.aid。