Глава 14

SELECT cname, city, rating, High Rating FROM Customers WHERE rating >= 200

UNION

SELECT cname, city, rating, Low Ratlng FROM Customers WHERE rating < 200;

SELECT cname, city, rating, High Rating FROM Customers

WHERE rating >= 200 UNION

SELECT cname, city, rating, Low Rating FROM Customers

WHERE NOT rating >= 200;

Различие между этими двумя предложениями - в форме второго предиката. Обратите внимание, что в обоих случаях строка Low Rating имеет в начале дополнительный пробел для того, чтобы совпадать со строкой High Rating по длине.

SELECT *

FROM Salespeople

WHERE city <> ALL (SELECT city

FROM Customers);

SELECT *

FROM Salespeople

WHERE NOT city = ANY (SELECT city

FROM Customers);

SELECT * FROM Orders

WHERE amt > ALL (SELECT amt

FROM Orders a, Customers b

WHERE a.cnum = b.cnum AND b.city = London);

SELECT *

FROM Orders

WHERE amt > (SELECT MAX (amt)

FROM Orders a, Customers b

WHERE a.cnum = b.cnum AND b.city = London);

SELECT cnum, cname FROM Customers a WHERE 1 < (SELECT COUNT (*)

FROM Orders b

WHERE a.cnum = b.cnum)

UNION

SELECT snum, sname FROM Salespeople a WHERE 1 < (SELECT COUNT (*) FROM Orders b

WHERE a.snum = b.snum)

ORDER BY 2;

SELECT snum

FROM Salespeople

WHERE city = San Jose

UNION

(SELECT cnum

FROM Customers

WHERE city = San Jose

UNION ALL

SELECT onum FROM Orders

WHERE odate = 10/03/1990);

Глава 15

INSERT INTO Salespeople (city, cname, comm, cnum) VALUES (San Jose, Blanco, NULL, 1100);

DELETE FROM Orders WHERE cnum = 2006;

UPDATE Customers

SET rating = rating + 100 WHERE city = Rome;

UPDATE Customers SET snum = 1004 WHERE snum = 1002;

INSERT INTO Multicust SELECT *

FROM Salespeople

WHERE 1 < (SELECT COUNT (*)

FROM Customers

WHERE Customers.snum = Salespeople.snum);

DELETE FROM Customers

WHERE NOT EXISTS (SELECT *

FROM Orders

WHERE cnum = Customers.cnum);

UPDATE Salespeople

SET comm = comm + (comm * .2) WHERE 3000 < (SELECT SUM (amt)

FROM Orders

WHERE snum = Salespeople.snum);

В более сложный вариант этой команды можно было бы вставить проверку, чтобы убедиться, что значения комиссионных не превышают 1.0 (100%):

UPDATE Salespeople

SET comm = comm + (comm * .2)

WHERE 3000 < (SELECT SUM (amt) FROM Orders

WHERE snum = Salespeople.snum) AND comm + (comm * .2) < 1.0;

Эти проблемы могут иметь другие, такие же хорошие решения.

Глава 17

CREATE TABLE Customers (cnum integer, cname char(10), city char(10), rating integer, snum integer);

CREATE INDEX Datesearch ON Orders(odate);

(Все индексные имена, используемые в этих ответах - произвольные.)

CREATE UNIQUE INDEX Onumkey ON Orders(onum);