SQL (диалект Oracle).Выборки с помощью SELECTлекция-семинар

1. Простейшие запросы
select table_name from user_tables
select sysdate from dual
общий вид простых запросов:
SELECT [DISTINCT] {*, column [alias],...}
[WHERE … [AND …] [OR …]]
FROM table;

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

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

Наполнение используемых таблиц

Выражения, ограничивающие набор строк (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)

Сортировка (ORDER BY)
select salary, name
from employee
order by salary, name
DESC – сортировка в направлении убывания
select e.salary, e.name
from employee e //e - alias
order by e.salary desc, e.name

Можно использовать функцию вместо атрибута сортировки после order by
(а также вместо выбираемого атрибута после select
и в условии отбора после where)
select salary, name
from employee
order by dbms_random.random //это функция в пакете

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
Упражнение 1: Выполнить аналогичный запрос c выдачей зарплат, причем зарплаты <> 5000 (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
Функции работы со строками
Упражнение 2: в запросе упражнения 1 выдавать имена с большой буквы (остальные - строчные), независимо от того, как они хранятся в БД. Применить SUBSTR, UPPER, LOWER, CONCAT (или оператор ||)

Функции переопределения значения
select name, salary, nvl(department_id, 0) department_id
from employee

select name, decode (salary, 1500, 'Good', 2000, 'Very Good', 'Cool!') status
from employee

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

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

Условие HAVING
select department_id, avg(salary)
from employee
group by department_id
having max(salary) > 2000
Если требуется отфильтровать строки до группировки – 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
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(+)

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

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(+)

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: Выбрать сотрудников, чья зарплата превышает среднюю з/п по какому-либо отделу
2500
2000, 3500