Untitled

-- Створення таблиці "Клієнти"
CREATE TABLE Клієнти (
    Id INT PRIMARY KEY,
    ПІБ VARCHAR(255),
    Посвідчення_водія VARCHAR(255),
    Дата_отримання_посвідчення_водія DATE
);

-- Створення таблиці "Моделі"
CREATE TABLE Моделі (
    Id INT PRIMARY KEY,
    Назва_моделі VARCHAR(255),
    Марка VARCHAR(255),
    Ціна_за_день_оренди DECIMAL(10, 2),
    Місткість INT,
    Тип_кузова VARCHAR(255)
);

-- Створення таблиці "Автомобілі"
CREATE TABLE Автомобілі (
    Id INT PRIMARY KEY,
    Держномер VARCHAR(20),
    Рік_випуску INT,
    Колір VARCHAR(255),
    Стан VARCHAR(255),
    Пробіг INT,
    Модель_Id INT,
    FOREIGN KEY (Модель_Id) REFERENCES Моделі(Id)
);

-- Створення таблиці "Договори_на_оренду"
CREATE TABLE Договори_на_оренду (
    Id INT PRIMARY KEY,
    Клієнт_Id INT,
    Автомобіль_Id INT,
    Дата_укладення_договору DATETIME,
    Тривалість_договору NUMERIC(3, 0),
    Вартість_договору DECIMAL(10, 2),
    Дата_повернення DATETIME,
    FOREIGN KEY (Клієнт_Id) REFERENCES Клієнти(Id),
    FOREIGN KEY (Автомобіль_Id) REFERENCES Автомобілі(Id)
);

-- Перевірка на термін дії водійського посвідчення
DELIMITER //
CREATE TRIGGER Перевірка_терміну_посвідчення
BEFORE INSERT ON Договори_на_оренду
FOR EACH ROW
BEGIN
    DECLARE дія DATE;
    SELECT Дата_отримання_посвідчення_водія INTO дія FROM Клієнти WHERE Id = NEW.Клієнт_Id;
    IF DATE_ADD(дія, INTERVAL 10 YEAR) < NOW() THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Термін дії посвідчення закінчився';
    END IF;
END;
//
DELIMITER ;


ALTER TABLE Клієнти
ADD COLUMN Новий_стовпець VARCHAR(255);

ALTER TABLE Моделі
MODIFY COLUMN Ціна_за_день_оренди DECIMAL(12, 2);

UPDATE Автомобілі
SET Стан = 'В хорошому стані'
WHERE Id = 1;

DELETE FROM Договори_на_оренду
WHERE Клієнт_Id = 2 AND Автомобіль_Id = 1;

-- Вставка даних в таблицю "Клієнти"
INSERT INTO Клієнти (Id, ПІБ, Посвідчення_водія, Дата_отримання_посвідчення_водія)
VALUES
    (1, 'Іванов Іван Іванович', 'AB123456', '2020-01-01'),
    (2, 'Петров Петро Петрович', 'CD789012', '2015-03-15'),
    (3, 'Сидоров Сидір Сидорович', 'EF345678', '2020-06-20'),
    (4, 'Коваленко Костянтин Олександрович', 'GH012345', '2022-09-10'),
    (5, 'Михайленко Марія Володимирівна', 'IJ678901', '2015-12-05'),
    (6, 'Білоконь Ірина Василівна', 'KL234567', '2018-03-22'),
    (7, 'Олійник Олексій Володимирович', 'MN890123', '2020-07-18'),
    (8, 'Захарчук Зоя Михайлівна', 'OP456789', '2022-01-30'),
    (9, 'Кузнецов Кирило Ігорович', 'QR012345', '2023-04-14'),
    (10, 'Павленко Поліна Сергіївна', 'ST678901', '2017-08-07');


-- Вставка даних в таблицю "Моделі"
INSERT INTO Моделі (Id, Назва_моделі, Марка, Ціна_за_день_оренди, Місткість, Тип_кузова)
VALUES
    (1, 'Civic', 'Honda', 50.00, 5, 'Седан'),
    (2, 'Camry', 'Toyota', 60.00, 5, 'Седан'),
    (3, 'X5', 'BMW', 80.00, 7, 'Позашляховик'),
    (4, 'Accord', 'Honda', 55.00, 5, 'Купе'),
    (5, 'Corolla', 'Toyota', 55.00, 5, 'Хетчбек'),
    (6, '3 Series', 'BMW', 70.00, 5, 'Седан'),
    (7, 'Pilot', 'Honda', 75.00, 8, 'Позашляховик'),
    (8, 'RAV4', 'Toyota', 65.00, 5, 'Позашляховик'),
    (9, '5 Series', 'BMW', 75.00, 5, 'Седан'),
    (10, 'Fit', 'Honda', 45.00, 5, 'Хетчбек');


-- Вставка даних в таблицю "Автомобілі"
INSERT INTO Автомобілі (Id, Держномер, Рік_випуску, Колір, Стан, Пробіг, Модель_Id)
VALUES
    (1, 'АВ1234ВС', 2020, 'Чорний', 'В ідеальному стані', 10000, 1),
    (2, 'ВС5678АВ', 2018, 'Сріблястий', 'Технічно справний', 15000, 2),
    (3, 'СВ9012ІВ', 2019, 'Синій', 'Є невеликі пошкодження', 20000, 3),
    (4, 'АВ3456ВС', 2021, 'Білий', 'В ідеальному стані', 8000, 4),
    (5, 'ВС7890АВ', 2017, 'Червоний', 'Технічно справний', 25000, 5),
    (6, 'СВ1234ІВ', 2019, 'Сірий', 'Є дрібні подряпини', 18000, 6),
    (7, 'АВ6789ВС', 2020, 'Зелений', 'В ідеальному стані', 12000, 7),
    (8, 'ВС0123АВ', 2018, 'Чорний', 'Технічно справний', 22000, 8),
    (9, 'СВ4567ІВ', 2022, 'Сріблястий', 'Новий', 5000, 9),
    (10, 'АВ8901ВС', 2016, 'Чорний', 'Технічно справний', 30000, 10);


