Извлечение информации из таблиц

Цель и задачи работы

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

 

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

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

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

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

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

 

Оформление отчета

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

 

Теоретические сведения

Запрос - команда, которую вы даете вашей СУБД и которая сообщает ей чтобы она вывела определенную информацию из таблиц в память. Эта информация обычно посылается непосредственно на экран компьютера или терминала, которым вы пользуетесь, хотя, в большинстве случаев, ее можно также послать на принтер, сохранить в файле (как объект в памяти компьютера), или представить, как вводную информацию для другой команды или процесса.

В самой простой форме, команда SELECT просто инструктирует базу данных, чтобы извлечь информацию из таблицы. Например, можно вывести таблицу «Продавцов» напечатав следующее:

SELECTsnum, sname, sity, commFROMSalespeople;

Другими словами, эта команда просто выводит все данные из таблицы. Большинство программ будут также давать заголовки столбца как выше, а некоторые позволяют детальное форматирование вывода, но это уже вне стандартной спецификации. Имеется объяснение каждой части этой команды:

SELECT	Ключевое слово, которое сообщает базе данных что эта команда – запрос. Все запросы начинаются этим словом, сопровождаемым пробелом.
snum sname	Это – список столбцов из таблицы, которые выбираются запросом. Любые столбцы, не перечисленные здесь не будут включены в вывод команды. Это, конечно, не значит что они будут удалены или их информация будет стерта из таблиц, потому что запрос не воздействует на информацию в таблицах; он только показывает данные.
FROM	FROM - ключевое слово, подобно SELECT, которое должно Salespeople быть представлено в каждом запросе. Оно сопровождается пробелом и затем именем таблицы используемой в качестве источника информации. В данном случае – это таблица Продавцов (Salespeople).
;	Точка с запятой используется во всех интерактивных командах SQL чтобы сообщать базе данных что команда заполнена и готова выполниться. В некоторых системах наклонна черта влево (\) в строке, является индикатором конца команды.

Оператор SELECT имеет следующий вид:

SELECT [ ALL | DISTINCT [ ON (выражение [,...]) ] ]

[ * | выражение [ [ AS ] имя_результата ] [,...] ]

[ FROM элемент_FROM [,...] ]

[ WHERE условие ]

[ GROUP BY элемент_группирования [,...] ]

[ HAVING условие [,...] ]

[ ORDERBY выражение ]

Формат запроса с использованием данного оператора:

SELECT список полей FROM список таблиц WHERE условия…

Основные ключевые слова, относящиеся к запросу SELECT:

WHERE — используется для определения, какие строки должны быть выбраны или включены в GROUP BY.

GROUP BY — используется для объединения строк с общими значениями в элементы меньшего набора строк.

HAVING — используется для определения, какие строки после GROUP BY должны быть выбраны.

ORDER BY— используется для определения, какие столбцы используются для сортировки результирующего набора данных.

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

SELECT * FROM Salespeople;       

Условия использующие ключевое слово WHERE.

Оператор	Описание
=	Отбираются значения равные указанному SELECT * FROMtopicsWHEREid_author=4;
>	Отбираются значения больше указанного SELECT * FROM topics WHERE id_author>2;
<	Отбираются значения меньше указанного SELECT * FROM topics WHERE id_author<3;
>=	Отбираются значения большие и равные указанному SELECT * FROM topics WHERE id_author>=2;
<=	Отбираются значения меньшие и равные указанному SELECT * FROM topics WHERE id_author<=3;
!=	Отбираются значения не равные указанному SELECT * FROM topics WHERE id_author!=1;
IS NOT NULL	Отбираются строки, имеющие значения в указанном поле SELECT * FROM topics WHERE id_author IS NOT NULL;
IS NULL	Отбираются строки, не имеющие значения в указанном поле SELECT * FROM topics WHERE id_author IS NULL;
BETWEEN (между)	Отбираются значения, находящиеся между указанными SELECT * FROM topics WHERE id_author BETWEEN 1 AND 3;
IN (значение содержится)	Отбираются значения, соответствующие указанным SELECT * FROM topics WHERE id_author IN (1, 4);
NOT IN (значение не содержится)	Отбираются значения, кроме указанных SELECT * FROMtopics WHERE id_author NOT IN (1, 4);
LIKE (соответствие)	Отбираются значения, соответствующие образцу SELECT * FROM topics WHERE topic_name LIKE 'вел%';
NOT LIKE (не соответствие)	Отбираются значения, не соответствующие образцу SELECT * FROM topics WHERE topic_name NOT LIKE 'вел%';

 

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

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

