Задания практических занятий разделены на тесты и упражнения. Тесты предназначены для проверки общего уровня понимания рассмотренного материала. Упражнения дают возможность применить на практике идеи, обсуждавшиеся в ходе текущего урока, в комбинации с идеями из предыдущих уроков. Мы рекомендуем ответить на тестовые вопросы и выполнить упражнения прежде, чем продолжать дальнейшее чтение книги. Ответы можно проверить по Приложению Б, "Ответы".
Тесты
1. В чем состоит назначение подзапроса при использовании его в операторе SELECT?
2. Можно ли одновременно обновить несколько столбцов таблицы с помощью оператора UPDATE с подзапросом?
3. Будут ли работать следующие операторы? Если нет, то что в них следует исправить?
a. SELECT CUST_ID, CUST_NAME FROM CUSTOMER_TBL WHERE CUST_ID =
(SELECT CUST_ID FROM ORDERS_TBL WHERE ORD_NUM = ' 16C17' ) ;
6. SELECT EMP_ID, SALARY FROM EMPLOYEE_PAY_TBL WHERE SALARY BETWEEN '20000'
AND (SELECT SALARY
FROM EMPLOYEE_ID
WHERE SALARY = '40000');
B. UPDATE PRODUCTS_TBL SET COST = 1.15 WHERE CUST_ID =
(SELECT CUST_ID FROM ORDERS_TBL WHERE ORD_NUM = '32A132');
4. Каков будет результат выполнения следующего оператора?
DELETE FROM EMPLOYEE_TBL WHERE EMP_ID IN
(SELECT EMP_ID FROM EMPLOYEE_PAY_TBL};
Упражнения
Выполните упражнения для следующих таблиц.
EMPLOYEE TBL
ЕМР ID LAST NAME FIRST NAME MIDDLE NAME ADDRESS CITY STATE ZIP PHONE PAGER
VARCHAR2 ( 9 ) VARCHAR2 (15) VARCHAR2 (15) VARCHAR2 (15) VARCHAR2 (30) VARCHAR2 (15) CHAR ( 2 ) NUMBER (5) CHAR (10) CHAR (10)
NOT NULL NOT NULL NOT NULL
NOT NULL NOT NULL NOT NULL NOT NULL
Ключевое поле
EMPLOYEE_PA Y_ TBL
EMP ID POSITION DATE HIRE PAY RATE DATE LAST-RAISE
VARCHAR2 ( 9 ) VARCHAR2 (15) DATE NUMBER (4,2) DATE
NOT NULL NOT NULL
NOT NULL
Ключевое поле
CONSTRAINT EMP EMPLOYEEJTBL (]
FK FOREIGN KEY EMP_ID)
(EMP ID) REF
ERENCED
CUSTOMERS_TBL
CUST ID CUST NAME CUST ADDRESS CUST CITY CUST STATE CUST ZIP CUST PHONE CUST_FAX
VARCHAR2 (10) VARCHAR2 (30) VARCHAR2 (20) VARCHAR2 (15) CHAR ( 2 ) NUMBER (5) NUMBER (10) NUMBER (10)
NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL NOT NULL
Ключевое поле
ORDERS TBL
ORD NUM CUST ID PROD ID QTY ORD DATE
VARCHAR2 (10) VARCHAR2 (10) VARCHAR2 (10) NUMBER (6) DATE
NOT NULL NOT NULL NOT NULL NOT NULL
Ключевое поле
PRODUCTS_TBL
PROD ID PROD DESC COST
VARCHAR2 (10) VARCHAR2 (40) NUMBER (6, 2)
NOT NULL NOT NULL NOT NULL
Ключевое поле
1. Используя подзапрос, запишите оператор SQL, который в таблице CUSTOMER_TBL заменит имя заказчика, разместившего заказ с номером 23Е934, На DAVIDS MARKET.
2. Используя подзапрос, создайте запрос, возвращающий имена всех служащих, которые имеют более высокую зарплату, чем служащий по имени JOHN DOE, чей табельный номер 343559876.
3. Используя подзапрос, создайте запрос, возвращающий список всех товаров с ценой, превышающей среднюю цену всех имеющихся товаров.
15-й час Объединение запросов
Из этого урока вы узнаете, как объединить несколько запросов SQL в один с помощью команд UNION, UNION ALL, INTERSECT И EXCEPT. опять же особенности использования UNION, UNION ALL, INTERSECT и EXCEPT в случае используемой вами конкретной реализации SQL вы должны выяснить по соответствующей документации Основными на этом уроке будут следующие темы.
• Обзор команд для объединения запросов
• Когда следует использовать команды объединения запросов
• Использование GROUP BY с составными операторами
• Использование ORDER BY с составными операторами
• Обеспечение правильности результатов
Обычные и составные запросы
Обычный запрос состоит из одного оператора SELECT, а составной - из двух или более.
Составные запросы строятся с помощью определенных команд, позволяющих связать два запроса в один. Так, в следующем примере для этого используется команда UNION.
Например, оператор SQL может выглядеть следующим образом.
SELECT EMP_ID, SALARY, PAY_RATE
FROM EMPLOYEE_PAY_TBL
WHERE SALARY IS NOT NULL OR
PAY_RATE IS NOT NULL;
Тот же оператор с помощью UNION можно записать по-другому.
SELECT EMP_ID, SALARY
FROM EMPLOYEE_PAY_TBL
WHERE SALARY IS NOT NULL
UNION
SELECT EMP_ID, PAY_RATE
FROM EMPLOYEE_PAY_TBL
WHERE PAY_RATE IS NOT NULL;
Оба эти оператора возвратят данные об оплате труда всех служащих, для которых указана либо почасовая оплата, либо ставка.
В случае второго оператора в выводе будет присутствовать два столбца - EMP_ID и SALARY и нормы почасовой оплаты (PAY_RATE) тоже будут помещены ~ в столбец SALARY При использовании команды UNION названия (или псевдонимы) столбцов в выводе определяются первым из операторов SELECT
Зачем использовать составные запросы?
Составные запросы используются для того, чтобы комбинировать результаты двух операторов SELECT. Использование составного запроса позволяет организовать или, наоборот, подавить вывод повторяющихся записей. С помощью таких запросов можно выводить одновременно аналогичные данные, хранящиеся в разных столбцах
Составные запросы позволяют комбинировать результаты нескольких запросов в одном выводимом наборе данных Такие запросы часто строятся проще, чем запросы со сложными условиями, и часто допускают большую гибкость в решении разнообразных задач извлечения данных
Команды построения сложных запросов
Команды, использующиеся для построения сложных запросов, зависят от конкретной реализации языка. Стандарт ANSI определяет команды UNION, UNION ALL, INTERSECT и EXCEPT. Все они обсуждаются ниже.
Команда UNION
Команда UNION используется для объединения результатов двух или более операторов SELECT с исключением повторяющихся строк. Другими словами, если строка попадает в вывод одного запроса, то второй раз она не выводится, даже если она возвращается вторым запросом. При использовании UNION в каждом из связываемых операторов SELECT должно быть выбрано одинаковое число столбцов, столбцы должны быть одинакового типа и следовать в том же порядке.
Синтаксис оператора должен быть следующим.
SELECT столбец1 [, столбец2 ]
FROM таблица1 [, таблица2 ]
[ WHERE ]
UNION
SELECT столбец1 [, столбец2 ]
FROM таблица1 [, таблица2 ]
[ WHERE ]
Рассмотрим пример.
SELECT EMP_ID FROM EMPLOYEEJTBL
UNION
SELECT EMP_ID FROM EMPLOYEE_PAY_TBL;
В результате табельные номера, имеющиеся в обеих таблицах, отобразятся только по одному разу.
Для построения примеров с реальными данными используем следующие простые запросы к двум таблицам.
SELECT PROD_DESC FROM PRODUCTS_TBL;
PROD_DESC
КОСТЮМ ВЕДЬМЫ
ПЛАСТИКОВЫЕ ТЫКВЫ
ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ
ФОНАРИ
КОСТЮМЫ В АССОРТИМЕНТЕ
СЛАДКАЯ КУКУРУЗА
ТЫКВЕННЫЕ КОНФЕТЫ
ПЛАСТИКОВЫЕ ПАУКИ
МАСКИ В АССОРТИМЕНТЕ
ЦЕПОЧКА ДЛЯ КЛЮЧЕЙ
ПОЛОЧКА ИЗ ДУБА
11 строк выбраны.
SELECT PROD_DESC FROM PRODUCTS_TMP;
PROD_DESC
КОСТЮМ ВЕДЬМЫ
ПЛАСТИКОВЫЕ ТЫКВЫ
ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ
ФОНАРИ
КОСТЮМЫ В АССОРТИМЕНТЕ
СЛАДКАЯ КУКУРУЗА
ТЫКВЕННЫЕ КОНФЕТЫ
ПЛАСТИКОВЫЕ ПАУКИ
МАСКИ В АССОРТИМЕНТЕ
9 строк выбраны.
Таблица PRODUCTS_TMP была создана в ходе урока 3 Обратитесь к уроку 3 снова, если вам необходимо воссоздать эту таблицу
Теперь объединим эти два запроса с помощью команды UNION, чтобы получить следующий составной запрос
SELECT PROD_DESC FROM PRODUCTS_TBL
UNION
SELECT PROD_DESC FROM PRODUCTS_TMP;
PROD_DESC
ИСКУССТВЕННЫЕ ПАРАФИНОВЫЕ ЗУБЫ
КОСТЮМ ВЕДЬМЫ
КОСТЮМЫ В АССОРТИМЕНТЕ
МАСКИ В АССОРТИМЕНТЕ
ПЛАСТИКОВЫЕ ПАУКИ
ПЛАСТИКОВЫЕ ТЫКВЫ
ПОЛОЧКА ИЗ ДУБА
СЛАДКАЯ КУКУРУЗА
ТЫКВЕННЫЕ КОНФЕТЫ
ФОНАРИ
ЦЕПОЧКА ДЛЯ КЛЮЧЕЙ
11 строк выбраны.
Этот запрос тоже возвращает 11 записей, как и два предыдущих, поскольку при объединении двух запросов с помощью команды UNION повторяющиеся в таблицах записи повторно не выводятся.
