CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    category_id INT,
    price DECIMAL(10, 2)
);

INSERT INTO Products (product_id, product_name, category_id, price)
VALUES
    (1, 'TV', 1, 500.00),
    (2, 'Phone', 1, 800.00),
    (3, 'Laptop', 2, 1200.00),
    (4, 'Headphones', 3, 100.00);


   CREATE TABLE Categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(50)
);

INSERT INTO Categories (category_id, category_name)
VALUES
    (1, 'Electronics'),
    (2, 'Computers'),
    (3, 'Audio');


   CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    city VARCHAR(50)
);

INSERT INTO Customers (customer_id, customer_name, city)
VALUES
    (1, 'John Smith', 'New York'),
    (2, 'Alice Johnson', 'London'),
    (3, 'Michael Brown', 'Paris'),
    (4, 'Maria Rodriguez', 'Berlin');

   CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (customer_id) REFERENCES Customers (customer_id),
    FOREIGN KEY (product_id) REFERENCES Products (product_id)
);

INSERT INTO Orders (order_id, order_date, customer_id, product_id, quantity)
VALUES
    (1, '2023-06-01', 1, 2, 2),
    (2, '2023-06-02', 2, 1, 1),
    (3, '2023-06-03', 3, 3, 1),
    (4, '2023-06-04', 4, 2, 3);


-- Scrivi una query che restituisca il nome del cliente e
-- il numero di ordini effettuati in una determinata città,
-- ordinando i risultati in base al numero di ordini in ordine decrescente.

select c.*, count(o.order_id) as 'N ordini'
from Orders o
right join Customers c on o.customer_id = c.customer_id
where c.city = 'Berlin'
group by c.customer_id
order by count(o.order_id) desc


-- Scrivi una query che restituisca il nome del prodotto e il numero
-- di clienti diversi che lo hanno acquistato, mostrando solo i prodotti con almeno 3 clienti diversi.
select p.product_name, count(c.customer_id) as 'Numero clienti'
from Products p
left join Orders o on o.product_id = o.product_id
left join Customers c on c.customer_id = o.customer_id
group by p.product_id
having count(p.product_id) >= 3