Задание на работу

1. Напишите запрос, выбирающий все поля и все строки из таблицы, используемой в данной лабораторной. При этом таблица должна содержать 10-15 строк. Приведите содержимое данной таблицы.

2. Напишите запрос, демонстрирующий выбор нескольких (не всех) полей таблицы с удалением дубликатов строк (DISTINCT).

2. Напишите запрос, демонстрирующий выбор всех полей (*) и задание условий выборки в виде операций сравнения (>, <, =) и логических операций (AND, OR, NOT).

3. Напишите запрос, демонстрирующий работу конструкций IN, BETWEEN, IS NULL, IS NOT NULL.

4. Напишите запрос, демонстрирующий работу конструкции LIKE (с символами "%" и "_").

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

Указания к выполнению заданий 1-5.

а) сформулируете смысл запроса на языке, понятном пользователю в данной предметной области, например, "Запрос, выбирающий данные о фамилии, имени и номере курса для студентов, получающих стипендию больше 1400"

б) приведите сам запрос

в) приведите результат выполнения запроса в виде скриншота. Данные (и параметры в условии выборки) должны быть подобраны таким образом, чтобы в результирующей выборке было 3-4 записи.

 

7. Контрольные вопросы

1. Дана таблица Рейс. Вывести в убывающем порядке список рейсов, вылетающих не позднее 1 апреля в Москву, Петербург или Самару, стоимость билета не более 1500 р.; в Саратов - не позднее 7 апреля, стоимость билета - от 500 до 800 р.

2. Дана таблица Город. Вывести в алфавитном порядке список городов Поволжского региона, в коде которых встречается цифра 9, а в названии города на втором месте стоит буква <д> или <ж>.

3. Дана таблица Автор. Вывести в алфавитном порядке фамилии авторов из Самары, в телефонном номере которых на первом или третьем месте стоит цифра от 5 до 8, а последними являются цифры 7 и 8.

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

5. Дана таблица Рейс. Вывести список рейсов, продолжительность маршрутов которых не более 500 км и не менее 100 км, а стоимость билета - от 800 до 1500 руб.

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

Обобщение данных с помощью агрегатных функций

Цели и задачи работы

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

 

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

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

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

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

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

 

Оформление отчета

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

 

Теоретические сведения

Агрегирующие функции позволяют получать из таблицы сводную (агрегированную) информацию, выполняя операции над группой строк таблицы. Для задания в SELECT – запросе агрегирующих операций используются следующие ключевые слова:

avg(выражение)	smallint, int, bigint, real, double precision, numericили interval	арифметическое среднее для всех входных значений
count(*)		количество входных строк
count(выражение)	any	количество входных строк, для которых значение выражения не равно NULL
max(выражение)	любой числовой, строковый, сетевой тип или тип даты/времени, либо массив этих типов	максимальное значение выражения среди всех входных данных
min(выражение)	любой числовой, строковый, сетевой тип или тип даты/времени, либо массив этих типов	минимальное значение выражения среди всех входных данных
sum(выражение)	smallint, int, bigint, real, double precision, numeric, interval или money	сумма значений выражения по всем входным данным

Например, для определения среднего значения поля MARK (оценки) по всем записям таблицы EXAM _ MARKS можно использовать запрос с функцией AVG следующего вида:

SELECT AVG(MARK)
FROM EXAM_MARKS;
Для подсчета общего количества строк в таблице следует использовать

функцию COUNT со звёздочкой. SELECT COUNT (*)

FROM EXAM _ MARKS;

Аргументы DISTINCT и ALL позволяют, соответственно, исключать и включать дубликаты обрабатываемых функцией COUNT значений, при этом необходимо учитывать, что при использовании опции ALL значения NULL все равно не войдут в число подсчитываемых значений.

