内容发布更新时间 : 2024/5/6 7:06:24星期一 下面是文章的全部内容请认真阅读。

实验二 简单查询及子查询

一. 目的：

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

Example3.4.10

select distinct c.cname 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;