MySQL——homework05

#(1) Find all (ordno, pid) pairs for orders of quantity equal to 1000 or more.
SELECT ordno,pid
FROM orders
WHERE qty>=1000
#(2) Find all product names of products priced between $0.50 and $1.00, inclusive.
SELECT pname
FROM products
WHERE price BETWEEN 0.5 AND 1
#(3) Find all (ordno, cname) pairs for orders of dollar value less than $500. Use one join here.
SELECT ordno,cname
FROM orders JOIN customers
ON orders.cid=customers.cid
WHERE dollars<500
#(4) Find all (ordno, aname) pairs for orders in March. Use one join here.
SELECT ordno,aname
FROM orders JOIN agents
ON orders.aid=agents.aid
WHERE month='Mar'
#(5) Find all (ordno, cname, aname) triples for orders in March. Use two joins here.
SELECT ordno,cname,aname
FROM orders,agents,customers
WHERE orders.cid=customers.cid AND orders.aid=agents.aid AND month='Mar'
#(6) Find all the names of agents in New York who placed orders of individual dollar value less than $500.
SELECT DISTINCT aname
FROM agents JOIN orders
ON agents.aid=orders.aid
WHERE city='New York' AND dollars<500
#(7) Find all product names of products in Duluth ordered in March.
SELECT DISTINCT pname
FROM products JOIN orders
ON products.pid=orders.pid
WHERE city='Duluth' AND month='Mar'
#(8) 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 selection.//并不需要出现在订单里
SELECT cid,aid,pid
FROM products,agents,customers
WHERE products.city=customers.city AND customers.city=agents.city
#(9) Find all (cid, aid, pid) triples for customer, agent, product combinations that are not all in the same city (any two maybe).
SELECT cid,aid,pid
FROM products,agents,customers
WHERE products.city!=customers.city OR customers.city!=agents.city
#(10) Find all (cid, aid, pid) triples for customer, agent, product combination, no two of which are in the same city.
SELECT cid,aid,pid
FROM products,agents,customers
WHERE products.city!=customers.city AND customers.city!=agents.city AND products.city!=agents.city
#(11) Get cities of agents booking an order from customer c002.
SELECT city 
FROM agents
WHERE aid IN
	(SELECT aid
	FROM orders
	WHERE cid='c002')
#(12) Get product names ordered by at least one customer based in Dallas through an agent based in Tokyo.
SELECT pname
FROM products
WHERE pid IN(
	SELECT pid
	FROM (orders JOIN customers ON orders.cid=customers.cid) JOIN agents
	ON orders.aid=agents.aid
	WHERE customers.city='Dallas' AND agents.city='Tokyo')
#(13) Get pids of products ordered through any agent who makes at least one order for a customer in Kyoto. NOTE: The request posed here is not the same as asking for pids of products ordered by a customer in Kyoto.
SELECT DISTINCT pid
FROM orders
WHERE aid IN(
	SELECT agents.aid
	FROM (customers JOIN orders ON customers.cid=orders.cid) JOIN agents
	ON orders.aid=agents.aid
	WHERE customers.city='Kyoto')
#(14) Display all pairs of aids for agents who live in the same city.
SELECT aid
FROM agents
WHERE city IN(
	SELECT city
	FROM agents
	GROUP BY city
	HAVING COUNT(*)>1)
#(15) Find cids of customers who did not place an order through agent a03.
SELECT cid
FROM customers
WHERE cid NOT IN(
	SELECT cid
	FROM orders
	WHERE aid='a03')
#(16) Find cids of customers who have the largest discount; separately, find those who have the smallest discount. NOTE: This is quite hard with the operations provided in relational algebra.
SELECT cid
FROM customers
WHERE discnt=(SELECT MAX(discnt) FROM customers)

SELECT cid
FROM customers
WHERE discnt=(SELECT MIN(discnt) FROM customers)
#(17) Find cids of customers who order all products.
SELECT *
FROM customers
WHERE cid='c001' AND NOT EXISTS(
	SELECT *
	FROM products
	WHERE NOT EXISTS(
		SELECT *
		FROM orders
		WHERE customers.cid=orders.cid AND products.pid=orders.pid
		)
	) 
#(18) Find pids of products ordered through agent a03 but not through agent a06.
SELECT pid
FROM products
WHERE pid IN (SELECT pid FROM orders WHERE aid='a03') AND NOT EXISTS(
	SELECT * FROM orders WHERE products.pid=orders.pid AND aid='a06'
)
#(19) Get pnames and pids of products that are stored in the same city as one of the agents who sold these products.
SELECT pname,products.pid
FROM (orders JOIN agents ON orders.aid=agents.aid) JOIN products
ON products.pid=orders.pid
WHERE products.city=agents.city
#(20) Get aids and anames of agents with aname beginning with the letter “N” who do not place orders for any product in Newark.
SELECT aid,aname
FROM agents
WHERE aname='N%' AND NOT EXISTS(SELECT *
								 FROM orders
								 WHERE orders.pid IN(SELECT pid FROM products WHERE city='Newark') AND agents.aid=orders.aid)
#(21) Get cids of customers who order both product p01 and product p07.
SELECT cid
FROM customers
WHERE NOT EXISTS(
	SELECT *
	FROM products
	WHERE pid IN ('p01','p07') AND NOT EXISTS(
		SELECT * FROM orders WHERE orders.pid=products.pid AND orders.cid=customers.cid
								  )
								)
#(22) Get names of agents who place orders for all products ordered by customer c002.
SELECT aname
FROM agents
WHERE NOT EXISTS(
	SELECT * FROM orders WHERE agents.aid=orders.aid AND orders.cid!='c002'
								)
#(23) Get names of agents who place orders for all products that are ordered by any customer at all. (Hint: The phrase “any customer at all” means the same as “some customer.”)
SELECT aname
FROM agents
WHERE NOT EXISTS(
	SELECT * FROM orders WHERE agents.aid=orders.aid AND orders.cid!='XXX'
								)
#(24) Get (cid, aid, pid) triples for customer, agent, product combination so that at most two of them are in the same city. (Is this equivalent to any of the first three queries of this exercise, (1), (2), (3)？)
SELECT * FROM customers,agents,products
MINUS #该函数在SQLsever中可用 MySQL中需要用leftjoin模拟 但我不会
SELECT * FROM customers,agents,products WHERE customers.city=agents.city AND agents.city=products.city
#(25) Get pids of products ordered by all customers who place any order through agent a03.
SELECT pid
FROM orders
WHERE cid IN
	(SELECT cid
	FROM orders
	WHERE aid='a03')
#(26) Get aids of agents who place individual orders in dollar value greater than $500 for customer living in Kyoto.
SELECT agents.aid
FROM (agents JOIN orders ON agents.aid=orders.aid) JOIN customers ON orders.cid=customers.cid
WHERE dollars>500 AND customers.city='Kyoto'
#(27) Give all (cname, aname) pairs where the customer places an order through the agent.
SELECT cname,aname
FROM orders,customers,agents
WHERE orders.cid=customers.cid AND agents.aid=orders.aid
#(28) [Hard] Get cids of customers who order all their products through only one agent.
SELECT cid
FROM orders
GROUP BY cid
HAVING COUNT(DISTINCT aid)=1