Лабораторная работа №2 (4 часа)

Цель работы: изучение команды SELECT.

Теория про SELECT (с примерами).

Примеры рассматриваются на основе следующих таблиц:

STUDENTS(студенты)	
STUD_ID (id студента)	
SURNAME (фамилия)	
NAME (имя)	
PATRON (отчество)	
GR_ID (id группы)	
GROUPS (группы)	
GR_ID (id группы)	
NUM_GR (номер группы)	
FORM  (форма обучения)	
EXAMS (экзамены)	
EX_ID (id экзамена)	
DAT (дата и время экзамена)	
GR_ID (id группы)	
SUBJ_ID (id предмета)	

RATING (оценки)	
R_ID (id оценки)	
EX_ID (id экзамена)	
STUD_ID (id студента)	
MARK (оценка)	

SUBJECTS (предметы)	
SUBJ_ID (id предмета)	
SUBJ_NAME (название предмета)	

Основные категории команд языка SQL:
DDL – язык определения данных; 
DML – язык манипулирования данными; 
DQL – язык запросов; 
DCL – язык управления данными; 
команды администрирования данных; 
команды управления транзакциями

Язык запросов DQL наиболее известен пользователям реляционной базы данных, несмотря на то, что он включает одну команду SELECT. Эта команда вместе со своими многочисленными опциями и предложениями используется для формирования запросов к реляционной базе данных.
Оператор SELECT – один из наиболее важных и самых распространенных операторов SQL. Он позволяет производить выборки данных из таблиц и преобразовывать к нужному виду полученные результаты. Будучи очень мощным, он способен выполнять действия, эквивалентные операторам реляционной алгебры, причем в пределах единственной выполняемой команды. При его помощи можно реализовать сложные и громоздкие условия отбора данных из различных таблиц.

Оператор SELECT имеет следующий формат:
SELECT [ALL | DISTINCT ] {*|[имя_столбца  [AS 	новое_имя]]} [,...n]
  FROM  имя_таблицы [[AS] псевдоним] [,...n]
  [WHERE  <условие_поиска>]
  [GROUP BY  имя_столбца [,...n]]
  [HAVING <критерии выбора групп>]
  [ORDER BY имя_столбца [,...n]]

Оператор SELECT определяет поля (столбцы), которые будут входить в результат выполнения запроса. В списке они разделяются запятыми и приводятся в такой очередности, в какой должны быть представлены в результате запроса. Символом * можно выбрать все поля, а вместо имени поля применить выражение из нескольких имен.
Если обрабатывается ряд таблиц, то (при наличии одноименных полей в разных таблицах) в списке полей используется полная спецификация поля, т.е. имя_таблицы.имя_поля или псевдоним.имя_таблицы.

Предикат ALL задает включение в выходной набор всех дубликатов, отобранных по критерию WHERE. Нет необходимости указывать ALL явно, поскольку это значение действует по умолчанию. Результат выполнения запроса может содержать дублирующиеся значения, поскольку в отличие от операций реляционной алгебры оператор SELECT не исключает повторяющихся значений при выполнении выборки данных. Предикат DISTINCT следует применять в тех случаях, когда требуется отбросить блоки данных, содержащие дублирующие записи в выбранных полях. Значения для каждого из приведенных в инструкции SELECT полей должны быть уникальными, чтобы содержащая их запись смогла войти в выходной набор.

Обработка элементов оператора SELECT выполняется в следующей последовательности:
FROM – определяются имена используемых таблиц; 
WHERE – выполняется фильтрация строк объекта в соответствии с заданными условиями; 
GROUP BY – образуются группы строк, имеющих одно и то же значение в указанном столбце; 
HAVING – фильтруются группы строк объекта в соответствии с указанным условием; 
SELECT – устанавливается, какие столбцы должны присутствовать в выходных данных; 
ORDER BY – определяется упорядоченность результатов выполнения операторов

Предложение FROM
Предложение FROM задает имена таблиц и просмотров, которые содержат поля, перечисленные в операторе SELECT. Необязательный параметр псевдонима – это сокращение, устанавливаемое для имени таблицы.

