Лабораторные_БД_ЭВМ_20 (AutoRecovered). Методические указания по выполнению лабораторных работ по дисциплине (модулю)

• Название: Методические указания по выполнению лабораторных работ по дисциплине (модулю)
• Дата: 05.05.2022
• Размер: 0.75 Mb.
• Имя файла: Лабораторные_БД_ЭВМ_20 (AutoRecovered).docx
• Тип: Методические указания; #512616
• страница: 4 из 12

5. Оборудование

персональный компьютер с установленной операционной системой Windows XP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБД PostgreSQL.
6. Задание на работу

1. Напишите запрос, демонстрирующий объединение (UNION) результатов двух запросов. Количество выбираемых столбцов и типы данных соответствующих столбцов обоих запросов должны совпадать.

2. Напишите запрос, демонстрирующий объединение (UNION) результатов трех запросов. Тип данных одного столбца у всех трех запросов должны быть разными (но приводимыми, например, целые числа, действительныечисла и строки). Эти типы данных должны быть указаны в дополнительном столбце результирующей выборки.

3. Дополните запрос из п.2 сортировкой по двум столбцам (сначала по возрастанию одного, затем по убыванию другого).
7. Контрольные вопросы

1. 
   Вывести список авторов-женщин, работающих в жанре романа:
   

   CREATE TABLE Автор (Код_Автора INT , Фамилия VARCHAR(50), Пол VARCHAR(50) NOT NULL )

   CREATE TABLE Книга ( Код_Книги INT, Название VARCHAR(50) NOT NULL, Тематика VARCHAR(50) NOT NULL, Издательство VARCHAR(50) NOT NULL, Код_Автора INT NOT NULL)

2. 
   Даны таблицы Город и Разговор:
   

CREATE TABLE Город ( Код_Города INT , Название VARCHAR(20) NOT NULL, Тариф MONEY)

CREATE TABLE Разговор (Код_Разговора INT , Код_ГородаINTNOTNULL, Фамилия VARCHAR(20), Дата DATETIME NOT NULL, Продолжительность INT NOT NULL)

Вывести список абонентов, которые говорили с Москвой в апреле.

3. 
   Даны таблицы Рейс и Билет:
   

CREATE TABLE Рейс (Номер_рейса INT, Конечный_пункт VARCHAR(30), Дата_вылета DATETIME)

CREATE TABLE БИЛЕТ (Номер_места CHAR(3), Номер_рейса CHAR(6), Дата_продажи DATETIME, Фамилия_пассажира VARCHAR(30))

Определить номера мест и дату продажи билетов на рейсы до Москвы с датой вылета 1 мая 2004 года

4. 
   Даны таблицы Автор и Книга:
   

CREATE TABLE Автор (Код_Автора INT , Фамилия VARCHAR(50), Пол VARCHAR(50) NOT NULL )

CREATE TABLE Книга ( Код_Книги INT, Название VARCHAR(50) NOT NULL, Тематика VARCHAR(50) NOT NULL, Издательство VARCHAR(50) NOT NULL, Код_Автора INT NOT NULL)

Вывести список авторов, работающих в жанре детектив.

5. 
   Даны таблицы:
   

CREATE TABLE Блюдо (Название_блюда VARCHAR(20) NOT NULL, Время_приготовления INT NOT NULL, Общая_калорийность INT NOT NULL, Номер_рецепта INT, Повар VARCHAR(20), Стоимость INT )

CREATE TABLE Компонент (Название_компонента VARCHAR(20), КалорийностьINTNOTNULL, ЖирыINT, БелкиINT, БлюдоVARCHAR(20), Углеводы INT, Стоимость_100_грамм FLOAT NOT NULL)

Сформировать список поваров, которые используют масло.
Лабораторная работа №7

Запросы на соединение отношений
1. Цель и задачи работы

Целью лабораторной работы является изучение и практическое применение запросов на соединение отношений.
2. Порядок выполнения работы

