Основные понятия и способы применения SQL - в примерах и задачах

SQL язык запросов к реляционным базам данных Учебник

• Основные понятия и определения
• Выборка данных
• Манипулирование данными
• Создание объектов базы данных

Внешнее соединение таблиц

Как отмечалось ранее, при использовании внутреннего (INNER) соединения таблиц соединяются только те их строки, в которых совпадают значения полей, задаваемые в запросе предложением WHERE. Однако во многих случаях это может при­вести к нежелательной потере информации. Рассмотрим еще раз приведенный выше пример запроса на выборку списка фамилий студентов с полученными ими оценками и идентификаторами предметов. При использовании, как это было сделано в рассмат­риваемом примере, внутреннего соединения в результат запроса не попадут студенты, которые еще не сдавали экзамены, и кото­рые, следовательно, отсутствуют в таблице EXAMJMARKS. Если же необходимо иметь записи об этих студентах в выдаваемом запро­сом списке, то можно присоединить сведения о студентах, не сдававших экзамен, путем использования оператора UNION с со­ответствующим запросом.  Например, следующим образом:

SELECT SURNAME, CAST MARK AS CHARdV CAST SUBJ_ID AS CHAR (10) FROM STUDENT, EXAM_MARKS

WHERE STUDENT.STUDENT ID = EXAM MARKS.STUDENT ID UNION

SELECT SURNAME, CAST NULL AS CHAR(1), CAST NULL AS CHAR(10) FROM STUDENT WHERE NOT EXIST (SELECT * FROM EXAM_MARKS WHERE  STUDENT.STUDENT ID = EXAM_MARKS.STUDENT_ID);

(здесь функция преобразования типов CAST используется для обес­печения совместимости типов полей объединяемых запросов).

Нужный результат может быть получен и путем использова­ния внешнего соединения, точнее, одной из его разновидностей — левого внешнего соединения, с применением которого запрос бу­дет выглядеть следующим образом:

SELECT SURNAME, MARK

FROM STUDENT LEFT OUTER JOIN EXAM_MARKS

ON STUDENT.STUDENT ID = EXAM MARKS.STUDENT ID;

При использовании левого соединения расширение выводи­мой таблицы осуществляется за счет записей входной таблицы, имя которой указано слева от оператора JOIN.

Следует заметить, что нотация запросов с внешним соеди­нением в СУБД Oracle отличается от приведенной нотации, за­даваемой стандартом языка SQL. В нотации, используемой в Oracle, этот же запрос будет иметь вид:

SELECT SURNAME, MARK, SUBJ_ID FROM STUDENT,EXAM_MARKS WHERE STUDENT.STUDENT ID = EXAM_MARKS.STUDENT_ID(+);

Знак (+) ставится у той таблицы, которая дополняется за­писями с NULL-значениями, чтобы при соединении таблиц в выходное отношение попали и те записи другой таблицы, для которых в таблице со знаком (+) не находится строк с соответ­ствующими значениями атрибутов, используемых для соеди­нения. То есть для левого внешнего соединения (по нотации стандарта SQL) в запросе Oracle-SQL указатель (+) ставится у правой таблицы.

Приведенный выше запрос может быть реализован и с при­менением правого внешнего соединения. Он будет иметь следую­щий вид:

SELECT SURNAME, MARK

FROM EXAM_MARKS RIGHT OUTER JOIN STUDENT

ON EXAM_MARKS.STUDENT_ID = STUDENT.STUDENT_ID;

Здесь таблица STUDENT, за счет записей которой осущес­твляется расширение выводимой таблицы, указана справа от оператора JOIN.

В нотации Oracle этот запрос будет выглядеть следующим образом:

SELECT SURNAME, MARK, SUBJ ID

FROM STUDENT,EXAM_MARKS

WHERE EXAM_MARKS.STUDENT_ID(+) = STUDENT.STUDENT_ID;

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

Иногда возникает необходимость включения в результат за­проса записей из обеих (правой и левой) соединяемых таблиц, для которых не удовлетворяется условие соединения. Такое со­единение называется полным внешним соединением и осуществля­ется указанием в запросе ключевых слов FULL OUTER JOIN или UNION JOIN.

Упражнения

1. Напишите запрос, который выполняет вывод данных о фамилиях сдававших экзамены студентов (вместе с идентификаторами каж­ дого сданного ими предмета обучения).

2. Напишите запрос, который выполняет выборку значений фами­ лии всех студентов с указанием для студентов, сдававших экзаме­ ны, идентификаторов сданных ими предметов обучения.

1 3. Напишите запрос, который выполняет вывод данных о фамилиях студентов, сдававших экзамены, вместе с наименованиями каждого сданного ими предмета обучения.

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

5. Напишите запрос на выдачу данных о названиях всех предметов, по которым студенты получили только хорошие (4 и 5) оценки. В выходных данных должны быть приведены фамилии студентов, названия предметов и оценка.

Напишите запрос, который выполняет вывод списка университе­ тов с рейтингом, превышающим 300, вместе со значением макси­ мального размера стипендии, получаемой студентами в этих уни­ верситетах.

Напишите запрос на выдачу списка фамилий студентов (в алфа­ витном порядке) вместе со значением рейтинга университета, где каждый из них учится, включив в список и тех студентов, для кото­ рых в базе данных не указано место их учебы.