Контрольная работа. Работа с запросами SQL. Условия задач одинаковы для всех вариантов контрольной работы и приведены ниже. В качестве учебной базы данных выступает база «Борей» (см. прил.1). Задача 1
Содержат ли тексты запросов ошибку? Если содержат, то в чем она заключается? Если запрос верный, что будет результатом выполнения запроса? Задача 2
Приведите тексты SQL-запросов, выполняющих заданные действия. Задача 3
Дайте теоретическую справку по приведенному ниже вопросу. Варианты заданий контрольной работы приведены в таблице 1. Таблица 1. Варианты заданий контрольной работы.
№ Варианта
| Задача 1
| Задача 2
|
| SELECT Клиенты.Название, Заказы.КодЗаказа, Доставка.Название, Заказы.ДатаРазмещения, Заказы.ДатаРазмещения
FROM Клиенты LEFT JOIN (Доставка RIGHT JOIN Заказы ON Доставка.КодДоставки = Заказы.Доставка) ON Клиенты.КодКлиента = Заказы.КодКлиента
ORDER BY Клиенты. Название, Заказы.КодЗаказа;
SELECT Типы.Категория, Count(Товары.КодТовара) AS [Count-КодТовара], Max(Товары.Цена) AS [Max-Цена], Типы.Описание, Min(Товары.МинимальныйЗапас) AS [Min-МинимальныйЗапас] FROM Типы LEFT JOIN Товары ON Типы.КодТипа = Товары.КодТипа
GROUP BY Типы.Категория, Типы.Описание;
| Вывести список сотрудников вида Фамилия, Имя, Дата найма, количество заказов данного сотрудника. Список отсортировать по дате найма по количеству заказов сотрудника по убыванию и по дате найма по возрастанию.
|
| SELECT Товары.Марка, Типы.Категория, Поставщики.Название, Товары.НаСкладе, MAX (Товары.МинимальныйЗапас)
FROM Поставщики RIGHT JOIN (Типы RIGHT JOIN Товары ON Типы.КодТипа = Товары.КодТипа) ON Поставщики.КодПоставщика = Товары.КодПоставщика
WHERE (((Товары.Цена)<5000))
SELECT Доставка.Название, Доставка.Телефон, Заказы.КодЗаказа, Заказано.Цена, Заказано.Скидка, [Заказано]![Цена]*(1-[Заказано]![Скидка]) AS Итоговая_цена
FROM (Доставка INNER JOIN Заказы ON Доставка.КодДоставки = Заказы.Доставка) INNER JOIN Заказано;
| Вывести список следующего вида Код Заказа, Категория товара, Марка товара, стоимость данного товара в заказе. В список включить товары, поставки которых не прекращены и цена складского запаса которых меньше 15000. Список отсортировать по категории товара по возрастанию и по марке товара по убыванию.
|
| SELECT Товары.Марка, Типы.Категория, Поставщики.Название, Товары.НаСкладе, Товары.МинимальныйЗапас
FROM Поставщики RIGHT JOIN (Типы RIGHT JOIN Товары ON Типы.КодТипа = Товары.КодТипа) ON Поставщики.КодПоставщика = Товары.КодПоставщика
WHERE (((Товары.Цена)<15000))
ORDER BY Товары.НаСкладе DESC , Товары.МинимальныйЗапас;
SELECT Типы.Категория, Count(Товары.КодТовара) AS [Count-КодТовара], Max(Товары.Цена) AS [Max-Цена], Типы.Описание, Min(Товары.МинимальныйЗапас) AS [Min-МинимальныйЗапас]
FROM Типы LEFT JOIN Товары ON Типы.КодТипа = Товары.КодТипа
GROUP BY Типы.Категория, Типы.Описание;
| Вывести список заказов с минимальной датой размещения. Список следующего вида Код заказа, Адрес получателя, Название службы доставки, Стоимость доставки, Стоимость доставки с НДС (18%). Для заказов у которых не определена служба доставки вывести в Название службы доставки null.
|
| SELECT Товары.Марка, Типы.Категория, Поставщики.Название, Товары.НаСкладе, MAX (Товары.МинимальныйЗапас)
FROM Поставщики RIGHT JOIN (Типы RIGHT JOIN Товары ON Типы.КодТипа = Товары.КодТипа) ON Поставщики.КодПоставщика = Товары.КодПоставщика
GROUP BY Товары.Марка, Типы.Категория, Поставщики.Название
SELECT Товары.Марка, Товары.Цена, Типы.Категория, Товары.ЕдиницаИзмерения, Товары.НаСкладе, Заказано.КодЗаказа, Заказано.Количество, Заказано.Цена
FROM (Типы RIGHT JOIN Товары ON Типы.КодТипа = Товары.КодТипа) LEFT JOIN Заказано ON Товары.КодТовара = Заказано.КодТовара
ORDER BY 2 DESC;
| Сформировать список клиентов, имеющих не более двух заказов (включая клиентов не имеющих заказов), следующего вида Название, Город, Телефон, Число заказов клиента. Список отсортировать по названию клиентов по убыванию.
|
| SELECT Доставка.КодДоставки, Доставка.Название, Sum(Заказы.КодКлиента) AS Sum-КодКлиента
FROM (Доставка INNER JOIN Заказы ON Доставка.КодДоставки = Заказы.Доставка) INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа
GROUP BY Доставка.КодДоставки, Доставка.Название; SELECT Клиенты.КодКлиента, Клиенты.Название, Sum(Заказы.СтоимостьДоставки) AS Sum-СтоимостьДоставки, Min(Заказы.ДатаИсполнения) AS Min-ДатаИсполнения, Max(Заказано.Цена) AS Max-Цена
FROM Клиенты LEFT JOIN (Заказы LEFT JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа) ON Клиенты.КодКлиента = Заказы.КодКлиента
GROUP BY Клиенты.КодКлиента, Клиенты.Название DESC
ORDER BY Клиенты.КодКлиента DESC , Sum(Заказы.СтоимостьДоставки);
| Сформировать список заказов, в которых цена товара в заказе ниже цены, указанной в таблице «Товары». Список имеет следующий вид: Номер заказа, Наименование товара, Цена товара в заказе, Цена товара в таблице «Товары».
|
| SELECT Доставка.КодДоставки, Доставка.Название, Заказы.КодКлиента, Заказано.Количество
FROM Доставка, Заказано, Заказы;
SELECT Доставка.КодДоставки, Доставка.Название, Sum(Заказы.КодКлиента) AS Sum-КодКлиента
FROM (Доставка INNER JOIN Заказы ON Доставка.КодДоставки = Заказы.Доставка) INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа
GROUP BY Доставка.КодДоставки;
| Сформировать список заказов, в котором присутствует товар с минимальной ценой следующего вида: Код заказа, Дата исполнения, Стоимость доставки.
Список отсортировать по Стоимости доставки по возрастанию.
|
| SELECT COUNT(Доставка.КодДоставки), COUNT(Доставка.Название), MIN (Заказы.КодКлиента), Заказано.Количество
FROM Доставка, Заказано, Заказы;
SELECT Доставка.КодДоставки, Доставка.Название, Sum(Заказы.КодКлиента) AS Sum-КодКлиента
FROM (Доставка INNER JOIN Заказы ON Доставка.КодДоставки = Заказы.Доставка) INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа
GROUP BY Доставка.КодДоставки, Доставка.Название;
| Сформировать список всех городов, в которых расположены клиенты, исключив дубликаты. Итоговый список отсортировать в алфавитном порядке. Кроме того, посчитать и вывести количество различных городов (то есть число записей в списке).
|
| SELECT Клиенты.Название, Заказы.ДатаРазмещения, Заказы.ДатаНазначения, Поставщики.Название, Товары.Марка, Заказано.Количество
FROM Поставщики LEFT JOIN (Товары INNER JOIN ((Клиенты RIGHT JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента) INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа) ON Товары.КодТовара = Заказано.КодТовара) ON Поставщики.КодПоставщика = Товары.КодПоставщика
ORDER BY Клиенты.Название, Заказы.ДатаРазмещения DESC , Поставщики.Название DESC , Товары.Марка;
SELECT Типы.Категория, Count(Товары.КодТовара) AS [Count-КодТовара], Max(Товары.Цена) AS [Max-Цена], Типы.Описание, Min(Товары.МинимальныйЗапас) AS [Min-МинимальныйЗапас]
FROM Типы LEFT JOIN Товары ON Типы.КодТипа = Товары.КодТипа
GROUP BY Типы.Категория, Типы.Описание;
ORDER BY Типы.Категория DESC;
| Сформировать список товаров (Марка, Категория, Минимальный Запас, стоимость остатков на складе), поставки которых прекращены. Список отсортировать по величине минимального запаса по возрастанию.
|
| SELECT Клиенты.КодКлиента, Клиенты.Название, Sum(Заказы.СтоимостьДоставки) AS Sum-СтоимостьДоставки, Min(Заказы.ДатаИсполнения) AS Min-ДатаИсполнения, Max(Заказано.Цена) AS Max-Цена
FROM Клиенты LEFT JOIN (Заказы LEFT JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа) ON Клиенты.КодКлиента = Заказы.КодКлиента
GROUP BY Клиенты.Название
ORDER BY Клиенты.КодКлиента DESC , Sum(Заказы.СтоимостьДоставки);
SELECT Доставка.Название, Доставка.Телефон, Заказы.КодЗаказа, Заказано.Цена, Заказано.Скидка, Заказано.Цена*(1-Заказано.Скидка) AS Итоговая_цена
FROM (Доставка INNER JOIN Заказы ON Доставка.КодДоставки = Заказы.Доставка) INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа;
| Сформировать список товаров (Марка, Цена, Ожидается, На складе), которые вошли в заказ с минимальным кодом. Список отсортировать по цене по убыванию
|
| SELECT Доставка.КодДоставки, Доставка.Название, Sum(Заказы.КодКлиента) AS Sum-КодКлиента
FROM (Доставка INNER JOIN Заказы ON Доставка.КодДоставки = Заказы.Доставка) INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа
SELECT Клиенты.КодКлиента, Клиенты.Название, Sum(Заказы.СтоимостьДоставки) AS Sum-СтоимостьДоставки, Min(Заказы.ДатаИсполнения) AS Min-ДатаИсполнения, Max(Заказано.Цена) AS Max-Цена
FROM Клиенты LEFT JOIN (Заказы LEFT JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа) ON Клиенты.КодКлиента = Заказы.КодКлиента
GROUP BY Клиенты.КодКлиента, Клиенты.Название;
| Сформировать список поставщиков (Наименование, адрес, телефон), чьи товары поставлялись клиенту разметившему заказ с минимальным кодом.
|
| SELECT Клиенты.Название, Заказы.ДатаРазмещения, Заказы.ДатаНазначения, Поставщики.Название, Товары.Марка, Заказано.Количество
FROM Поставщики INNER JOIN (Товары INNER JOIN ((Клиенты INNER JOIN Заказы ON Клиенты.КодКлиента = Заказы.КодКлиента) INNER JOIN Заказано ON Заказы.КодЗаказа = Заказано.КодЗаказа) ON Товары.КодТовара = Заказано.КодТовара) ON Поставщики.КодПоставщика = Товары.КодПоставщика
ORDER BY Клиенты.Название, Заказы.ДатаРазмещения DESC , Поставщики.Название DESC , Товары.Марка;
SELECT Товары.Марка, Товары.Цена, Типы.Категория, Товары.ЕдиницаИзмерения, Товары.НаСкладе, Заказано.КодЗаказа, Заказано.Количество, Заказано.Цена
FROM (Типы RIGHT JOIN Товары ON Типы.КодТипа = Товары.КодТипа) LEFT JOIN Заказано ON Товары.КодТовара = Заказано.КодТовара
ORDER BY Товары.Марка DESC , Товары.Цена, Типы.Категория;
| Сформировать список следующего вида: Фамилия сотрудника, Общее количество обслуженных им заказов, Последняя дата исполненного заказа, Максимальная стоимость доставки среди всех заказов, обслуженных сотрудником. При формировании учесть сотрудников, не имеющих обслуженных заказов, а также однофамильцев.
|
| SELECT Товары.Марка, Товары.Цена, Типы.Категория, Товары.ЕдиницаИзмерения, Товары.НаСкладе, Заказано.КодЗаказа, Заказано.Количество, Заказано.Цена
FROM (Типы RIGHT JOIN Товары) LEFT JOIN Заказано ON Товары.КодТовара = Заказано.КодТовара
ORDER BY 2 DESC;
SELECT 2, 3, 4
From Типы
Order by 2,3;
| Сформировать список заказов (код заказа, код клиента, дата размещения заказа, суммарная стоимость заказа).
|
| SELECT Типы.Категория, Count(Товары.КодТовара) AS [Count-КодТовара], Max(Товары.Цена) AS [Max-Цена], Типы.Описание, Min(Товары.МинимальныйЗапас) AS [Min-МинимальныйЗапас]
FROM Типы LEFT JOIN Товары ON Типы.КодТипа = Товары.КодТипа
GROUP BY Типы.Категория, Типы.Описание;
ORDER BY 3 DESC, 1;
SELECT Типы.Категория, Товары.Марка, Товары.ЕдиницаИзмерения, Товары.НаСкладе, Товары.ПоставкиПрекращены
FROM Типы INNER JOIN Товары ON Типы.КодТипа=Товары.КодТипа
WHERE (((Товары.ПоставкиПрекращены)<>Yes))
ORDER BY Типы.Категория, Товары.Марка;
| Сформировать список поставщиков тех товаров, поставки которых прекращены. Список должен содержать все атрибуты поставщика, а также не содержать дубликатов. Список отсортировать по названию поставщика в порядке, обратном алфавитному.
|
| SELECT Заказано.КодЗаказа, Заказано.КодТовара, Товары.Марка, Заказано.Цена, Заказано.Количество, Заказано.Скидка, (Заказано.Цена*[Количество]*(1-[Скидка])/100)*100 AS ОтпускнаяЦена
FROM Товары INNER JOIN Заказано ON Товары.КодТовара=Заказано.КодТовара
ORDER BY Заказано.КодЗаказа;
SELECT Типы.КодТипа, Типы.Категория, Товары.Марка, Sum([Сведения о заказах].ОтпускнаяЦена) AS ПродажиТовара
FROM Типы INNER JOIN (Товары INNER JOIN (Заказы INNER JOIN [Сведения о заказах] ON Заказы.КодЗаказа=[Сведения о заказах].КодЗаказа) ON Товары.КодТовара=[Сведения о заказах].КодТовара) ON Типы.КодТипа=Товары.КодТипа
WHERE (((Заказы.ДатаРазмещения) Between ‘1/1/1997’ And ‘12/31/1997’))
GROUP BY Типы.КодТипа, Типы.Категория, Товары.Марка
ORDER BY Товары.Марка;
| SELECT COUNT(Доставка.КодДоставки), COUNT(Доставка.Название), MIN (Заказы.КодКлиента), Заказано.Количество
FROM Доставка, Заказано, Заказы
WHERE Клиенты.Код Клиента< >10000;
|
| SELECT Типы.КодТипа, Типы.Категория, Товары.Марка, Sum([Сведения о заказах].ОтпускнаяЦена) AS ПродажиТовара FROM Типы INNER JOIN (Товары INNER JOIN (Заказы INNER JOIN [Сведения о заказах] ON Заказы.КодЗаказа=[Сведения о заказах].КодЗаказа) ON Товары.КодТовара=[Сведения о заказах].КодТовара) ON Типы.КодТипа=Товары.КодТипа
WHERE (((Заказы.ДатаРазмещения) Between ‘1/1/1997’ And ‘12/31/1997’))
GROUP BY Типы.КодТипа, Типы.Категория, Товары.Марка
ORDER BY 3;
| Сформировать список клиентов (Название, ОбращатьсяК, Должность, Адрес, Город, Количество сотрудников, обслуживавших заказы этого клиента). Список отсортировать по названию города в алфавитном порядке, и по названию клиента в обратном алфавитном порядке.
|
| INSERT INTO Доставка ( КодДоставки, Название, Телефон )
SELECT Доставка.КодДоставки, Доставка.КодДоставки, Доставка.Телефон
FROM Доп_Доставка
WHERE (((Доставка.КодДоставки)>10000) AND ((Доставка.КодДоставки) Is Not Null));
| Увеличить на 50% цену всех товаров, поставляемых не российскими поставщиками, в случае если стоимость складского остатка меньше 10000.
|
| DELETE
FROM Заказано
WHERE (((Заказано.Скидка)>10) AND ((Заказано.Количество)<1500));
GROUP BY Заказано.Скидка
| Сформировать список (Название, Город, Адрес, Телефон) российских поставщиков товаров с наибольшим значением Минимального запаса. Список отсортировать по Названию. Список записать в новую таблицу с именем «Т1»
|
| UPDATE Товары SET Товары.Цена = Цена*0,98
WHERE
((( Товары.МинимальныйЗапас < 1500 Or Товары.НаСкладе = 1000) And Товары.ЕдиницаИзмерения<>'Л'));
| Сформировать список заказов, доставкой которых занята фирма «Фирма1». Список должен иметь вид: код заказа, дата размещения, код сотрудника, код клиента. Список отсортировать по коду заказа по убыванию. Список записать в таблицу с именем «Т1», предполагая, что такая таблица существует
|
| INSERT INTO Доставка
SELECT Доставка.КодДоставки, Доставка.КодДоставки, Доставка.Телефон
FROM Доставка
WHERE (((Доставка.КодДоставки)>10000) AND ((Доставка.КодДоставки) Is Not Null));
| Увеличить вдвое цену всех товаров, у которых название категории начинается со слова «Винт», либо поставки которых прекращены.
|
| UPDATE Сотрудники SET Сотрудники.Обращение = "Sr"
WHERE (((Сотрудники.Должность) Like "Директор%") AND ((Сотрудники.Страна)="Испания")) OR (((Сотрудники.Страна)="Мексика"))
GROUP BY Сотрудники.Должность;
| Вывести всю информацию о заказах (то есть все атрибуты заказа), которые обслуживаются сотрудниками, родившимися в период с 01/05/1972 по 31/12/1990
|
| UPDATE Сотрудники SET Сотрудники.Обращение = "Sr"
WHERE (((Сотрудники.Должность) Like "Директор%") AND ((Сотрудники.Страна)="Испания")) OR (((Сотрудники.Страна)="Мексика"));
| Вывести всю информацию о товарах, с максимальной величиной ожидаемой поставки (независимо от единиц измерения), а также о товарах, относящихся к категориям «Продукты питания», «Бытовая химия» и «Автозапчасти». Информацию записать в таблицу “Т1”, предполагая, что такая таблица существует
|
| DELETE *
FROM Заказано
WHERE (((Заказано.Скидка)>10) AND ((Заказано.Количество)<1500));
GROUP BY Заказано.Скидка
| Сформировать список поставщиков товаров (все атрибуты поставщиков), в описании типов которых отсутствует изображение. В списке исключить дубликаты. Информацию записать в таблицу “Т1”, предполагая, что такая таблица существует
|
| DELETE Заказано.Цена, Заказано.Скидка
FROM Заказано
WHERE (((Заказано.Цена)>100) AND ((Заказано.Скидка)<5));
| Сформировать список заказов, которые обслуживали самые молодые сотрудники (с учетом того, что могут быть сотрудники, родившиеся в один день). Список записать в новую таблицу с именем «Т1»
|
| DELETE Заказано.Цена, Заказано.Скидка
FROM Заказано
WHERE (((Заказано.Цена)>100) AND ((Заказано.Скидка)<5));
ORDER BY Цена
| Сформировать список всех товаров, в названии которых встречается слово «гайка». Список включает в себя Марку, Описание категории товара, На Складе, Минимальный Запас.
Список отсортировать по цене по убыванию.
Список записать в новую таблицу с именем «Т1»
|
| DELETE
FROM Заказано
WHERE (((Заказано.Скидка)>10) AND ((Заказано.Количество)<1500));
| Исправить дату исполнения заказов с 05/05/2007 и 10/10/2007 на 01/02/2008.
|
| UPDATE Товары SET Товары.Цена = Цена*0,98
WHERE
(((Товары.МинимальныйЗапас < 1500 Or Товары.НаСкладе = 1000) And Товары.ЕдиницаИзмерения<>'Л'))
GROUP BY Товары.МинимальныйЗапас;
| Сформировать единый список сотрудников и клиентов следующего вида: Фамилия (или ОбращатьсяК для клиентов), страна, город, адрес.
Список записать в новую таблицу с именем «Т2»
|
| SELECT Типы.КодТипа, Типы.Категория, Товары.Марка, Sum([Сведения о заказах].ОтпускнаяЦена) AS ПродажиТовара
INTO T21
FROM Типы INNER JOIN (Товары INNER JOIN (Заказы INNER JOIN [Сведения о заказах] ON Заказы.КодЗаказа=[Сведения о заказах].КодЗаказа) ON Товары.КодТовара=[Сведения о заказах].КодТовара) ON Типы.КодТипа=Товары.КодТипа
WHERE (((Заказы.ДатаРазмещения) Between ‘1/1/1997’ And ‘12/31/1997’))
GROUP BY Типы.КодТипа, Типы.Категория, Товары.Марка
ORDER BY 3;
| Вывести в новую таблицу с именем «Т1» названия и телефоны фирм-доставщиков, доставлявших заказы клиентов из Москвы, Пскова и Смоленска. Список не должен содержать дубликатов и должен быть отсортирован по названиям фирм в алфавитном порядке.
|
| SELECT Заказы.КодКлиента, Заказы.КодСотрудника,
Заказы.ДатаРазмещения,
Заказано.КодТовара, Заказано.Цена, Заказано.Количество
INTO Т1
FROM Заказы
INNER JOIN Заказано
ON Заказы.КодЗаказа =
Заказано.КодЗаказа;
| Сформировать список сотрудников, не обслуживавших ни один заказ. Вид списка: Фамилия, Должность, Страна, Город. Список отсортировать по дате рождения по убыванию.
|
| DELETE
FROM Сотрудники
WHERE (((Сотрудники.КодСотрудника)>100000) AND ((Сотрудники.Город)="Москва") AND ((Сотрудники.Примечания) Is Not Null)) OR (((Сотрудники.КодСотрудника)<150) AND ((Сотрудники.Город)="Псков"));
| Установить величину скидки 32% на все товары, заказанные (по дате размещения) между 01/01/2003 и 05/07/2005
|
| DELETE
FROM Сотрудники
WHERE (((Сотрудники.КодСотрудника)>100000) AND ((Сотрудники.Город)="Москва") AND ((Сотрудники.Примечания) Is Not Null)) OR (((Сотрудники.КодСотрудника)<150) AND ((Сотрудники.Город)="Псков"))
ORDER BY 1, 3;
| Составить SQL-запрос, увеличивающий на 50% цену всех товаров, поставляемых не российскими поставщиками, в случае если стоимость складского остатка меньше 10000
|
Приложение 1. Структура учебной базы данных «Борей».
Рисунок 1 – Структура учебной базы данных «Борей».
Список Используемых источников
Иванов А.А., Авербух А.Б. Базы данных. Языки запросов. Базовый курс [Текст]: учебное пособие для студентов заочной формы обучения. – СПб.: СПбГТИ(ТУ), 2011. –45 с.
Вейскас Дж. Эффективная работа: Microsoft Access 2003 – СПб.: Питер, 2003. – 1163 c.
Макдональд К. Oracle PL/SQL для профессионалов. Практические решения – М.: Apress, 2005 – 560с.
Молинаро Э. SQL Сборник рецептов. – М.: Apress, 2009 – 672с.
Грофф Дж., Вайнберг П SQL: полное руководство – К.: Издательская группа BHV, 2000. – 608 с
Грабер М SQL. Справочное руководство – М: Лори, 1997. – 291с
Мамаев Е Microsoft SQL Server 2000 в подлиннике – СПб.: Изд-во BHV, 2001 – 1280с.
Астахова И.Ф., Толстобров А.П., Мельников В.М. SQL в примерах и задачах. – М.: Новое знание, 2002 – 456 с.
Учебная база данных «Борей» - http://www.canpuy.ru/Борей.mdb
|