USE Carsh
GO
--1) Запросы с выбором строк по условию
--запрос с использованием операторов сравнения
--Вывести информацию о тарифах, цена за час которых больше 60
SELECT *
 FROM Тариф
 WHERE Цена_за_час > 60
--запрос с использованием логического оператора AND
--Вывести госномер и год выпуска автомобилей марки 'Volkswagen', модели 'Polo'
SELECT Госномер, Год_выпуска
 FROM Автомобиль
 WHERE Марка = 'Volkswagen' AND Модель = 'Polo'
--запрос с использованием логического оператора OR
--Вывести госномер, марку, модель, год выпуска автомобилей марок 'Renault',
--                                                               'Hyundai'
SELECT Госномер, Марка, Модель, Год_выпуска
 FROM Автомобиль
 WHERE Марка = 'Renault' OR Марка = 'Hyundai'
--запрос с использованием логического оператора NOT
--Вывести названия тарифов, при использовании которых не предусмотрена страховка
SELECT Название
 FROM Тариф
 WHERE NOT Страховка = 'Без страховки'
--запрос на использование выражений над столбцами
--Вывести названия тарифов и цену за час со скидкой в 20%
SELECT Название, CAST(Цена_за_час * 0.8 AS int) AS [Цена за час со скидкой]
 FROM Тариф
--запрос с проверкой на принадлежность множеству
--Вывыести информацию о поездках, совершенных на автомобилях с id 1, 2, 3
SELECT *
 FROM Поездка
 WHERE Автомобиль IN (1, 2, 3)
--запрос с проверкой на принадлежность диапазону значений
--Вывести информацию о тарифах, цена за час которых не меньше 70 и не больше 120
SELECT *
 FROM Тариф
 WHERE Цена_за_час BETWEEN 70 AND 120
--запрос с проверкой на соответствие шаблону
--Вывести госномер, марку, модель автомобилей, госномер которых начинается с 'О' или с 'У'
SELECT Госномер, Марка, Модель
 FROM Автомобиль
 WHERE Госномер LIKE '[ОУ]%'
--запрос с проверкой на неопределенное значение
--Вывести названия тарифов, у которых отсутсвует описание
SELECT Название
 FROM Тариф
 WHERE Описание IS NULl
--2) Запросы на теоретико-множественные реляционные операции
--запрос на объединение таблиц
--Вывести ФИО клиентов, проживающих в Рязани или Санкт-Петербурге
SELECT ФИО
 FROM Клиент
 WHERE Адрес LIKE 'г. Рязань,%'
UNION
SELECT ФИО
 FROM Клиент
 WHERE Адрес LIKE 'г. Санкт-Петербург,%'
--запрос на пересечение таблиц
--Вывести id клиентов, которые совершали поездки и на автомобиле с id = 3,
--                                               и на автомобиле с id = 5
SELECT Клиент
 FROM Поездка
 WHERE Автомобиль = 3
INTERSECT
SELECT Клиент
 FROM Поездка
 WHERE Автомобиль = 5
--запрос на вычитание таблиц
--Вывести id автомобилей, на которых не было совершено ни одной поездки
SELECT id_Автомобиля
 FROM Автомобиль
EXCEPT
SELECT Автомобиль
 FROM Поездка
--запрос с использованием декартового произведения
--Вывести id_Поездки, ФИО и телефон клиента
SELECT id_Поездки, ФИО, Телефон
 FROM Клиент, Поездка
 WHERE Клиент.id_Клиента = Поездка.Клиент
--3) Многотабличные запросы
--запрос с использованием соединения двух таблиц по равенству и условием отбора
--Вывести ФИО клиентов, проживающих в Рязани
--и id Автомобилей, на которых ими совершались поездки.
SELECT ФИО, Автомобиль
 FROM Клиент JOIN Поездка ON Клиент.id_Клиента = Поездка.Клиент
 WHERE Адрес LIKE 'г. Рязань,%'
