Полякова Л.П. Основы SQL. Курс лекций
Подождите немного. Документ загружается.
2. Определить авторов, не печатающих свои книги в
издательстве «АСТ».
SELECT Автор.Фамилия, Автор.КодАвтора
FROM Автор
WHERE Автор.КодАвтора NOT IN (SELECT Книга.КодАвтора
FROM Книга
WHERE Книга.Издательство=”АСТ”)
SELECT Автор.Фамилия, Автор.КодАвтора
FROM Автор,Книга
WHERE Книга.Издательство<>”АСТ”
SELECT Автор.Фамилия, Автор.КодАвтора
FROM Автор, Книга
WHERE Автор.КодАвтора=Книга.КодАвтора
AND Книга.Издательство<>”АСТ”
SELECT Автор.Фамилия, Автор.КодАвтора
FROM Автор
WHERE NOT EXISTS (SELECT Книга.КодАвтора
FROM Книга
WHERE Книга.Издательство=”АСТ”)
151
Лекция 7 Построение нетривиальных запросов
3. Определить авторов, написавших наибольшее количество
книг.
SELECT Автор.Фамилия
FROM Книга INNER JOIN Автор
ON Книга.КодАвтора = Автор.КодАвтора
GROUP BY Автор.Фамилия
HAVING Count(Книга.Название)>=All
(SELECT Count(Книга.Название)
FROM Книга
GROUP BY Книга.КодАвтора)
SELECT Автор.Фамилия
FROM Книга INNER JOIN Автор
ON Книга.КодАвтора = Автор.КодАвтора
GROUP BY Автор.Фамилия
HAVING Count(Книга.Название)=MAX
(SELECT Count(Книга.Название)
FROM Книга
GROUP BY Книга.КодАвтора)
SELECT Автор.Фамилия
FROM Книга INNER JOIN Автор
ON Книга.КодАвтора = Автор.КодАвтора
GROUP BY Автор.Фамилия
HAVING Count(Книга.Название)>=All
(SELECT Count(Книга.Название)
FROM Книга)
SELECT Автор.Фамилия
FROM Книга INNER JOIN Автор
ON Книга.КодАвтора = Автор.КодАвтора
WHERE Count(Книга.Название)>=All
(SELECT Count(Книга.Название)
FROM Книга)
Курс
152
Основы SQL
4.
Определить авторов, общая стоимость книг которых,
выпущенных издательством «Мир», составила наименьшую
сумму.
SELECT Автор.Фамилия, Sum(Книга.Цена*Книга.Количество)
AS Стоимость
FROM Книга INNER JOIN Автор
ON Книга.КодАвтора = Автор.КодАвтора
WHERE Книга.Издательство=”Мир”
GROUP BY Автор.Фамилия
HAVING Sum(Книга.Цена*Книга.Количество<=All
(SELECT Sum(Книга.Цена*Книга.Количество)
FROM Книга
WHERE Книга.Издательство=”Мир”
GROUP BY Книга.КодАвтора)
SELECT Автор.Фамилия, Sum(Книга.Цена*Книга.Количество)
AS Стоимость
FROM Книга INNER JOIN Автор
ON Книга.КодАвтора = Автор.КодАвтора
WHERE Книга.Издательство=”Мир”
GROUP BY Автор.Фамилия
HAVING Sum(Книга.Цена*Книга.Количество=MIN(SELECT S
um(Книга.Цена*Книга.Количество)
FROM Книга
WHERE Книга.Издательство=”Мир”
GROUP BY Книга.КодАвтора)
SELECT Автор.Фамилия, Sum(Книга.Цена*Книга.Количество)
AS Стоимость
FROM Книга INNER JOIN Автор
ON Книга.КодАвтора = Автор.КодАвтора
WHERE Книга.Издательство=”Мир”
HAVING Sum(Книга.Цена*Книга.Количество<=All
(SELECT Sum(Книга.Цена*Книга.Количество)
FROM Книга
WHERE Книга.Издательство=”Мир”)
SELECT Автор.Фамилия, Sum(Книга.Цена*Книга.Количество)
AS Стоимость
FROM Книга INNER JOIN Автор
ON Книга.КодАвтора = Автор.КодАвтора
WHERE Книга.Издательство=”Мир”
AND Sum(Книга.Цена*Книга.Количество<=All
(SELECT Sum(Книга.Цена*Книга.Количество)
FROM Книга
WHERE Книга.Издательство=”Мир”)
153
Лекция 7 Построение нетривиальных запросов
Вариант 2
1. Даны таблицы Рейс и Билет
Определить список пассажиров, покупающих билеты на
самые дальние рейсы.
SELECT Билет.Фамилия_пассажира,
Рейс.Продолжительность_маршрута
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса = Билет.Номер_рейса
WHERE Рейс.Продолжительность_маршрута=(SELECT
Max(Рейс.Продолжительность_маршрута)
FROM Рейс)
SELECT Билет.Фамилия_пассажира,
Рейс.Продолжительность_маршрута
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса = Билет.Номер_рейса
WHERE Рейс.Продолжительность_маршрута=MAX
(Рейс.Продолжительность_маршрута)
SELECT Билет.Фамилия_пассажира,
Рейс.Продолжительность_маршрута
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса = Билет.Номер_рейса
WHERE Рейс.Продолжительность_маршрута=MAX(SELECT
Рейс.Продолжительность_маршрута
FROM Рейс)
Курс
154
Основы SQL
CREATE TABLE Рейс
(Номер_рейса INT,
Конечный_пункт VARCHAR(30),
Дата_вылета DATETIME,
Продолжительность_ маршрута INT,
Число_билетов INT,
Стоимость MONEY)
CREATE TABLE БИЛЕТ
(Номер_места CHAR(3),
Номер_рейса CHAR(6),
Дата_продажи DATETIME,
Стоимость MONEY,
Фамилия_пассажира VARCHAR(20))
SELECT Билет.Фамилия_пассажира,
Рейс.Продолжительность_маршрута
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса = Билет.Номер_рейса
WHERE Рейс.Продолжительность_маршрута>=ALL(SELECT
Рейс.Продолжительность_маршрута
FROM Рейс)
2. Вывести список пассажиров, не летающих в Самару.
SELECT DISTINCT Билет.Фамилия_пассажира
FROM Билет
WHERE Билет.Фамилия_пассажира NOT IN
(SELECTБилет.Фамилия_пассажира
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса = Билет.Номер_рейса
WHERE Рейс.Конечный_пункт=”Самара”)
SELECT DISTINCT Билет.Фамилия_пассажира
FROM Билет, Рейс
WHERE Рейс.Конечный_пункт<>”Самара”
SELECT DISTINCT Билет.Фамилия_пассажира
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса = Билет.Номер_рейса
WHERE Рейс.Конечный_пункт<>”Самара”)
SELECT DISTINCT Билет.Фамилия_пассажира
FROM Билет
WHERE NOT EXISTS (SELECT Билет.Фамилия_пассажира
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса = Билет.Номер_рейса
WHERE Рейс.Конечный_пункт=”Самара”)
155
Лекция 7 Построение нетривиальных запросов
3. Определить, кто из пассажиров потратил наибольшую
сумму на покупку авиабилетов.
SELECT Билет.Фамилия_пассажира, Sum(Рейс.Стоимость)
AS Sum_Стоимость
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
GROUP BY Билет.Фамилия_пассажира
HAVING Sum(Рейс.Стоимость)>=ALL(SELECT
Sum(Рейс.Стоимость)
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
GROUP BY Билет.Фамилия_пассажира)
SELECT Билет.Фамилия_пассажира, Sum(Рейс.Стоимость)
AS Sum_Стоимость
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
GROUP BY Билет.Фамилия_пассажира
HAVING Sum(Рейс.Стоимость)=MAX(SELECT
Sum(Рейс.Стоимость)
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
GROUP BY Билет.Фамилия_пассажира)
SELECT Билет.Фамилия_пассажира, Sum(Рейс.Стоимость)
AS Sum_Стоимость
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
GROUP BY Билет.Фамилия_пассажира
HAVING Sum(Рейс.Стоимость)>=ALL(SELECT
Sum(Рейс.Стоимость)
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса)
SELECT Билет.Фамилия_пассажира, Sum(Рейс.Стоимость)
AS Sum_Стоимость
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
WHERE Sum(Рейс.Стоимость)>=ALL(SELECT
Sum(Рейс.Стоимость)
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса)
Курс
156
Основы SQL
4. Определить пассажиров, реже других летающих в Москву.
SELECT Билет.Фамилия_пассажира,
Count(Рейс.Номер_рейса) AS Кол_рейсов
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
WHERE Рейс.Конечный_пункт=”Москва”
GROUP BY Билет.Фамилия_пассажира
HAVING Count(Рейс.Номер_рейса)<=ALL(SELECT
Count(Рейс.Конечный_пункт)
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
WHERE Рейс.Конечный_пункт=”Москва”
GROUP BY Билет.Фамилия_пассажира)
SELECT Билет.Фамилия_пассажира,
Count(Рейс.Номер_рейса) AS Кол_рейсов
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
WHERE Рейс.Конечный_пункт=”Москва”
GROUP BY Билет.Фамилия_пассажира
HAVING Count(Рейс.Номер_рейса)<=MIN(SELECT
Count(Рейс.Конечный_пункт)
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
WHERE Рейс.Конечный_пункт=”Москва”
GROUP BY Билет.Фамилия_пассажира)
SELECT Билет.Фамилия_пассажира,
Count(Рейс.Номер_рейса) AS Кол_рейсов
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
WHERE Рейс.Конечный_пункт=”Москва”
HAVING Count(Рейс.Номер_рейса)<=ALL(SELECT
Count(Рейс.Конечный_пункт)
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
WHERE Рейс.Конечный_пункт=”Москва”)
SELECT Билет.Фамилия_пассажира,
Count(Рейс.Номер_рейса) AS Кол_рейсов
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
WHERE Рейс.Конечный_пункт=”Москва”
AND Count(Рейс.Номер_рейса)<=ALL(SELECT
Count(Рейс.Конечный_пункт)
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса = Рейс.Номер_рейса
WHERE Рейс.Конечный_пункт=”Москва”)
157
Лекция 7 Построение нетривиальных запросов
Вариант 3
1. Даны таблицы Блюдо и Компонент
Определить блюдо, которое можно приготовить быстрее
всех остальных блюд.
SELECT Блюдо.Название_блюда, Блюдо.Время_приготовления
FROM Блюдо
WHERE Блюдо.Время_приготовления=(SELECT
Min(Блюдо.Время_приготовления)
FROM Блюдо)
SELECT Блюдо.Название_блюда, Блюдо.Время_приготовления
FROM Блюдо
WHERE Блюдо.Время_приготовления<=ALL(SELECT
Блюдо.Время_приготовления
FROM Блюдо)
SELECT Блюдо.Название_блюда, Блюдо.Время_приготовления
FROM Блюдо
WHERE Блюдо.Время_приготовления=
Min(Блюдо.Время_приготовления)
SELECT Блюдо.Название_блюда, Блюдо.Время_приготовления
FROM Блюдо
WHERE Блюдо.Время_приготовления=
Min (SELECT Блюдо.Время_приготовления)
FROM Блюдо)
Курс
158
Основы SQL
CREATE TABLE Блюдо
(Название_блюда VARCHAR(20) NOT NULL,
Время_приготовления INT NOT NULL,
Общая_калорийность INT NOT NULL,
Номер_рецепта INT,
Повар VARCHAR(20),
Стоимость MONEY )
CREATE TABLE Компонент
(Название_компонента VARCHAR(20),
Калорийность INT NOTNULL,
Вес FLOAT,
Белки INT,
Блюдо VARCHAR(20),
Углеводы INT,
Стоимость MONEY NOT NULL)
2. Определить, кто из поваров не готовит десерт.
SELECT DISTINCT Блюдо.Повар
FROM Блюдо
WHERE Блюдо.Повар NOT IN (SELECT Блюдо.Повар
FROM Блюдо
WHERE Блюдо.Тип_блюда=”Десерт”
SELECT DISTINCT Блюдо.Повар
FROM Блюдо
WHERE Блюдо.Тип_блюда<>”Десерт”
SELECT DISTINCT Блюдо.Повар
FROM Блюдо
WHERE NOT EXISTS (SELECT Блюдо.Повар
FROM Блюдо
WHERE Блюдо.Тип_блюда=”Десерт”)
SELECT DISTINCT Блюдо.Повар
FROM Блюдо
WHERE Блюдо.Повар<> ALL (SELECT Блюдо.Повар
FROM Блюдо
WHERE Блюдо.Тип_блюда=”Десерт”)
159
Лекция 7 Построение нетривиальных запросов
3. Определить самое калорийное блюдо.
SELECT Компонент.Блюдо,
Sum(Компонент.Калорийность*Компонент.Вес)
AS Общ_калорийность
FROM Компонент
GROUP BY Компонент.Блюдо
HAVING
Sum(Компонент.Калорийность*Компонент.Вес)>=ALL(SELECT
Sum(Компонент.Калорийность*Компонент.Вес)
FROM Компонент
GROUP BY Компонент.Блюдо)
SELECT Компонент.Блюдо,
Sum(Компонент.Калорийность*Компонент.Вес)
AS Общ_калорийность
FROM Компонент
GROUP BY Компонент.Блюдо
HAVING Sum(Компонент.Калорийность*Компонент.Вес) =MAX
(SELECT Sum(Компонент.Калорийность*Компонент.Вес)
FROM Компонент
GROUP BY Компонент.Блюдо)
SELECT Компонент.Блюдо,
Sum(Компонент.Калорийность*Компонент.Вес)
AS Общ_калорийность
FROM Компонент
GROUP BY Компонент.Блюдо
HAVING Sum(Компонент.Калорийность*Компонент.Вес)>=ALL
(SELECT Sum(Компонент.Калорийность*Компонент.Вес)
FROM Компонент)
SELECT Компонент.Блюдо,
Sum(Компонент.Калорийность*Компонент.Вес)
AS Общ_калорийность
FROM Компонент
WHERE Sum(Компонент.Калорийность*Компонент.Вес)>=ALL
(SELECT Sum(Компонент.Калорийность*Компонент.Вес)
FROM Компонент)
Курс
160
Основы SQL