Понимание SQL

Грубер Мартин

Глава 3.

* 1. SELECT onum, amt, odate FROM Orders;

* 2. SELECT * FROM Customers WHERE snum=1001;

* SELECT city, sname, snum, comm FROM Salespeople;

* SELECT rating, cname FROM Customers WHERE city='SanJose';

* SELECT DISTINCT snum FROM Orders;

Глава 4.

* 1. SELECT * FROM Orders WHERE amt > 1000;

* SELECT sname, city FROM Salespeople WHERE city='London' AND comm > .10;

SELECT * FROM Customers WHERE rating > 100 OR city='Rome';

или

SELECT * FROM Customers WHERE NOT rating <=100 OR city ='Rome';

или

SELECT * FROM Customers WHERE NOT (rating <=100 AND city < > 'Rome');

*

Могут быть еще другие решения.

onum amt odate cnum snum

3001 18.69 10/03/1990 2008 1007

3003 767.19 10/03/1990 2001 1001

3005 5160.45 10/03/1990 2003 1002

3009 1713.23 10/04/1990 2002 1003

3007 75.75 10/04/1990 2004 1002

3008 4723.00 10/05/1990 2006 1001

3010 1309.95 10/06/1990 2004 1002

* 3011 9891.88 10/06/1990 2006 1001

onum amt odate cnum snum

3001 18.69 10/03/1990 2008 1007

3003 767.19 10/03/1990 2001 1001

3006 1098.16 10/03/1990 2008 1007

3009 1713.23 10/04/1990 2002 1003

3007 75.75 10/04/1990 2004 1002

3008 4723.00 10/05/1990 2006 1001

3010 1309.95 10/06/1990 2004 1002

* 3011 9891.88 10/06/1990 2006 1001

* . SELECT * FROM Salespeople;

Глава 5.

SELECT * FROM Orders WHERE odate IN (10/03/1990,10/04/1990);

и

* SELECT * FROM Orders WHERE odate BETWEEN 10/03/1990 AND 10/04,1990;

* SELECT * FROM Customers WHERE snum IN (1001,1004);

SELECT * FROM Customers WHERE cname BETWEEN 'A' AND 'H';

* ПРИМЕЧАНИЕ: В ASCII базовой системе Hoffman не буде выведен из-за конечных пробелов после H. По той же самой причине воторая граница не может быть G, поскольку она не выведет имена Giovanni и Grass. G может использоваться в сопровождении с Z, так чтобы следовать за другими символами в алфавитном порядке, а не предшествовать им, как это делают пробелы.

* SELECT * FROM Customers WHERE cname LIKE 'C%';

SELECT * FROM Orders WHERE amt < > O AND (amt IS NOT NULL);

или

* SELECT * FROM Orders WHERE NOT (amt=O OR amt IS NULL);

Глава 6.

* SELECT COUNT(*) FROM Orders WHERE odate=10/03/1990;

* SELECT COUNT (DISTINCT city) FROM Customers;

* SELECT cnum, MIN (amt) FROM Orders GROUP BY cnum;

* SELECT MIN (cname) FROM Customers WHERE cname LIKE 'G%';

* SELECT city, MAX (rating) FROM Customers GROUP BY city;

* SELECT odate, count (DISTINCT snum FROM Orders GROUP BY odate;

Глава 7.

* SELECT onum, snum, amt * .12 FROM Orders;

SELECT 'For the city ', city, ', the highest rating is ', ", MAX (rating)

* FROM Customers GROUP BY city;

* SELECT rating, cname, cnum FROM Customers ORDER BY rating DESC;

* SELECT odate, SUM (amt) FROM Orders GROUP BY odate ORDER BY 2 DESC;

Глава 8.

SELECT onum, cname FROM Orders, Customers

* WHERE Customers.cnum=Orders.cnum;

SELECT onum, cname, sname FROM Orders, Customers, Salespeople

* WHERE Customers.cnum=Orders.cnum AND Salespeople.snum=Orders.snum;

SELECT cname, sname, comm FROM Salespeople, Customers

* WHERE Salespeople.snum=Customers.snum AND comm * .12;

SELECT onum, comm * amt FROM Salespeople, Orders, Customers

WHERE rating > 100 AND Orders.cnum=Customers.cnum

* AND Orders.snum=Salespeople.snum;

Глава 9.

SELECT first.sname, second.sname FROM Salespeople first, Salespeople second

WHERE first.city=second.city AND first.sname < second.sname;

* Псевдонимам нет необходимости иметь именно такие имена.

SELECT cname, first.onum, second.onum FROM Orders first, Orders second, Customers WHERE first.cnum=second.cnum

AND first.cnum=Customers.cnum

AND first.onum < second.onum;

* Ваш вывод может иметь некоторые отличия, но в вашем ответе все логические компоненты должны быть такими же.

SELECT a.cname, a.city FROM Customers a, Customers b

* WHERE a.rating=b.rating AND b.cnum=2001;

Глава 10.

SELECT * FROM Orders WHERE cnum=(SELECT cnum

FROM Customers WHERE cname='Cisneros');

или

SELECT * FROM Orders WHERE cnum IN

* (SELECT cnum FROM Customers WHERE cname='Cisneros');

SELECT DISTINCT cname, rating FROM Customers, Orders

WHERE amt > (SELECT AVG (amt) FROM Orders)

* AND Orders.cnum=Customers.cnum;

SELECT snum, SUM (amt) FROM Orders

GROUP BY snum HAVING SUM (amt) >

* (SELECT MAX (amt) FROM Orders);

Глава 11.

1. SELECT cnum, cname FROM Customers outer

* WHERE rating=(SELECT MAX (rating) FROM Customers inner WHERE inner.city=outer.city);

. Решение с помощью соотнесенного подзапроса:

SELECT snum, sname FROM Salespeople main

WHERE city IN (SELECT city FROM Customers inner

WHERE inner.snum < > main.snum);