Лабораторная работа № 15. Многотабличные запросы

Для следующих упражнений понадобятся следующие таблицы из базы данных «Борей»: «Заказы», «Заказано», «Сотрудники», «Клиенты» и «Товары». Импортируйте в Вашу базу данных недостающие таблицы.

 

1.Создайте с помощью оператора CREATE TABLE таблицы «писатель» и «книга».и заполните их так, как показано ниже.

 

Табл. писатель		Табл. книга
КодП	ФИО		КодКн	Наим	Автор
	Толстой Л.Н.			В и М
	Есенин			А.К.
	Пушкин			Воскр
	Тургенев			Стихи
	Горький			Ев. О.
				Р и Л
				Обломов
				ПётрI
				На дне

 

В колонке «Автор» таблицы «книга» указаны коды писателей из таблицы

«писатель». Несколько клеток в последних строках обеих таблиц специально оставлены пустыми. В строке 7 таблицы «книги» указан код 12, отсутствующий в таблице «писатель».

Таблицы, состоящие из двух столбцов, в одном из которых хранится наименование объекта, а в другом – номер или код, называется справочником. Код из справочника используется в других таблицах вместо имени объекта. Замена наименование кодом уменьшает вероятность ошибки при вводе данных и позволяет при изменении наименования, например фамилии, внести изменение только в одно поле справочника.

2. Запрос на выборку наименований книг и их авторов выглядит так:

SELECT a.Наим,b.ФИО
FROM книга a,писатель b
WHERE a.Автор=b.КодП

Введите и выполните этот запрос. Обратите внимание на то, что данные из строк с пустыми полями в результаты запроса не входят.

В многотабличном запросе можно использовать любые условия для отбора данных. Если не использовать никаких условий, то будет выведено декартово произведение из всех строк, таблиц, используемых в запросе, то есть 4*9=36 строк. Удалите из последнего запроса предложение WHERE и выполните получившийся запрос.

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

SELECT a.Наим,b.ФИО
FROM книга a INNER JOIN писатель b ON a.Автор=b.КодП

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

книгa.Автор=писатель.КодП.

 

Чтобы в выборку включить названия книг из записей, не удовлетворяющих условию запроса, применяется «левое внешнее соединение» (LEFT JOIN):

SELECT a.Наим,b.ФИО
FROM книга a LEFT JOIN писатель b ON a.Автор=b.КодП.

Для книг «Обломов», «Пётр I» и «На дне» поле ФИО в результатах этого запроса останется пустым

Чтобы в выборку включить ФИО писателей из записей, не удовлетворяющих условию запроса, применяется, применяется «правое внешнее соединение» (RIGHT JOIN):

SELECT a.Наим,b.ФИО
FROM книга a RIGHT JOIN писатель b ON a.Автор=b.КодП

Введите и выполните последние 3 запрос. Сравните результаты выборок между собой и с первым запросом данной лаб. работы.

Самостоятельно составьте и выполните запрос на выборку всех имеющихся в базе названий произведений Толстого Л.Н. и Пушкина вместе с ФИО авторов.

 

3. Cоставьте и выполните следующие запросы:

· используя таблицы «заказы» и «клиенты», выбрать названия клиентов, их представителей (поле «ОбращатьсяК») и даты размещения их заказов при условии, что клиенты из Лондона.

· используя таблицы «заказы» «заказано», «товары» и «клиенты», выбрать названия клиентов, их представителей (поле «ОбращатьсяК») и коды и марки выбранных ими товаров при условии, что названия клиентов начинаются на “F” и заказы оформлял сотрудник Кротов.

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

Пример. Допустим, в таблице «Сотрудники» имеются поля «ФИО», «должность» и «отдел». Требуется найти всех программистов и их начальников. Известно, что программисты работают почти во всех отделах. Запрос выглядит так:

SELECT а.ФИО AS программист,b.ФИО AS начальник_отдела
FROM Сотрудники а,Сотрудники b
WHERE a.должность=’программист’ AND b.должность=’нач_отдела’
AND a.отдел=b.отдел

Результаты будут выглядеть примерно так:

Программист	Начальник_отдела
Андреев А.Б.	Петров К.Ю.
Борисова Г.П.	Петров К.Ю.
Смирнов П.С.	Новикова А.Г.

 

Самостоятельно составьте и выполните следующий запрос: из таблицы «Клиенты» всех продавцов и представителей из тех городов, в которых есть и продавцы и представители.

 

Лабораторная работа № 16. Предикат NULL.
Подзапросы. Предикаты EXISTS, ANY, ALL

Для выяснения смысла значения NULL рассмотрим пример. Пусть в городе N ведётся база данных, в которой хранятся данные обо всех жителях, включая детей. Очевидно, что в графу «профессия» записи о ребёнке поместить нечего, так как у ребёнка ещё нет профессии. Графа профессия может оказаться пустой и в том случае, когда в момент занесения данных профессия жителя не была известна. Предполагается, что графа будет заполнена позже. Для неизвестного значения в SQL применяется специальное обозначение NULL. Значение NULL имеют по умолчанию все поля, в которые ничего не заносилось.

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

