Многотабличные запросы. Использование псевдонимов

Пример: пусть необходимо поставить в соответствие преподавателей и учебные предметы, которые он ведет

Предполагается, что созданы следующие таблицы:

STUDENT (SNUM, SFAM, SIMA, STIP) USPEV (UNUM, OCENKA, UDATA, SNUM, PNUM) PREDMET (PNUM, PNAME, TNUM) TEACHER (TNUM, TFAM, TIMA, TDATE)

UDATA – дата оценки

TDATE – день рождения учителя

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

SELECT TEACHER.TFAM, PREDMET.PNAME; FROM TEACHER, PREDMET;

WHERE TEACHER.TNUM=PREDMET.TNUM

Эти таблицы уже были соединены через поле TNUM. Эта связь называется состоянием справочной целостности. Объединение многотабличных запросов, которые используют предикаты, основанные на равенствах – объединение по равенству. Этот же подход может использоваться для объединения вместе двух копий одиночной таблицы. Когда объединяется таблица сама с собой, все повторяемые имена столбца заполняются префиксами имени таблицы. Чтобы ссылаться к этим столбцам внутри запроса, необходимо иметь 2 различных имени для этой таблицы. Это можно сделать с помощью определения временных имен, называемых псевдонимами, которые определяются в предложении FROM запроса.

2. Вывод фамилий студентов, имеющих одинаковый размер стипендии

SELECT FIRST.SFAM, SECOND.SFAM, FIRST.STIP; FROM STUDENT FIRST, STUDENT SECOND; WHERE FIRST.STIP=SECOND.STIP

FIRST, SECOND - псевдонимы

В данном запросе SQL ведет себя так, как если бы он соединял 2 различные таблицы, называемые FIRST и SECOND. Псевдоним существует только тогда, команда выполняется, а после завершения запроса псевдоним больше не имеет никакого значения. Допускается использовать любое число псевдонимов для одной таблицы запросов.

3. Для назначения стипендии на следующий семестр, необходимо просмореть все варианты комбинаций студентов с разными размерами стипендий 25%, 15%, 0%

SELECT FIRST.SFAM, SECOND.SFAM, THIRD.SFAM; FROM STUDENT FIRST, STUDENT SECOND, STUDENT THIRD;

WHERE FIRST.STIP=25 AND SECOND.STIP=15 AND THIRD.STIP=0

Этот запрос находит все комбинации студентов с 3-мя различными размерами стипендии. Такой запрос может быть выполнен с помощью GROUP BY и ORDER BY, поскольку они сравнивают значение только в одном столбце вывода.

В SQL предусматривается создание объединения, которое включает и различные таблицы и псевдонимы одной таблицы.

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

SELECT PREDMET.PNAME, FIRST.SNUM, SECOND.SNUM; FROM USP FIRST, USP SECOND, PREDMET; WHERE FIRST.PNUM=SECOND.PNUM; AND PREDMET.PNUM=FIRST.PNUM; AND FIRST.SNUM<SECOND.SNUM

Таким образом, операция объединения в SQL соединяет информацию из двух таблиц, формируя из них пары связанных строк. Объединяемую таблицу образуют пары тех строк из разных таблиц, у которых в связанных столбцах содержатся одинаковые значения.

Иногда необходимо использовать DISTINCT для того, чтобы в подзапросе получать одиночное значение.

5. Преподаватели могут вести занятия по разным дисциплинам. Написать запрос, выводящий дисциплины, которые ведет преподаватель Иванова.

SELECT * FROM PREDMET;

WHERE TNUM=(SELECT DISTINCT TNUM FROM TEACHER;

WHERE TFAM=’Иванова’)

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

6. Вывести все оценки по учебным дисциплинам, значение которых выше среднего.

SELECT * FROM USP;

WHERE OCENKA > (SELECT AVG(OCENKA) FROM USP);