[课业] 25 | 数据库基础 | 基础SQL查询语言

原创已于 2023-01-21 22:02:30 修改 · 1.6k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #oracle

于 2020-08-13 01:49:01 首次发布

课业专栏收录该内容

25 篇文章

订阅专栏

文章目录

知识
例题

知识

介绍

SQL，结构化查询语言，允许我们对计算机化的操作数据库进行查询和操作的语言，包括update，insert，delete等操作；现在正由关系模型转为对象关系模型
注：定义中，[]中内容可选，{}中内容表示该短语内容在实际应用中可能出现0次或多次

创建数据库

ANSI SQL的数据类型
字符型(Character)：定长字符串(Character(n), Char(n))，变长字符串(Character Varying(n), Char Varying(n))
数值型(Numeric)：Numeric(p, s), Decimal(p, s), Dec(p, s)
数值型中，p(precision)表示精度，即总的数字个数；s(scale)表示小数位数，小数点右侧的数据个数
数值型数据类型还包括：Interger, int, smallint, Float§, Real, Double precision
Oracle中数据类型
字符类型：定长字符串(char(n), 1 $\leq$ n $\leq$ 2000)，变长字符串（varchar(n), 1 $\leq$ n $\leq$ 4000），变长字符串数据/文本数据(long, 最大2GB)
数值类型：固定的浮点数(Number)，还可以表示成Number(precision, scale)，precision表示精度，scale表示小数位数；Number(*, scale)，表示小数点右侧取scale位；Number(precision)，缺省scale值，scale值的缺省值为0
注意：
p值可以 $\geq$ 整数部分数字个数，但是不能小于

scale的负值：表示对小数点左边的s个数字进行舍入（如1234567.89，NUMBER(7, -4)表示对小数点左4位数字进行舍入，结果为1230000）
举例

Input Data	Specified As	Stored As
7,456,123.89	NUMBER	7456123.89
7,456,123.89	NUMBER(* , 1)	7456123.9
7,456,123.89	NUMBER(9)	7456124 同NUMBER(7)答案
7,456,123.89	NUMBER(9, 2)	7456123.89
7,456,123.89	NUMBER(9, 1)	7456123.9
7,456,123.89	NUMBER(6)	not accepted
7,456,123.89	NUMBER(7, -2)	7456100

创建表的声明

CREATE TABlE tablename(
	colname datatype [NOT NULL]
	{, colname datatype [NOT NULL]...}	//表示可以单属性，也可以多属性
	[, PRIMARY KEY (colname{, colname...})]  //表示可以设置主键，也可以不设置
);

简单选择语句

简单的select语句

SELECT ...
FROM ...
[WHERE...]
[GROUP BY ...[HAVING ...]]
[ORDER BY];

具体格式

SELECT * | colname{, colname...}
FROM tablename{, tablename...}
[WHERE search_conition]
[GROUP BY colname{, colname...}
	[HAVING search_condition]]
[ORDER BY colname [ASC | DESC]
	{, colname [ASC | DESC]...}];

FROM子句的集合之间是笛卡尔积
对于关系代数中的查询
– 单个关系
(R where Condition) [A₁, A₂, …, A_m]
写成SQL语句为：
```
SELECT A1, A2, …, Am
FROM R
WHERE Condition;
```
– 乘积关系
((R₁ $\times$ R₂ $\times$ … $\times$ R_n) where Condition)[A₁, A₂, …, A_m]
写成SQL语句为
```
SELECT A1, A2, ..., Am
FROM R1, R2, ..., Rn
WHERE Condition;
```
– 连接关系
Head(R) = {A₁, …, A_n, B₁, …, B_k}, Head(S) = {B₁, …, B_k, C₁, …, C_m}
((R $\infty$ S) where Condition)[A₁, …, A_n]
写成SQL语句
```
SELECT A1, A2, ..., An
FROM R, S
WHERE Condition and R.B1 = S.B1 and R.B2 = S.B2 and ... and R.Bk = R.Bk;	//这里将笛卡尔积转化为连接
```
– Theta连接
(R $\infty$ _Condition S)[A₁, …, A_n]
```
//literally，Theta连接的含义，完全吻合
SELECT A1, A2, ..., An
FROM R, S
WHERE Condition;
```
all与distinct
all和distinct是SQL语言中的两个关键字：all为缺省值，表示重复的行不会被删掉；distinct表示检索后的每一行是唯一的
表和列的别名(table and column alias)
– From子句体现表别名，两种方式
```
1. table_name as alias_name
2. table_name alias_name
```
– Select子句中体现列的别名
```
expression as alias_name
```

子查询

概述

出现在SELECT语句中的SELECT语句
子查询和格式

