2020-05-11
147
Если надо вывести записи с полями без значения NULL, можно использовать NOT:
SELECT * FROM Customers WHERE city NOT NULL;
При отсутствии значений NULL, будет выведена вся таблица Заказчиков. Аналогично можно ввести следующее
SELECT * FROM Customers WHERE NOT city IS NULL;
Можно также использовать NOT с IN:
SELECT * FROM Salespeople WHERE city NOT IN ('London', 'San Jose');
Таким же способом можно использовать NOT BETWEEN и NOT LIKE.
Агрегатные функции
Агрегатные функции возвращают одиночное значение для всей группы таблицы:
* COUNT - подсчёт количества строк в запросе.
* SUM - арифметическая сумму всех выбранных значений данного поля.
* AVG - производит усреднение всех выбранных значений данного поля.
* MAX - находит наибольшее из всех выбранных значений данного поля.
* MIN - находит наименьшее из всех выбранных значений данного поля.
Агрегатные функции используются подобно именам полей в предложении SELECT-запроса, но с одним исключением, они берут имена полей, как аргументы. Только числовые поля могут использоваться с SUM и AVG. С COUNT, MAX, и MIN, могут использоваться числовые или символьные поля. Когда они используются с символьными полями, MAX и MIN будут переводить их в эквивалент ASCII.
|
|
|
Чтобы найти SUM всех покупок в таблицы Порядков, можно ввести следующий запрос:
SELECT SUM (amt) FROM Orders;
Специальные атрибуты COUNT
Функция COUNT несколько отличается от всех. Она считает число значений в данном столбце, или число строк в таблице. Когда она считает значения столбца, она используется с DISTINCT чтобы производить счет чисел различных значений в данном поле. Для подсчёта номеров продавцов можно использовать следующий запрос:
SELECT COUNT (DISTINCT snum) FROM Orders;
Использование COUNT со строками
Чтобы подсчитать общее число строк в таблице, можно использовать функцию COUNT со звездочкой вместо имени поля, как например в следующем примере:
SELECT COUNT (*) FROM Customers
Включение дубликатов в агрегатные функции
Агрегатные функции могут использовать аргумент ALL, который помещается перед именем поля, подобно DISTINCT, но означает противоположное: - включать дубликаты. Различи между ALL и * когда они используются с COUNT:
* ALL использует имя_поля как аргумент.
* ALL не может подсчитать значения NULL.
Пока * является единственным аргументом который включает NULL значения, и он используется только с COUNT; функции отличные от COUNT игнорируют значения NULL в любом случае. Следующая команда подсчитает (COUNT) число не-NULL значений в поле rating в таблице Заказчиков (включая повторения):
SELECT COUNT (ALL rating) FROM Customers;
Агрегаты, построенные на скалярном выражении
Можно использовать агрегатные функции с аргументами, которые состоят из скалярных выражений включающих одно или более полей. Предположим, что таблица Порядков имеет еще один столбец который хранит предыдущий неуплаченный баланс (поле blnc) для каждого заказчика. Необходимо найти этот текущий баланс, добавлением суммы приобретений к предыдущему балансу. Найти наибольший неуплаченный баланс можно следующим образом:
|
|
|
SELECT MAX (blnc + (amt)) FROM Orders;
Для каждой строки таблицы этот запрос будет складывать blnc и amt для каждого заказчика и выбирать самое большое значение, которое он найдет. Пока заказчики могут иметь многочисленные порядки, их неуплаченный баланс оценивается отдельно для каждого порядка. Возможно, порядок с более поздней датой будет иметь самый большой неуплаченный баланс. Иначе, старый баланс должен быть выбран как в запросе выше.
Предложение GROUP BY
Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого пол, и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT. Например, нужно найти наибольшую сумму приобретений полученную каждым продавцом. Можно сделать раздельный запрос для каждого из них, выбрав MAX (amt) из таблицы Порядков для каждого значения поля snum. GROUP BY позволит поместить их все в одну команду:
SELECT snum, MAX (amt) FROM Orders GROUP BY snum;
SELECT snum, odate, MAX (amt) FROM Orders
GROUP BY snum, odate;
Предложение HAVING
Пусть в последнем примере нужно увидеть только максимальную сумму приобретений значение, которой выше $3000.00. В этом случае нельзя использовать агрегатную функцию в предложении WHERE (если не использовать подзапрос), потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции оцениваются в терминах групп строк.
Чтобы увидеть максимальную стоимость приобретений свыше $3000.00, можно использовать предложение HAVING. Предложение HAVING определяет критерии, используемые для удаления определенных групп из выводящих данных, точно также как предложение WHERE делает это для индивидуальных строк:
SELECT snum, odate, MAX (amt) FROM Orders
GROUP BY snum, odate HAVING MAX (amt) > 3000.00;
Аргументы в предложении HAVING следуют тем же самым правилам, что и в предложении SELECT, состоящей из команд использующих GROUP BY. Они должны иметь одно значение на группу вывода.
Поле оdate не может быть вызвано предложением HAVING, потому что оно может иметь больше, чем одно значение на группу вывода. Чтобы избегать такой ситуации, предложение HAVING должно ссылаться только на агрегаты и поля выбранные GROUP BY. Имеется правильный способ сделать вышеупомянутый запрос:
SELECT snum, MAX (amt) FROM Orders
WHERE odate = 10/03/1990 GROUP BY snum;
Поскольку поля odate нет, не может быть и выбранных полей, значение этих данных меньше чем в некоторых других примерах.
ПОРЯДОК ВЫПОЛНЕНИЯ
1. Создайте базу данных.
Таблицы 1.1, 1.2, и 1.3 составляют реляционную базу данных, которая является минимально достаточной, чтобы легко ее отслеживать, и достаточно полной, чтобы иллюстрировать главные понятия и практику использования SQL.
Первый столбец каждой таблицы содержит номера чьи значения различны для каждой строки. Это - первичные ключи таблиц. Некоторые из этих номеров также показаны в столбцах других таблиц. Они показывают связь между строками которые используют значение принимаемое из первичного ключа, и строками где это значение используется в самом первичном ключе.
Таблица 1.1: Продавцы
| SNUM | SNAME | CITY | COMM |
| 1001 | Peel | London | .12 |
| 1002 | Serres | San Jose | .13 |
| 1004 | Motika | London | .11 |
| 1007 | Rifkin | Barcelona | .15 |
| 1003 | Axelrod | New York | .10 |
Таблица 1.2: Заказчики
| CNUM | CNAME | CITY | RATING | SNUM |
| 2001 | Hoffman | London | 100 | 1001 |
| 2002 | Giovanni | Rome | 200 | 1003 |
| 2003 | Liu | SanJose | 200 | 1002 |
| 2004 | Grass | Berlin | 300 | 1002 |
| 2006 | Clemens | London | 100 | 1001 |
| 2008 | Cisneros | San Jose | 300 | 1007 |
| 2007 | Pereira | Rome | 100 | 1004 |
Таблица 1.3: Порядки
| ONUM | AMT | ODATE | CNUM | SNUM |
| 3001 | 18.69 | 10/03/1990 | 2008 | 1007 |
| 3003 | 767.19 | 10/03/1990 | 2001 | 1001 |
| 3002 | 1900.10 | 10/03/1990 | 2007 | 1004 |
| 3005 | 5160.45 | 10/03/1990 | 2003 | 1002 |
| 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 |
|
|
|
Поле snum в таблице Заказчиков указывает, какому продавцу назначен данный заказчик. Номер поля snum связан с таблицей Продавцов, которая дает информацию об этих продавцах. Очевидно, что продавец, которому назначены заказчики, должен уже существовать - то есть, значение snum из таблицы Заказчиков должно также быть представлено в таблице Продавцов. Если это так, то говорят, что " система находится в состоянии справочной целостности ".
Значения столбцов Таблицы 1.1
| ПОЛЕ | СОДЕРЖАНИЕ |
| snum | уникальный номер, назначенный каждому продавцу ("номер служащего") |
| sname | имя продавца |
| city | расположение продавца (город) |
| comm | комиссионные продавцов в десятичной форме |
Значения столбцов Таблицы 1.2
| ПОЛЕ | СОДЕРЖАНИЕ |
| cnum | уникальный номер, назначенный каждому заказчику |
| cname | имя заказчика |
| city | расположение заказчика (город) |
| rating | код указывающего уровень предпочтения данного заказчика перед другими. Более высокий номер указывают на большее предпочтение (рейтинг) |
| snum | номер продавца назначенного этому заказчику (из таблицы Продавцов) |
Значения столбцов Таблицы 1.3
| ПОЛЕ | СОДЕРЖАНИЕ |
| onum | уникальный номер, данный каждому приобретению |
| amt | значение суммы приобретений |
| odate | дата приобретения |
| cnum | номер заказчика делающего приобретение (из таблицы Заказчиков) |
| snum | номер продавца продающего приобретение (из таблицы Продавцов) |
2. Напишите команду SELECT, которая бы вывела номер порядка, сумму и дату для всех строк из таблицы Порядков.
3. Напишите запрос, который вывел бы все строки из таблицы Заказчиков для которых номер продавца = 1001.
4. Напишите запрос, который вывел бы таблицу со столбцами в следующем порядке: city, sname, snum, comm.
5. Напишите команду SELECT, которая вывела бы оценку(rating), сопровождаемую именем каждого заказчика в San Jose.
|
|
|
6. Напишите запрос, который вывел бы значения snum всех продавцов в текущем порядке из таблицы Порядков без каких бы то ни было повторений.
7. Напишите запрос, который может дать все порядки со значениями суммы выше, чем $1,000.
8. Напишите запрос, который может выдать поля sname и city для всех продавцов в Лондоне с комиссионными выше.10.
9. Напишите запрос к таблице Заказчиков, чей вывод может включить всех заказчиков с оценкой =< 100, если они не находятся в Риме.
10. Что может быть выведено в результате следующего запроса?
SELECT *
FROM Orders
WHERE (amt < 1000 OR
NOT (odate = 10/03/1990
AND cnum > 2003));
11. Что может быть выведено в результате следующего запроса?
SELECT *
FROM Orders
WHERE NOT ((odate = 10/03/1990 OR snum > 1006)
AND amt > = 1500);
12. Как можно проще переписать такой запрос?
SELECT snum, sname, city, comm
FROM Salespeople
WHERE (comm > +.12 OR
comm <.14);
13. Напишите два запроса, которые могли бы вывести все порядки на 3 или 4 Октября 1990
14. Напишите запрос, который выберет всех заказчиков, обслуживаемых продавцами Peel или Motika. (Подсказка: из типовых таблиц, поле snum связывает вторую таблицу с первой)
15. Напишите запрос, который может вывести всех заказчиков, чьи имена начинаются с буквы, попадающей в диапазон от A до G.
16. Напишите запрос, который выберет всех пользователей, чьи имена начинаются с буквы C.
17. Напишите запрос, который выберет все порядки, имеющие нулевые значения или NULL в поле amt(сумма).
18. Напишите запрос, который сосчитал бы все суммы приобретений на 3 Октября.
19. Напишите запрос, который сосчитал бы число различных не-NULL значений поля city в таблице Заказчиков.
20. Напишите запрос, который выбрал бы наименьшую сумму для каждого заказчика.
21. Напишите запрос, который бы выбирал заказчиков в алфавитном порядке, чьи имена начинаются с буквы G.
22. Напишите запрос, который выбрал бы высшую оценку в каждом городе.
23. Напишите запрос, который сосчитал бы число заказчиков регистрирующих каждый день свои порядки. (Если продавец имел более одного порядка в данный день, он должен учитываться только один раз.)
