Реализация операции селекции в простых запросах на языке

Реализация операции селекции в простых запросах на языке SQL Лекция 3

Операция селекции предназначена для выбора из исходной таблицы строк/кортежей, удовлетворяющих условиям поиска Базовая конструкция команды SELECT для выполнения операции селекции имеет следующий синтаксис: • SELECT <имя атрибута 1>, <имя атрибута 2> • FROM <имя таблицы> • WHERE <условие>; Условия, входящие в предложение WHERE, делятся на простые и составные.

Простые условия представляются как: <имя атрибута 1> <значение для сравнения>, – оператор сравнения. Запрос 8. Выдать фамилии сотрудников, у которых надбавка • = Равно nadb превышает оклад: • > Больше SELECT fio, oklad, nadb • >= Больше или равно FROM sotr WHERE oklad< nadb; • < Меньше Предложения WHERE: • <= Меньше или равно WHERE pod= ‘АСУ’; • <> Не равно WHERHE okl > 2500; WHERE data= ’ 01 -JAN-02’. В СУБД Oracle можно изменить формат даты, используемый в рамках текущей сессии, с помощью команды ALTER SESSION c опцией SET: ALTER SESSION SET NLS_DATE_FORMAT=’<формат даты>’ NLS_DATE_LANGUAGE=’<название языка>’; Например, установить российские стандарты для работы с датами : ALTER SESSION SET NLS_DATE_FORMAT=’dd. mm. yyyy’ NLS_DATE_LANGUAGE=’RUSSIAN’;

Составные условия содержат два или более простых условия, соединенных между собой логическими операторами: AND – возвращает результат “истинно” (TRUE), если выполняются оба условия; OR – возвращает результат “истинно” (TRUE), если выполняется любое из условий; NOT – возвращает результат “истинно” (TRUE), если следующее за ним условие не выполняется Запрос 11. Выдать информацию о сотрудниках, которые либо приняты на должность декана либо на должность заведующего кафедрой с окладом свыше 5000 рублей: • SELECT fio, dol, oklad • FROM sotr • WHERE dol = ‘ДЕКАН’ OR dol = ‘ЗАВКАФЕДРОЙ’ AND oklad>5000; Запрос 12. Выдать информацию о деканах и заведующих кафедрами, у которых оклад свыше 5000 рублей: • SELECT fio, dol, oklad • FROM sotr • WHERE (dol = ‘ДЕКАН’ OR dol = ‘ЗАВКАФЕДРОЙ’) AND oklad>5000;

Специальные операторы сравнения SQL, используемые в операциях селекции • проверка на принадлежность диапазону значений BETWEEN (предназначен для проверки вхождения значения выражения в заданный диапазон значений) <выражение 2> AND <выражение 3>; WHERE <выражение 1> BETWEEN • проверка на членство в множестве IN(применяется для проверки принадлежности значений множеству) WHERE < выражение> IN (<значение 1>, <значение 2>); • проверка на соответствие шаблону LIKE(позволяет выбрать из таблицы кортежи, соответствующие определенной символьной строке, включающей один или более подстановочных знаков или метасимволов) WHERE <имя атрибута> LIKE <шаблон>; • проверка на равенство неопределенному значению IS NULL - WHERE <имя атрибута> IS NULL; .

ПРИМЕРЫ Запрос 13. Выдать фамилии сотрудников университета, у которых оклад находится в диапазоне от 2000 до 3000 рублей: • SELECT fio, oklad • FROM sotr • WHERE oklad BETWEEN 2000 AND 3000; Запрос 14. Выдать фамилии преподавателей вуза, принятых на должности ассистента либо доцента: • SELECT fio, dol • FROM sotr • WHERE dol IN (‘АССИСТЕНТ’, ‘ДОЦЕНТ); Выдать фамилии сотрудников, принятых на работу с января по декабрь 1999 года: • SELECT fio, datap • FROM sotr • WHERE datap LIKE ‘%1999’; Выдать подразделения вуза, в наименовании которых имеется последовательность из трех символов ‘A_B’: • SELECT pod • FROM sotr • WHERE pod LIKE ‘%A_B%’ ESCAPE ‘’; Опция ESCAPE задает символ пропуска, наличие которого в шаблоне превращает метасимвол в обычный символ. В качестве символа пропуска применен символ ‘’, его наличие в шаблоне после символа ‘A’ указывает на то, что символ подчеркивание _ является не метасимволом, а обычным литералом