子查询	子查询格式
IN谓词	expr [NOT] IN (subquery)
量化比较谓词	expr $\theta$ SOME
EXISTS谓词	[NOT] EXISTS (subquery)
BETWEEN谓词	expr [NOT] BETWEEN expr1 and expr2
NULL谓词	column IS [NOT] NULL
LIKE谓词	column [NOT] LIKE val1 [ESCAPE val2]

注：LIKE谓词中会出现’_‘或’%'，前者代表单个字符，后者代表任意0个或多个字符序列

相关子查询和独立子查询
相关子查询从外层select语句中接受数据
独立子查询中内层独立于外层
表和列名的合适作用域
外层不可以用内层的表，内层可用外层的表

IN谓词

将原本的嵌套for循环改为两个并行for循环，一先一后，提高查询速度
示例见后

量化比较谓词

SOME 与ANY是等价的，不过ANY含义可能带来歧义，故只用SOME就可以
量化比较谓词与IN谓词
IN <——> =SOME
NOT <——> <>ALL

EXISTS谓词

EXISTS谓词为true当且仅当后面的子查询返回非空集合
NOT EXISTS谓词为true当且仅当后面的子查询返回空集
NOT EXIST谓词可以实现MINUS

BETWEEN谓词

expr [NOT] BETWEEN expr1 AND expr2

IS NULL谓词

colname IS [NOT] NULL

LIKE谓词

colname [NOT] LIKE val1 [ESCAPE val2]
注意：
‘_’：任意单个字符的通配符
‘%’：0个/多个字母序列的通配符
转义指示符
其他字符代表自身

UNION操作符和FOR ALL条件

UNION操作符
Subquery UNION [ALL] Subquery
当关键词是UNION时，无重复行
当关键词是UNION ALL时，可能有重复行
FOR ALL条件用来实现除法
实现除法的三步骤
写出反例的样子，找出所有反例
说明不存在反例满足的条件
用这个条件搜索
（“所有”描述的东西的特征放在中间层，外层放直接问的东西，内层放将外面两层联系起来的东西）

一些高级的SQL语法

INTERSECT与EXCEPT
subquery {UNION | INTERSECT | EXCEPT [ALL]} subquery
这三个不带ALL时是在计算之后消除左右的重复行
INTERSECT——差不多同 $\cap$
INTERSECT ALL
如果Q中有2个行A，P中有3个行A，那么P INTERSECT ALL的结果是2个行A（同一重复行在两边出现的最小数目）
EXCEPT——差不多同–，结果是左边独有的行
EXCEPT ALL
同一重复行，左边的数量减去右边的数量（但如果左边少结果里就有0个该行）
高级SQL中的连接形式
tableref简单来讲就是在FROM字句中做子查询，注意子查询结果要起一个别名
还可以选择某个表中的部分属性属性临时使用

SQL中的集合方法

COUNT, MAX, MIN, SUM, AVG
除了COUNT外都必须作用在简单值（数字或字符串集合，而不是拥有多个列值的行集）组成的集合上
集合方法对列中的空值忽略不计
空值的含义：未定义的、不适用的；有意义但是目前处于未知状态的
空值返回boolean为unknown，基本同false
计算平均值时，为空值的位置直接忽略
一个重要约束：WHERE子句中的比较操作不许出现集合函数
集合函数不允许嵌套（但是可以实现嵌套，通过语法）

Groups of Rows in SQL

GROUP BY语句将产生一个行集
一种自然的报表功能：根据某些列值的共性把一个表所包含的全部行分成若干个子集，然后对每个子集执行集合函数

出现在选择列表里的集合函数分别将每一组里的行聚集起来并为每个组创建一个值，选择列表中的所有属性都必须以单个原子值来对应由GROUP BY分得的每一组（意味选出来几个属性，group by后就得有相应个数的属性（好把每一列都有自己的表头，选择列表中用了集合函数那个不算））
例如

注：选择列表是SELECT子句中的各个被选出来的属性组成的列表；集合函数是MAX那几个函数
GROUP BY子句要紧跟在WHERE子句后面
求解子查询步骤（不考虑UNION INTERSECT EXCEPT等操作）
首先，对FROM子句中所有的表做笛卡尔积
接着，删除不满足WHERE子句中的行
然后，根据GROUP BY子句对剩余的行进行分组
然后，求解HAVING子句
最后，求出选择列表中表达式的值
想要在包含group by子句的select语句的结果中去掉一些行，不可以在where子句中设置条件，集合函数不能出现在WHERE子句中，除非他是在子查询的选择列表中（因为WHERE子句中的条件不可能顾及在选择列表中的合计值，因为这些合计值取决于实际的分组情况）
一个不正确的例子
为了能创建基于分组情况的条件，新的子句——HAIVING语句，他的求解过程发生在GROUP BY操作之后
一个正确的例子