--запрос с использованием соединения трех таблиц по равенству и условием отбора
--Вывести id Поездки; ФИО клиента, фамилия, которого 'Шишкина';
--марку и модель автомобиля, на котором им соверашалсь поездка,
SELECT id_Поездки, ФИО, Марка, Модель
 FROM Автомобиль JOIN (Клиент JOIN Поездка ON Клиент.id_Клиента = Поездка.Клиент) ON Автомобиль.id_Автомобиля = Автомобиль
 WHERE ФИО LIKE 'Шишкина%'
--запрос с использованием соединения по четырем таблицам
--Вывести информацию о поездках: Название тарифа, страховка, Госномер автомобиля, ФИО клиента
SELECT Название, Страховка, Госномер, Фио
 FROM Тариф JOIN (Автомобиль JOIN (Клиент JOIN Поездка ON Клиент.id_Клиента = Поездка.Клиент) ON Автомобиль.id_Автомобиля = Автомобиль) ON Тариф.id_Тарифа = Тариф
--запрос с использованием левого внешнего соединения
--Вывести информацию о поездке: id_Поездки, госномер автомобиля
SELECT id_Поездки, Госномер
 FROM Поездка LEFT JOIN Автомобиль ON Автомобиль.id_Автомобиля = Поездка.Автомобиль
--запрос на использование правого внешнего соединения
--Вывести информацию о поездке: id_Поездки, номер ВУ клиента
SELECT Номер_ВУ, id_Поездки
 FROM Клиент RIGHT JOIN Поездка ON Поездка.Клиент = Клиент.id_Клиента
--- запрос с использованием функции COUNT
--Вывести ФИО клиента и количество, соверешенных им, поездок
SELECT ФИО, COUNT (ФИО) AS [Совершенные поездки]
 FROM Клиент JOIN Поездка ON Клиент.id_Клиента = Поездка.Клиент
 GROUP BY ФИО
--запрос с использованием функции SUM
--Вывести Марку автомобиля и количество часов поездок, совершенных на автомобилях данной марки
SELECT Марка, SUM(DATEDIFF(hour,Дата_время_начала, Дата_время_окончания)) AS [Время поездки в часах]
 FROM Тариф JOIN (Автомобиль JOIN Поездка ON Автомобиль.id_Автомобиля = Поездка.Автомобиль) ON Тариф.id_Тарифа = Тариф
 GROUP BY Марка
--3 запроса с использованием временных функций
--Вывести ФИО клиента и количество дней с момента его последней поездки относительно текущей даты.
SELECT ФИО, DATEDIFF(DAY, Дата_время_окончания, SYSDATETIME()) AS [Прошло дней с момента поездки]
 FROM Клиент JOIN Поездка ON Клиент.id_Клиента = Поездка.Клиент
--Вывести информацию о поездке: ФИО клиента, id Автомобиля, Дату, время начала и окончания поездки в RU формате
SELECT ФИО, Автомобиль, FORMAT(Дата_время_начала, 'D', 'ru-RU') + FORMAT(Дата_время_начала, N'HH:mm:ss'),
                        FORMAT(Дата_время_окончания, 'D', 'ru-RU') + FORMAT(Дата_время_окончания, N'HH:mm:ss')
 FROM Клиент JOIN Поездка ON Клиент.id_Клиента = Поездка.Клиент
--Вывести информацию о среднем количестве часов поездок, совершенных на автомобиле конкретной марки
SELECT Марка, AVG(DATEDIFF(HOUR, Дата_время_начала, Дата_время_окончания))
 FROM Автомобиль JOIN Поездка ON Автомобиль.id_Автомобиля = Поездка.Автомобиль
 GROUP BY Марка
--запрос с использованием группировки по одному столбцу
--Вывести количество поездок, совершенных на автомобиле каждой марки
SELECT Марка, COUNT(Марка) AS [Количество поездок]
 FROM Автомобиль JOIN Поездка ON Автомобиль.id_Автомобиля = Поездка.Автомобиль
 GROUP BY Марка