- ознакомится с теоретическими сведениями;

- выполнить задание;

- оформить отчет;

- ответить на контрольные вопросы, заданные преподавателем.
3. Оформление отчета

Отчет должен содержать: титульный лист, цель работы, описание пунктов выполнения лабораторной работы в соответствии с заданием, ответы на контрольные вопросы и выводы по работе.
4. Теоретические сведения

Если в операторе SELECT после ключевого слова FROM указывается не одна, а две таблицы, то в результате выполнения запроса, в котором отсутствует предложение WHERE, каждая строка одной таблицы будет соединена с каждой строкой второй таблицы. Такая операция называется декартовым произведением или полным (CROSS) соединением таблиц базы данных. Сама по себе эта операция не имеет практического значения, более того, при ошибочном использовании она может привести к неожиданным нештатным ситуациям, так как в этом случае в ответе на запрос количество записей будет равно произведению числа записей в соединяемых таблицах, то есть может оказаться чрезвычайно большим. Соединение таблиц имеет смысл тогда, когда соединяются не все строки исходных таблиц, а только те, которые интересуют пользователя. Такое ограничение может быть осуществлено с помощью использования в запросе соответствующего условия в предложении WHERE. Таким образом, SQL позволяет выводить информацию из нескольких таблиц, связывая их по значениям определенных полей.

Например, если необходимо получить фамилии студентов (таблица STUDENT) и для каждого студента - названия университетов (таблица UNIVERSITY), расположенных в городе, где живет студент, то необходимо получить все комбинации записей о студентах и университетах в обеих таблицах, в которых значение поля CITY совпадает. Это можно сделать с помощью следующего запроса.

SELECTSTUDENT.SURNAME, UNIVERSITY.UNIV_NAME, STUDENT.CITY

FROM STUDENT, UNIVERSITY

WHERE STUDENT.CITY= UNIVERSITY.CITY;

Возможные типы соединений с сопоставлениями строк:

INNER JOIN

Для каждой строки R1 из Tаблицы 1 в результирующей таблице содержится строка для каждой строки в Tаблицы 2, удовлетворяющей условию соединения с R1.

LEFT OUTER JOIN

Сначала выполняется внутреннее соединение (INNER JOIN). Затем в результат добавляются все строки из Tаблицы 1, которым не соответствуют никакие строки в Таблице2, а вместо значений столбцов Tаблицы 2 вставляются NULL. Таким образом, в результирующей таблице всегда будет минимум одна строка для каждой строки из Tаблицы 1.

RIGHT OUTER JOIN

Сначала выполняется внутреннее соединение (INNER JOIN). Затем в результат добавляются все строки из Tаблицы 2, которым не соответствуют никакие строки в Таблице1, а вместо значений столбцов Tаблицы 1 вставляются NULL. Это соединение является обратным к левому (LEFT JOIN): в результирующей таблице всегда будет минимум одна строка для каждой строки из Tаблицы 2.

FULL OUTER JOIN

Сначала выполняется внутреннее соединение. Затем в результат добавляются все строки из T1, которым не соответствуют никакие строки в T2, а вместо значений столбцов T2 вставляются NULL. И наконец, в результат включаются все строки из T2, которым не соответствуют никакие строки в T1, а вместо значений столбцов T1 вставляются NULL.

Соединение, использующее предикаты, основанные на равенствах, называется эквисоединением. Рассмотренный пример соединения таблиц относятся к виду так называемого внутреннего (INNER) соединения. При таком типе соединения соединяются только те строки таблиц, для которых является истинным предикат, задаваемый в предложении ON выполняемого запроса.

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

SELECT STUDENT.SURNAME, UNIVERS ITY.UNIV_NAME, STUDENT.CITY

FROM STUDENT INNERJOIN UNIVERSITY

ON STUDENT.CITY= UNIVERSITY.CITY;

Ключевое слово INNER в запросе может быть опущено, так как эта опция в операторе JOIN действует по умолчанию.

Рассмотренный выше случай полного соединения (декартова произведения таблиц) с использованием ключевого слова JOIN будет выглядеть следующим образом

SELECT * FROM STUDENT JOIN UNIVERSITY;

что эквивалентно

SELECT * FROM STUDENT, UNIVERSITY;

Информация в таблицах STUDENT и EXAM _ MARKS уже связана посредством поля STUDENT _ ID. В таблице STUDENT поле STUDENT _ ID является первичным ключом, а в таблице EXAM _ MARKS, ссылающимся на него внешним ключом. Состояние связанных таким образом таблиц называется состоянием ссылочной целостности. В данном случае ссылочная целостность этих таблиц подразумевает, что каждому значению поля STUDENT _ ID в таблице EXAM _ MARKS обязательно соответствует такое же значение поля STUDENT _ ID в таблице STUDENT. Другими словами, в таблице EXAM _ MARKS не может быть записей, имеющих идентификаторы студентов, которых нет в таблице STUDENT. Стандартное применение операции соединения состоит в извлечении данных в терминах этой связи.

Чтобы получить список фамилий студентов с полученными ими оценками и идентификаторами предметов можно использовать следующий запрос:

SELECT SURNAME, MARK,SUBJ_ID

FROM STUDENT, EXAM_MARKS

WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID;

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

SELECT SURNAME,MARK

FROM STUDENT JOIN EXAM_MARKS

ON STUDENT. STUDENT_ID = EXAM_MARKS.STUDENT_ID;

Хотя выше речь шла о соединении двух таблиц, можно сформировать запросы путем соединения более чем двух таблиц.

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

SELECT SUB J_NAME, SURNAME, MARK

FROM STUDENT, SUBJECT, EXAM_MARKS

WHERE STUDENT.STUDENT_ID= EXAM_MARKS.STUDENT_ID

AND SUBJECT. SUB J ID = EXAM MARKS. SUB J ID

AND EXAM _ MARKS . MARK = 2;

To же самое с использованием опера- тора JOIN

SELECT SUBJ_NAME, SURNAME, MARK

FROM STUDENT JOIN SUBJECT

JOIN EXAM_MARKS ON STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID

AND SUB JECT.SUB J_ID = EXAM_MARKS.SUB J_ID AND EXAM_MARKS.MARK= 2;

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

SELECT SURNAME,CAST MARK ASCHAR(1), CAST SUBJ_ID AS CHAR(IO)

FROMSTUDENT, EXAM_MARKS

WHERE STUDENT.STUDENT_ID= EXAM_MARKS.STUDENT_ID

UNIONSELECT SURNAME,CASTNULLASCHAR(l), CASTNULLAS CHAR(IO)

FROM STUDENT WHERE NOT EXIST

(SELECT * FROMEXAM_MARKS WHERESTUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID) ;

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

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

SELECT SURNAME,MARKFROM STUDENT LEFTOUTER

JOIN EXAM_MARKS ON STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID;

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

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

SELECT SURNAME,MARK

FROM EXAM_MARKS

RIGHT OUTER JOIN STUDENT ON EXAM_MARKS.STUDENT_ID = STUDENT.STUDENT_ID;

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

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

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

Часто при получении информации из таблиц базы данных необходимо осуществлять соединение таблицы с ее же копией. Например, это требуется в случае, когда требуется найти фамилии студентов, имеющих одинаковые имена. При соединении таблицы с ее же копией вводят псевдонимы (алиасы) таблицы. Запрос для поиска фамилий студентов, имеющих одинаковые имена, выглядит следующим образом

SELECT FIRS Т . SURNAME, SECOND. SURNAME

FROM STUDENT FIRST, STUDENT SECOND

WHEREFIRST . NAME = SECOND . NAME

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

SELECT FIRS Т . SURNAME, SECOND. SURNAME