Предложение WHERE
С помощью WHERE-параметра пользователь определяет, какие блоки данных из приведенных в списке FROM таблиц появятся в результате запроса. За ключевым словом WHERE следует перечень условий поиска, определяющих те строки, которые должны быть выбраны при выполнении запроса. Существует пять основных типов условий поиска.
Сравнение
В языке SQL можно использовать следующие операторы сравнения: = – равенство; < – меньше; > – больше; <= – меньше или равно; >= – больше или равно; <> – не равно.
Более сложные предикаты могут быть построены с помощью логических операторов AND, OR или NOT, а также скобок, используемых для определения порядка вычисления выражения.
Диапазон
Оператор BETWEEN используется для поиска значения внутри некоторого интервала, определяемого своими минимальным и максимальным значениями. При этом указанные значения включаются в условие поиска.
Принадлежность множеству
Оператор IN используется для сравнения некоторого значения со списком заданных значений, при этом проверяется, соответствует ли результат вычисления выражения одному из значений в предоставленном списке. При помощи оператора IN может быть достигнут тот же результат, что и в случае применения оператора OR, однако оператор IN выполняется быстрее.
Соответствие шаблону
С помощью оператора LIKE можно выполнять сравнение выражения с заданным шаблоном, в котором допускается использование символов-заменителей:
Символ % – вместо этого символа может быть подставлено любое количество произвольных символов. 
Символ _ заменяет один символ строки.     
Значение NULL
Оператор IS NULL используется для сравнения текущего значения со значением NULL – специальным значением, указывающим на отсутствие любого значения. NULL – это не то же самое, что знак пробела (пробел – допустимый символ) или ноль (0 – допустимое число). NULL отличается и от строки нулевой длины (пустой строки).
Примеры.
Выбор всей информации о студентах с именем Елена.
SELECT * FROM students WHERE name=’Елена’  
Выбор всей информации о студентах с фамилиями: Иванов, Петров, Сидоров.
SELECT * FROM students WHERE surname in (‘Иванов’,’Петров’,’Сидоров’)
Выбор фамилий студентов начинающихся на букву Я.
SELECT surname FROM students WHERE surname like ‘Я%’
Выбор номеров групп специальности ПОВТ и АС.
SELECT gr.num_gr FROM groups gr WHERE gr.num_gr like ‘_09_’
Выбор экзаменов, назначенных на даты с 15 по 20 января включительно.
SELECT s.name FROM exams ex, subject s WHERE
 ex.subj_id=s.subj_id and ex.dat BETWEEN  ’15.01.2009’ AND ’20.01.2009’
Выбор экзаменов у группы ‘0092’ с не назначенной (пустой) датой.
SELECT s.name FROM exams ex, subject s,groups gr WHERE
 ex.subj_id=s.subj_id and gr.gr_id=ex.gr_id and gr.num_group=’0092’ and ex.dat IS NULL

Предложение ORDER BY
В общем случае строки в результирующей таблице SQL-запроса никак не упорядочены. Однако их можно требуемым образом отсортировать, для чего в оператор SELECT помещается фраза ORDER BY, которая сортирует данные выходного набора в заданной последовательности. Сортировка может выполняться по нескольким полям, в этом случае они перечисляются за ключевым словом ORDER BY через запятую. Способ сортировки задается ключевым словом, указываемым в рамках параметра ORDER BY следом за названием поля, по которому выполняется сортировка. По умолчанию реализуется сортировка по возрастанию. Явно она задается ключевым словом ASC. Для выполнения сортировки в обратной последовательности необходимо после имени поля, по которому она выполняется, указать ключевое слово DESC. Фраза ORDER BY позволяет упорядочить выбранные записи в порядке возрастания или убывания значений любого столбца или комбинации столбцов, независимо от того, присутствуют эти столбцы в таблице результата или нет. Фраза ORDER BY всегда должна быть последним элементом в операторе SELECT.
Во фразе ORDER BY вместо полей можно также указать порядковый номер столбцов результирующей таблицы.

Примеры.
Выбор ФИО всех студентов из группы ‘0092’ в алфавитном порядке.
SELECT s.*  FROM students s,groups gr
WHERE gr.gr_id=s.gr_id
ORDER BY s.surname,s.name,s.patron 
или
SELECT s.surname,s.name,s.patron FROM students s,groups gr
WHERE gr.gr_id=s.gr_id
ORDER BY 1, 2, 3

