Практические работы для 11 класса (по учебнику К.Ю. Полякова, Е.А. Еремина)



Практическая работа № 20-SQL
«Язык SQL (многотабличная база данных, OpenOffice Base)»




Файлы-заготовки для выполнения этой практической работы


В этой работе вы познакомитесь с новой командой ALTER TABLE (изменить таблицу) языка SQL и научитесь составлять запросы к многотабличной реляционной базе данных.

Создание и заполнение таблиц


1. Используя только SQL-запросы, постройте три таблицы для реляционной базы данных кафе (значок обозначает первичный ключ таблицы):

2. Теперь нужно построить связи между таблицами:

Для этой цели используется команда ALTER TABLE (англ. изменить таблицу). Построить связь между таблицами – это значит задать ограничение (CONSTRAINT), которое связывает первичный ключ одной таблицы с полем соответствующего типа другой. Если связываемое поле второй таблицы – неключевое, то оно называется внешним ключом (FOREIGN KEY).

Например, команда для создания связи 1:N между ключевым полем Номер таблицы Заказы и неключевым полем Номер заказа таблицы Заказано выглядит так: 

ALTER TABLE "Заказано" 
  ADD CONSTRAINT ORDER_NO 
  FOREIGN KEY("Номер заказа")  
  REFERENCES "Заказы"("Номер")

Такая запись дословно означает: 

Изменить таблицу "Заказано"
  добавить связь ORDER_NO
  внешний ключ "Номер заказа"
  ссылается на поле "Номер" таблицы "Заказы"

Здесь ORDER_NO – это просто имя, которое мы выбрали для этой связи (можно было выбрать и другое).

3. Введите и выполните показанный выше SQL-запрос на добавление связи. Зайдите в меню Сервис – Связи и убедитесь, что связь действительно создана 3.

3 В OpenOffice Base иногда связи появляются в окне не сразу, а только после перезагрузки базы.


4. Составьте и выполните SQL-запрос на добавление второй связи. Запишите этот запрос в поле для ответа.

Ответ:

5. С помощью SQL-запросов заполните базу следующими данными


Выбор данных и сортировка


6. Построим в режиме SQL запрос СоставЗаказа, который выводит номер заказа и название заказанных блюд. Эти данные находятся в двух таблицах – Заказано и Блюда, поэтому их нужно как-то объединить. Для этого используется связь 1:N между таблицами, которую мы недавно установили. Действительно, для каждой записи в таблице Заказано нужно выбрать название блюда из таблицы Блюда, код которого совпадает с полем Заказано.Код блюда.

Это запрос на выборку данных, поэтому используем оператор SELECT: 

SELECT "Заказано"."Номер заказа", "Блюда"."Название" 
   FROM "Заказано", "Блюда" 
   WHERE "Заказано"."Код блюда" = "Блюда"."Код"

Здесь из таблиц Заказано и Блюда выбираются поля Номер заказа и Название; условие в последней строке связывает таблицы.

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

SELECT "Номер заказа", "Название" 
   FROM "Заказано", "Блюда" 
   WHERE "Заказано"."Код блюда" = "Блюда"."Код"

7. Теперь добавим в запрос дату заказа. Она находится в таблице Заказы, которая пока в запросе не участвует. Таким образом, нам нужно объединить три таблицы. Условие отбора получается сложным, два условия (связи по коду блюда между таблицами Заказано и Блюда и по номеру заказа между таблицами Заказы и Заказано) объединяются с помощью логической операции AND (И): 

SELECT "Номер заказа", "Дата", "Название" 
   FROM "Заказано", "Блюда", "Заказы" 
   WHERE "Заказано"."Код блюда" = "Блюда"."Код" 
     AND "Заказано"."Номер заказа" = "Заказы"."Номер"

Проверьте результат выполнения этого запроса.

8. Измените запрос так, чтобы он выбирал только блюда из состава заказа № 1. Запишите этот запрос:

Ответ:

9. Построим еще один запрос Итоги, в котором для каждого заказа выводится его номер, дата и общая сумма (с помощью функции SUM). 

SELECT "Номер заказа", "Дата", SUM("Цена") 
   FROM "Заказано", "Блюда", "Заказы" 
   WHERE "Заказано"."Код блюда" = "Блюда"."Код" 
      AND "Заказано"."Номер заказа" = "Заказы"."Номер" 
   GROUP BY "Номер заказа", "Дата"

В последней строке указано, что по полям Номер заказа и Дата выполняется группировка, то есть сумма цен считается для каждой уникальной пары «Номер заказа – Дата».

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

«SUM("Блюда"."Цена")»

Для того, чтобы сделать у этого столбца заголовок «Сумма», нужно добавить в первую строку запроса после SUM("Цена") так называемый псевдоним (подпись) с ключевым словом AS: 

SELECT "Номер заказа", "Дата", SUM("Цена")  AS "Сумма" 
   ...

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

10. Измените запрос так, чтобы заказы были отсортированы в порядке убывания суммы (используйте ключевые слова ORDER BY).

Ответ:


Вложенные запросы


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

SELECT MIN("Сумма") AS "Сумма" FROM "Итоги"

12. Наконец, можно вывести информацию о заказе с минимальной суммой: 

SELECT "Номер заказа", "Дата", "Сумма" 
   FROM "Итоги", "МинСумма"
   WHERE "Итоги"."Сумма" = "МинСумма"."Сумма"

Обратите внимание, что этот запрос использует результаты выполнения двух ранее построенных запросов – Итоги и МинСумма. Запрос Итоги можно было и не составлять, а вместо этого использовать вложенный запрос (запрос в запросе): 

SELECT "Номер заказа", "Дата", "Сумма" 
   FROM "Итоги" 
   WHERE "Сумма" = 
         (SELECT MIN("Сумма") FROM "Итоги")

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

13. Измените запрос так, чтобы получить список всех заказов, сумма которых больше средней.

Ответ:






Наверх