--запрос на использование группировки по нескольким столбцам
--Вывести количество поездок, совершенных на автомобиле каждой Марки и модели
SELECT Марка, Модель, COUNT(Марка) AS [Количество поездок]
 FROM Автомобиль JOIN Поездка ON Автомобиль.id_Автомобиля = Поездка.Автомобиль
 GROUP BY Марка, Модель
--запрос с использованием условия отбора групп HAVING
--Вывести количество поездок, совершенных на автомобиле каждой Марки и модели,
--на которых совершено более одной поездки
SELECT Марка, COUNT(Марка) AS [Количество поездок]
 FROM Автомобиль JOIN Поездка ON Автомобиль.id_Автомобиля = Поездка.Автомобиль
 GROUP BY Марка
 HAVING COUNT(Марка) > 1
--запрос с использованием сортировки по столбцу
--Вывести Марку автомобиля и количество часов поездок, совершенных на автомобилях данной марки
--в порядке возрастания часов
SELECT Марка, SUM(DATEDIFF(hour,Дата_время_начала, Дата_время_окончания)) AS [Время поездки в часах]
 FROM Тариф JOIN (Автомобиль JOIN Поездка ON Автомобиль.id_Автомобиля = Поездка.Автомобиль) ON Тариф.id_Тарифа = Тариф
 GROUP BY Марка
 ORDER BY [Время поездки в часах]
 --4) Запросы на модификацию таблиц
 /*
--запрос на добавление новых данных в таблицу
--Добавить в таблицу Автомобиль новое авто 'Mercedes E200' с госномером К837ВТ71 2018 года выпуска
INSERT INTO Автомобиль (Госномер, Марка, Модель, Год_выпуска)
       VALUES ('К837ВТ71', 'Mercedes', 'E200', '01-01-2018')*/
--!!!запрос на добавление добавление новых данных по результатам выполненного запроса
/*
--запрос на обноление существующих данных в таблице
--Уменьшить цену за час тарифов на 10%
UPDATE Тариф
 SET Цена_за_час = Цена_за_час * 0.9

--запрос на изменение обновление существующих данных по результатам подзапроса во фразе WHERE
--Увеличить цену за час на 15% у тарифов, по которым совершались поездки
UPDATE Тариф
 SET Цена_за_час = Цена_за_час * 1.15
 WHERE id_Тарифа in (SELECT Тариф
                      FROM Поездка)

--запрос на удаление существующих данных
--Удалить автомобиль с госномером К837ВТ71
DELETE Автомобиль
 WHERE Госномер = 'К837ВТ71'

 --запрос на удаление существующих данных из одной таблицы на основе связных с ней таблиц
 --Удалить информацию о поездке клиента с номером ВУ 4044410564, начатой 21.10.2020 в период 21:30 - 22:30.

 DELETE Поездка
  FROM Поездка JOIN Клиент ON Поездка.Клиент = Клиент.id_Клиента
  WHERE Номер_ВУ = '4044410564' AND Дата_время_начала >= '21-10-2020 21:30:00' AND Дата_время_окончания <= '21-10-2020 22:30:00'

 --запрос на удаление существующих данных по результатам подзапроса во фразе WHERE
 --Удалить информацию о поездке на автомобиле с госномером Т422ХН190, начатой 13.10.2020 в период 08:30 - 9:00.

 DELETE
  FROM Поездка
  WHERE Поездка.Автомобиль IN (SELECT id_Автомобиля
                                FROM Автомобиль
                                WHERE Госномер = 'Т422ХН190') AND Дата_время_начала >= '13.10.2020 08:30'AND Дата_время_начала <= '13.10.2020 9:00'
*/
--5) Многотабличные запросы с использованием подзапросов
--3 запроса с использованием операций сравнения
--Вывести id Поездок, совершенных по тарифам, цена за час которых больше средней.
SELECT id_Поездки
 FROM Поездка JOIN Тариф ON Поездка.Тариф = Тариф.id_Тарифа
 WHERE Цена_за_час > (SELECT AVG(Цена_за_час)
                       FROM Тариф)