FROM STUDENT FIRST, STUDENT SECOND

WHERE FIRST.NAME= SECOND.NAME

AND FIRST.SURNAME< SECOND.SURNAME.
5. Оборудование

Оборудование: персональный компьютер с установленной операционной системой WindowsXP/7/8, браузер (Например, InternetExplorer, GoogleChrome, Opera), СУБДPostgreSQL.
6. Задание на работу

1. Напишите запрос, демонстрирующий соединение двух таблиц с помощью конструкции SELECT ... FROM TABLE1, TABLE2 WHERE... . Перепишите тот же запрос с помощью конструкции JOIN. Убедитесь, что результаты выполнения запросов одинаковы.

2. Напишите запрос, демонстрирующий смысл и назначение конструкции LEFT JOIN. Перепишите его с помощью конструкции RIGHT JOIN. Убедитесь, что результаты выполнения запросов одинаковы.

3. Напишите запрос, в котором таблица соединяется (JOIN) сама с собой.

4. Напишите запрос, в котором агрегация происходит по результату соединения таблиц. То есть, в запросе должны присутствовать агрегирующая функция (SUM, AVG, MAX, MIN или COUNT), GRUOP BY и HAVING, WHERE и JOIN (внутренний или внешний). Будьте внимательны к этому заданию, оно высоко оценивается.
7. Контрольные вопросы

1. 
   Найти абонентов, которые звонят в Москву, но ни разу не звонили в Самару в мае:
   

   CREATE TABLE Город ( Код_Города INT , Название VARCHAR(20) NOT NULL, Тариф MONEY)

   CREATE TABLE Разговор (Код_Разговора INT , Код_Города INT NOT NULL, Фамилия VARCHAR(20), Дата DATETIME NOT NULL, Продолжительность INT NOT NULL)

2. 
   Вывести список авторов-женщин, работающих в жанре романа, но не в жанре фантастики:
   

   CREATE TABLE Автор (Код_Автора INT , Фамилия VARCHAR(50), Пол VARCHAR(50) NOT NULL )

   CREATE TABLE Книга ( Код_Книги INT, Название VARCHAR(50) NOT NULL, Тематика VARCHAR(50) NOT NULL, Издательство VARCHAR(50) NOT NULL, Код_Автора INT NOT NULL)

3. 
   Даны таблицы Город и Разговор:
   

CREATE TABLE Город ( Код_Города INT , Название VARCHAR(20) NOT NULL, Тариф MONEY)

CREATE TABLE Разговор (Код_Разговора INT , Код_ГородаINTNOTNULL, Фамилия VARCHAR(20), Дата DATETIME NOT NULL, Продолжительность INT NOT NULL)

Вывести список абонентов, которые говорили с Москвой в апреле, но не с Тулой.

4. 
   Даны таблицы Рейс и Билет:
   

CREATE TABLE Рейс (Номер_рейса INT, Конечный_пункт VARCHAR(30), Дата_вылета DATETIME)

CREATE TABLE БИЛЕТ (Номер_места CHAR(3), Номер_рейса CHAR(6), Дата_продажи DATETIME, Фамилия_пассажира VARCHAR(30))

Определить номера мест и дату продажи билетов на рейсы до Москвы с датой вылета не позднее 1 мая 2004 года.

6. 
   Даны таблицы:
   

CREATE TABLE Блюдо (Название_блюда VARCHAR(20) NOT NULL, Время_приготовления INT NOT NULL, Общая_калорийность INT NOT NULL, Номер_рецепта INT, Повар VARCHAR(20), Стоимость INT )

CREATE TABLE Компонент (Название_компонента VARCHAR(20), КалорийностьINTNOTNULL, ЖирыINT, БелкиINT, БлюдоVARCHAR(20), Углеводы INT, Стоимость_100_грамм FLOAT NOT NULL)

Сформировать список поваров, которые используют масло, но не молоко.

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