HAVING子句只能对于各个分组相对应的单值（即合法地出现在选择序列中的值）进行测试（因为它发生在WHERE之后）
一般情况下HAVING子句和GROUP BY子句配套使用，如果GROUP BY子句被省略，那么HAVING子句将整个结果当作一个组使用

Insert, Update, Delete Statements

Insert插入元组
Update改变元组
Delete删除元组

例题

e.g. 1. 创建CAP数据库
解：

CREATE TABLE CUSOMERS(
	cid CHAR(4) NOT NULL,
	cname VARCHAR(13),
	city VARCHAR(20),
	discount REAL,
	PRIMARY KEY(cid)
);
CREATE TABLE AGENTS(
	aid CHAR(3) NOT NULL,
	aname VARCHAR(13),
	city VARCHAR(20),
	percent SMALLINT,
	PRIMARY KEY(aid)
);
CREATE TABLE PRODUCTS(
	pid CHAR(3) NOT NULL,
	pname VARCHAR(3),
	city VARCHAR(20),
	quantity INTEGER,
	price DOUBLE PRECISION,
	PRIMARY KEY(pid)
);
CREATE TABLE ORDERS(
	ordno INTEGER NOT NULL,
	cid CHAR(4),
	aid CHAR(3),
	pid CHAR(3),
	quantity INTEGER,
	dollars DOUBLE PRECISION,
	PRIMARY KEY(ordno)
);

e.g. 2. Find aid and names of agents that are based in NYC
解：
SELECT aid, aname
FROM AGENTS
WHERE city='NYC';

e.g. 3. Display all values of customers in table CUSTOMERS
解：
SELECT *
FROM CUSTOMERS;

e.g. 4. Select product ids of products for which orders are placed
解：
SELECT distinct pid
FROM ORDERS;

e.g. 5. Retrieve all (cname, aname) pairs where the customer places an order through the agent
解：
SELECT distinct cname, aname
FROM ORDERS, CUSTOMERS, AGENTS
WHERE ORDERS.cid = CUSTOMERS.cid and ORDERS.aid = AGENTS.aid;

e.g. 6. Retrieve all (cname, aname) pairs where the customer place an order through the agent
解：
SELECT distinct cname, aname
FROM CUSTOMERS C, ORDERS O, AGENTS A
WHERE C.cid = O.cid and A.aid = O.aid;

e.g. 7. 展示每一单的订单号，原价和折后价
解：

SELECT O.ordno, O.dollars, O.quantity * P.price * (1 - C.discount * 0.01) as MyBills	//列别名的应用，生成的结果表的属性名
FROM CUSTOMERS C, ORDERS O, PRODUCTS P
WHERE O.cid = C.cid and P.pid = O.pid;

e.g. 8. Lis all pairs of customer cids based in the same city
解：
SELECT C1.cid, C2.cid
FROM CUSTOMERS C1, CUSTOMRS C2
WHERE C1.cid < C2.cid and C1.city = C2.city;
解释：执行过程是一个嵌套for循环

e.g. 8. Find pids of products ordered by at least two customers
解：

SELECT O1.pid
FROM ORDERS O1, ORDERS O2
WHERE O1.cid < O2.cid and O1.pid = O2.pid;

或

SELECT distinct O1.pid
FROM ORDERS O1, ORDERS O2
WHERE O1.cid <> O2.cid and O1.pid = O2.pid;

e.g. 9. Get cids and names of customers ordering a product for which an order is placed by agent a06
解：
SELECT C.cid, C.cname
FROM CUSTOMERS C, ORDERS O1, ORDERS O2
WHERE O1.aid='a06' and O1.pid = O2.pid and C.cid = O2.cid;

e.g. 10.Retrieve cids of customers who place orders with agents in Duluth or Dallas
解：
不用子查询的解法

SELECT distinct O.cid
FROM ORDERS O, AGENTS A
WHERE O.aid = A.aid and (A.city = 'Duluth' or A.city = 'Dallad');

使用子查询

SELECT distinct cid
FROM ORDERS
WHERE aid IN(
	SELECT aid
	FROM AGENTS
	WHERE city='Duluth' or city='Dallas'; 
);

e.g. 11. Get all information concerning agents based in Duluth or Dallas
解：
SELECT *
FROM AGENTS
WHERE city IN {'Duluth', 'Dallas'};

e.g. 12. Get the names and discount of all customers who place orders through agents in Duluth or Dallas
解：

SELECT cname, discount
FROM CUSTOMERS
WHERE cid IN(
	SELECT ORDERS.cid
	FROM ORDERS
	WHERE ORDERS.aid IN(
		SELECT AGENTS.aid
		FROM AGENTS
		WHERE city IN {'Duluth', 'Dallas'};
	);
);