SELECT COUNT (DISTINCT SUBJID) FROM SUBJECT;

Предложение GROUP BY (ГРУППИРОВАТЬ ПО) позволяет группировать записи в подмножества, определяемые значениями какого-либо поля, и применять агрегирующие функции уже не ко всем записям таблицы, а раздельно к каждой сформированной группе.

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

SELECT STUDENT_ID, MAX (MARK) FROM EXAM_MARKS GROUP BY STUDENT_ID;

Выбираемые из таблицы EXAM _ MARKS записи группируются по значениям поля STUDENT _ ID, указанного в предложении GROUP BY, и для каждой группы находится максимальное значение поля MARK. Предложение GROUP BY группирует строки таблицы, объединяя их в одну группу при совпадении значений во всех перечисленных столбцах. Порядок, в котором указаны столбцы, не имеет значения. В результате наборы строк с одинаковыми значениями преобразуются в отдельные строки, представляющие все строки группы. Это может быть полезно для устранения избыточности выходных данных и/или для вычисления агрегатных функций, применённых к этим группам. В приведенном запросе рассматриваются группы записей, сгруппированные по идентификаторам студентов.

В конструкции GROUP BY для группирования может быть использовано более одного столбца. Например:

SELECT STUDENT_ID,SUB
J_ID, MAX (MARK) FROM EXAM_MARKS GROUPBY STUDENT_ID, SUBJ_ID;

В этом случае строки вначале группируются по значениям первого столбца, а внутри этих групп – в подгруппы по значениям второго столбца. Таким образом, GROUP BY не только устанавливает столбцы, по которым осуществляется группирование, но и указывает порядок разбиения столбцов на группы.

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

Если таблица была сгруппирована с помощью GROUP BY, но интерес представляют только некоторые группы, отфильтровать их можно с помощью предложения HAVING, действующего подобно WHERE.

SELECT SUB J_NAME, MAX(HOUR) FROM SUBJECT GROUP BY SUBJ_NAME HAVING MAX(HOUR)>=72;

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

Влияние NULL – значений в функции count.

Если аргумент функции COUNT является константой или столбцом без пустых значений, то функция возвращает количество строк, к которым применимо определенное условие или группирование.

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

Если бы механизм NULL не был доступен, то неприменимые и отсутствующие значения пришлось бы исключать с помощью конструкции WHERE.

Поведение функции COUNT (*) не зависит от пустых значений. Она возвратит общее количество строк в таблице.

Влияние NULL – значений в функции avg.

Среднее значение множества чисел равно сумме чисел, делённой на число элементов множества. Однако, если некоторые элементы пусты, то есть их значения неизвестны или не существуют, то деление на количество всех элементов множества приведет к неправильному результату.

Функция AVG вычисляет среднее значение всех известных значений множества элементов, то есть эта функция подсчитывает сумму известных значений и делит её на количество этих значений, а не на общее количество значений, среди которых могут быть NULL – значения. Если столбец состоит только из пустых значений, то функция AVG также возвратит NULL.

Более подробная информация: https://www.postgresql.org/docs/9.5/tutorial-sql.html

 

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

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

 

Задания на работу

1. Напишите запрос, выбирающий все поля и все строки из таблицы, используемой в данной лабораторной работе. При этом таблица должна содержать 10-15 строк. Приведите содержимое данной таблицы.

2. Напишите запрос, демонстрирующий возможности функций MAX и MIN.

3. Напишите запрос, демонстрирующий возможности функций AVG и SUM.

4. Напишите запрос, демонстрирующий работу конструкций COUNT, COUNT(*) и COUNT(DISTINCT).

5. Напишите запрос, демонстрирующий работу конструкции GROUP BY

6. Напишите запрос, демонстрирующий работу конструкции HAVING.

7. Напишите запрос, демонстрирующий совместную работу конструкций HAVING и WHERE.

Указания к выполнению заданий 1-7.

а) сформулируете смысл запроса на языке, понятном пользователю в данной предметной области, например, «Запрос, выбирающий среднее значение стипендии по каждому курсу»

б) приведите сам запрос

в) приведите результат выполнения запроса. Данные (и параметры в условии выборки) должны быть подобраны таким образом, чтобы в результирующей выборке было 3-4 записи (или одна).

 

