Полякова Л.П. Основы SQL. Курс лекций
Подождите немного. Документ загружается.
Вариант 2
1. Даны таблицы Рейс и Билет
Рассчитать общую стоимость билетов для каждого рейса,
5% налог с продаж и общий доход рейса.
SELECT Номер_рейса,
Стоимость*Число_билетов AS Общ_стоимость,
Стоимость*Число_билетов 0.05 AS Налог,
Стоимость*Число_билетов 0.95 AS Доход
FROM Рейс
SELECT Номер_рейса,
Стоимость*Число_билетов AS Общ_стоимость,
Общ_стоимость* 0.05 AS Налог, Общ_стоимость *0.95
AS Доход
FROM Рейс
SELECT Номер_рейса,
Общ_стоимость =Стоимость*Число_билетов,
Налог =Общ_стоимость* 0.05,
Доход =Общ_стоимость *0.95
FROM Рейс
SELECT Номер_рейса, @s=Стоимость*Число_билетов,
@n=Стоимость*Число_билетов 0.05,
@d=Стоимость*Число_билетов 0.95
FROM Рейс
131
Лекция 6 Вычисления и подведение итогов в запросах
CREATE TABLE Рейс
(Номер_рейса INT,
Конечный_пункт VARCHAR(30),
Дата_вылета DATETIME,
Продолжительность_маршрута INT,
Число_билетов INT,
Стоимость MONEY)
CREATE TABLE Билет
(Номер_места CHAR(3),
Номер_рейса CHAR(6),
Дата_продажи DATETIME,
Стоимость MONEY,
Фамилия_пассажира VARCHAR(20))
2.
Определить количество проданных на каждый рейс билетов.
SELECT Рейс.Номер_рейса,
Count(Билет.Фамилия_пассажира) AS Кол_пассажиров
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса =Билет.Номер_рейса
GROUP BY Рейс.Номер_рейса
SELECT Рейс.Номер_рейса,
Кол_пассажиров= Count(Билет.Фамилия_пассажира)
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса =Билет.Номер_рейса
GROUP BY Рейс.Номер_рейса
SELECT Рейс.Номер_рейса,
Count(Билет.Фамилия_пассажира) AS Кол_пассажиров
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса =Билет.Номер_рейса
SELECT Номер_рейса,
Count(Фамилия_пассажира) AS Кол_пассажиров
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса =Билет.Номер_рейса
GROUP BY Номер_рейса
3. На какую сумму были проданы билеты в день вылета?
SELECT Sum(Билет.Стоимость) AS Общ_Стоимость,
Билет.Дата_продажи
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса =Билет.Номер_рейса
WHERE Билет.Дата_продажи=Рейс.Дата_вылета
GROUP BY Билет.Дата_продажи
SELECT Sum(Билет.Стоимость) AS Общ_Стоимость,
Билет.Дата_продажи
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса =Билет.Номер_рейса
GROUP BY Билет.Дата_продажи
HAVING Билет.Дата_продажи=Рейс.Дата_вылета
SELECT Sum(Билет.Стоимость) AS Общ_Стоимость
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса =Билет.Номер_рейса
WHERE Билет.Дата_продажи=Рейс.Дата_вылета
GROUP BY Билет.Дата_продажи
Курс
132
Основы SQL
SELECT Билет.Дата_продажи,
Общ_Стоимость =Sum(Билет.Стоимость)
FROM Рейс INNER JOIN Билет
ON Рейс.Номер_рейса =Билет.Номер_рейса
WHERE Билет.Дата_продажи=Рейс.Дата_вылета
GROUP BY Билет.Дата_продажи
4. Вывести список городов, куда было продано более 200
билетов на сумму, превышающую 10000 руб.
SELECT Рейс.Конечный_пункт,
Count(Билет.Фамилия_пассажира) AS Кол_пассажиров,
Sum(Рейс.Стоимость) AS Общ_Стоимость
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса =Рейс.Номер_рейса
GROUP BY Рейс.Конечный_пункт
HAVING Count(Билет.Фамилия_пассажира)>=200 AND
Sum(Рейс.Стоимость)>10000
SELECT Рейс.Конечный_пункт,
Count(Билет.Фамилия_пассажира) AS Кол_пассажиров,
Sum(Рейс.Стоимость) AS Общ_Стоимость
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса =Рейс.Номер_рейса
GROUP BY Рейс.Конечный_пункт
HAVING Кол_пассажиров>=200 AND Общ_Стоимость>10000
SELECT Count(Билет.Фамилия_пассажира),
Sum(Рейс.Стоимость)
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса =Рейс.Номер_рейса
GROUP BY Рейс.Конечный_пункт
HAVING Count(Билет.Фамилия_пассажира)>=200 AND
Sum(Рейс.Стоимость)>10000
SELECT Рейс.Конечный_пункт,
Count(Билет.Фамилия_пассажира) AS Кол_пассажиров,
Sum(Рейс.Стоимость) AS Общ_Стоимость
FROM Билет INNER JOIN Рейс
ON Билет.Номер_рейса =Рейс.Номер_рейса
HAVING Count(Билет.Фамилия_пассажира)>=200 AND
Sum(Рейс.Стоимость)>10000
133
Лекция 6 Вычисления и подведение итогов в запросах
Вариант 3
1. Даны таблицы Блюдо и Компонент
Рассчитать общую стоимость компонентов блюда и
калорийность каждого компонента.
Название_компонента, Стоимость*Вес AS Общ_Стоимость,
Калорийность*Вес AS Общ_калорийность
FROM Компонент
SELECT Название_компонента, Стоимость*Вес,
Калорийность*Вес
FROM Компонент
SELECT Название_компонента,
Общ_Стоимость =Стоимость*Вес,
Общ_калорийность =Калорийность*Вес
FROM Компонент
SELECT Название_компонента, @s=Стоимость*Вес,
@k=Калорийность*Вес
FROM Компонент
Курс
134
Основы SQL
CREATE TABLE Блюдо
(Название_блюда VARCHAR(20) NOT NULL,
Время_приготовления INT NOT NULL,
Общая_калорийность INT NOT NULL,
Номер_рецепта INT,
Повар VARCHAR(20),
Стоимость MONEY )
CREATE TABLE Компонент
(Название_компонента VARCHAR(20),
Калорийность INT NOT NULL,
Вес FLOAT,
Белки INT,
Блюдо VARCHAR(20),
Углеводы INT,
Стоимость MONEY NOT NULL)
2.
Для каждого повара определить количество используемых
компонентов и их общую стоимость
SELECT Блюдо.Повар,
Sum(Компонент.Вес*Компонент.Стоимость) AS Общ_Стоимость,
Count(Компонент.Название_компонента) AS Кол_компонент
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
GROUP BY Блюдо.Повар
SELECT Блюдо.Повар,
Общ_Стоимость
=Sum(Компонент.Вес*Компонент.Стоимость),
Кол_компонент =Count(Компонент.Название_компонента)
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
GROUP BY Блюдо.Повар
SELECT Блюдо.Повар,
Sum(Компонент.Вес*Компонент.Стоимость) AS Общ_Стоимость,
Count(Компонент.Название_компонента) AS Кол_компонент
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
SELECT Sum(Вес* Стоимость) AS Общ_Стоимость,
Count(Название_компонента) AS Кол_компонент
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
GROUP BY Повар
3. Для каждого повара определить общую калорийность
десертов и количество компонентов в каждом из них.
SELECT Блюдо.Повар,
Sum(Компонент.Вес*Компонент.Калорийность) AS
Общ_калорийность, Count(Компонент.Название_компонента)
AS Кол_компонент
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
WHERE Блюдо.Тип_блюда=”Десерт”
GROUP BY Блюдо.Повар
SELECT Блюдо.Повар,
Sum(Компонент.Вес*Компонент.Калорийность) AS
Общ_калорийность, Count(Компонент.Название_компонента)
AS Кол_компонент
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
GROUP BY Блюдо.Повар
HAVING Блюдо.Тип_блюда=”Десерт”
135
Лекция 6 Вычисления и подведение итогов в запросах
SELECT Sum(Компонент.Вес*Компонент.Калорийность),
Count(Компонент.Название_компонента)
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
WHERE Блюдо.Тип_блюда=”Десерт”
GROUP BY Блюдо.Повар
SELECT Блюдо.Повар,
Sum(Компонент.Вес*Компонент.Калорийность) AS
Общ_калорийность, Count(Компонент.Название_компонента)
AS Кол_компонент
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
WHERE Блюдо.Тип_блюда=”Десерт”
4. Определить поваров, которые на приготовление каждого
из своих блюд тратят в среднем не более 10 мин, а общий
вес блюд превосходит 500 г.
SELECT Блюдо.Повар, Avg(Блюдо.Время_приготовления)
AS Ср_время, Sum(Компонент.Вес) AS Общ_вес
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
GROUP BY Блюдо.Повар
HAVING Avg(Блюдо.Время_приготовления)<10 AND
Sum(Компонент.Вес)>500
SELECT Блюдо.Повар, Avg(Блюдо.Время_приготовления)
AS Ср_время, Sum(Компонент.Вес) AS Общ_вес
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
GROUP BY Блюдо.Повар
HAVING Ср_время <10 AND Общ_вес >500
SELECT Блюдо.Повар, Avg(Блюдо.Время_приготовления)
AS Ср_время, Sum(Компонент.Вес) AS Общ_вес
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
GROUP BY Блюдо.Повар
WHERE Avg(Блюдо.Время_приготовления<10 AND
Sum(Компонент.Вес>500
SELECT Блюдо.Повар, Avg(Блюдо.Время_приготовления)
AS Ср_время, Sum(Компонент.Вес) AS Общ_вес
FROM Блюдо INNER JOIN Компонент
ON Блюдо.Название_блюда = Компонент.Блюдо
WHERE Avg(Блюдо.Время_приготовления<10 AND
Sum(Компонент.Вес>500
Курс
136
Основы SQL
Вариант 4
1. Даны таблицы Город и Разговор
Рассчитать стоимость каждого разговора.
SELECT Разговор.Фамилия, Город.Название,
Город.Тариф*Разговор.Продолжительность AS Стоимость
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
SELECT Разговор.Фамилия, Город.Название,
Город.Тариф*Разговор.Продолжительность
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
SELECT Разговор.Фамилия, Город.Название,
Стоимость=Город.Тариф*Разговор.Продолжительность
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
SELECT Разговор.Фамилия, Город.Название,
@s=Город.Тариф*Разговор.Продолжительность
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
137
Лекция 6 Вычисления и подведение итогов в запросах
CREATE TABLE Город
(Код_Города INT,
Название VARCHAR(20) NOT NULL,
Тариф MONEY,
Регион VARCHAR(20))
CREATE TABLE Разговор
(Код_Разговора INT ,
Код_Города INT NOT NULL,
Фамилия IVARCHAR(20),
Дата DATETIME NOT NULL,
Продолжительность INT NOT NULL)
2. Определить среднюю стоимость разговоров в каждом
регионе.
SELECT Город.Регион,
Avg(Город.Тариф*Разговор.Продолжительность)
AS Ср_Стоимость
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
GROUP BY Город.Регион
SELECT Avg(Тариф* Продолжительность) AS Ср_Стоимость
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
GROUP BY Город.Регион
SELECT Город.Регион, Ср_Стоимость=
Avg(Город.Тариф*Разговор.Продолжительность)
FROM Город INNER JOIN Разговор ON Город.КодГорода
= Разговор.КодГорода
GROUP BY Город.Регион
SELECT Avg(Город.Тариф*Разговор.Продолжительность)
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
GROUP BY Город.Регион
3. Определить количество и общую продолжительность
разговоров для каждого региона, с жителями городов
которого вели телефонные переговоры абоненты, чьи
фамилии начинаются на И.
SELECT Город.Регион, Count(Разговор.КодРазговора) AS
Кол_разговоров, Sum(Разговор.Продолжительность) AS
Общ_Продолжительность
FROM Город INNER JOIN Разговор ON Город.КодГорода
= Разговор.КодГорода
WHERE Разговор.Фамилия LIKE ”И%”
GROUP BY Город.Регион
SELECT Город.Регион, Count(Разговор.КодРазговора)
AS Кол_разговоров, Sum(Разговор.Продолжительность)
AS Общ_Продолжительность
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
WHERE Разговор.Фамилия LIKE ”И%”
SELECT Город.Регион, Count(Разговор.КодРазговора)
AS Кол_разговоров, Sum(Разговор.Продолжительность)
AS Общ_Продолжительность
Курс
138
Основы SQL
FROM Город INNER JOIN Разговор ON Город.КодГорода =
Разговор.КодГорода
GROUP BY Город.Регион
HAVING Разговор.Фамилия LIKE ”И%”
SELECT Count(Разговор.КодРазговора),
Sum(Разговор.Продолжительность)
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
WHERE Разговор.Фамилия LIKE ”И%”
GROUP BY Город.Регион
4. Определить фамилии абонентов, общее время разгово-
ров которых не превысило 10 мин, а общая стоимость
оказалась больше 100 руб.
SELECT Разговор.Фамилия,
Sum(Разговор.Продолжительность) AS Общ_Продолжительность,
Sum(Город.Тариф*Разговор.Продолжительность) AS Стоимость
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
GROUP BY Разговор.Фамилия
HAVING Sum(Разговор.Продолжительность)<10 AND
Sum(Город.Тариф*Разговор.Продолжительность)>100
SELECT Разговор.Фамилия,
Sum(Разговор.Продолжительность) AS Общ_Продолжительность,
Sum(Город.Тариф*Разговор.Продолжительность) AS Стоимость
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
GROUP BY Разговор.Фамилия
HAVING Общ_Продолжительность <10 AND Стоимость 100
SELECT Разговор.Фамилия,
Sum(Разговор.Продолжительность) AS Общ_Продолжительность,
Sum(Город.Тариф*Разговор.Продолжительность) AS Стоимость
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
WHERE Sum(Разговор.Продолжительность<10) AND
Sum(Город.Тариф*Разговор.Продолжительность)>100
SELECT Разговор.Фамилия,
Sum(Разговор.Продолжительность) AS Общ_Продолжительность,
Sum(Город.Тариф*Разговор.Продолжительность) AS Стоимость
FROM Город INNER JOIN Разговор
ON Город.КодГорода = Разговор.КодГорода
GROUP BY Разговор.Фамилия
WHERE Sum(Разговор.Продолжительность<10) AND
Sum(Город.Тариф*Разговор.Продолжительность)>100
139
Лекция 6 Вычисления и подведение итогов в запросах
Курс
140
Основы SQL