Предложение сортировки данных ORDER BY (самостоятельная работа стр. 218 -219) Правила выполнения простого запроса SELECT [DISTINCT] {*, <имя атрибута> [алиас], …} FROM <имя таблицы> [WHERE <условие>] [ORDER BY{<имя атрибута>, <выражение>, <алиас>}[ASC DESC]; Выполнение простого запроса включает следующие шаги. Шаг 1. Выбирается таблица, указанная в предложении FROM. Шаг 2. Для запросов, содержащих предложение WHERE, условие поиска применяется к каждой строке таблицы, и отбираются строки, для которых заданное условие выполняется, т. е. имеет значение TRUE: строки, для которых условие поиска принимает значение FALSE или NULL – отбрасываются. Шаг 3. Для каждой из оставшихся строк вычисляется значение каждого элемента в списке возвращаемых атрибутов предложения SELECT, и формируется одна строка в результирующей таблице. Шаг 4. Для запросов с ключевым словом DISTINCT из результирующей таблицы удаляются все повторяющиеся строки. Шаг 5. При наличие предложения ORDER BY производится сортировка строк в результирующей таблице. Шаг 6. Результирующая таблица выдается пользователю

Агрегатные функции в SQL (самостоятельно стр. 220 -221) Общая форма агрегатной функции имеет следующий вид: SUM () – вычисление суммы элементов данных столбца; AVG () – вычисление среднего значения элементов данных столбца; MIN () – определение минимального среди всех значений столбца; MAX () – определение максимального среди всех значений столбца; COUNT () – нахождение количества значений, содержащихся в столбце; • COUNT (*) – нахождение количества строк в результирующей таблице. • • • В СУБД ORACLE дополнительно поддерживается определение таких функций, как: • STDDEV () – вычисление стандартного отклонения значений элементов данных столбца; • VARIANCE () – вычисление дисперсии значений элементов данных столбца.

Группирование данных в SQL • Например, проректору по экономике необходимы данные о распределении среднего должностного оклада по подразделениям университета. • Для получения такой информации из фрагмента таблицы sotr необходимо сформировать группы кортежей по совпадению значения атрибута pod, а затем для каждой группы определить среднее значение должностного оклада. fio Айвазян М. К. Арапитов Ю. Р. Буров Ю. В. Вотин Н. К. Каплунов М. Р. Кетов Д. Р. fio Айвазян М. К. Кетов Д. Р. Буров Ю. В. Каплунов М. Р. Арапитов Ю. Р. Вотин Н. К. pod ЭВМ Аи. Т АСУ ЭВМ pod ЭВМ АСУ Аи. Т okl 2500 2000 3500 2100 2500 okl 2500 2100 3000 2500 3500 2000 1 -я группа, среднее значение 2250 2 -я группа, среднее значение 3250 3 -я группа, среднее значение 2300 = = =

Для выполнения таких запросов в SQL введено предложение GROUP BY, имеющее следующий вид: GROUP BY <атрибут 1>, … , <атрибут n>, где <атрибут 1>, …, <атрибут n> – это имена столбцов, по которым производится группирование данных АСУ АССИСТЕНТ • SELECT <атрибут 1>, <атрибут 2>, <агрегатная функция> (<атрибут 3>) АСУ ДОЦЕНТ АСУ СТАРШИЙ ПРЕПОДАВАТЕЛЬ • FROM <имя таблицы> ПОВТ АССИСТЕНТ • GROUP BY < атрибут 1>, < атрибут 2>; ПОВТ ДОЦЕНТ • атрибуты, указанные в SELECT-списке, ПОВТ ПРОФЕССОР обязательно должны быть включены ПОВТ СТАРШИЙ ПРЕПОДАВАТЕЛЬ в список атрибутов предложения ЭВМ АССИСТЕНТ GROUP BY. • Последовательность атрибутов в предложении GROUP BY задает Выдать распределение среднего порядок сортировки строк. В частности, применение предложения оклада по подразделениям вуза и GROUP BY pod, dol позволяет должностям сотрудников: получить следующий порядок SELECT pod, dol, AVG (oklad) группирования строк таблицы: FROM sotr GROUP BY pod, dol;

Предложение GROUP BY определяет порядок группирования строк: - в начале по названию подразделений; - затем по должностям внутри каждого подразделения. • Для отбора групп используется предложение HAVING, которое имеет следующий синтаксис: • HAVING <условие>. • Наиболее распространенным является предложением HAVING, в котором условие содержит агрегатную функцию, оператор сравнения и значение для сравнения , т. е. • HAVING <агрегатная функция> (<атрибут>) . • Запрос. Выдать распределение суммарного оклада по тем подразделениям вуза, в которых минимальный оклад превышает 2000: • SELECT pod, SUM (oklad) • FROM sotr • GROUP BY pod • HAVING MIN (oklad)>2000; из результатов будет исключена группа кортежей со значениями атрибута pod = ‘ЭВМ’, т. к. минимальный оклад в этой группе равен 2000.

Предложение GROUP BY обычно предшествует предложению HAVING. • В запросах группирования возможно включение предложения WHERE, которое производит контроль выполнения условия для каждого из кортежей. Предложение WHERE предшествует предложению GROUP BY. • Запрос. Выдать распределение среднего оклада доцентов университета по тем подразделениям, в которых их суммарный оклад больше 50000: • SELECT pod, AVG (oklad) • FROM sotr • WHERE dol = ‘ДОЦЕНТ’ (1) • GROUP BY pod (2) • HAVING SUM (oklad)>50000; (3)

• Замечание 1. Неопределенные значения атрибута группирования образуют самостоятельную группу. • Замечание 2. Предложение HAVING может использоваться без предложения GROUP BY. В этом случае все кортежи отношения рассматриваются как одна группа, для которой производится проверка условия предложения HAVING. • Замечание 3. В качестве значения для сравнения может использоваться выражение. Например, HAVING AVG(okl)>2*MIN (okl). • Замечание 4. Агрегатные функции при использовании предложения GROUP BY могут иметь два уровня вложенности. Например, запрос, предназначенный для получения максимального суммарного оклада в подразделениях вуза, представляется следующим образом: • SELECT MAX (SUM (oklad)) SELECT pod, MAX (SUM (oklad)) • FROM sotr • GROUP BY pod; GROUP BY pod; Ошибочным является модификация этого запроса, направленная на получение наименования подразделения, в котором максимален суммарный оклад сотрудников. !!!!!

Однострочные функции в SQL • В промышленных СУБД имеется широкий набор функций, позволяющих производить вычисления над данными, изменения значений элементов данных, преобразования типов данных, форматирование чисел и дат для вывода. • Однострочные функции возвращают одно результирующее значение для каждой строки таблицы и делятся на следующие типы: символьные; числовые; общие; для работы с датами; преобразования. Аргументом однострочной функции являются: имя столбца; выражение; константа; значение переменной. Синтаксис функции: • <имя функции> (<атрибут> <выражение> [arq 1, arq 2, …, ]), • где arq 1, arq 2 – любой аргумент, используемый функцией.

Реализация операций реляционной алгебры для результатов независимых запросов • К таким операциям реляционной алгебры относятся следующие: • UNION – объединение; INTERSECT – пересечение; EXCEPT – разность. Общая схема выполнения этих операций Необходимые условия выполнения перечисленных операций: 1) результирующие таблицы независимых запросов должны содержать одинаковое количество атрибутов; 2) атрибуты результирующих таблиц попарно должны иметь одинаковый тип и размер данных; 3) независимые запросы не могут содержать предложения сортировки данных ORDER BY в соответствии со стандартом ANSI/ISO; 4) SELECT-списки запроса 1 и запроса 2 не должны содержать выражения, а могут включать только имена атрибутов или указатели на все атрибуты (*); 5) в ряде коммерческих СУБД не разрешается включать в операторы SELECT независимых запросов предложения GROUP BY и HAVING.