e.g. 13. Find the names of customers who order product p05
解：
独立子查询

SELECT distinct cname
FROM CUSTOMERS
WHERE cid IN(
	SELECT ORDERS.cid
	FROM ORDERS
	WHERE pid='p05';
);

相关子查询

SELECT distinct cname
FROM CUSTOMERS C
WHERE 'p05' IN(
	SELECT pid
	FROM ORDERS O
	WHERE O.cid = C.cid;
);

e.g. 14. Get the names of customers who order product p07 from agent a03
解：
SELECT cname
FROM CUSTOMERS C
WHERE cid IN(
	SELECT O.cid
	FROM ORDERS O
	WHERE O.aid='a03' and O.pid='p07';
);

e.g. 15. Find ordno values for all orders placed by customers in Duluth through agents in NYC
解：
SELECT ordno
FROM ORDERS O
WHERE (cid, aid) IN(
	SELECT cid, aid
	FROM CUSTOMERS C, AGENTS A
	WHERE C.city='Duluth' and A.city='NYC';
);

e.g. 16. Find aid values of agents with a minimum percent commission
解：
SELECT aid 
FROM AGENTS
WHERE percent <=ALL(
	SELECT percent 
	FROM AGENTS;
);

e.g. 17. Find all customers who have the same discount as that of any of the customers in Dallas or Boston
解：
SELECT *
FROM CUSTOMERS C1
WHERE C1.discount =SOME(
	SELECT C2.discount 
	FROM CUSTOMER C2
	WHERE C2.city IN {'Dallas', 'Boston'};
);

e.g. 18. Get cid values of customers with discount smaller than those of any customers who live in Duluth
解：

SELECT cid
FROM CUSTOMRERS
WHERE discount <ALL(
	SELECT discount
	FROM CUSTOMERS
	WHERE ciy='Duluth';
);

或

SELECT cid 
FROM CUSTOMERS C1
WHERE NOT EXISTS(
	SELECT *
	FROM CUSTOMRES C2
	WHERE C1.discount >= C2.discount;
);

e.g. 19. Find the cid values of customers with discount smaller than customer who lives in Duluth
解：

SELECT cid
FROM CUSTOMRS
WHERE discount <SOME(
	SELECT discount 
	FROM CUSTOMERS
	WHERE cit='Duluth';
);

或

SELECT cid
FROM CUSTOMERS C1
WHERE EXISTS(
	SELECT *
	FROM CUSTOMRS C2
	WHERE C1.discount < C2.discount;
);

或

SELECT C1.cid 
FROM CUSTOMRS C1, CUSTOMERS C2
WHERE C1.discount < C2.discount;

e.g. 20. Retrieve all customers’ names where the customer places an order through agent a05
解：

SELECT cname
FROM CUSTOMERS
WHERE cid IN(
	SELECT O.cid
	FROM ORDERS O
	WHERE O.aid='a05';
);

或

SELECT cname
FROM CUSTOMERS C
WHERE EXISTS(
	SELECT * 
	FROM ORDERS O
	WHEER O.cid=C.cid and O.aid='a05';
);

e.g. 21. Get cids of customers who order both products p01 and p07
解：

(SELECT cid FROM ORDERS WHERE pid='p01';)
	INTERSECT
(SELECT cid FROM ORDERS WHERE pid='p07';)

或

SELECT O1.cid
FROM ORDERS O1
WHERE O1.pid='p01' and O1.pid IN(
	SELECT O2.pid
	FROM ORDERS O2
	WHERE O2.pid='p07';
);

e.g. 22. Find all customer names where the customer does not place an order through agent a05
解：

SELECT cname 
FROM CUSTOMERS 
WHERE cid NOT IN(
	SELECT cid
	FROM ORDERS
	WHERE aid='a05';
);

或

SELECT cname
FROM CUSTOMERS C
WHERE NOT EXIST(
	SELECT * 
	FROM ORDERS O
	WHERE O.cid=C.cid and O.aid='a05';
);

e.g. 23. Find all (cid, aid) pairs where the customers does not place an order through the agent
解：

SELECT cid, aid
FROM CUSTOMERS C, AGENTS A
WHERE NOT EXISTS(
	SELECT *
	FROM ORDERS O
	WHERE O.aid=A.aid and O.cid=C.cid;
);

或

SELECT cid, aid
FROM CUSTOMERS, AGENTS
WHERE (cid, aid) NOT IN(
	SELECT (cid, aid)
	FROM ORDERS;
);

e.g. 24. Find cids of all customers who don’t place any order through agent a03
解：

SELECT cid
FROM CUSTOMERS
WHERE cid NOT IN(
	SELECT cid
	FROM ORDERS
	WHERE aid='a03';
);