7. Контрольные вопросы

1. Определить количество и общую продолжительность разговоров для каждого региона, с городами которого осуществляли телефонную связь абоненты, чьи фамилии содержат слог <-ва->.

2. На какую сумму были проданы билеты на рейс до Москвы в день вылета?

3. В каком количестве и на какую сумму издавал свои книги автор Борисов в каждом издательстве?

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

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

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

 Рассчитать стоимость каждого телефонного разговора с Москвой

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

 

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

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

Книги каких авторов были проданы на сумму, превышающую 10000 руб.

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

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

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

Определить фамилии абонентов, общее время разговоров которых менее 10 мин, а общая стоимость оказалась больше 100 руб.

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

Запросы на объединение отношений

Цель и задачи работы

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

 

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

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

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

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

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

 

Оформление отчета

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

 

Теоретические сведения

Можно поместить многочисленные запросы вместе и объединить их вывод используя предложение UNION. Предложение UNION объединяет вывод двух или более SQL запросов в единый набор строк и столбцов. Например, чтобы получить всех продавцов и заказчиков размещенных в Лондоне и вывести их как единое целое вы могли бы ввести:

SELECT snum, snameFROM Salespeople WHERE city = 'London' UNION SELECT cnum, cnameFROM Customers WHERE city = 'London';

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

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

=============== SQL Execution Log ============|                                          || SELECT snum, sname                       || FROM Salespeople                         || WHERE city = 'London'                    || UNION                                         || SELECT cnum, cname                       || FROM Customers                           || WHERE city = 'London';                   || ============================================= ||                                          || ----- --------                       || 1001 Peel                           || 1004 Motika                         || 2001 Hoffman                        || 2006 Climens                        ||                                          |=============================================

Когда два (или более) запроса подвергаются объединению, их столбцы вывода должны быть совместимы для объединения. Это означает, что каждый запрос должен указывать одинаковое число столбцов и в том же порядке что и первый, второй, третий, и так далее, и каждый должен иметь тип, совместимый с каждым. Значение совместимости типов - меняется. ANSI следит за этим очень строго и поэтому числовые пол должны иметь одинаковый числовой тип и размер, хотя некоторые имена, используемые ANSI для этих типов, являются - синонимами. Кроме того, символьные поля должны иметь одинаковое число символов.

Хорошо, что некоторые SQL программы обладают большей гибкостью чем это определяется ANSI. Типы, не определенные ANSI, такие как DATA и BINARY, обычно должны совпадать с другими столбцами такого же нестандартного типа. Длина строки также может стать проблемой. Большинство программ разрешают пол переменной длины, но они не обязательно будут использоваться с UNION. С другой стороны, некоторые программы (и ANSI тоже) требуют, чтобы символьные поля были точно равной длины. В этих вопросах вы должны проконсультироваться с документацией вашей собственной программы.

Более подробная информация: https://www.postgresql.org/docs/9.5/sql.html

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

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

 

Задание на работу

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

Запросы на соединение отношений

Цель и задачи работы

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

 

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

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

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

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

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

 

Оформление отчета

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

 

Теоретические сведения

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

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

SELECT STUDENT.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 AS CHAR(1), CAST SUBJ_ID AS CHAR(IO)

FROM STUDENT, EXAM_MARKS

WHERE STUDENT.STUDENT_ID= EXAM_MARKS.STUDENT_ID

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

FROM STUDENT WHERE NOT EXIST

(SELECT * FROM EXAM_MARKS WHERE STUDENT.STUDENT_ID = EXAM_MARKS.STUDENT_ID);

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

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

SELECT SURNAME,MARK FROM 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

WHERE FIRST. NAME = SECOND. NAME

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

SELECT FIRS Т. SURNAME, SECOND. SURNAME

FROM STUDENT FIRST, STUDENT SECOND

WHERE FIRST.NAME= SECOND.NAME

AND FIRST.SURNAME< SECOND.SURNAME.

Более подробная информация: https://www.postgresql.org/docs/9.5/sql.html

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

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

 

Задание на работу

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

Подзапросы

Цель и задачи работы

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

 

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