• При выполнении операции UNION в итоговую таблицу будут включены кортежи, входящие либо в результирующую таблицу 1, либо в результирующую таблицу 2, при этом повторяющиеся кортежи будут автоматически удалены из итоговой таблицы. • Для исключения удаления строк-дубликатов необходимо использовать опцию ALL. Сортировка кортежей в итоговой таблице производится с помощью предложения ORDER BY, которое указывается после второй команды SELECT. Так как имена атрибутов в SELECT-списке 1 и SELECT-списке 2 могут не совпадать, то обычно приводятся номера столбцов/атрибутов.

• • • Запрос. Предположим, что в отношении sotr хранится информация только о штатных сотрудниках вуза, которые условно делятся на две группы: первая группа включает преподавателей, занятых учебной работой, а вторая группа – преподавателей, повышающих профессиональный уровень и временно не участвующих в проведении учебных занятий. Кроме того, имеется отношение nagrus, которая содержит следующие атрибуты: код учебной дисциплины codd; наименование дисциплины naimdisp фамилия преподавателя fio_p; количество часов, отводимых на изучение дисциплины colch_d. В этом отношении указаны фамилии, как штатных преподавателей, так и совместителей. Необходимо получить фамилии всех штатных сотрудников вуза и совместителей: SELECT fio FROM sotr UNION SELECT fio_p FROM nagrus ORDER BY 1;