<выражение> IS [NOT]NULL

Предикат IS NULL принимаетзначение «истина» только, если выражение равно NULL.

1.Для работы с NULL-значениями полей создайте в базе данных таблицу NullPusto, состоящую из двух текстовых полей длиной по 30 символов. Назовите поля «ФИО» и «адр». Запишите в таблицу следующие данные:

Поле	Значение в поле «адр»
ФИО	адр
А	К	“К”
Б		“” (две двойные кавычки)
В		NULL
Г	М	“М”
Д		NULL
Е		“” (две двойные кавычки)
Ж		NULL

 

2.Создайте и выполните следующие запросы к таблице NullPusto:

a) выбрать все записи с NULL;

b) выбрать все записи с “”;

c) выбрать все записи, в которых есть адреса;

d) выбрать все записи, в которых нет адресов;

e) подсчитать количество записей, содержащих NULL;

f) подсчитать количество записей, содержащих NULL и “”.

Сохраните запросы и покажите их преподавателю.

 

2 .Подзапросы. С помощью SQL можно вкладывать один запросы внутрь другого. Внутренний запрос называют подзапросом. Обычно, внутренний запрос генерирует значение, которое проверяется в предикате внешнего запроса, определяющего верно оно или нет. Например, в следующем запросе выбираются из таблицы «Товары» те товары, цена которых меньше средней цены всех товаров таблицы:

SELECT *
FROM Товары
WHERE Цена<(SELECT AVG(Цена) FROM Товары);

Самостоятельно с помощью подзапроса выберите из таблицы «Заказано» заказы на товары с маркой «Pavlova». Марки товаров хранятся в таблице «Товары».

3.Предикат EXISTS имеет синтаксис

EXISTS подзапрос

и принимает значение ИСТИНА (TRUE), если подзапрос содержит хотя бы одну строку.

В следующем запросе выбираются фамилии всех сотрудников, оформлявших заказы для клиента ANTON, при условии, что хотя бы один заказ для клиента ANTON был размещён в мае любого года.

SELECT DISTINCT b.Фамилия
FROM Заказы a, Сотрудники b
WHERE EXISTS (SELECT * FROM Заказы WHERE КодКлиента='ANTON' AND DatePart('m',ДатаРазмещения)=5)
AND a.КодСотрудника = b.КодСотрудника AND a.КодКлиента='ANTON';

Самостоятельно, используя таблицы «Сотрудники», «Клиенты» и «Заказы», создайте и выполните запрос на выборку всех клиентов из Рио-Де-Жанейро, если был сделан хотя бы один заказ из Рио-Де-Жанейро, оформленый сотрудником Кротовым.

4.Предикаты количественного сравнения ANY, SOME и ALL имеют синтаксис

оператор сравнения {ANY | SOME | ALL} подзапрос.

ANY и SOME – синонимы.

Пример использования предиката ANY:

SELECT КодЗаказа
FROM Заказы
WHERE СтоимостьДоставки < ANY(SELECT СтоимостьДоставки FROM Заказы WHERE ГородПолучателя ='Ванкувер');

Для исследования особенностей предиката ANY проделайте следующее упражнение:

· выберите из таблицы «Товары» цены товаров от поставщика с кодом 2; запишите эти цены;

· используя ANY, выберите все товары, цены которых больше цен поставщика 2; сравните выбранные цены с записанными

· повторите предыдущий пункт, иcпользуя вместо ANY предикат ALL; сравните результаты

Сохраните все выполненные запросы и покажите их преподавателю

 

 

Лабораторная работа № 17. Объединение результатов нескольких запросов – UNION. Создание таблицы из существующих таблиц – SELECT … INTO

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

SELECT ALL Адрес,Город,'Заказы ' AS Источник
FROM Клиенты
UNION
SELECT ALL АдресПолучателя AS Адрес,ГородПолучателя AS Город,'Клиенты ' AS Источник
FROM Заказы;

Выполните этот запрос.

Самостоятельно выберите из таблиц «Клиенты» и «Сотрудники» следующие данные:

· фамилию и имя;

· должность;

· город.

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

 

2. Создание таблицы из существующих таблиц с помощью SELECT … INTO. Во многих СУБД конструкция SELECT … INTO <имя таблицы> используется для создания новой таблицы и вывода в неё результатов запроса. Например, таблица «Страны» с названиями всех стран, в которые направляются заказы, создаётся в результате выполнения следующего запроса:

SELECT DISTINCT СтранаПолучателя
INTO Страны
FROM Заказы;

Самостоятельно с помощью SELECT … INTO создайте таблицу «Клиенты2», содержащую данные из таблицы «Клиенты» обо всех клиентах, живущих в Лондоне.

Сохраните выполненные запросы и покажите их преподавателю