А.М. Гудов 1 Выборка данных ПОДЗАПРОСЫ. А.М. Гудов 2 Что такое подзапрос? Синтаксис команды SELECT SELECT...FROM...WHERE... Синтаксис SELECT (SELECT...FROM...WHERE...); - презентация

1 А.М. Гудов 1 Выборка данных ПОДЗАПРОСЫ

2 А.М. Гудов 2 Что такое подзапрос? Синтаксис команды SELECT SELECT...FROM...WHERE... Синтаксис SELECT (SELECT...FROM...WHERE...); Главный запрос Подзапрос SELECT Подзапрос - это команда SELECT, вложенная в предложение другой команды SQL. С помощью подзапросов можно из простых команд создавать более сложные и мощные.

3 А.М. Гудов 3 Подзапросы: синтаксис Подзапрос выполняется до выполнения главного запроса. Результат подзапроса используется главным внешним подзапросом. > =>= INNOT INОператоры сравнения при использовании подзапроса бывают двух типов: однострочные (>, =, >=, ) и многострочные (IN, NOT IN). SELECTselect_list FROMtable WHEREexpr operator (SELECTselect_list FROMtable); FROMtable); SELECTselect_list FROMtable WHEREexpr operator (SELECTselect_list FROMtable); FROMtable);

4 А.М. Гудов 4 Указания по использованию подзапросов Подзапрос должен быть заключен в скобки. В подзапросах используются операторы сравнения двух типов: однострочные и многострочные. Подзапрос должен находиться справа от оператора. Подзапросы могут использоваться во многих командах SQL. ORDER BYПодзапросы не могут содержать предложение ORDER BY.

5 А.М. Гудов 5 Как обрабатываются подзапросы? SELECTlast_name, title FROMs_emp WHEREdept_id = SELECTdept_id FROMs_emp WHERElast_name='Biri'; Вложенный запрос Главный запрос 43 SELECT 1. Вложенная команда SELECT выполняется первой. 2. Результат передается в условие главного запроса. SQL> SELECT last_name 2 FROM s_emp 2 FROM s_emp 3 WHERE dept_id = 3 WHERE dept_id = 4 (SELECT dept_id FROM s_emp 4 (SELECT dept_id FROM s_emp 5 WHERE last_name = Biri); 5 WHERE last_name = Biri); SQL> SELECT last_name 2 FROM s_emp 2 FROM s_emp 3 WHERE dept_id = 3 WHERE dept_id = 4 (SELECT dept_id FROM s_emp 4 (SELECT dept_id FROM s_emp 5 WHERE last_name = Biri); 5 WHERE last_name = Biri);

6 А.М. Гудов 6 Однострочные подзапросы SELECT last_name, title FROM s_emp WHERE title = SELECT title FROM s_emp WHERE last_name = Smith SQL> SELECT last_name, title 2 FROM s_emp 3 WHERE title = 4 (SELECT title 5 FROM s_emp 6 WHERE last_name = Smith); Команда SQL для вывода фамилии и должности служащего Команда SQL для выяснения должности сотрудника с фамилией Smith Соедините обе команды, и пусть SQL определит должность сотрудника с фамилией Smith.

7 А.М. Гудов 7 S_EMP S_EMP LAST_NAME TITLETITLELAST_NAME MaduroStock ClerkStock Clerk Smith SmithStock Clerk NozakiStock Clerk PatelStock Clerk NewmanStock Clerk ChangStock Clerk PatelStock Clerk DancsStock Clerk SchwartzStock Clerk Сервер Однострочные подзапросы

8 А.М. Гудов 8 Групповая функция в подзапросе Групповая функция AVG возвращает единственное значение. SQL> SELECTlast_name, title, salary 2 FROMs_emp 2 FROMs_emp 3 WHEREsalary < 3 WHEREsalary < 4(SELECTAVG(salary) 4(SELECTAVG(salary) 5 FROMs_emp); 5 FROMs_emp); SQL> SELECTlast_name, title, salary 2 FROMs_emp 2 FROMs_emp 3 WHEREsalary < 3 WHEREsalary < 4(SELECTAVG(salary) 4(SELECTAVG(salary) 5 FROMs_emp); 5 FROMs_emp); Вывод фамилии, должности и зарплаты всех служащих с зарплатой ниже средней.

9 А.М. Гудов 9 Групповая функция в подзапросе LAST_NAME TITLE SALARY Urguhart Warehouse Manager 1200 Menchu Warehouse Manager 1250 Biri Warehouse Manager 1100 Smith Stock Clerk 940 Nozaki Stock Clerk 1200 Patel Stock Clerk 795 Newman Stock Clerk 750 Markarian Stock Clerk 850 Chang Stock Clerk 800 Patel Stock Clerk 795 Dancs Stock Clerk 860 Schwartz Stock Clerk 1100 AVG(SALARY) SALARY SALARY Сервер