或

SELECT cid 
FROM CUSTOMERS C
WHERE NOT EXISTS(
	SELECT *
	FROM ORDERS O
	WHERE O.cid=C.cid and O.aid='a03';
);

e.g. 25. Get the names of customers who order at least one product at $0.50
解：

SELECT cname
FROM CUSTOMERS 
WHERE cid IN(
	SELECT cid 
	FROM ORDERS
	WHERE pid IN(
		SELECT pid
		FROM PRODUCTS
		WHERE price=0.50;
	);
);

e.g. 26. Retrieve customers who place orders only through agent a03
解：
SELECT cid
FROM ORDERS
WHERE NOT EXISTS(	
	SELECT *
	FROM ORDERS
	WHERE aid<>'a03';
);

e.g. 27. Find products that have never been ordered by a customer based in NYC through an agent based Boston
解：

SELECT pid
FROM PRODUCTS
WHERE pid NOT IN(
	SELECT pid
	FROM ORDERS O
	WHERE (cid, aid) IN (
		SELECT cid, aid
		FROM CUSTOMERS C, AGENTS a
		WHERE O.cid = C.cid and O.aid = A.aid and C.city='NYC' and A.city='Boston';
	);
);

e.g. 28. Get cids of customers who place an order through at least one agent who places an order for product p03
解：
SELECT cid
FROM ORDERS
WHERE aid IN(
	SELECT aid
	FROM ORDERS
	WHERE pid='p03';
);

e.g. 29. Get cids of all customers who have the same discount as any customer in Dallas or Boston
解：
SELECT cid
FROM CUSTOMERS
WHERE discount IN(
	SELECT discount
	FROM CUSTOMERS
	WHERE city='Dallas' or city='Boston';
);

e.g. 30. List pids of products that are ordered through agents who place orders for (possibly different) customers who order at least one product from an agent who has placed an order for customer c001
解：
SELECT pid
FROM ORDERS O1
WHERE O1.aid IN(
	SELECT O2.aid
	FROM ORDERS O2
	WHERE O2.cid IN(
		SELECT O3.cid
		FROM ORDERS O3
		WHERE O3.aid IN(
			SELECT O4.aid
			FROM ORDERS O4
			WHERE O4.cid='c001';
		);
	);
);

e.g. 31. Get pids of products not ordered by any customer living in a city whose name begin with the letter ‘D’
解：
SELECT pid
FROM PRODUCTS P
WHERE NOT EXISTS(
	SELECT *
	FROM ORDERS O
	WHERE P.pid = O.pid and O.cid IN(
		SELECT C.cid
		FROM CUSTOMERS C
		WHERE C.city LIKE 'D%' ;
	);
):

e.g. 32. Retrieve all customer names where the customer place an order through agent a05
解：
SELECT distinct cname
FROM CUSTOMERS
WHERE cid IN(
	SELECT O.cid
	FROM ORDERS O
	WHERE O.aid='a05';
);

e.g. 33. Get cid values of customers who order both product p01 and p07
解：
SELECT cid
FROM ORDERS
WHERE pid='p01' and cid IN(
	SELECT O.cid
	FROM ORDERS O
	WHERE O.pid='p07';
);

e.g. 34. Retrieve all customers’ names where the customer does not place an order through a05
解：
SELECT cname
FROM CUSTOMERS
WHERE cid NOT IN(
	SELECT O.cid
	FROM ORDERS O
	WHERE O.aid='a05';
);

e.g. 35. Retrieve all data about customers whose name begins with letter ‘A’
解：
SELECT *
FROM CUSTOMERS 
WHERE cname LIKE 'A%';

e.g.36. Retrieve cid values of customers whose name begins with ‘Tip_’ and has a arbitrary number of characters following
解：
SELECT cid
FROM CUSTOMERS
WHERE cname LIKE "Tip\_%' ESCAPE '\';

e.g. 37. Retrieve cid values of customers whose name does not have a third letter equal to ‘%’
解：
SELECT cid
FROM CUSOTMERS
WHERE cname LIKE '__\%%' ESCAPE '\';

e.g. 38. Retrieve cid values of customers whose name starts with the sequence ‘ab’
解：
SELECT cid
FROM CUSTOMERS 
WHERE cname LIKE 'ab\\%' ESCAPE'\';
或
SELECT cid
FROM CUSTOMERS
WHERE cname LIKE 'ab\%';

e.g. 39. Find cid values of customers with discount smaller than those of any customers who live in Duluth
解：

SELECT C1.cid
FROM CUSTOMER C1
WHERE C1.discount <ALL(
	SELECT C2.discount
	FROM CUSTOMERS C2
	WHERE C2.city='Duluth';
);

