Глава 12
1. SELECT *
FROM Salespeople first
WHERE EXISTS
(SELECT *
FROM Customers second
WHERE first.snum = second.snum
AND rating = 300);
ОТВЕТЫ НА УПРАЖНЕНИЯ 367
___________________________________________________________________
2. SELECT a.snum, sname, a.city, comm
FROM Salespeople a, Customers b
WHERE a.snum = b.snum
AND b.rating = 300;
3. SELECT *
FROM Salespeople a
WHERE EXISTS
(SELECT *
FROM Customers b
WHERE b.city = a.city
AND a.snum < > b.snum);
4. SELECT *
FROM Customers a
WHERE EXISTS
(SELECT *
FROM Orders b
WHERE a.snum = b.snum
AND a.cnum < > b.cnum)
Глава 13
1. SELECT *
FROM Customers
WHERE rating > = ANY
(SELECT rating
FROM Customers
WHERE snum = 1002);
2. cnum cname city rating snum
2002 Giovanni Rome 200 1003
2003 Liu San Jose 200 1002
2004 Grass Berlin 300 1002
2008 Cisneros SanJose 300 1007
3. SELECT *
FROM Salespeople
WHERE city < > ALL
(SELECT city
FROM Customers);
368 ПОНИМАНИЕ SQL
___________________________________________________________________
ПРИЛОЖЕНИЕ A
или
SELECT *
FROM Salespeople
WHERE NOT city = ANY
(SELECT city
FROM Customers);
4. SELECT *
FROM Orders
WHERE amt > ALL
(SELECT amt
FROM Orders a, Customers b
WHERE a.cnum = b.cnum
AND b.city = 'London');
5. SELECT *
FROM Orders
WHERE amt >
(SELECT MAX (amt)
FROM Orders a, Customers b
WHERE a.cnum = b.cnum
AND b.city = 'London');
Глава 14
1. 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;
ОТВЕТЫ НА УПРАЖНЕНИЯ 369
___________________________________________________________________
Различие между этими двумя предложениями, в форме второго предиката.
Обратите внимание что, в обоих случаях, строка "Low Rating" имеет в начале
дополнительный пробел для того чтобы совпадать со строкой "High Rating"
по длине.
2. 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;
3. 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
1. INSERT INTO Salespeople (city, cname, comm, cnum)
VALUES ('San Jose', 'Blanco', NULL, 1100);
2. DELETE FROM Orders WHERE cnum = 2006;
370 ПОНИМАНИЕ SQL
___________________________________________________________________
ПРИЛОЖЕНИЕ A
3. UPDATE Customers
SET rating = rating + 100
WHERE city = 'Rome';
4. UPDATE Customers
SET snum = 1004
WHERE snum = 1002;
Глава 16
1. INSERT INTO Multicust
SELECT *
FROM Salespeople
WHERE 1 <
(SELECT COUNT (*)
FROM Customers
WHERE Customers.snum = Salespeople.snum);
2. DELETE FROM Customers
WHERE NOT EXISTS
(SELECT *
FROM Orders
WHERE cnum = Customers.cnum);
3. 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;
Эти проблемы могут иметь другие, такие же хорошие решения.
ОТВЕТЫ НА УПРАЖНЕНИЯ 371
___________________________________________________________________