实验二简单查询及子查询一. 目的：练习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.4select distinct cid from orderswhere aid in (select aid from agentswhere city='Duluth' or city='Dallas');Example3.4.2 Retrieve all information concerning agents based in Duluth or Dallas.select*from agentswhere city in('Duluth','Dallas');Example3.4.3select cname,discnt from customerswhere cid in(select cid from orders where aid in(select aid from agents where city in('Duluth','Dallas')));Example3.4.6select ordno from orders x where exists(select cid,aid from customers c,agents awhere c.cid=x.cid and a.aid=x.aid and c.city='Duluth' and a.city='New York);Example3.4.7select aid from agents where commission<=all(select commission from agents);Example3.4.8select cid,cname from customerswhere discnt =some(select discnt from customerswhere city='Dallas'or city='Boston');Example3.4.9select cid from customerswhere discnt<all(select discnt from customerswhere city='Duluth');Example3.4.10select distinct ame from customers c,orders xwhere c.cid =x.cid and x.aid='a05';Example3.5.1select city from customersunion select city from agents;Example3.5.2select c.cid from customers c wherenot exxists(select*from agents awhere a.city='New York'andnot exists(select*from orders xwhere x.cid=c.cid and x.aid=a.aid));3. 验证、分析作业题: 3.1 (2.5 (a), (c), (e), (g),(k))( (o), (s)); 3.22.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 pwhere 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 city.select c.cid,a.aid,p.pid from customers c,agents a,products pwhere c.city<>a.city and a.city<>p.city and p.city<>c.city(e)Get product names ordered by at least one customers based in Dallas though an agent based in Tokyo.s elect p.pname from customers c,agents a,products p,orders ow here o.cid=c.cid and o.aid=a.aid and o.pid=p.pid and c.city='Dallas'and a.city='Tokyo'(g)Display all pairs of aids for agents who live in the same city.select distinct a1.city,a2.city from agents a1,agents a2Where a1.city=a2.city and a1.aid<a2.aid(k) Find pids of products ordered through agent a03 bur not through agent a06s elect distinct pid from orders xw here x.aid='a03' and not exists (select pid from orders ywhere y.aid='a06'and x.pid=y.pid)(o)Get names of agents who play orders for all products ordered by customer c002select distinct aname from agents a where not exists(select * from orders x where x.cid='c002' and not exists(select * from orders y where y.aid=a.aid and x.pid=y.pid));(s) Get aids of agents who place individual orders in dollar value greater than $500 for customers living in Kyoto.select distinct aid from customers c,orders owhere c.cid = o.cid and o.dollars>500 and c.city='Kyoto'3.2(a)Retrieve aid values of agents who receive more than the minimum percent commission(column name:percent)select aid from agents where commission>any(select commission from agents)(b)Retrieve aid value of agents who receive the minimum percent commission(column name:percent)select aid from agentswhere commission=(select max(commission)from agents)(c)Explain why the following query fails to answer request(a)above,although it retrieves the right rows from the agents table of Figure 2.1:select aid from agents where mission>5;The fact that two queries have the same result for a table of a given content is not sufficient to guarantee that the two queries are equivalent.3、小结：这次实验主要是练习简单的查询和子查询，有了第一次的上机，这次就得心应手了许多。
先是上机练习一下课本上的例子(Example3.4.1-3.4.3;Example3.4.6- 3.4.14；Example3.5.1.-3.5.2).找出查询的写法规律。
然后是查询课后练习，有时查询出来的结果觉得很不可思议，亲自查了一下表才知道错了，于是检查错误，然后更改，比如2.5(c)、(b)就遇到这种情况。
这样反复练习之后，就大致掌握了解题的基本规律，基本方法。
做其他的题目时，思考的时间明显节省了很多。