或

SELECT C1.cid
FROM CUSTOMERS C1
WHERE NOT EXISTS(
	SELECT *
	FROM CUSOTMERS C2
	WHERE C1.discount >= C2.discount and C2.city='Duluth';
);

e.g. 40. Find cid values of customers with discount smaller than a customer who lives in Duluth
解：

SELECT C1.cid
FROM CUSTOMERS C1
WHERE C1.discount <=SOME(
	SELECT C2.discount
	FROM CUSTOMERS C2
	WHERE C2.city='Duluth;
);

或

SELECT C1.cid
FROM CUSTOMERS C1
WHERE EXISTS(
	SELECT * 
	FROM CUSTOMERS C2
	WHERE C1.discount < C2.discount and C2.city='Duluth';
);

或

SELECT C1.cid
FROM CUTOMERS C1, CUSTOMERS C2
WHERE C1.discount < C2.discount and C2.city='Duluth';

e.g. 41. Retrieve the city names containing customers who ordered product po1
解：

SELECT distinct city
FROM CUSTOMERS  C
WHERE C.cid IN(
	SELECT O.cid
	FROM ORDERS O
	WHERE O.pid='p01'
);

或

SELECT distinct city
FROM CUSTOMERS C
WHERE EXISTS(
	SELECT *
	FROM ORDERS O
	WHERE O.cid = C.cid and O.pid='p01';
);

或

SELECT distinct C.city
FROM CUSTOMERS C, ORDERS O
WHERE C.cid = O.cid and O.pid='p01';

e.g. 42. Get the name of customers who order at least one product priced at $0.50
解：

SELECT cname
FROM CUSTOMERS C
WHERE C.cid IN(
	SELECT O.cid
	FROM ORDERS O
	WHERE O.pid IN(
		SELECT P.pid
		FROM PRODUCTS P
		WHERE P.price=0.05;
	);
);

或

SELECT cname
FROM CUSTOMERS C, ORDERS O, PRODUCTS P
WHERE C.cid = O.cid and O.pid = P.pid and P.price=0.50;

e.g. 43. Retrieve customers who place orders only through agent a03
解：
SELECT O1.cname
FROM ORDERS O1
WHERE O1.cid NOT IN(
	SELECT O2.cid
	FROM ORDERS O2
	WHERE O2.aid<>'03';
);

e.g. 44. Find products that have never been ordered by a customer based in NYC through an agent based in Boston
解：

SELECT P.pid
FROM PRODUCTS P
WHERE P.pid NOT IN(
	SELECT O.pid
	FROM ORDERS O
	WHERE (O.cid, O.aid) IN (
		SELECT C.cid, A.aid
		FROM CUSTOMRES C, AGENTS A
		WHERE C.city='NYC' and A.city='Boston';
	);
);

e.g. 45. Get cids of customers who place an order through at least one agent who places an order for product p03
解：
SELECT O1.cid
FROM ORDERS O1
WHERE O1.aid IN(
	SELECT O2.aid
	FROM ORDERS O2
	WHERE O2.pid='p03';
);
或
SELECT O1.cid
FROM ORDERS O1, ORDERS O2
WHERE O1.aid = O2.aid and O2.pid='p03';

e.g. 46. Get cids of all customers who have the same discount as any customer in Dallas or Boston
解：
SELECT cid
FROM CUSTOMERS
WHERE discount =SOME(
	SELECT discount
	FROM CUSTOMERS 
	WHERE city IN {'Dallas', 'Boston'};
);

e.g. 47. List pids of products that are ordered through agents who place orders for (possibly different) customers who order at least one product from an agent who has placed an order for customer c001
解：
SELECT O1.pid
FROM ORDERS O1
WHERE O1.aid IN(
	SELECT O2.aid
	FROM ORDERS O2
	WHERE O2.cid IN(
		SELECT O3.cid
		FROM ORDERS O3
		WHERE O3.aid IN(
			SELECT O4.aid
			FROM ORDERS O4
			WHERE O4.cid='c001';
		);
	);
);

e.g. 48. Get pids of products not ordered by any customer living in a city whose name starts with letter ‘D’
解：
SELECT P.pid
FROM PRODUCTS P
WHERE P.pid NOT IN(
	SELECT O.pid
	FROM ORDERS O, CUSTOMERS C
	WHERE O.cid = C.cid and C.city LIKE 'D%';
);

e.g. 49. Retrieve all cities where either a customer or an agent, or both, is based
解：
(SELECT city FROM CUSTOMERS)
	UNION
(SELECT city FROM AGENTS)

e.g. 50. Find cids of customers who place orders with all agents based in NYC
解：