10 А.М. Гудов 10 Ошибки в подзапросах Если пишется подзапрос, возвращающий более одной строки, и в нем используется однострочный оператор сравнения, выдается сообщение об ошибке. Для исправления измените этот оператор на IN (многострочный оператор сравнения). SQL> SELECTlast_name, first_name, title 2 FROMs_emp 2 FROMs_emp 3 WHEREdept_in = 3 WHEREdept_in = 4(SELECTID 4(SELECTID 5FROM s_dept 5FROM s_dept 6WHERE name = 'Finance' 6WHERE name = 'Finance' 7 OR region_id = 2); ORA-01427:single-row subquery returns more than one row SQL> SELECTlast_name, first_name, title 2 FROMs_emp 2 FROMs_emp 3 WHEREdept_in = 3 WHEREdept_in = 4(SELECTID 4(SELECTID 5FROM s_dept 5FROM s_dept 6WHERE name = 'Finance' 6WHERE name = 'Finance' 7 OR region_id = 2); ORA-01427:single-row subquery returns more than one row

11 А.М. Гудов 11 Многострочные подзапросы Многострочные подзапросы возвращают более одной строки. WHERE INВ предложении WHERE использование многострочного оператора (например, оператора IN) обязательно. SQL> SELECTlast_name, first_name, title 2 FROMs_emp 2 FROMs_emp 3 WHEREdept_in IN 3 WHEREdept_in IN 4(SELECTID 4(SELECTID 5FROM s_dept 5FROM s_dept 6WHERE name = 'Finance' 7 OR region_id = 2); SQL> SELECTlast_name, first_name, title 2 FROMs_emp 2 FROMs_emp 3 WHEREdept_in IN 3 WHEREdept_in IN 4(SELECTID 4(SELECTID 5FROM s_dept 5FROM s_dept 6WHERE name = 'Finance' 7 OR region_id = 2);

12 А.М. Гудов 12 Предложение HAVING с подзапросами HAVINGПодзапросы используются и в предложениях HAVING. Сервер Oracle7 выполняет подзапросы первыми. HAVINGСервер возвращает результаты в предложение HAVING главного запроса. SQL> SELECTdept_id, AVG(salary) 2 FROMs_emp 2 FROMs_emp 3 GROUP BYdept_id 3 GROUP BYdept_id 4 HAVINGAVG(salary) > 4 HAVINGAVG(salary) > 5(SELECTAVG(salary) 5(SELECTAVG(salary) 6 FROM s_emp 6 FROM s_emp 7 WHERE dept_id = 32); 7 WHERE dept_id = 32); SQL> SELECTdept_id, AVG(salary) 2 FROMs_emp 2 FROMs_emp 3 GROUP BYdept_id 3 GROUP BYdept_id 4 HAVINGAVG(salary) > 4 HAVINGAVG(salary) > 5(SELECTAVG(salary) 5(SELECTAVG(salary) 6 FROM s_emp 6 FROM s_emp 7 WHERE dept_id = 32); 7 WHERE dept_id = 32);

13 А.М. Гудов 13Заключение Подзапросы полезны для выборки данных по неизвестным значениям. SELECTВложенный запрос содержит более одного предложения SELECT. WHERE HAVINGПодзапросы обрабатываются первыми, после чего выполняется основной запрос по результатам подзапроса, переданным в предложение WHERE или HAVING. SELECTselect_list FROMtable WHEREexpr operator (SELECTselect_list FROMtable); FROMtable); SELECTselect_list FROMtable WHEREexpr operator (SELECTselect_list FROMtable); FROMtable);

14 А.М. Гудов 14 Практическое занятие 1. При использовании подзапроса какой запрос выполняется первым? 2. Сколько раз выполняется первый запрос? =Да Нет 3. Если подзапрос возвращает более одного значения, то можно ли использовать оператор =? (Да/Нет) 4. Вывести имя, фамилию и дату начала работы всех служащих, работающих в одном отделе с Magee. 5. Вывести номер, имя и фамилию всех служащих, заработная плата которых выше средней. 6. Вывести номер, имя и фамилию служащих, которые получают заработную плату выше средней и работающих в одном отделе с сотрудниками, фамилии которых содержат букву t. 7. Вывести наименование и краткое описание товаров, которые не были ни разу заказаны в сентябре 1992 года. 8. Вывести наименование и кредитный рейтинг всех клиентов, чьими торговыми представителем является Andre Dumas.