Внутреннее соединениеОперация INNER JOIN объединяет записи из двух таблиц, если связующие поля этих таблиц содержат одинаковые значения.Синтаксис операции:FROM таблица_1 INNER JOIN таблица_2 ON таблица_1.поле_1 оператор таблица_2.поле_2 где таблица_1, таблица_2 — имена таблиц, записи которых подлежат объединению;поле_1, поле_2 — имена объединяемых полей. Поля должны иметь одинаковый тип данных и содержать данные одного рода, однако эти поля могут иметь разные имена;оператор — любой оператор сравнения: "=," "<," ">," "<=," ">=," или "<>".Операцию INNER JOIN можно использовать в любом предложении FROM. Это самые обычные типы связывания. Они объединяют записи двух таблиц, если связующие поля обеих таблиц содержат одинаковые значения. Предыдущий пример использования команды SELECT можно записать с использованием конструкции INNER JOIN следующим образом:SELECT Группы.Номер_группы, Группы.ФИО_куратора, Студенты.ФИО_студентаFROM Группы INNER JOIN СтудентыON Группы.Номер_группы = Студенты.Номер_группы;Внешнее соединениеОперации LEFT JOIN, RIGHT JOIN объединяют записи исходных таблиц при использовании в любом предложении FROM. Операция LEFT JOIN используется для создания внешнего соединения, при котором все записи из первой (левой) таблицы включаются в результирующий набор, даже если во второй (правой)таблице нет соответствующих им записей.Операция RIGHT JOIN используется для создания внешнего объединения, при котором все записи из второй (правой) таблицы включаются в результирующий набор, даже если в первой (левой) таблице нет соответствующих им записей.Синтаксис операции:FROM таблица_1 [ LEFT | RIGHT ] JOIN таблица_2 ON таблица_1.поле_1 оператор таблица_2.поле_2Например, операцию LEFT JOIN можно использовать с таблицами «Студенты» (левая) и «Задолженность_за_обучение» (правая) для отбора всех студентов, в том числе тех, которые не являются задолжниками:SELECT Студенты.ФИО_студента, Задолженность_за_обучение.Сумма_задолженности FROM Студенты LEFT JOIN Задолженность_за_обучение ON Студенты.Номер_зачетной_книжки = Задолженность_за_обучение.Номер_зачетной_книжки;Поле «Номер_зачетной_книжки» в этом примере используется для объединения таблиц, однако, оно не включается в результат выполнения запроса, поскольку не включено в инструкцию SELECT. Чтобы включить связующее поле (в данном случае поле«Номер_зачетной_книжки») в результат выполнения запроса, его имя необходимо включить в инструкцию SELECT.Важно отметить, что операции LEFT JOIN или RIGHT JOIN могут быть вложены в операцию INNER JOIN, но операция INNER JOIN не может быть вложена в операцию LEFT JOIN или RIGHT JOIN.
Перекрестные запросыВ некоторых СУБД (в частности в MS Access) существует такой вид запросов как перекрестный. В перекрестном запросе отображаются результаты статистических функций — суммы, средние значения и др., а также количество записей. При этом подсчет выполняется по данным из одного полей таблицы.Результаты группируются по двум наборам данных, один из которых расположен в левом столбце таблицы, а другой в заголовке таблицы. Например, при необходимости вычислить средний балл студентов за семестр, обучающихся на разных кафедрах, необходимо реализовать перекрестный запрос, в результате выполнения которого будет создана таблица, где заголовками строк будут служить номер семестра, заголовками столбцов — названия кафедр, а в полях таблицы будет рассчитан средний балл.Для создания перекрестного запроса необходимо использовать следующую инструкцию:TRANSFORM статистическая_функция инструкция_SELECTPIVOT поле [IN (значение_1[, значение_2[, ...]])],где статистическая_функция — статистическая функция SQL, обрабатывающая указанные данные;инструкция_SELECT — запрос на выборку;поле — поле или выражение, которое содержит заголовки столбцов для результирующего набора;значение_1, значение_2 — фиксированные значения, используемые при создании заголовков столбцов.Составим SQL-запрос, реализующий описанный выше пример. В качестве исходного набора данных используется таблица Успеваемость (рис. 3).Рис. 3. Таблица УСПЕВАЕМОСТЬВ результате выполнения нижеприведенного перекрестного SQL-запроса формируется следующая таблица (рис. 4):TRANSFORM AVG(Успеваемость.Оценка) AS Сред_балл SELECT Успеваемость.СеместрFROM УспеваемостьGROUP BY Успеваемость.Семестр PIVOT Успеваемость.КафедраРис. 4. Результат выполнения перекрестного запросаТаким образом, когда данные сгруппированы с помощью перекрестного запроса, можно выбирать значения из заданных столбцов или выражений и определять как заголовки столбцов. Это позволяет просматривать данные в более компактной форме, чем при работе с запросом на выборку.Подчиненные запросыЧасто возникает ситуация, когда желаемый результат нельзя получить с помощью одного SQL-запроса. Одним из способов решения такой задачи является использование подчиненных запросов в составе главного SQL-запроса. Подчиненным SQL- запросом называют инструкцию SELECT, включаемую в инструкции SELECT, SELECT...INTO, INSERT...INTO, DELETE или
UPDATE или в другой подчиненный запрос. Подчиненный запрос может быть создан одним из трех способов, синтаксис которых представлен ниже:1) сравнение [ANY | ALL | SOME] (инструкцияSQL)2) выражение [NOT] IN (инструкцияSQL)3) [NOT] EXISTS (инструкцияSQL),где сравнение — выражение и оператор сравнения, который сравнивает выражение с результатами подчиненного запроса;выражение — выражение, для которого проводится поиск в результирующем наборе записей подчиненного запроса;инструкцияSQL — инструкция SELECT, заключенная круглые скобки.Подчиненный запрос можно использовать вместо выражения в списке полей инструкции SELECT или в предложениях WHERE и HAVING. Инструкция SELECT используется в подчиненном запросе для задания набора конкретных значений, вычисляемых в выражениях предложений WHERE или HAVING.Предикаты ANY или SOME, являющиеся синонимами, используются для отбора записей в главном запросе, которые удовлетворяют сравнению с записями, отобранными в подчиненном запросе. В следующем примере отбираются все студенты, средний балл которых за семестр больше 4.SELECT * FROM СтудентыWHERE Номер_зачетной_книжки = ANY(SELECT Номер_зачетной_книжки FROM Успеваемость WHERE оценка > 4)Предикат ALL используется для отбора в главном запросе только тех записей, которые удовлетворяют сравнению со всеми записями, отобранными в подчиненном запросе. Если в предыдущем примере предикат ANY заменить предикатом ALL, результат запроса будет включать только тех студентов, у которых средний балл больше4. Это условие является значительно более жестким.Предикат IN используется для отбора в главном запросе только тех записей, которые содержат значения, совпадающие с одним из отобранных подчиненным запросом. Следующий пример возвращает сведения обо всех студентах, средний балл которых за семестр был больше 4.SELECT * FROM Студенты WHERE Номер_зачетной_книжки in(SELECT Номер_зачетной_книжки FROM Успеваемость WHERE оценка > 4)Предикат NOT IN используется для отбора в главном запросе только тех записей, которые содержат значения, не совпадающие ни с одним из отобранных подчиненным запросом.Предикат EXISTS (с необязательным зарезервированным словом NOT) используется в логическом выражении для определения того, должен ли подчиненный запрос возвращать какие-либо записи.В подчиненном запросе можно использовать псевдонимы таблиц для ссылки на таблицы, перечисленные в предложении FROM, расположенном вне подчиненного запроса. В следующем примере отбираются фамилии и имена студентов, чья стипендия равна или больше средней стипендии студентов, обучающихся в той же группе. В данном примере таблица СТУДЕНТЫ получает псевдоним С1:
SELECT Фамилия,Имя, Номер_группы, Стипендия FROM СТУДЕНТЫ AS С1WHERE Стипендия >= (SELECT Avg(Стипендия) FROM СТУДЕНТЫWHERE С1.Номер_группы = СТУДЕНТЫ.Номер_группы) Order by Номер_группы;В последнем примере зарезервированное слово AS не является обязательным.Некоторые подчиненные запросы можно использовать в перекрестных запросах как предикаты (в предложении WHERE). Подчиненные запросы, используемые для вывода результатов (в списке SELECT), нельзя использовать в перекрестных запросах.Содержание работы:Задание 1.Всезапросы,создаваемыеврамкахданной практической работы,должныбытьреализованынаязыкеSQLбезпомощипостроителязапросов.ДлясозданияновогоSQL-запросавокнебазыданныхнажмитекнопкуСозданиезапросаврежимеконструктораиневыбираятаблицы для запроса нажмите кнопку Вид напанелиинструментов.1. ИспользуякомандуSELECT,создайтезапроснавыборкузаписейиздвух(илиболее)таблиц,используяправилавнешнегоивнутреннегосоединения,атакжеразличныеусловияотбораисортировки.2. Используя команду UPDATE, создайте запрос на обновлениеданныхвсозданныхранеетаблицах.3. ИспользуякомандуINSERTINTO,создайтезапросынадобавление группы записей и одной записи в существующуютаблицу.4. ИспользуякомандуCREATEINDEX,создайтезапроснасозданиеновогоиндекса,используяразличныеусловияназначенияиндексов(IGNORENULL,DISSALLOWNULL,PRIMARY),а такжетипысортировки.5. Используя команду DROP, создайте запросы на удаление таблицы и индекса, созданных ранее в БД.Сохраните все созданные запросы в базе данных.Задание 2.
Выполнить задание, показать преподавателю и оформить отчёт в тетради.Список рекомендуемой литературы:1. Шустова Л.И. Базы данных: учебник/Л.И. Шустова, О.В. Тараканов – М.:ИНФРА-М, 2018-304с + Доп. Материалы [Электронный ресурс:Режим доступа :http://www.znfnium.com] – (Среднее профессиональное образование)2. Кумская И.А. Базы данных: учебник / И.А. Кумская - Москва: КноРус. 2016. Режим доступа в ЭБС по паролю ISBN 978-5- 406-04521-3 на WWW.BOOK.ruПрактическое занятие (практическая подготовка)№ 13Тема: SQL – запросы на создание таблицы, создание перекрестного запроса.Цель занятия: записать SQL-запросы на создание таблицы, используя создание перекрестного запроса.