SELECT O1.cid
FROM ORDERS O1
WHERE NOT EXISTS(
	SELECT *
	FROM AGENTS A
	WHERE A.city='NYC' and NOT EXISTS(
		SELECT *
		FROM ORDERS.O2
		WHERE O1.cid = O2.cid and A.aid = O2.aid;
	);
);

e.g. 51. Get the aid values of agents in NYC or Duluth who place orders for all products costing more than a dollar
解：

SELECT A.aid
FROM AGENTS A
WHERE (A.city='Duluth' or A.city='NYC') and NOT EXISTS(
	SELECT * 
	FROM PRODUCTS P
	WHERE P.price>1.0 and NOT EXISTS(
		SELECT *
		FROM ORDERS O2
		WHERE O2.aid = A.aid and O2.pid = P.pid;
	);
);

e.g. 52. Find aid values of agents who place orders for product p01 as well as for all products costing more than a dollar
解：

SELECT A.aid
FROM AGENTS A
WHERE A.aid IN 
(
	SELECT O1.aid 
	FROM ORDERS O1 
	WHERE O1.pid='p01';
)
and NOT EXISTS
(
	SELECT *
	FROM PRODUCTS P
	WHERE P.price>1.0 and NOT EXISTS(
		SELECT *
		FROM ORDERS O2
		WHERE O2.aid = A.aid and O2.pid = P.pid;
	);
);

e.g. 53. Find cid for customers who order all products ordered by customer c006
解：

SELECT cid C
FROM CUSTOMERS
WHERE NOT EXISTS(
	SELECT * 
	FROM ORDERS O1
	WHERE O1.cid='c006' and NOT EXISTS(
		SELECT *
		FROM ORDERS O2
		WHERE O1.pid = O2.pid and O.cid = C.cid;
	);
);

e.g. 54. Find pid values of products supplied to all customers in Duluth
解：

SELECT pid
FROM PRODUCTS P
WHERE NOT EXISTS(
	SELECT *
	FROM CUSTOMERS C
	WHERE C.city=='Duluth' and NOT EXISTS(
		SELECT *
		FROM ORDERS O
		WHERE O.pid = P.pid and O.cid = C.cid;
	);
);

e.g. 55. Get names of customers who order all products priced at $0.5
解：

SELECT cname
FROM CUSTOMERS C
WHERE NOT EXISTS(
	SELECT *
	FROM PRODUCTS P
	WHERE P.price=0.50 and NOT EXISTS(
		SELECT *
		FROM ORDERS O
		WHERE O.cid = C.cid and O.pid = P.pid;
	);
);

e.g. 56. Get cids of customers who order all products that anybody orders
解：

SELECT cid
FROM CUSTOMERS C
WHERE NOT EXISTS(
	SELECT *
	FROM ORDERS O1
	WHERE NOT EXISTS(
		SELECT *
		FROM ORDERS O2
		WHERE O1.pid = O2.pid and O2.cid = C.cid;
	);
);

e.g. 57. Get aids of agents who take orders on at least that set of products ordered by c004
解：

SELECT aid
FROM AGENTS A
WHERE NOT EXISTS(
	SELECT *
	FROM ORDERS O1
	WHERE O1.cid = 'c004' and NOT EXISTS(
		SELECT *
		FROM ORDERS O2
		WHERE O2.aid = A.aid and O2.pid = O1.pid;
	);
);

e.g. 58. Get aids of agents who place orders for all customers who have discount greater than 8
解：

SELECT aid
FROM AGENTS
WHERE NOT EXISTS(
	SELECT *
	FROM CUSTOMERS C
	WHERE C.discount>8 and NOT EXISTS(
		SELECT *
		FROM ORDERS O
		WHERE O.aid = A.aid and O.cid = C.cid;
	);
);

e.g. 59. Get cids for customers with the following property: if customer c006 orders a product x through agent y, so the customer orders the product x through the agent y
解：
SELECT cid
FROM CUSTOMERS C
WHERE NOT EXISTS(
	SELECT *
	FROM ORDERS O1
	WHERE O1.cid='c006' and NOT EXISTS(
		SELECT * 
		FROM ORDERS O2
		WHERE O2.aid = O1.aid and O2.cid = C.cid and O1.pid = O2.pid;
	);
);

e.g. 60. Get aids of agents who place orders for all customers who place orders for all products costing more than a dollar through the agent
解：
SELECT aid
FROM AGENTS A
WHERE NOT EXISTS(
	SELECT *
	FROM PRODUCTS P, CUSTOMERS C
	WHERE P.price>1.0 and NOT EXISTS(
		SELECT *
		FROM ORDERS O
		WHERE A.aid = O.aid and P.pid = O.pid and C.cid = O.cid;
	);
);

e.g. 61. Get aids of agents who place orders for all customers who place orders for all products
解：