-- Вставка даних в таблицю "Договори_на_оренду"
INSERT INTO Договори_на_оренду (Id, Клієнт_Id, Автомобіль_Id, Дата_укладення_договору, Тривалість_договору, Вартість_договору, Дата_повернення)
VALUES
    (1, 1, 2, '2023-01-01 08:00:00', 7, 420.00, '2023-01-08 10:00:00'),
    (2, 2, 1, '2023-02-15 12:00:00', 5, 300.00, '2023-02-20 15:30:00'),
    (3, 3, 3, '2023-03-20 10:30:00', 10, 800.00, '2023-09-24 12:00:00'),
    (4, 4, 4, '2023-04-10 15:45:00', 3, 165.00, '2023-04-13 10:00:00'),
    (5, 5, 5, '2023-05-05 09:30:00', 6, 330.00, '2023-05-11 14:20:00'),
    (6, 6, 6, '2023-06-18 14:15:00', 8, 560.00, '2023-06-26 16:45:00'),
    (7, 7, 7, '2023-07-30 11:00:00', 5, 375.00, '2023-08-04 13:30:00'),
    (8, 8, 8, '2023-08-25 16:30:00', 7, 455.00, '2023-09-01 10:00:00'),
    (9, 9, 9, '2023-09-15 10:45:00', 9, 720.00, '2023-09-24 12:00:00'),
    (10, 10, 10, '2023-10-08 13:20:00', 4, 180.00, '2023-10-12 18:00:00');


SELECT * FROM Клієнти;
SELECT * FROM Моделі;
SELECT * FROM Автомобілі;
SELECT * FROM Договори_на_оренду;


SELECT DISTINCT Марка FROM Моделі;

SELECT * FROM Автомобілі
WHERE Пробіг BETWEEN 10000 AND 20000 AND Пробіг NOT IN (15000);

SELECT * FROM Клієнти
WHERE ПІБ LIKE 'Петр%';

SELECT * FROM Моделі
WHERE Тип_кузова LIKE '%Седан%';

SELECT * FROM Автомобілі
WHERE Держномер LIKE '%ВС';

SELECT * FROM Клієнти
WHERE ПІБ LIKE '___%';

SELECT * FROM Моделі
WHERE Тип_кузова NOT LIKE '%Позашляховик%';

SELECT * FROM Автомобілі
WHERE Держномер REGEXP '^А';

SELECT * FROM Клієнти
WHERE ПІБ REGEXP '^[A-Za-z]+$';

SELECT * FROM Автомобілі
WHERE Держномер REGEXP '^[0-9]{6}$';

SELECT * FROM Моделі
WHERE Назва_моделі REGEXP '[A-Za-z]';

SELECT * FROM Клієнти
WHERE ПІБ REGEXP '[0-9]';

SELECT * FROM Автомобілі
WHERE Колір IS NOT NULL;

SELECT * FROM Автомобілі
ORDER BY Рік_випуску ASC;

SELECT * FROM Клієнти
LIMIT 5;

SELECT AVG(Пробіг) AS Середній_пробіг FROM Автомобілі;

SELECT SUM(Пробіг) AS Загальний_пробіг FROM Автомобілі;

SELECT MIN(Рік_випуску) AS Мінімальний_рік FROM Автомобілі;

SELECT MAX(Ціна_за_день_оренди) AS Максимальна_ціна FROM Моделі;

SELECT COUNT(*) AS Кількість_договорів FROM Договори_на_оренду;

SELECT Моделі.Назва_моделі, AVG(Моделі.Ціна_за_день_оренди) AS Середня_ціна
FROM Моделі
JOIN Автомобілі ON Моделі.Id = Автомобілі.Модель_Id
GROUP BY Моделі.Id
HAVING COUNT(Автомобілі.Id) > 0;

UPDATE Моделі
SET Ціна_за_день_оренди = Ціна_за_день_оренди * 1.1
WHERE Назва_моделі = 'Civic';

SELECT *
FROM Автомобілі
WHERE Id NOT IN (SELECT DISTINCT Автомобіль_Id FROM Договори_на_оренду);

SELECT ПІБ, (SELECT COUNT(*) FROM Договори_на_оренду WHERE Клієнт_Id = Клієнти.Id) AS Кількість_договорів
FROM Клієнти;

UPDATE Моделі
SET Ціна_за_день_оренди = Ціна_за_день_оренди + 5
WHERE Id IN (SELECT Модель_Id FROM Автомобілі WHERE Пробіг > 15000);

DELETE FROM Договори_на_оренду
WHERE Клієнт_Id = (SELECT Id FROM Клієнти WHERE ПІБ = 'Петров Петро Петрович');

SELECT *
FROM Клієнти
WHERE EXISTS (SELECT 1 FROM Договори_на_оренду WHERE Клієнт_Id = Клієнти.Id);