Построение вычисляемых полей
В общем случае для создания вычисляемого поля в списке SELECT следует указать некоторое выражение языка SQL. В этих выражениях применяются арифметические операции сложения, вычитания, умножения и деления, а также встроенные функции языка SQL. Можно указать имя любого столбца (поля) таблицы или запроса, но использовать имя столбца только той таблицы или запроса, которые указаны в списке предложения FROM соответствующей инструкции. При построении сложных выражений могут понадобиться скобки.
Стандарты SQL позволяют явным образом задавать имена столбцов результирующей таблицы, для чего применяется фраза AS (но она может быть опущена).
Примеры.
Расписание экзаменов для группы 0092 с датой и временем экзамена.
SELECT s.subj_name предмет, to_char(e.dat,’DD.MM.YYYY’) AS Дата, to_char(e.dat,’HH24:MI’) Время
FROM groups gr, exams e,subjects s
WHERE gr.gr_id=e.gr_id and e.subj_id=s.subj_id and gr.num_gr=’0092’ and gr.form=’Д’
ORDER BY 2
--функция to_char преобразует число или строку даты в строку символов, возможно задание формата(существуют подобные функции to_number и to_date).

Расписание экзаменов для всех групп по датам, вместо пустых дат выводится «?».
SELECT s.subj_name, nvl(to_char(e.dat,’DD.MM.YYYY’),’?’)
FROM exams e,subjects s WHERE e.subj_id=s.subj_id
--функция nvl возвращает второе значение, если первое значение является неопределенным

Использование итоговых функций
С помощью итоговых функций в рамках SQL-запроса можно получить ряд обобщающих статистических сведений о множестве отобранных значений выходного набора.
Пользователю доступны следующие основные итоговые функции:
Count (Выражение) - определяет количество записей в выходном наборе SQL-запроса; 
Min/Max (Выражение) - определяют наименьшее и наибольшее из множества значений в некотором поле запроса; 
Avg (Выражение) - эта функция позволяет рассчитать среднее значение множества значений, хранящихся в определенном поле отобранных запросом записей. Оно является арифметическим средним значением, т.е. суммой значений, деленной на их количество. 
Sum (Выражение) - вычисляет сумму множества значений, содержащихся в определенном поле отобранных запросом записей.
Чаще всего в качестве выражения выступают имена столбцов. Выражение может вычисляться и по значениям нескольких таблиц.
Все эти функции оперируют со значениями в единственном столбце таблицы или с арифметическим выражением и возвращают единственное значение. Вариант COUNT(*) - особый случай использования функции COUNT, его назначение состоит в подсчете всех строк в результирующей таблице, независимо от того, содержатся там пустые, дублирующиеся или любые другие значения.

Примеры.
Подсчет общего числа студентов.
SELECT count(*) FROM students
Дата последнего экзамена.
SELECT max(dat) FROM exams

Предложение GROUP BY
Часто в запросах требуется формировать промежуточные итоги. Для этой цели в операторе SELECT используется предложение GROUP BY. Запрос, в котором присутствует GROUP BY, называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT, после чего для каждой отдельной группы создается единственная суммарная строка. Стандарт SQL требует, чтобы предложение SELECT и фраза GROUP BY были тесно связаны между собой. При наличии в операторе SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Более того, предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции, константы и выражения, включающие комбинации перечисленных выше элементов.
Все имена полей, приведенные в списке предложения SELECT, должны присутствовать и во фразе GROUP BY - за исключением случаев, когда имя столбца используется в итоговой функции. Обратное правило не является справедливым - во фразе GROUP BY могут быть имена столбцов, отсутствующие в списке предложения SELECT.
Если совместно с GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группированию подвергаются только те строки, которые удовлетворяют условию поиска.
Стандартом SQL определено, что при проведении группирования все отсутствующие значения рассматриваются как равные. Если две строки таблицы в одном и том же группируемом столбце содержат значение NULL и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу.
Пример.
Определение количества студентов в группах.
SELECT gr.num_gr, gr.form, count(s.surname)
FROM students s, groups gr WHERE s.gr_id=gr.gr_id
GROUP BY gr.num_gr, gr.form
ORDER BY 1,2

