CREATE TABLE Cars (
    car_id INT PRIMARY KEY,
    brand VARCHAR(50),
    model VARCHAR(50),
    year INT,
    rental_price DECIMAL(10, 2),
    available BOOLEAN,
    location_id INT,
    FOREIGN KEY (location_id) REFERENCES Locations (location_id)
);

INSERT INTO Cars (car_id, brand, model, year, rental_price, available, location_id)
VALUES
    (1, 'Ford', 'Focus', 2019, 50.00, true, 1),
    (2, 'Toyota', 'Corolla', 2020, 60.00, true, 2),
    (3, 'Honda', 'Civic', 2021, 55.00, true, 3),
    (4, 'Volkswagen', 'Golf', 2018, 45.00, false, 2);

   CREATE TABLE Rentals (
    rental_id INT PRIMARY KEY,
    rental_date DATE,
    customer_id INT,
    car_id INT,
    rental_days INT,
    total_cost DECIMAL(10, 2),
    location_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers (customer_id),
    FOREIGN KEY (car_id) REFERENCES Cars (car_id),
    FOREIGN KEY (location_id) REFERENCES Locations (location_id)
);

INSERT INTO Rentals (rental_id, rental_date, customer_id, car_id, rental_days, total_cost, location_id)
VALUES
    (1, '2023-06-01', 1, 2, 5, 300.00, 2),
    (2, '2023-06-03', 2, 1, 3, 180.00, 1),
    (3, '2023-06-04', 3, 3, 2, 110.00, 3),
    (4, '2023-06-05', 4, 2, 7, 420.00, 2);

   CREATE TABLE Locations (
    location_id INT PRIMARY KEY,
    city VARCHAR(50),
    address VARCHAR(100)
);

insert into Rentals (rental_id, rental_date, customer_id, car_id, rental_days, total_cost, location_id)
values
    (8, '2023-06-12', 3, 2, 5, 320.00, 2);

INSERT INTO Locations (location_id, city, address)
VALUES
    (1, 'New York', '123 Main Street'),
    (2, 'London', '456 Park Avenue'),
    (3, 'Paris', '789 Elm Road'),
    (4, 'Berlin', '321 Oak Lane');


   CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50),
    phone_number VARCHAR(20)
);

INSERT INTO Customers (customer_id, first_name, last_name, email, phone_number)
VALUES
    (1, 'John', 'Smith', '[email protected]', '123-456-7890'),
    (2, 'Alice', 'Johnson', '[email protected]', '987-654-3210'),
    (3, 'Michael', 'Brown', '[email protected]', '555-123-4567'),
    (4, 'Maria', 'Rodriguez', '[email protected]', '111-222-3333');

INSERT INTO Customers (customer_id, first_name, last_name, email, phone_number)
VALUES
    (10, 'Mario', 'Catalasso', '[email protected]', '323-321-9999');

-- Esercizio 1:
-- Scrivi una query che restituisca i dettagli completi dei noleggi effettuati, inclusi i dettagli del cliente e le informazioni sull'auto noleggiata.
select r.*, cc.*, c.*
from Rentals r
inner join Cars c on c.car_id
inner join Customers cc on cc.customer_id

-- Esercizio 2:
-- Scrivi una query che restituisca il conteggio totale dei noleggi effettuati da ciascun cliente.
select c.first_name, c.last_name, count(r.rental_id)
from Rentals r
right join Customers c on c.customer_id = r.customer_id
group by c.customer_id


-- Esercizio 3:
-- Scrivi una query che restituisca l'auto con il prezzo di noleggio più alto.
select c.*
from Cars c
order by rental_price desc
limit 1

-- Esercizio 4:
-- Scrivi una query che restituisca i dettagli dei clienti che hanno effettuato almeno 3 noleggi.
select c.*
from Customers c
left join Rentals r on r.customer_id = c.customer_id
group by c.customer_id
having count(r.rental_id) >= 3

-- Esercizio 5:
-- Scrivi una query che restituisca i dettagli delle auto disponibili presso una determinata posizione,
-- ordinati per prezzo di noleggio crescente.
select c.*
from Cars c
inner join Locations l on c.location_id = l.location_id and l.location_id = 2
order by c.rental_price


-- Esercizio 6:
-- Scrivi una query che restituisca i dettagli delle auto noleggiate dai clienti con almeno 2 noleggi,
-- mostrando solo le auto con il prezzo di noleggio massimo.
select  c.*, c2.customer_id
from Cars c
inner join Rentals r on c.car_id = r.car_id
inner join Customers c2 on c2.customer_id = r.customer_id
where (
        select count(*)
        from Customers c3
        left join Rentals r on r.customer_id  = c3.customer_id
        where c3.customer_id = c2.customer_id) > 2
order by c.rental_price desc
limit 1

-- Esercizio 7:
-- Scrivi una query che restituisca il conteggio delle auto disponibili presso ciascuna posizione,
-- ordinando i risultati in modo decrescente per il numero di auto disponibili.
select l.city, count(c.car_id)
from Cars c
right join Locations l on c.location_id = l.location_id
group by l.city
order by count(c.car_id) desc

-- Esercizio 8:
-- Scrivi una query che restituisca i dettagli dei noleggi effettuati in una data specifica, inclusi i
-- dettagli del cliente, le informazioni sull'auto noleggiata e la posizione di ritiro.
select r.*, c.*, cc.*, l.*
from Rentals r
inner join Cars c on c.car_id = r.car_id
inner join Customers cc on r.customer_id = cc.customer_id
inner join Locations l on l.location_id = c.location_id