数据库实验二报告-推荐下载

实验二简单查询及子查询

一. 目的：

练习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 discnt

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 city.

select c.cid,a.aid,p.pid from customers c,agents a,products p

where 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 o

w 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 a2

Where a1.city=a2.city and a1.aid

(k) Find pids of products ordered through agent a03 bur not through agent a06

s elect distinct pid from orders x

w here x.aid='a03' and not exists (select pid from orders y

where y.aid='a06'and x.pid=y.pid)

(o)Get names of agents who play orders for all products ordered by customer c002

select 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 o

where c.cid = o.cid and o.dollars>500 and c.city='Kyoto'