CREATE TABLE Employees (
  employee_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  age INT CHECK (age > 0),
  gender CHAR(1) CHECK (gender IN ('M', 'F'))
);

CREATE TABLE Customers (
  customer_id SERIAL PRIMARY KEY,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  address TEXT,
  phone VARCHAR(15)
);

CREATE TABLE Orders (
  order_id SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  customer_id INT NOT NULL,
  date DATE,
  finish_date DATE,
  status VARCHAR(20),
  FOREIGN KEY (employee_id) REFERENCES Employees(employee_id) ON DELETE CASCADE,
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ON DELETE SET NULL
);

CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(50),
  price INT CHECK (price >= 0),
  quantity INT CHECK (quantity >= 0)
);

CREATE TABLE OrderItems (
  order_item_id SERIAL PRIMARY KEY,
  order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT CHECK (quantity > 0),
  price NUMERIC(10, 2) CHECK (price >= 0),
  FOREIGN KEY (order_id) REFERENCES Orders(order_id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES Products(product_id) ON DELETE SET NULL
);


INSERT INTO Employees VALUES (1, 'John', 'Doe', 25, 'M');
INSERT INTO Employees VALUES (2, 'John', 'Smith', 30, 'M');
INSERT INTO Employees VALUES (3, 'Tes', 'Paul', 21, 'F');
INSERT INTO Employees VALUES (4, 'Lala', 'Blabla', 40, 'F');

SELECT * FROM Employees;

INSERT INTO Customers VALUES (1, 'AAAA', 'DDDDD', 'Street lalal 12', '+111222333');
INSERT INTO Customers VALUES (2, 'QQQ', 'WWWW', 'Street 11 12', '+113333333');
INSERT INTO Customers VALUES (3, 'RRR', 'AASSSDDDD', 'Street 3232 12', '+1166666666');
INSERT INTO Customers VALUES (4, 'FFFF', 'VVVVV', 'Street 4ffff 12', '+119999999');

SELECT * FROM Customers;

INSERT INTO Orders VALUES (1, 1, 1, '2022-12-12', '2022-12-12', 'Pending');
INSERT INTO Orders VALUES (2, 2, 2, '2022-11-11', '2022-12-12', 'Done');
INSERT INTO Orders VALUES (3, 3, 3, '2022-10-10', '2022-12-12', 'In proggress');
INSERT INTO Orders VALUES (4, 4, 4, '2022-09-09', '2022-12-12', 'Canceled');

SELECT * FROM Orders;

INSERT INTO Products VALUES (1, 'Phone', 'Electronic', 10, 100);
INSERT INTO Products VALUES (2, 'Notebooks', 'Electronic', 20, 200);
INSERT INTO Products VALUES (3, 'Kinder', 'Toys', 5, 150);
INSERT INTO Products VALUES (4, 'Bread', 'Food', 10, 100);

SELECT * FROM Products;

INSERT INTO OrderItems VALUES (1, 1, 2, 10, 100);
INSERT INTO OrderItems VALUES (2, 2, 1, 30, 153);
INSERT INTO OrderItems VALUES (3, 2, 2, 134, 125);
INSERT INTO OrderItems VALUES (4, 3, 4, 19, 176);

SELECT * FROM OrderItems;

-- Найдите сумму всех заказов для каждого клиента.

SELECT customer_id, SUM(quantity*price) AS total_order_sum
FROM Orders o
JOIN OrderItems oi ON o.order_id = oi.order_id
GROUP BY customer_id;

-- Определите средний возраст всех сотрудников в компании.

SELECT AVG(age) FROM Employees;

-- Подсчитайте общую стоимость всех продуктов на складе.

SELECT SUM(price * quantity) FROM Products;

-- Найдите клиента с самым большим количеством заказов.

SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 1;

--  Определите долю мужчин и женщин среди всех сотрудников.

SELECT gender, COUNT(*) * 100.0 / (SELECT COUNT(*) FROM Employees) AS gender_ratation
FROM Employees
GROUP BY gender;


-- Подсчитайте общую стоимость заказов для каждой категории продуктов.
-- Найдите клиента с самым дорогим заказом.

-- Определите среднюю продолжительность выполнения заказов.

SELECT AVG(finish_date - date) AS avg_order_duration FROM Orders;

-- Найдите сотрудника с наибольшим числом выполненных заказов.

SELECT employee_id, COUNT(order_id) AS completed_orders
FROM Orders
WHERE status = 'Done'
GROUP BY employee_id
ORDER BY completed_orders DESC
LIMIT 1;

-- Определите общую стоимость заказов для каждого года.

-- Найдите все клиенты, у которых имя начинается с буквы "А".

SELECT * FROM Customers WHERE first_name LIKE 'A%';

-- Найдите все продукты, название которых содержит слово "кофе".

SELECT * FROM Products WHERE name ILIKE '%teb%';

-- Найдите всех сотрудников, фамилия которых заканчивается на "ов".

SELECT * FROM Customers WHERE last_name LIKE '%ов';

-- Найдите все заказы, у которых номер начинается с "2023".

SELECT * FROM Orders WHERE CAST(order_id AS TEXT) LIKE '2%';

-- Найдите всех клиентов, у которых телефон содержит номер "555".

SELECT * FROM Customers WHERE phone ILIKE '%555%';

-- Найдите все продукты, категория которых начинается с "Фрукты".

SELECT * FROM Products WHERE category LIKE 'Фрукты%';

-- Найдите все заказы, у которых дата содержит "2022-12".

SELECT * FROM Orders WHERE date::TEXT LIKE '2022-12%';

-- Найдите всех сотрудников, у которых имя содержит букву "и" и возраст больше 30.

SELECT * FROM Employees WHERE first_name ILIKE '%и%' AND age > 30;

-- Найдите все продукты, название которых начинается с буквы "С" и стоимость меньше 10.

SELECT * FROM Products WHERE name LIKE 'С%' AND price < 10;

-- Найдите все заказы, у которых статус содержит слово "готово".

SELECT * FROM Orders WHERE status ILIKE '%готово%';