Использование агрегатных функций для отобранных записей.
Подсчитать суммарную стоимость всех заказов, с ценой меньше100 руб.;
SELECT count(*)
FROM Заказано
WHERE Цена<100;
Найти минимальную, максимальную и среднюю цены для товаров с количеством, равным 10;
SELECT MIN (Цена), MAX (Цена), AVG (Цена)
FROM Заказано
WHERE Количество=10;
Функция CDATE().
Найти в таблице «Заказы» все заказы, исполненные между 01.07.1996 и 01.01.1997. Для этого нужно преобразовать даты с помощью функции CDATE() из текстового типа в тип «date». Запрос имеет вид:
SELECT *
FROM Заказы
WHERE ДатаИсполнения>=CDATE('01.07.1996') AND ДатаИсполнения<CDATE('01.01.1997');
Запрос на выборку из таблицы «Заказы» всех заказов, исполненных после 15.04.1998 и выполненных более чем за 5 дней.
SELECT *
FROM Заказы
WHERE ДатаИсполнения>CDATE(' 15.04.1998 ') And ДатаИсполнения-ДатаНазначения>5;
Функция DatePart().
Найти с помощью функции DatePart()все заказы, размещённые
a) в первом квартале 1997 г;
SELECT *
FROM Заказы
WHERE DatePart('q',ДатаРазмещения)=1 And DatePart('yyyy',ДатаРазмещения)=1997;
b) по понедельникам в январе за все годы.
SELECT *
FROM Заказы
WHERE DatePart('w',ДатаРазмещения)=2 And DatePart('m',ДатаРазмещения)=1;
Использование в условии выборки списка значений.
Найдите в таблице «Заказы» все заказы, размещённые:
a)в мае, августе и декабре в1996 и 1998 годах;
SELECT *
FROM Заказы
WHERE DatePart('m',ДатаРазмещения) In (5,9,12) And DatePart('yyyy',ДатаРазмещения) In (1996,1998);
b)пятого августа в 1996, 1997 и 1998 годах.
SELECT *
FROM Заказы
WHERE DatePart('d',ДатаРазмещения)=5 And DatePart('m',ДатаРазмещения)=8 And DatePart('yyyy',ДатаРазмещения) In (1996,1997,1998);
Использование в условии выборки диапазона значений.
Выберать из таблицы “Заказы”:
a) только поле СтранаПолучателя, при условии, что названия стран начинаются на А, Б, В или Р, С, Т и выборке не должно быть повторений названий стран.
SELECT DISTINCT СтранаПолучателя
FROM Заказы
WHERE (СтранаПолучателя Between 'А' And 'Вя') Or (СтранаПолучателя Between 'Р' And 'Тя');
b) алфавитном порядке города (поле ГородПолучателя) от Лилля до Парижа.
SELECT DISTINCT ГородПолучателя
FROM Заказы
WHERE ГородПолучателя BETWEEN 'Лилль' AND 'Париж'
ORDER BY ГородПолучателя;
Сформировать, используя оператор LIKE, и выполните следующие запросы к таблице «Заказы»:
a) выбрать все заказы с названием города получателя, начинающимся на А, Л или П;
SELECT *
FROM Заказы
WHERE ГородПолучателя Like 'А*' Or ГородПолучателя Like 'Л*' Or ГородПолучателя Like 'П*';
b)изменить предыдущий запрос, выбирая только поле ГородПолучателя и не допуская повторений;
SELECT DISTINCT ГородПолучателя
FROM Заказы
WHERE ГородПолучателя Like 'А*' Or ГородПолучателя Like 'Л*' Or ГородПолучателя Like 'П*';
c) выбрать все заказы с названием города получателя, начинающимся на Л, а со второй буквой – «и» или «о»;
SELECT *
FROM Заказы
WHERE ГородПолучателя Like 'Ли*' Or ГородПолучателя Like 'Ло*'
ORDER BY ГородПолучателя;
d) выбрать названия городов, состоящие из пяти букв и начинающиеся на букву П;
SELECT *
FROM Заказы
WHERE ГородПолучателя Like 'П????';
e) выбрать заказы, в которых адрес получателя сдержит запятую;
SELECT *
FROM Заказы
WHERE АдресПолучателя Like '*,*';
f) выбрать заказы, в которых адрес получателя начинается с цифры;
SELECT *
FROM Заказы
WHERE АдресПолучателя Like '#*';
g) выбрать заказы, в которых адрес получателя начинается не с цифры;
SELECT *
FROM Заказы
WHERE АдресПолучателя Like '[!0-9]*';
h) выбрать заказы, в которых адрес получателя начинается не с букв с C (лат.) по L и не с цифры;
SELECT *
FROM Заказы
WHERE АдресПолучателя Like '[!C-L]*' AND АдресПолучателя Like '[!0-9]*';
i) выбрать заказы, в которых адрес получателя начинается с цифры и имеет длину, не более 20 символов.
SELECT *
FROM Заказы
WHERE АдресПолучателя Like '#[1-20]*';
Лабораторная работа № 14. Предложения GROUP BY и HAVING
Предложение GROUP BY.
Составить и выполните следующие запросы к таблице «Заказы»:
a)подсчитать для каждой страны количество заказов, минимальную, среднюю и максимальную стоимость доставки;
SELECT КодТовара, Count(*) AS [К-во заказов], Max([Количество]) AS [Макс_к-во_товара]
FROM Заказано
WHERE КодТовара<6
GROUP BY КодТовара;
b) подсчитать для каждого города суммарную стоимость доставки всех заказов,
отсортировать выбранные записи по суммарной стоимости доставки;
SELECT ГородПолучателя, Count(*) AS [Кол-во заказов], SUM(СтоимостьДоставки)
FROM Заказы
GROUP BY ГородПолучателя
ORDER BY SUM(СтоимостьДоставки);
c) подсчитать для каждой страны суммарную стоимость доставки всех заказов;
SELECT СтранаПолучателя, SUM(СтоимостьДоставки) AS СумСтоимость
FROM Заказы
GROUP BY СтранаПолучателя;
d) подсчитать для каждого города каждой страны суммарную стоимость доставки всех заказов;
SELECT ГородПолучателя, SUM(СтоимостьДоставки), СтранаПолучателя
FROM Заказы
GROUP BY ГородПолучателя, СтранаПолучателя;
Предложение HAVING.
Составить и выполнить следующие запросы:
а) выбрать из таблицы «Заказано» коды товаров, у которых максимальная скидка больше 20%;
SELECT КодЗаказа, MAX(Скидка) AS Макс_скидка
FROM Заказано
GROUP BY КодЗаказа
HAVING MAX(Скидка)>0.2;
в) выбрать из таблицы «Товары» для каждого товара код товара, среднюю, минимальную и максимальную цены при условии, что средняя цена меньше 2000
SELECT КодЗаказа, MIN(Цена) AS МинимальнаяЦена, MAX(Цена) AS МаксимальнаяЦена, AVG(Цена) AS СредняяЦена
FROM Заказано
GROUP BY (КодЗаказа)
HAVING AVG(Цена)<2000;
Лабораторная работа № 15. Многотабличные запросы
Многотабличный запрос с описанием связей между таблицами в предложении
Составить запрос на выборку наименований книг и их авторов:
WHERE.
SELECT a.Наим, b.ФИО
FROM Книга AS a, Писатель AS b
WHERE a.Автор=b.КодП;
Левые и правые внешние соединения.
Составить и выполнить следующие запросы:
а) выбрать все имеющиеся в базе названия произведений Пушкина и Толстого Л.Н. и вместе с ФИО авторов;
SELECT a.Наим, b.ФИО
FROM Книга AS a, Писатель AS b
WHERE b.КодП=a.Автор And (b.ФИО='Пушкин' Or b.ФИО='Толстой Л. Н.');
б) Используя таблицы «заказы» и «клиенты», выбрать названия клиентов, их представителей (поле «ОбращатьсяК») и даты размещения их заказов при условии, что клиенты из Лондона;
SELECT a.Клиент, b.ОбращатьсяК, c.ДатаРазмещения
FROM Заказы INNER JOIN Клиенты ON Заказы.КодКлиента=Клиенты.КодКлиента
WHERE ГородПолучателя=Лондон;
в) Используя таблицы «заказы» «заказано», «товары» и «клиенты», выбрать названия клиентов, их представителей (поле «ОбращатьсяК») и коды и марки выбранных ими товаров при условии, что названия клиентов начинаются на “F” и заказы оформлял сотрудник Кротов.
SELECT a.КодКлиента, b.ОбращатьсяК, c.КодТовара, Марка
FROM Товары AS c, Заказы AS a, Заказано AS d, Клиенты AS b, Сотрудники AS f
WHERE a.НазваниеПолучателя Like 'F*' And f.Фамилия='Кротов' And a.КодЗаказа=d.КодЗаказа And d.КодТовара=c.КодТовара And a.КодСотрудника=f.КодСотрудника And a.КодКлиента=b.КодКлиента;