Предложение HAVING
При помощи HAVING отражаются все предварительно сгруппированные посредством GROUP BY блоки данных, удовлетворяющие заданным в HAVING условиям. Это дополнительная возможность "профильтровать" выходной набор.
Пример.
Определение групп из более десяти человек.
SELECT gr.num_gr, gr.form, count(s.surname)
FROM students s, groups gr WHERE s.gr_id=gr.gr_id
GROUP BY gr.num_gr, gr.form
HAVING count(s.surname)>10

Понятие подзапроса
Часто невозможно решить поставленную задачу путем одного запроса. Это особенно актуально, когда при использовании условия поиска в предложении WHERE значение, с которым надо сравнивать, заранее не определено и должно быть вычислено в момент выполнения оператора SELECT. В таком случае приходят на помощь законченные операторы SELECT, внедренные в тело другого оператора SELECT. Внутренний подзапрос представляет собой также оператор SELECT, а кодирование его предложений подчиняется тем же правилам, что и основного оператора SELECT. Внешний оператор SELECT использует результат выполнения внутреннего оператора для определения содержания окончательного результата всей операции. Внутренние запросы могут быть помещены непосредственно после оператора сравнения (=, <, >, <=, >=, <>) в предложения WHERE и HAVING внешнего оператора SELECT – они получают название подзапросов или вложенных запросов. Кроме того, внутренние операторы SELECT могут применяться в операторах INSERT, UPDATE и DELETE.
Подзапрос – это инструмент создания временной таблицы, содержимое которой извлекается и обрабатывается внешним оператором. Текст подзапроса должен быть заключен в скобки. К подзапросам применяются следующие правила и ограничения:
•	фраза ORDER BY не используется, хотя и может присутствовать во внешнем подзапросе; 
•	список в предложении SELECT состоит из имен отдельных столбцов или составленных из них выражений – за исключением случая, когда в подзапросе присутствует ключевое слово EXISTS; 
•	по умолчанию имена столбцов в подзапросе относятся к таблице, имя которой указано в предложении FROM. Однако допускается ссылка и на столбцы таблицы, указанной во фразе FROM внешнего запроса, для чего применяются квалифицированные имена столбцов (т.е. с указанием таблицы); 
•	если подзапрос является одним из двух операндов, участвующих в операции сравнения, то запрос должен указываться в правой части этой операции.
Существует два типа подзапросов:
Скалярный подзапрос возвращает единственное значение. В принципе, он может использоваться везде, где требуется указать единственное значение. 
Табличный подзапрос возвращает множество значений, т.е. значения одного или нескольких столбцов таблицы, размещенные в более чем одной строке. Он возможен везде, где допускается наличие таблицы.

Примеры.
Определение, по какому предмету сдается последний экзамен.
SELECT  subj_name, dat FROM exams,subject 
WHERE dat=(SELECT max(dat) FROM exams)

Выбор первых экзаменов для всех групп.
SELECT num_gr, form,subj, name, dat FROM groups g,exams ex,subjects su,
(SELECT gr_id,min(dat) mindat FROM exams GROUP BY gr_id) xx
WHERE g.gr_id=ex.gr_id AND ex.subj_id=su.subj_id AND xx.gr_id=g.gr_id AND e.dat=xx.mindat
Использование ключевых слов ANY и ALL
Ключевые слова ANY и ALL могут использоваться с подзапросами, возвращающими один столбец чисел. 
Если подзапросу будет предшествовать ключевое слово ALL, условие сравнения считается выполненным, только когда оно выполняется для всех значений в результирующем столбце подзапроса. 
Если записи подзапроса предшествует ключевое слово ANY, то условие сравнения считается выполненным, когда оно выполняется хотя бы для одного из значений в результирующем столбце подзапроса. 
Если в результате выполнения подзапроса получено пустое значение, то для ключевого слова ALL условие сравнения будет считаться выполненным, а для ключевого слова ANY – невыполненным. 
Примеры.
Выбор студентов-отличников (при условии, что у каждого студента есть хотя бы одна оценка).
SELECT s.surname, s.name,s.patron FROM student s
WHERE 5=ALL (SELECT mark FROM rating r WHERE s.stud_id=r.stud.id)

Выбор студентов, получивших хотя бы одну пятерку.
SELECT s.surname, s.name,s.patron FROM student s
WHERE 5=ANY (SELECT mark FROM rating r WHERE s.stud_id=r.stud.id)

