Pastein: -- 3. Вывести два инициала (с точками) и фамилии всех сотрудников (например: W. J. Smith) SELECT LEFT(first

Загрузка данных
-- 3. Вывести два инициала (с точками) и фамилии всех сотрудников (например: W. J. Smith)
SELECT 
    LEFT(first_name, 1) + '. ' + ISNULL(middle_initial + '.', '') + ' ' + last_name AS employee_name
FROM employee;

-- 4. По каждой сделке вывести точную сумму, округленную в большую, меньшую сторону и по правилам математики
SELECT 
    total AS точная_сумма,
    CEILING(total) AS округление_вверх,
    FLOOR(total) AS округление_вниз,
    ROUND(total, 0) AS стандартное_округление
FROM sales_order;

-- 5. Выбрать фамилии всех сотрудников, у которых комиссионные больше зарплаты[span_4](start_span)[span_4](end_span)
SELECT last_name
FROM employee
WHERE commission > salary;

-- 6. Выбрать фамилии всех сотрудников, фамилии которых начинаются на букву 'S'
SELECT last_name
FROM employee
WHERE last_name LIKE 'S%';

-- 7. Выбрать имена сотрудников, которые не являются менеджерами (job_id = 671) и не работают в отделе 13 (SALES в NEW YORK)
SELECT first_name
FROM employee
WHERE job_id <> 671 AND department_id <> 13;

-- 8. Выбрать фамилии всех сотрудников, у которых код должности 670 или 667
SELECT last_name
FROM employee
WHERE job_id IN (670, 667);

-- 9. Выбрать всех сотрудников, имена которых состоят из 6 букв и начинаются на 'MAR'
SELECT *
FROM employee
WHERE first_name LIKE 'MAR___';

-- 10. Выбрать фамилии всех сотрудников, которые поступили на работу после 15 апреля 1985 года
SELECT last_name
FROM employee
WHERE hire_date > '1985-04-15';

-- 11. Выбрать фамилии всех сотрудников, которые поступили на работу в 1985 году
SELECT last_name
FROM employee
WHERE YEAR(hire_date) = 1985;

-- 12. Для каждого сотрудника выбрать количество полных лет работы в фирме
SELECT last_name, 
       DATEDIFF(YEAR, hire_date, GETDATE()) - 
       CASE WHEN FORMAT(GETDATE(), 'MMdd') < FORMAT(hire_date, 'MMdd') THEN 1 ELSE 0 END AS полных_лет
FROM employee;

-- 13. Выбрать количество сотрудников, получающих комиссионные
SELECT COUNT(*) AS count_with_commission
FROM employee
WHERE commission IS NOT NULL AND commission > 0;

-- 14. Выбрать количество и общую сумму сделок, совершенных с покупателем, код которого 104
SELECT COUNT(*) AS количество_сделок, SUM(total) AS общая_сумма
FROM sales_order
WHERE customer_id = 104;

-- 15. Выбрать среднюю зарплату по каждой должности
SELECT job_id, AVG(salary) AS avg_salary
FROM employee
GROUP BY job_id;

-- 16. Выбрать среднюю зарплату продавцов (код должности - 670)
SELECT AVG(salary) AS avg_salary_salesperson
FROM employee
WHERE job_id = 670;

-- 17. Выбрать средние зарплаты продавцов (670) и клерков (667)
SELECT job_id, AVG(salary) AS avg_salary
FROM employee
WHERE job_id IN (670, 667)
GROUP BY job_id;

-- 18. Выбрать коды продуктов, по которым было совершено меньше 10 продаж
SELECT product_id
FROM item
GROUP BY product_id
HAVING SUM(quantity) < 10;

-- 19. Выбрать максимальную зарплату продавцов (код должности 670) по каждому отделу
SELECT department_id, MAX(salary) AS max_salary
FROM employee
WHERE job_id = 670
GROUP BY department_id;

-- 20. Выбрать список сотрудников фирмы в алфавитном порядке
SELECT *
FROM employee
ORDER BY last_name, first_name;

-- 21. Выбрать всю информацию о каждом отделе и месте его расположения
SELECT d.*, l.regional_group
FROM department d
JOIN location l ON d.location_id = l.location_id;

-- 22. Выбрать список фамилий сотрудников по городам, где они работают, с указанием должности
SELECT e.last_name, l.regional_group, j.[function]
FROM employee e
JOIN department d ON e.department_id = d.department_id
JOIN location l ON d.location_id = l.location_id
JOIN job j ON e.job_id = j.job_id;

-- 23. Выбрать всех покупателей, с которыми работает продавец TURNER
SELECT c.*
FROM customer c
JOIN employee e ON c.salesperson_id = e.employee_id
WHERE e.last_name = 'TURNER';

-- 24. Выбрать сотрудников, которые не закреплены за конкретным заказчиком (нет обслуживаемых клиентов)
SELECT *
FROM employee
WHERE employee_id NOT IN (SELECT DISTINCT salesperson_id FROM customer WHERE salesperson_id IS NOT NULL);

-- 25. Выбрать имена продавцов, которые работают более чем с одним покупателем, и названия покупателей
SELECT e.first_name, c.name
FROM employee e
JOIN customer c ON e.employee_id = c.salesperson_id
WHERE e.employee_id IN (
    SELECT salesperson_id
    FROM customer
    WHERE salesperson_id IS NOT NULL
    GROUP BY salesperson_id
    HAVING COUNT(*) > 1
);

-- 26. Выбрать сумму всех продаж, которые обеспечил продавец TURNER
SELECT SUM(so.total) AS total_sales_by_turner
FROM sales_order so
JOIN customer c ON so.customer_id = c.customer_id
JOIN employee e ON c.salesperson_id = e.employee_id
WHERE e.last_name = 'TURNER';

-- 27. Выбрать сумму всех продаж товаров, связанных с теннисом, за лето 1990г.
SELECT SUM(i.total) AS tennis_sales_summer_1990
FROM item i
JOIN product p ON i.product_id = p.product_id
JOIN sales_order so ON i.order_id = so.order_id
WHERE p.description LIKE '%TENNIS%'
  AND so.order_date >= '1990-06-01' AND so.order_date <= '1990-08-31';

-- 28. Выбрать имена и зарплаты самого старого и самого молодого сотрудника фирмы (по дате найма)
SELECT TOP 1 first_name, salary, hire_date, 'Самый старый' AS category FROM employee ORDER BY hire_date ASC
UNION ALL
SELECT TOP 1 first_name, salary, hire_date, 'Самый молодой' AS category FROM employee ORDER BY hire_date DESC;

-- 29. Для каждого продавца вывести разность между его зарплатой и средней зарплатой продавцов в фирме
SELECT last_name, salary, 
       salary - (SELECT AVG(salary) FROM employee WHERE job_id = 670) AS diff_from_avg
FROM employee
WHERE job_id = 670;

-- 30. Вывести имена, средние инициалы и фамилии всех однофамильцев
SELECT first_name, middle_initial, last_name
FROM employee
WHERE last_name IN (
    SELECT last_name 
    FROM employee 
    GROUP BY last_name 
    HAVING COUNT(*) > 1
);

-- 31. Для каждого отдела выбрать процент сотрудников от общего числа и процент зарплаты от общей суммы
SELECT 
    d.name,
    COUNT(e.employee_id) * 100.0 / (SELECT COUNT(*) FROM employee) AS percent_employees,
    ISNULL(SUM(e.salary), 0) * 100.0 / (SELECT SUM(salary) FROM employee) AS percent_salary
FROM department d
LEFT JOIN employee e ON d.department_id = e.department_id
GROUP BY d.department_id, d.name;

-- 32. Выбрать фамилии начальников тех отделов, которые имеют разветвленную структуру (у прямых подчиненных есть свои подчиненные)
SELECT DISTINCT m.last_name
FROM employee m
JOIN employee sub ON m.employee_id = sub.manager_id
WHERE sub.employee_id IN (SELECT DISTINCT manager_id FROM employee WHERE manager_id IS NOT NULL);

-- 33. Выбрать название отдела, заключившего сделки на самую большую сумму, и города
SELECT TOP 1 d.name, l.regional_group, SUM(so.total) AS total_sum
FROM department d
JOIN location l ON d.location_id = l.location_id
JOIN employee e ON d.department_id = e.department_id
JOIN customer c ON e.employee_id = c.salesperson_id
JOIN sales_order so ON c.customer_id = so.customer_id
GROUP BY d.department_id, d.name, l.regional_group
ORDER BY total_sum DESC;

-- 34. Выбрать название товара, дату продажи, цену продажи, когда товары продавались ниже чем за 75% объявленной цены
SELECT p.description, so.order_date, i.actual_price
FROM item i
JOIN product p ON i.product_id = p.product_id
JOIN sales_order so ON i.order_id = so.order_id
JOIN price pr ON p.product_id = pr.product_id AND so.order_date BETWEEN pr.start_date AND ISNULL(pr.end_date, GETDATE())
WHERE i.actual_price < pr.list_price * 0.75;

-- 35. Выбрать общую сумму скидок/продаж, предоставленных покупателю STADIUM SPORTS в 1989г.
SELECT SUM(so.total) AS total_1989
FROM sales_order so
JOIN customer c ON so.customer_id = c.customer_id
WHERE c.name = 'STADIUM SPORTS' AND YEAR(so.order_date) = 1989;

-- 36. Выбрать список сотрудников с указанием фамилии непосредственного начальника
SELECT e.last_name AS employee, m.last_name AS manager
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.employee_id;

-- 37. Для каждого сотрудника вывести разность между его зарплатой и средней зарплатой по его функции (должности)
SELECT e.last_name, e.salary, 
       e.salary - (SELECT AVG(salary) FROM employee WHERE job_id = e.job_id) AS diff_from_job_avg
FROM employee e;

-- 38. Вывести таблицу распределения объема продаж товара DUNK BASKETBALL INDOOR по годам
SELECT YEAR(so.order_date) AS order_year, SUM(i.quantity) AS total_quantity, SUM(i.total) AS total_amount
FROM item i
JOIN product p ON i.product_id = p.product_id
JOIN sales_order so ON i.order_id = so.order_id
WHERE p.description = 'DUNK BASKETBALL INDOOR'
GROUP BY YEAR(so.order_date);

-- 39. Выбрать фамилии тех сотрудников, у которых суммарный доход (зарплата + комиссионные) больше 2000
SELECT last_name
FROM employee
WHERE (salary + ISNULL(commission, 0)) > 2000;

-- 40. Выбрать названия товаров, для которых нынешняя цена увеличилась по сравнению с ценой на 15 декабря 1989г. более чем на 15%
SELECT p.description
FROM product p
JOIN price pr_curr ON p.product_id = pr_curr.product_id AND pr_curr.end_date IS NULL
JOIN price pr_past ON p.product_id = pr_past.product_id AND '1989-12-15' BETWEEN pr_past.start_date AND ISNULL(pr_past.end_date, '9999-12-31')
WHERE pr_curr.list_price > pr_past.list_price * 1.15;

-- 41. Выбрать имена и коды отдела для сотрудников, у которых зарплата выше средней по отделу (без учета комиссионных)
SELECT e.first_name, e.department_id
FROM employee e
WHERE e.salary > (
    SELECT AVG(salary) 
    FROM employee 
    WHERE department_id = e.department_id
);

-- 42. Выбрать имена и зарплаты 10 самых высокооплачиваемых сотрудников фирмы (без учета комиссионных)
SELECT TOP 10 first_name, salary
FROM employee
ORDER BY salary DESC;

-- 43. Выбрать данные для построения графика зависимости средней зарплаты рядового служащего (job_id = 667) от срока работы в фирме (в месяцах)
SELECT DATEDIFF(MONTH, hire_date, GETDATE()) AS months_worked, AVG(salary) AS avg_salary
FROM employee
WHERE job_id = 667
GROUP BY DATEDIFF(MONTH, hire_date, GETDATE());

-- 44. Выбрать продукты в порядке увеличения их прибыльности (относительная разность между реальной и минимальной ценой)
SELECT p.product_id, p.description, 
       AVG((i.actual_price - pr.min_price) / pr.min_price) AS avg_profitability
FROM item i
JOIN product p ON i.product_id = p.product_id
JOIN price pr ON p.product_id = pr.product_id 
GROUP BY p.product_id, p.description
ORDER BY avg_profitability ASC;

-- 45. Для каждого отдела выбрать разность между суммой продаж и суммой зарплаты и комиссионных сотрудников за 1990 год
SELECT d.name, 
       ISNULL((SELECT SUM(so.total) FROM sales_order so JOIN customer c ON so.customer_id = c.customer_id JOIN employee emp ON c.salesperson_id = emp.employee_id WHERE emp.department_id = d.department_id AND YEAR(so.order_date) = 1990), 0) -
       ISNULL((SELECT SUM(salary + ISNULL(commission, 0)) FROM employee WHERE department_id = d.department_id), 0) AS financial_balance_1990
FROM department d;

-- 46. Для каждого продукта выбрать регион, в котором он лучше всего продается
WITH ProductRegionSales AS (
    SELECT p.product_id, p.description, l.regional_group, SUM(i.quantity) AS total_qty,
           ROW_NUMBER() OVER(PARTITION BY p.product_id ORDER BY SUM(i.quantity) DESC) AS rn
    FROM item i
    JOIN product p ON i.product_id = p.product_id
    JOIN sales_order so ON i.order_id = so.order_id
    JOIN customer c ON so.customer_id = c.customer_id
    JOIN employee e ON c.salesperson_id = e.employee_id
    JOIN department d ON e.department_id = d.department_id
    JOIN location l ON d.location_id = l.location_id
    GROUP BY p.product_id, p.description, l.regional_group
)
SELECT description, regional_group, total_qty
FROM ProductRegionSales
WHERE rn = 1;

-- 47. Выбрать фамилии всех прямых и косвенных начальников сотрудника ADAMS
WITH ManagerHierarchy AS (
    SELECT employee_id, last_name, manager_id
    FROM employee
    WHERE last_name = 'ADAMS'
    UNION ALL
    SELECT e.employee_id, e.last_name, e.manager_id
    FROM employee e
    JOIN ManagerHierarchy mh ON e.employee_id = mh.manager_id
)
SELECT last_name
FROM ManagerHierarchy
WHERE last_name <> 'ADAMS';

-- 48. Вывести иерархическую структуру подчиненности в фирме
WITH OrgChart AS (
    SELECT employee_id, last_name, manager_id, 0 AS lvl
    FROM employee
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.last_name, e.manager_id, oc.lvl + 1
    FROM employee e
    JOIN OrgChart oc ON e.manager_id = oc.employee_id
)
SELECT REPT('  ', lvl) + last_name AS hierarchy
FROM OrgChart;

-- 49. Выбрать среднюю зарплату всех подчиненных сотрудника JONES
SELECT AVG(salary) AS avg_subordinate_salary
FROM employee
WHERE manager_id IN (SELECT employee_id FROM employee WHERE last_name = 'JONES');

-- 50. Выбрать список отделов и их менеджеров, включив также отделы без менеджеров и менеджеров без отделов
SELECT d.name AS department_name, e.last_name AS manager_last_name
FROM department d
FULL OUTER JOIN employee e ON d.department_id = e.department_id AND e.job_id = 671;
Больше возможностей при регистрации:
