SQL (диалект Oracle). Выборки с помощью SELECT лекция-семинар 1.Фильтрация (where) и сортировка (order by) 2.Многотабличные запросы, inner join 3.Single row functions. Псевдонимы (alias) 4.Group functions, группировка (group by, having) 5.Внешние соединения (left, right, full outer join) 6.Операции над выборками (union, minus, intersect) 7.Подзапросы, в т.ч. многострочные (in, any, all) 8.Иерархические запросы в Oracle (connect by)

1. Простейшие запросы select table_name from user_tables select sysdate from dual общий вид простых запросов: SYSDATE 11/16/ :00:11 TABLE_NAME EMPLOYEE DEPARTMENT SELECT[DISTINCT] {*, column [alias],...} [WHERE … [AND …] [OR …]] FROMtable; SELECT[DISTINCT] {*, column [alias],...} [WHERE … [AND …] [OR …]] FROMtable;

DISTINCT Выражение вида SELECT DISTINCT позволяет выбрать только уникальные записи (Использовать только тогда, когда это действительно необходимо: сильно падает скорость) select distinct salary from employee SALARY

Структура используемых таблиц

Наполнение используемых таблиц EMPLOYEE_IDNAMESALARYDEPARTMENT_ID 1Ivan Ivanov Petr Sidorov Andrey Petrov Sergey Vasiliev1500 5Oleg Fedorov Mihail Hohlov Alexey Alexeev Sergey Frolov Ivan Stulov Maxim Hlebnikov2500 DEPARTMENT_IDNAME 1R&D 2Sales 3QA 4IT

Выражения, ограничивающие набор строк (WHERE) select name, salary from employee where department_id is null select name, salary from employee where salary between 2500 and 3000 select name, salary from employee where salary not in (2000, 2500, 3000, 3500) NAMESALARY Petr Sidorov3000 Oleg Fedorov2500 Maxim Hlebnikov2500 NAMESALARY Andrey Petrov1500 Sergey Vasiliev1500 Sergey Frolov5000 NAMESALARY Sergey Vasiliev1500 Maxim Hlebnikov2500

Сортировка (ORDER BY) select salary, name from employee order by salary, name SALARYNAME 1500Andrey Petrov 1500Sergey Vasiliev 2000Ivan Ivanov 2000Ivan Stulov 2000Mihail Hohlov 2500Maxim Hlebnikov 2500Oleg Fedorov 3000Petr Sidorov 3500Alexey Alexeev 5000Sergey Frolov DESC – сортировка в направлении убывания select e.salary, e.name from employee e //e - alias order by e.salary desc, e.name SALARYNAME 5000Sergey Frolov 3500Alexey Alexeev 3000Petr Sidorov 2500Maxim Hlebnikov 2500Oleg Fedorov 2000Ivan Ivanov 2000Ivan Stulov 2000Mihail Hohlov 1500Andrey Petrov 1500Sergey Vasiliev

SALARYNAME 2500Oleg Fedorov 2000Mihail Hohlov 3000Petr Sidorov 2000Ivan Stulov 2000Ivan Ivanov 2500Maxim Hlebnikov 1500Andrey Petrov 1500Sergey Vasiliev 5000Sergey Frolov 3500Alexey Alexeev Можно использовать функцию вместо атрибута сортировки после order by (а также вместо выбираемого атрибута после select и в условии отбора после where) select salary, name from employee order by dbms_random.random //это функция в пакете SALARYNAME 1500Andrey Petrov 1500Sergey Vasiliev 2000Ivan Stulov 3500Alexey Alexeev 2500Maxim Hlebnikov 3000Petr Sidorov 5000Sergey Frolov 2000Ivan Ivanov 2000Mihail Hohlov 2500Oleg Fedorov

2. Многотабличные запросы. Соединение таблиц (Inner Join) select e.name, d.name department //department – alias столбца from employee e, department d //e, d – alias таблиц where e.department_id = d.department_id order by department NAMEDEPARTMENT Mihail HohlovQA Ivan StulovQA Petr SidorovRD Andrey PetrovRD Sergey FrolovRD Oleg FedorovRD Ivan IvanovSales Alexey AlexeevSales Упражнение 1: Выполнить аналогичный запрос c выдачей зарплат, причем зарплаты ( AND )

Функции Есть два типа функций (имена – из Oracle) : –Single Row Functions – функции, применяемые к конкретной строке выборки (SIN, ROUND, DECODE, NVL, SUBSTR,..) –Group Functions – функции, применяющиеся к некоторому подмножеству выборки (COUNT, AVG, STDDEV, MAX, MIN)

3. Single Row Functions Функции, применяемые к одной записи (точнее, к одной ячейке) из выборки Типы Single Row Functions: –Математические функции: SIN, EXP, MOD, … –Функции для работы со строками, датами и др.: SUBSTR, LOWER, LPAD, NEXT_DATE, … –Функции преобразования типов: TO_DATE, TO_CHAR, TO_NUMBER, … –Функции, переопределяющие значения: DECODE, NVL, …

Математические функции select name, mod(salary, 1000) mod from employee where name like %Petrov //like – сравнение строк по маске select sin(1) from dual NAMEMOD Andrey Petrov500 SIN(1) 0.84 Функции работы со строками Упражнение 2: в запросе упражнения 1 выдавать имена с большой буквы (остальные - строчные), независимо от того, как они хранятся в БД. Применить SUBSTR, UPPER, LOWER, CONCAT (или оператор ||)

Функции переопределения значения select name, salary, nvl(department_id, 0) department_id from employee NAMESALARYDEPARTMENT_ID Ivan Ivanov20002 Petr Sidorov30001 Andrey Petrov15001 Sergey Vasiliev15000 Oleg Fedorov25001 Mihail Hohlov20003 Alexey Alexeev35002 Sergey Frolov50001 Ivan Stulov20003 Maxim Hlebnikov25000

select name, decode (salary, 1500, 'Good', 2000, 'Very Good', 'Cool!') status from employee NAMESTATUS Ivan IvanovVery good Petr SidorovCool! Andrey PetrovGood Sergey VasilievGood Oleg FedorovCool! Mihail HohlovVery good Alexey AlexeevCool! Sergey FrolovCool! Ivan StulovVery good Maxim HlebnikovCool!

4. Group Functions. Выражение GROUP BY Выражение GROUP BY используется для разбиения выборки на группы с равными значениями в заданном(ых) столбце(ах) Групповая функция (COUNT, AVG, …) – для подсчета одного числа по каждой группе (или по всей выборке, если group by не задано) select salary, count(*) number from employee group by salary select avg(salary) average_salary from employee SALARYNUMBER

select department_id, max(salary) max_salary, min(salary) min_salary from employee group by department_id DEPARTMENT_IDMAX_SALARYMIN_SALARY Упражнение 3: на основе запроса упражнения 1 подсчитать среднюю зарплату по каждому отделу (выдавать название отдела, а не его id). Упражнение 4 (c outer join, см. далее): –//–, но также выдать ср. зарплату сотрудников без отдела (применить функцию NVL для названия несуществующего отдела)

Условие HAVING select department_id, avg(salary) from employee group by department_id having max(salary) > 2000 DEPARTMENT_IDAVG(SALARY) Если требуется отфильтровать строки до группировки – where, если после группировки – having

Две формы записи Inner Join Используется обычно: select e.name, d.name department from employee e, department d where e.department_id = d.department_id Стандарт ANSI: select e.name, d.name department from employee e inner join department d on e.department_id = d.department_id NAMEDEP... Sergey FrolovRD Oleg FedorovRD Andrey PetrovRD Petr SidorovRD Alexey AlexeevSales Ivan IvanovSales Ivan StulovQA Mihail HohlovQA 5. Внутр. и внешние соединения

Left Outer Join (внешнее соединение) Стандарт ANSI: select e.name, d.name department from employee e left outer join department d on e.department_id = d.department_id Используется в Oracle: select e.name, d.name department from employee e, department d where e.department_id = d.department_id(+) NAMEDEP... Sergey FrolovRD Oleg FedorovRD Andrey PetrovRD Petr SidorovRD Alexey AlexeevSales Ivan IvanovSales Ivan StulovQA Mihail HohlovQA Maxim Hlebnikov Sergey Vasiliev

Right Outer Join Стандарт ANSI: select e.name, d.name department from employee e right outer join department d on e.department_id = d.department_id Используется в Oracle: select e.name, d.name department from employee e, department d where e.department_id(+) = d.department_id NAMEDEP... Sergey FrolovRD Oleg FedorovRD Andrey PetrovRD Petr SidorovRD Alexey AlexeevSales Ivan IvanovSales Ivan StulovQA Mihail HohlovQA IT

Full Outer Join Стандарт ANSI: select e.name, d.name department from employee e full outer join department d on e.department_id = d.department_id Так неправильно!: select e.name, d.name department from employee e, department d where e.department_id(+) = d.department_id(+) NAMEDEP... Sergey FrolovRD Oleg FedorovRD Andrey PetrovRD Petr SidorovRD Alexey AlexeevSales Ivan IvanovSales Ivan StulovQA Mihail HohlovQA Maxim Hlebnikov Sergey Vasiliev IT

6. Теоретико-множественные операции над выборками Объединение множеств: select name, salary from employee where department_id=2 union [all] select name, salary from employee where department_id is null Вычитание множеств: select department_id from department minus select department_id from employee Упражнение 5: придумать осмысленный запрос с пересечением множеств - intersect

7. Подзапросы Подзапрос с единственным результатом: select name, salary from employee where salary > (select salary from employee where name=Oleg Fedorov) Многострочный подзапрос и сравнение с его результатами (in, any, all): select name, salary from employee where salary < all (select salary from employee where department_id=2) ANY – больше минимума; ALL – больше максимума; =ANY – эквивалентно IN; ALL – эквивалентно NOT IN… Упражнение 6: Выбрать сотрудников, чья зарплата превышает среднюю з/п по какому-либо отделу NAMESALARY Andrey Petrov1500 Sergey Vasiliev1500 NAMESALARY Petr Sidorov3000 Alexey Alexeev3500 Sergey Stulov , 3500

8. Иерархические запросы (Oracle) [start with условие] определяет корень(ни) дерева connect by и prior задает отношение parent-child можно использовать псевдостолбец level Пусть в таблице employee есть столбец manager references employee(employee_id). Тогда перечисление всех сотрудников с их подчиненными (если они есть): select lpad(,3*(level-1))||name name from employee connect by prior employee_id = manager_id //prior – перед PK Упражнение 7: построить иерархию начальников- подчиненных, в которой корни – только топ-менеджеры