SELECT aid
FROM AGENTS 
WHERE NOT EXISTS(
	SELECT *
	FROM CUSTOMERS C
	WHERE NOT EXISTS(
		SELECT *
		FROM PRODUCTS P
		WHERE NOT EXISTS(
			SELECT * 
			FROM ORDERS O1
			WHERE O1.pid = O.pid and O1.cid = C.cid;
		);
		and NOT EXISTS(
			SELECT *
			FROM ORDERS O2
			WHERE O2.aid = A.aid and O2.cid = C.cid;
		);
	);
);

e.g. 62. Get cids of all customers who have the same discount as any customer in Dallas or Boston
解：
SELECT C1.cid
FROM CUSTOMERS C1
WHERE C1.discount IN(
	SELECT C2.discount
	FROM CUSTOMERS C2
	WHERE C2.city = 'Dallas' or C2.city = 'Boston';
);

e.g. 63. 列出折扣值小于最大折扣值的所有顾客cid
解：
SELECT cid
FROM CUSTOMERS
WHERE discount <ALL(
	SELECT MAX(discount)
	FROM CUSTOMERS;
);

e.g. 64. Retrieve all customers’ names where the customer places at least two orders for the same product
解：

SELECT cname
FROM CUSTOMERS C, (
		SELECT O1.cid as TARGETcid
		FROM ORDERS O1, ORDERS O2
		WHERE O1.pid = O2.pid and O1.ordno <> O2.ordno and O1.pid = O2.pid;
	) as T
WHERE T.cid = C.cid;

e.g. 65. Get the total dollar amount of all dollars
解：
SELECT SUM(dollars) as TOTALDOLLARS
FROM ORDERS;

e.g. 66. Get the total quantity of product p03 that has been ordered
解：
SELECT COUNT(quantity) as TOTALQUANTITY
FROM ORDERS 
WHERE pid='p03';

e.g. 67. Find the total number of customers
解：
SELECT COUNT(*)
FROM CUSTOMERS;

e.g. 68. Get the number of cities where customers are based
解：
SELECT COUNT(distinct citied)
FROM CUSTOMERS;

e.g. 69. List the cid values of all customers who have a discount less than the maximum discount
解：
SELECT C1.cid
FROM CUSTOMERS C1
WHERE C1.discount <SOME(
	SELECT MAX(C2.discount)
	FROM CUSTOMERS C2;
);

e.g. 70. Find products ordered by at least two customers
解：

SELECT O1.pid
FROM ORDERS O1
WHERE 2 <= (
	SELECT COUNT(ordno)
	FROM ORDER O2
	WHERE O2.pid = O1.pid and O2.cid <> O1.cid;
);

e.g. 71. Find the total product quantity ordered of each individual product by each individual agent
解：
select pid, aid, SUM(quantity) as totalQuantity
from ORDERS
group by aid, pid;

e.g. 72. Find the agent name and aid, product name and pid, together with the total quantity each agent supplies of that product to customers c002 and c003
解：
select aname, aid, pname, pid, SUM(quantity) as total
from orders o, agents a, product p
where o.aid = a.aid and p.pid = o.pid and (o.cid = 'c002' or o.cid='c003')
group by aname, aid, pname, pid;

e.g. 73. Find all product and agent ids and the total quantity ordered of the product by the agent, when the quantity exceeds 1000
解：
select pid, aid, sum(quantity) as totalQuantity
from orders 
group by pid, aid
having sum(quantity) > 1000;

e.g. 74. Provide pid values of all products purchased by at least two customers
解：

select distinct pid
from orders o1, orders o2
where o1.pid = o2.pid and o1.cid <> o2.cid

或

select pid
from orders 
group by pid
having (distinct cid) > 2;

e.g. 75. Find the average, overall agents of the maximum dollar sales made by each agent
解：
select avg(t.x)
from (select aid, max(dollars) as x from orders group by aid) as t;

e.g. 76. Insert a turple
解：

insert into orders (ordno, month, cid, aid, pid)
	value(1107, 'aug', c006', 'a04', 'p01')

e.g. 77. Insert by subquery
解：

create table customers(	
	cid char(4) not null,
	cname varchar(13),
	city varchar(20),
	discount real
);
insert into customers
	select *
	from customers
	where city in {'Dallas', 'Boston'};

e.g. 78. Give all agents in NYC a 10% raise in the percent commission they earn on each order
解：
update agents 
	set percent = 1.1 * percent
	where city='NYC';

e.g. 79. Give all customers who have total orders of more than $1000 a 10% increase in the discount they receive
解：
update customers
	set discount = 1.1 * discount
	where cid in (
		select cid
		from orders
		group by cid
		having sum(dollars) > 1000;
	);