《2022年数据库实验二报告参考》由会员分享，可在线阅读，更多相关《2022年数据库实验二报告参考（7页珍藏版）》请在金锄头文库上搜索。
1、实验二简单查询及子查询一. 目的：练习 SQL. 二. 内容：1. 练习查询语句 :（Example3.4.1-3.4.3; Example3.4.6-3.4.14 ； Example3.5.1.-3.5.2). 操作内容截图如下：CUSTOMERS : AGENTS: PROODUCTS: ORDER; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 7 页 - - - - - - - - - 2 练习查询语句Example3.4.4 select distinct c
2、id 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 or
3、ders 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); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精
4、心整理 - - - - - - - 第 2 页，共 7 页 - - - - - - - - - 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 cust
5、omers 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; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理
6、 - - - - - - - 第 3 页，共 7 页 - - - - - - - - - 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.22.5（a)Find all
7、(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,produc
8、t combinations,no two of which are i the same city. 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 7 页 - - - - - - - - - select c.cid,a.aid,p.pid from customers c,agents a,products p where c.citya.city and a.cityp.city and p.cityc.city 名师资料总结 - - -精品资料欢迎下载 - -
9、- - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 7 页 - - - - - - - - - (e)Get product names ordered by at least one customers based in Dallas though an agent based in Tokyo. select p.pname from customers c,agents a ,products p,orders o w here o.cid=c.cid and o.aid=a.aid and o.pid=p.pid a
10、nd 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 a2 Where a1.city=a2.city and a1.aid500 and c.city=Kyoto 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 7 页
11、 - - - - - - - - - 3.2(a)Retrieve aid values of agents who receive more than the minimum percent commission(column name:percent) select aid from agents where commissionany (select commission from agents) (b)Retrieve aid value of agents who receive the minimum percent commission(column name:percent)
12、select aid from agents where 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 mission5; The fact that two queries have the same resu
13、lt for a table of a given content is not sufficient to guarantee that the two queries are equivalent. 三、小结：这次实验主要是练习简单的查询和子查询，有了第一次的上机，这次就得心应 手 了 许 多 。 先 是 上 机 练 习 一 下 课 本 上 的 例 子 (Example3.4.1-3.4.3; Example3.4.6- 3.4.14； Example3.5.1.-3.5.2). 找出查询的写法规律。然后是查询课后练习，有时查询出来的结果觉得很不可思议，亲自查了一下表才知道错了，于是检查错误， 然后更改， 比如 2.5(c)、(b)就遇到这种情况。 这样反复练习之后，就大致掌握了解题的基本规律，基本方法。做其他的题目时，思考的时间明显节省了很多。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 7 页 - - - - - - - - -