--Вывести id Поездок, совершенных на автомобилях, год выпуска которых не равен минимальному
SELECT id_Поездки
 FROM Поездка JOIN Автомобиль ON Поездка.Автомобиль = Автомобиль.id_Автомобиля
 WHERE Год_выпуска != (SELECT MIN(Год_выпуска)
                        FROM Автомобиль)
--Вывести id Поездок, совершенных на автомобилях, год выпуска которых меньше среднего
SELECT id_Поездки
 FROM Поездка JOIN Автомобиль ON Поездка.Автомобиль = Автомобиль.id_Автомобиля
 WHERE Год_выпуска < (SELECT AVG(Год_выпуска)
                        FROM Автомобиль)
--запрос с использованием операции ANY
--Вывести информацию о поездках (id_Поездки, ФИО клиента), совершенных клиентами, проживающих в г.Рязань или в г.Казань
SELECT id_Поездки, ФИО
 FROM Поездка JOIN Клиент ON Поездка.Клиент = Клиент.id_Клиента
 WHERE Адрес = ANY (SELECT Адрес
                     FROM Клиент
                     WHERE Адрес LIKE 'г. Рязань,%' or Адрес LIKE 'г. Казань,%')
--запрос с использованием операции ALL
--Вывести id Поездок, совершенных на автомобилях, год выпуска которых не меньше года выпуска каждого автомобиля
SELECT id_Поездки
 FROM Поездка JOIN Автомобиль ON Поездка.Автомобиль = Автомобиль.id_Автомобиля
 WHERE Год_выпуска >= ALL(SELECT Год_выпуска
                           FROM Автомобиль)
--запрос с использованием операции IN
--Вывести госномера автомобилей, на которых совершалась хотя бы одна поездка
SELECT Госномер
 FROM Автомобиль
 WHERE id_Автомобиля IN (SELECT DISTINCT(Автомобиль)
                          FROM Поездка)
--запрос с использованием операции EXISTS
--Вывести ФИО клиентов, которыми была совершена хотя бы одна поездка
SELECT ФИО
 FROM Клиент
 WHERE EXISTS (SELECT *
                FROM Поездка
                WHERE Клиент.id_Клиента = Поездка.Клиент)
--запрос с использованием двух вложенных друг в друга подзапросов
--Вывести ФИО клиентов, совершавших поездки на автомобилях марки 'Renault'
SELECT ФИО
 FROM Клиент
 WHERE id_Клиента IN (SELECT Клиент
                       FROM Поездка
                       WHERE Автомобиль IN ( SELECT id_Автомобиля
                                              FROM Автомобиль
                                              WHERE Марка = 'Renault'))
--запрос с использованием трех вложенных друг в друга подзапросов
--Вывести ФИО клиентов, которые совершали поездки по тарифу с максмальной ценой за час
SELECT ФИО
 FROM Клиент
 WHERE id_Клиента IN (SELECT Клиент
                      FROM Поездка
                      WHERE Тариф IN (SELECT id_Тарифа
                                       FROM Тариф
                                        WHERE Цена_за_час = (SELECT MAX(Цена_за_час)
                                                              FROM Тариф)))
--6) Запросы на изменение структуры существующей таблицы
--запрос на добавление нового столбца к таблице
ALTER TABLE Автомобиль
      ADD Страна_производитель varchar(15)
--запрос на добавление нового ограничения к таблице
ALTER TABLE Автомобиль
      ADD CONSTRAINT check_coutry CHECK (Страна_производитель in (NULL, 'Корея', 'Франция', 'Германия'))
--запрос на удаление нового ограничения к таблице
ALTER TABLE Автомобиль
      DROP CONSTRAINT check_coutry
--запрос на удаление нового столбца к таблице
ALTER TABLE Автомобиль
      DROP COLUMN Страна_производитель
ALTER TABLE Тариф
      ALTER COLUMN Описание varchar(100)