数据库实验二报数据库实验二报告.doc

实验二 简单查询及子查询 一. 目的： 练习SQL. 二. 内容： 1. 练习查询语句:（Example3.4.1-3.4.3; Example3.4.6-3.4.14； Example3.5.1.-3.5.2). 操作内容截图如下： CUSTOMERS : AGENTS: PROODUCTS: ORDER; 2练习查询语句 Example3.4.4 select distinct cid from orders where aid in (select aid from agents where city=Duluth or city=Dallas); Example3.4.2 Retrieve all information concerning agents based in Duluth or Dallas. select * from agents where city in(Duluth,Dallas); Example3.4.3 select cname,discnt from customers where cid in(select cid from orders where aid in(select aid from agents where city in(Duluth,Dallas))); Example3.4.6 select ordno from orders x where exists (select cid,aid from customers c,agents a where c.cid=x.cid and a.aid=x.aid and c.city=Duluth and a.city=New York); Example3.4.7 select aid from agents where commission=all (select commission from agents); Example3.4.8 select cid,cname from customers where discnt =some(select discnt from customers where city=Dallas or city= Boston); Example3.4.9 select cid from customers where discntall (select discnt from customers where city=Duluth); Example3.4.10 select distinct ame from customers c,orders x where c.cid =x.cid and x.aid=a05; Example3.5.1 select city from customers union select city from agents; Example3.5.2 select c.cid from customers c where not exxists(select * from agents a where a.city=New York and not exists (select * from orders x where x.cid=c.cid and x.aid=a.aid)); 3. 验证、分析作业题: 3.1 (2.5 (a), (c), (e), (g),(k))( (o), (s)); 3.2 2.5（a)Find all(cid,aid,pid)triples for customer,agent,product combinations that are all in the same city.Nothing about orders is involved in this selectio. select c.cid,a.aid,p.pid from customers c,agents a,products p where c.city=a.city and a.city=p.city (c)Find all(cid,aid,pid)triples for customer,agents,product combinations,no two of which are i the same