Использование операций EXISTS и NOT EXISTS
Ключевые слова EXISTS и NOT EXISTS предназначены для использования только совместно с подзапросами. Результат их обработки представляет собой логическое значение TRUE или FALSE. Для ключевого слова EXISTS результат равен TRUE в том и только в том случае, если в возвращаемой подзапросом результирующей таблице присутствует хотя бы одна строка. Если результирующая таблица подзапроса пуста, результатом обработки операции EXISTS будет значение FALSE. Для ключевого слова NOT EXISTS используются правила обратные по отношению к ключевому обработки, слову EXISTS. Поскольку по ключевым словам EXISTS и NOT EXISTS проверяется лишь наличие строк в результирующей таблице подзапроса, то эта таблица может содержать произвольное количество столбцов.
Пример.
Список групп, не сдающих философию.
SELECT num_gr, form FROM groups
WHERE NOT EXISTS (SELECT ex_id FROM exams,subjects
                  	WHERE exams.subj_id=subjects.subj_id AND groups.gr_id=exams.gr_id
			   AND subj_name=’Философия’)

Задание.
Зайдите в систему с правами администратора. Выберите в меню пункт Administration (Администрирование). Затем пункт Database users.Выберите пользователя HR. Задайте ему пароль hr. Account  Status сделайте Unlocked.
Зайдите в систему этим пользователем. Теперь в  Object Browser вы обнаружите несколько таблиц. Описание схемы HR приведено далее в приложении. Именно к этой схеме и относятся следующие задания.

Использованием команды  SELECT:
1.	Выбрать все данные из таблицы JOB_HISTORY.
2.	Из таблицы EMPLOYEES выбрать всех людей, чья зарплата больше 8000.
3.	Из таблицы должностей EMPLOYEES выбрать сотрудников, чей телефон начинается на 590.
4.	Из таблицы EMPLOYEES выбрать всех людей, чья зарплата больше или равна 7000 и меньше или равна 10000.
5.	Из таблицы EMPLOYEES выбрать дату и время начала работы в должности Стивена Кинга. 
6.	Вывести фамилии сотрудников отдела продаж (Sales).
7.	Подсчитать количество отделов всего.
8.	Подсчитать количество работников в каждом отделе.
9.	Выбрать работника, который начал работать последним.
10.	Выбрать названия отделов с общей заработной платой больше 18000.
11.	Вывести фамилий сотрудников, имеющих такую же должность, что и John Chen.
12.	Вывести названия всех отделов, где средняя зарплата выше, чем в отделе IT.
13.	Определите страны, в которых нет представительства фирмы (таблицы COUNTRIES, LOCATIONS). Определите страны, в которых есть представительства фирмы.
(EXISTS и NOT EXISTS)
14.	Определите отделы, в которых зарплата хотя бы одного работника больше 8000.

Требования к отчету.
Отчет по лабораторной работе должен содержать:
Цель работы
Команды Select для выполнения пунктов задания.

________________________________________________________________________________
Приложение.

Описание схемы HR.
	В записях о штате сотрудников каждый сотрудник имеет идентификационный номер, адрес электронной почты, идентификатор должности, оклад и (идентификатор) руководителя. Некоторые сотрудники дополнительно к окладу зарабатывают комиссионные. (Таблица EMPLOYEES).
	Также компания хранит информацию о должностях в рамках организации. (Таблица JOBS). У каждой должности есть идентификатор, название и диапазон («вилка») окладов.
	Некоторые сотрудники долгое время работают в компании и занимали в ней разные должности. При уходе сотрудника с должности записываются сведения о продолжительности его работы в данной должности, её идентификатор и подразделение, в котором работал сотрудник. (Таблица JOB_HISTORY).
	Компания из данного примера работает в нескольких регионах, поэтому хранятся сведения о местах расположения её складов и подразделений. Каждый сотрудник приписан  к одному подразделению, а каждое подразделение идентифицируется уникальным номером. (Таблица DEPARTMENTS). Каждое подразделение связано (находится во взаимно однозначном соответствии) с одним местоположением (Таблица LOCATIONS), а для каждого местоположения хранится полный адрес, содержащий название улицы, почтовый индекс, город, штат или область и код страны.
	Для местоположений подразделений и складов хранится детализированная информация: название страны и географический регион, где расположена страна. (Таблицы COUNTRIES и REGIONS)