INTERSECT, EXCEPT • • • • При выполнении операции INTERSECT в итоговую таблицу будут включены кортежи, которые одновременно входят в результирующую таблицу 1 и в результирующую таблицу 2. Запрос. На основе информации, хранящейся в отношениях sotr и nagrus, необходимо получить фамилии штатных преподавателей, занятых в учебном процессе: SELECT fio FROM sotr INTERSECT SELECT fio_p FROM nagrus ORDER BY 1; При выполнении операции EXCEPT в итоговую таблицу будут включены кортежи, которые входят в результирующую таблицу 1 и не входят в результирующую таблицу 2. Запрос. На основе информации, хранящейся в отношениях sotr и nagrus, необходимо получить фамилии совместителей, привлеченных для выполнения учебной нагрузки: SELECT fio_p FROM nagrus EXCEPT SELECT fio FROM sotr ORDER BY 1;

• Операцию объединения UNION целесообразно применять в том случае, когда необходимо изменить значения элементов столбца 1 на различную величину в зависимости от значений другого столбца. • Запрос. В отношении sotr необходимо увеличить оплату сотрудникам на различную величину в зависимости от подразделений, в котором они работают: ИИСТ – okl * 2. 0, Аи. Т – okl * 1. 8, для остальных подразделений – okl * 1. 5: • SELECT fio, pod, oklad*2. 0 • FROM sotr • WHERE pod = ‘АСУ’ • UNION • SELECT fio, pod, oklad * 1. 8 • FROM sotr • WHERE pod = ‘Аи. Т’ • UNION • SELECT fio, pod, oklad * 1. 5 • FROM sotr • WHERE pod NOT IN (‘АСУ’, ‘Аи. Т’);

• В том случае, когда необходимо выполнить приведенные операции реляционной алгебры над тремя и более отношениями, то вначале одна из операций производится над двумя отношениями, а затем следующая операция осуществляется над результирующей таблицей Результирующая таблица 1 Отношение 1 Операция 1 реляционной алгебры Запрос 1 Итоговая таблица 1 Результирующая таблица 2 Отношение 2 Запрос 2 Операция 2 реляционной алгебры Результирующая таблица 3 Отношение 3 Запрос 3 Итоговый результат Последовательность выполнения запросов может определяться с помощью круглых скобок.

Многотабличные запросы. Соединение таблиц • В исходных таблицах выделяются атрибуты, по которым производится соединение таблиц и указывается условие соединения. Наиболее распространенным является условие, предполагающее совпадение значений атрибута 1 соединения со значением атрибута 2 соединения Такие соединения называются простыми или эквисоединениями. При выполнении операции эквисоединения для каждого кортежа отношения 1 значение атрибута 1 соединения последовательно сравнивается со значениями атрибута 2 соединения, и в результирующую таблицу передаются попарно кортежи отношения 1 и отношения 2, для которых сравниваемые значения совпадают.

• Базовая структура многотабличного запроса эквисоединения представляется следующим образом: • SELECT <таблица 1>. <атрибут 1>, <таблица 2>. <атрибут 2> • FROM <таблица 1>, <таблица 2> • WHERE <таблица 1>. <атрибут 3> = <таблица 2>. <атрибут 4>; • Запрос. Созданы отношения sotr_5 и kafedra_1, большинство атрибутов которых были описаны ранее. Отношение kafedra_1 дополнительно содержит атрибут – корпус расположения кафедры camp • Необходимо получить распределение сотрудников по корпусам, в которых они работают: • SELECT sotr_5. fio, kafedra_1. camp • FROM sotr_5, kafedra_1 • WHERE kafedra_1. naim_kaf = sotr_5. pod;