 |
|
Рекомендации по выполнению
Лабораторная работа №14
Тема: «Разработка вложенных запросов SELECT. Объединение и соединение запросов.»
Проверил:
Преподаватель:
Резникова С.А
Выполнил:
студент 2 курса
группы ЭОП-21
Бельмач Артём
Барановичи 2013
Тема: Разработка вложенных запросов SELECT. Объединение и соединение запросов
Цель: Изучить основные приемы работы с многотабличными базами данных средствами языка SQL
Оснащение: IBM PC, MS Access
Ход работы
Проектирование и заполнение таблиц
Создали учебную базу данных Учет заказов, используя язык SQL. База данных состоит из трех таблиц: Продавцы, Покупатели и Заказы.
| Продавцы | Покупатели | ||||||
| Код_ПР | Название | Город | Цена | Код_ПК | Название | Город | |
| Пит | Лондон | 0,15 | Иванофф | Лондон | |||
| Серж | Сан-Хосе | 0,18 | Тонино | Рим | |||
| Алекс | Вашингтон | 0,19 | Луи | Сан-Хосе | |||
| Георг | Лондон | 0,16 | Дитер | Берлин | |||
| Хосе | Барселона | 0,17 | Клеменс | Лондон | |||
| Андре | Париж | ||||||
| Сениоре | Сан-Хосе |
| Заказы | ||||
| № | Дата | Код_ПК | Количество | Код_ПР |
| 13.03.2011 | ||||
| 15.03.2011 | ||||
| 15.03.2011 | ||||
| 17.03.2011 | ||||
| 18.03.2011 | ||||
| 20.03.2011 | ||||
| 22.03.2011 | ||||
| 25.03.2011 | ||||
| 27.03.2011 | ||||
| 29.03.2011 |
Рекомендации по выполнению
В созданной базе данных вызвали диалоговое окно создания запросов, выбрали в окне базы данных вкладку Запросы,и нажали кнопку [Создать].Выбрали Режим создания запроса Конструктор.Закрыли окно Добавление таблицыпри его появлении в бланке запроса. Вызвали окна SQL-запроса,выполнили команду Вид ®Режим SQL.
Спроектировали структуры таблиц, используякоманду CREATE TABLE(в структуре сразу указываются: первичный ключ – PRIMARY KEY и внешний ключ – FOREIGN KEY).
CREATE TABLE Продавцы
(Код_ПР Integer NOT NULL PRIMARY KEY,
Название CHAR(15) NOT NULL,
Город CHAR(15),
Цена NUMERIC);
Закрыли окно Запрос1: Управляющий запрос, ответив на вопрос о сохранении [Да].Присвоили запросу имя Запрос-создание таблицы. Запустили запрос, подтвердите его выполнение.
На базе этого запроса можно создать запросы на создание других таблиц.
CREATE TABLE Покупатели
(Код_ПК Integer NOT NULL PRIMARY KEY,
Название CHAR(15) NOT NULL,
Город CHAR(15));
Вариант ограничения столбца ограничением FOREIGN KEY – по-другому называется ссылочное ограничение (REFERENCES), так как он фактически не содержит в себе слов FOREIGN KEY, а просто использует слово REFERENCES, и далее имя родительского ключа, подобно этому:
CREATE TABLE Заказы
( № Integer NOT NULL PRIMARY KEY,
Дата datetime NOT NULL,
Код_ПК Integer NOT NULL REFERENCES Покупатели (Код_ПК),
Количество Integer,
Код_ПР Integer NOT NULL REFERENCES Продавцы (Код_ПР));
Просмотрели полученные структуры таблиц в режиме Конструктора, затем открыли окно Схема данных. Обратили внимание, что таблицы созданы вместе со связями между ними:
Заполнили созданные таблицы данными, используя язык SQL. Обратили внимание, как надо вводить данные типа Дата/время:
INSERT INTO Продавцы
VALUES (1005, "Хосе", "Барселона", 0.17);
INSERT INTO Покупатели
VALUES (2008, "Сениоре", "Сан-Хосе");
INSERT INTO Заказы
VALUES (10, "29.03.2011", 2007, 2000, 1005);
Поиск информации
Создайте запросы по базе данных Учет заказов:
• Сравнили работу простого запроса на выборку без использования аргумента DISTINCT и с указанным аргументом:
SELECT Код_ПР
FROM Заказы;
SELECT DISTINCT Код_ПР
FROM Заказы;
• Самостоятельно создали запросы с использованием языка SQL:
ü Найдите всех продавцов, цены у которых не превышают 0.17.
ü Найдите всех продавцов, проживающих в городе Лондон.
ü Для каждой даты заказа определите общее количество.
ü * Для каждого покупателя найдите, сколько раз он делал заказ.
SELECT Продавцы.Название
FROM Продавцы
WHERE (((Продавцы.Цена)<=0.17));
SELECT Название
FROM Продавцы
WHERE (Город="Лондон");
SELECT Заказы.Дата, Sum(Заказы.Количество) AS Сумма
FROM Заказы
GROUP BY Заказы.Дата;
SELECT Заказы.Код_ПК, Count(Заказы.Количество) AS [Количество обращений]
FROM Заказы
GROUP BY Заказы.Код_ПК;
• Создали запрос с использованием подзапроса (вложенного запроса).
Рассмотрим пример. Пусть надо найти всех клиентов, заказывавших товары у продавца из Барселоны. Для этого нужно сделать следующее:
× найти код продавца из Барселоны
× найти код покупателя, который делал заказ у этого продавца
× найти название этого покупателя
Каждый из этих шагов можно выполнить в виде отдельного запроса. Делая это, мы используем результаты, возвращенные одним оператором SELECT, чтобы заполнить предложение WHERE для следующего оператора SELECT. Мы можем также использовать подзапросы для того, чтобы объединить все три запроса в один-единственный оператор.
SELECT Покупатели.Название
FROM Покупатели, Продавцы, Заказы
WHERE Покупатели.Код_ПК=Заказы.Код_ПК
And Продавцы.Код_ПР=Заказы.Код_ПР
And Продавцы.Город='Барселона';
Следующий шаг состоит в получении клиентской информации для каждого из найденных кодов покупателей. Выполните этот шаг самостоятельно.
SELECT Название
FROM Покупатели
WHERE Код_ПК IN
(SELECT Код_ПК FROM Заказы WHERE Код_ПР IN
(SELECT Код_ПР FROM Продавцы WHERE Город='Барселона'));
• Создали запрос с использованием объединения запросов.
Поскольку данные хранятся в нескольких таблицах базы данных, то извлечь их с помощью одного оператора SELECT можно посредством объединения данных. Объединение представляет собой механизм, используемый для объединения таблиц внутри оператора (отсюда термин "объединение"). Используя особый синтаксис, можно объединить несколько таблиц таким образом, что будет возвращаться один результат, и это объединение будет "на лету" связывать нужные строки из каждой таблицы.
SELECT Цена, Дата, Количество
FROM Продавцы, Заказы
WHERE Продавцы.Код_ПР=Заказы.Код_ПР;
• Создали запрос с использованием объединения однотипных запросов.
При необходимости объединения нескольких однотипных результирующих наборов данных используют оператор UNION.
Пример: Показать цену товаров и цену товаров со скидкой. Скидка на товары для покупателей, приобретающих не менее 200 штук – 10%, остальных – 5%.
SELECT Покупатели.Название, Количество, Цена, Цена*0.9 AS Цена_скидка
FROM Покупатели, Продавцы, Заказы
WHERE Продавцы.Код_ПР=[Заказы].[Код_ПР]
AND Покупатели.Код_ПК=[Заказы].[Код_ПК]
AND Количество>=200
UNION SELECT Покупатели.Название, Количество, Цена, Цена*0.95 AS Цена_скидка
FROM Покупатели, Продавцы, Заказы
WHERE Продавцы.Код_ПР=[Заказы].[Код_ПР]
AND Покупатели.Код_ПК=[Заказы].[Код_ПК]
AND Количество<200;
• Выполните все запросы, рассмотренные выше.
• Самостоятельно создайте запросы с использованием языка SQL:
ü Отобразить информацию о том, какой покупатель, когда и у какого продавца заказывал товар.
ü Определить, у кого и когда заказывал товар покупатель из Берлина.
ü Определить продавцов, у которых ничего не заказывал покупатель из Парижа.
ü Определить, на какую сумму были сделаны заказы у каждого продавца (не зависимо от покупателя).
SELECT Покупатели.Название, Заказы.Дата, Продавцы.Название
FROM Заказы, Покупатели, Продавцы
WHERE Покупатели.Код_ПК=Заказы.Код_ПК
And Продавцы.Код_ПР=Заказы.Код_ПР;
SELECT Покупатели.Название, Заказы.Дата, Продавцы.Название
FROM Заказы, Покупатели, Продавцы
WHERE Покупатели.Код_ПК=Заказы.Код_ПК
And Продавцы.Код_ПР=Заказы.Код_ПР
And Покупатели.Город='Берлин';
SELECT Название
FROM Продавцы
WHERE Код_ПР NOT IN
(SELECT Код_Пр
FROM Заказы
WHERE Код_ПК IN
(SELECT Код_ПК
FROM Покупатели
WHERE Город='Париж'));
SELECT Продавцы.Название, Sum(Продавцы.Цена*Заказы.количество) AS Сумма_по_заказам
FROM Заказы, Покупатели, Продавцы
WHERE Покупатели.Код_ПК=Заказы.Код_ПК
And Продавцы.Код_ПР=Заказы.Код_ПР
GROUP BY Продавцы.Название;
Вывод:Изучили основные приемы работы с многотабличными базами данных средствами языка SQL.