CREATE DATABASE industrial_1;

USE industrial_1;

CREATE TABLE departments (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(255)
);

CREATE TABLE customers (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255)
 );

CREATE TABLE employees (
id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(255),
department_id INT REFERENCES departments(id),
salary FLOAT
);

CREATE TABLE orders (
id INT IDENTITY(1,1) PRIMARY KEY,
customer_id INT REFERENCES customers(id),
order_date DATE,
total_amount FLOAT
);
=========================================================
USE industrial_1;

INSERT INTO departments (name)
VALUES
('IT'),
('Sales'),
('Marketing');

INSERT INTO customers (name, email)
VALUES
('Алексей', 'alexey@example.com'),
('Елена', 'elena@example.com'),
('Дмитрий', 'dmitriy@example.com'),
('Ольга', 'olga@example.com');

INSERT INTO employees (name, department_id, salary)
VALUES
('Иван Иванов', 1, 50000),
('Петр Петров', 2, 60000),
('Анна Сидорова', 1, 45000),
('Мария Кузнецова', 3, 70000),
('Сергей Смирнов', 2, 55000);

INSERT INTO orders (costumer_id, order_date, total_amount)
VALUES
(1, '2024-10-01', 1000),
(2, '2024-10-15', 2500),
(3, '2024-10-22', 800),
(4, '2024-09-18', 1200),
(5, '2024-10-29', 3000);
=======================================================
--1)Выведите список всех сотрудников и их зарплаты, отсортированных по зарплате в порядке убывания.
USE industrial_1;

SELECT name, salary
FROM employees
ORDER BY salary DESC;

--2)Найдите количество заказов каждого клиента и отсортируйте результаты по количеству заказов в порядке убывания.
USE industrial_1;

SELECT customers.name AS 'Заказчик',
COUNT(orders.id)AS 'Заказы'
FROM customers
LEFT JOIN orders
ON customers.id=orders.customer_id
GROUP BY customers.name ORDER BY 'Заказы' DESC;

--3)Выведите список всех отделов и среднюю зарплату сотрудников в каждом отделе.
USE industrial_1;

SELECT departments.name AS 'Отделы',
AVG(employees.salary) AS 'Средняя зп'
FROM departments
LEFT JOIN employees
ON departments.id = employees.department_id
GROUP BY departments.name

--4)Найдите клиентов, которые не сделали ни одного заказа.
USE industrial_1;

SELECT DISTINCT customers.name AS 'Заказчик'
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
WHERE orders.id IS NULL;

--5)Выведите список всех сотрудников, работающих в отделе "Sales", и их зарплаты.
USE industrial_1;

SELECT DISTINCT employees.name AS 'Заказчик', employees.salary AS 'Зп'
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id
WHERE departments.name = 'Sales';

--6)Найдите самый дорогой заказ каждого клиента.
USE industrial_1;

SELECT customers.name AS 'Заказчик',
MAX(orders.total_amount) AS 'Маx_Стоимость'
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.name;

--7)Выведите список всех клиентов, которые сделали заказы в последние 30 дней.
USE industrial_1;

SELECT customers.name AS 'Заказчик'
FROM customers
JOIN orders
ON customers.id = orders.customer_id
WHERE orders.order_date >= GETDATE() - 30;

--8)Найдите отделы, в которых работает более 5 сотрудников.
--Вместо (пяти) больше (нуля), чтобы работал запрос.
USE industrial_1;

SELECT departments.name AS 'Отдел'
FROM departments
JOIN employees ON departments.id = employees.department_id
GROUP BY departments.name
HAVING COUNT(employees.id) > 0;

--9)Выведите список всех клиентов и общую сумму их заказов.
USE industrial_1;

SELECT customers.name AS 'Заказчик',
SUM(orders.total_amount) AS 'Сумма заказа'
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id
GROUP BY customers.name;

--10)Найдите сотрудников, чьи зарплаты выше средней зарплаты в их отделе.
USE industrial_1;

SELECT employees.name AS 'Сотрудник',
employees.salary AS 'Зарплата сотрудника',
departments.name AS 'Отдел',
da.average_salary AS 'Средняя зп в отделе'
FROM employees
JOIN departments ON employees.department_id = departments.id
JOIN (
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
)
da ON employees.department_id = da.department_id
WHERE employees.salary > da.average_salary;