Лабораторная работа №7 Простые подзапросы

Цель работы: Научиться создавать простые подзапросы.

Ключевые слова: простые подзапросы.

Теоретический материал:

Подзапрос – это запрос на выборку данных, вложенный в другой запрос.

SELECT <поля> FROM <таблица> WHERE (HAVING) УСЛОВИЕ (SELECT <поля> FROM <таблица> WHERE <условие>)

В свою очередь подзапрос может содержать другой подзапрос, но в первую очередь выполняется подзапрос, имеющий самый глубокий уровень вложения.

Часто, но не всегда, внешний запрос обращается к одной таблице, а подзапрос - к другой.

Простые подзапросы характеризуются тем, что они формально никак не связаны с содержащими их внешними запросами, что позволяет сначала выполнить подзапрос, результат которого используется для выполнения внешнего запроса.

Три вида простых подзапросов:

• подзапросы, возвращающие единственное значение;

• подзапросы, возвращающие список значений, из одного столбца таблицы;

• подзапросы, возвращающие набор записей.

Подзапросы, возвращающие единственное значение

1. Вывести оценки, которые больше среднего значения всех оценок

SELECT * FROM USPEV WHERE OCENKA > (SELECT AVG(OCENKA) FROM USPEV)

Подзапросы, возвращающие список значений, из одного столбца таблицы

2. Определить коды родителей студента Маркова и Иванова.

SELECT KOD_RODITEL FROM RODDETI WHERE KOD_STUDENT IN (SELECT KOD_STUDENT FROM DANNIE WHERE FAM='МАРКОВ' OR FAM='ИВАНОВ’)

3. Определить название дисциплин, которые сдавал студент с кодом 2.

SELECT NAZVANIE FROM DISCHIPLINA WHERE KOD_DISCHIPLINA IN (SELECT KOD_DISCHIPLINA FROM USPEV WHERE KOD_STUDENT='2')

4. Вывести фамилию, и место работы родителей студента с кодом 3.

SELECT FIO_ROD, RABOTA FROM RODITELI WHERE KOD_RODITEL IN (SELECT KOD_RODITEL FROM RODDETI WHERE KOD_STUDENT=3)

Порядок выполнения работы:

1. Вывести список оценок, которые получил студент Воркин.

2. Вывести все города Краснодарского края.

3. Вывести название группы студента Маркова.

4. Вывести название улицы, на которой живет студент Варечкин.

5. Определить фамилию преподавателя, поставившему студенту Климову оценку по программированию.

6. Определить специальность, на которой обучается Климова.

7. Какие оценки были получены по дисциплине Базы данных.

8. Найти дисциплины, по которым оценки ставил преподаватель Плюшкин.

9. Определить, где работают родители Смелова. Вывести полный адрес студента Петрова (регион, город, улица, дом и квартира).

Лабораторная работа №8 Связанные подзапросы

Цель работы: научится использовать связанные подзапросы.

Ключевые слова: связанные (соотнесенные / коррелированные) подзапросы.

Теоретический материал:

1. Вывести название дисциплин, по которым получена оценка 5.

SELECT NAZVANIE FROM DISCHIPLINA WHERE 5 IN (SELECT OCENKA FROM USPEV WHERE KOD_DISCHIPLINA= DISCHIPLINA.KOD_DISCHIPLINA)

Такой подзапрос отличается от простого подзапроса тем, что вложенный подзапрос не может быть обработан прежде, чем будет обрабатываться внешний подзапрос. Это связано с тем, что вложенный подзапрос зависит от значения DISCHIPLINA.KOD_DISCHIPLINA, а оно изменяется по мере того, как система проверяет различные строки таблицы DISCHIPLINA. Следовательно, с концептуальной точки зрения обработка осуществляется следующим образом:

1. Система проверяет первую строку таблицы DISCHIPLINA. Предположим, что это строка дисциплины с номером 1. Тогда значение DISCHIPLINA.KOD_DISCHIPLINA будет в данный момент имеет значение, равное 1, и система обрабатывает внутренний запрос:

(SELECT OCENKA FROM USPEV WHERE KOD_DISCHIPLINA= 1)

получая в результате множество (1, 4, 3, 5, 2, 4, 3, 3, 5). Теперь система может завершить обработку для дисциплины с номером 1. Выборка значения NAZVANIE для KOD_DISCHIPLINA= 1 (База данных) будет проведена тогда и только тогда, когда OCENKA=5 будет принадлежать этому множеству, что, очевидно, справедливо.

2. Далее система будет повторять обработку такого рода для следующей дисциплины и т.д. до тех пор, пока не будут рассмотрены все строки таблицы DISCHIPLINA.

Подобные подзапросы называются связанными / соотнесенными / коррелированными, так как их результат зависит от значений, определенных во внешнем подзапросе. Обработка связанного подзапроса, следовательно, должна повторяться для каждого значения извлекаемого из внешнего подзапроса, а не выполняться раз и навсегда.

Порядок выполнения работы:

1. Вывести фамилии преподавателей, которые поставили хотя бы одну двойку.

2. Вывести название предметов, средняя оценка по которым выше 3.

3. Вывести фамилии студентов, у которых имеются оценки 3 и 4 (одновременно).

4. Вывести фамилии студентов, которые получили хотя бы одну оценку, выше средней.

5. Вывести названия групп, в которых обучается 6 студентов.

Лабораторная работа №9

Операции соединения

Цель работы: Научиться соединять таблицы различными способами.

Ключевые слова: естественное соединение, условное соединение.

Теоретический материал:

Внутренние соединения таблиц

Естественное соединение (NATURAL JOIN)

Декартовым произведением двух таблиц называется таблица, составленная из всевозможных сочетаний записей обеих таблиц.

1. Вывести декартово произведение таблиц DANNIE и USPEV.

SELECT * FROM DANNIE, USPEV

Общим столбцом этих таблиц является столбец kod_student.

В полученном декартовом произведении интересуют не все данные, а только те, в которых идентичные столбцы имеют одинаковые значения. Кроме того, в результативной таблице не нужны два идентичных столбца, достаточно лишь одного из них.

SELECT DANNIE.*, USPEV.OCENKA FROM DANNIE, USPEV WHERE DANNIE.KOD_STUDENT=USPEV.KOD_STUDENT

В результате выполнения этого запроса получается таблица естественным соединением.

Данный запрос можно записать, используя псевдонимы.

SELECT T1.*, T2.OCENKA FROM DANNIE T1, USPEV T2 WHERE T1.KOD_STUDENT=T2.KOD_STUDENT

Эквивалентный запрос можно составить с помощью оператора NATURAL JOIN

SELECT T1.*, T2.OCENKA FROM DANNIE T1 NATURAL JOIN USPEV T2.

При естественном соединении, выполняемом с помощью NATURAL JOIN, проверяется равенство всех одноимённых столбцов соединяемых таблиц.