Pastein: -- 1. Выбрать фамилии всех сотрудников, у которых зарплата больше 1500[span_4](start_span)[span_4](end_span). CREATE PROCEDURE sp

Загрузка данных
-- 1. Выбрать фамилии всех сотрудников, у которых зарплата больше 1500[span_4](start_span)[span_4](end_span).
CREATE PROCEDURE sp_Task01 AS BEGIN
    SELECT last_name FROM employee WHERE salary > 1500;
END;
GO

-- 2. Выбрать фамилии всех сотрудников, имеющих код должности 670 и зарплату меньше 1500[span_5](start_span)[span_5](end_span).
CREATE PROCEDURE sp_Task02 AS BEGIN
    SELECT last_name FROM employee WHERE job_id = 670 AND salary < 1500;
END;
GO

-- 3. Выбрать фамилии всех сотрудников, у которых зарплата больше 1500 или сумма зарплаты и комиссионных больше 2000[span_6](start_span)[span_6](end_span).
CREATE PROCEDURE sp_Task03 AS BEGIN
    SELECT last_name FROM employee WHERE salary > 1500 OR (salary + ISNULL(commission, 0)) > 2000;
END;
GO

-- 4. Выбрать всех сотрудников, у которых зарплата лежит в пределах от 1100 до 2000[span_7](start_span)[span_7](end_span).
CREATE PROCEDURE sp_Task04 AS BEGIN
    SELECT * FROM employee WHERE salary BETWEEN 1100 AND 2000;
END;
GO

-- 5. Выбрать фамилии и имена всех сотрудников, имена которых начинаются на 'МА[span_8](start_span)'[span_8](end_span).
CREATE PROCEDURE sp_Task05 AS BEGIN
    SELECT last_name, first_name FROM employee WHERE first_name LIKE 'MA%';
END;
GO

-- 6. Выбрать фамилии всех сотрудников, которые получают комиссионные[span_9](start_span)[span_9](end_span).
CREATE PROCEDURE sp_Task06 AS BEGIN
    SELECT last_name FROM employee WHERE commission IS NOT NULL AND commission > 0;
END;
GO

-- 7. Выбрать общее число сотрудников фирмы[span_10](start_span)[span_10](end_span).
CREATE PROCEDURE sp_Task07 AS BEGIN
    SELECT COUNT(*) AS total_employees FROM employee;
END;
GO

-- 8. Выбрать среднюю должностную зарплату сотрудника (без учета количества сотрудников на каждой должности)[span_11](start_span)[span_11](end_span).
CREATE PROCEDURE sp_Task08 AS BEGIN
    SELECT AVG(job_avg) AS avg_job_salary FROM (
        SELECT AVG(salary) AS job_avg FROM employee GROUP BY job_id
    ) t;
END;
GO

-- 9. Выбрать разность между максимальной и минимальной зарплатой сотрудников фирмы[span_12](start_span)[span_12](end_span).
CREATE PROCEDURE sp_Task09 AS BEGIN
    SELECT MAX(salary) - MIN(salary) AS salary_diff FROM employee;
END;
GO

-- 10. Выбрать количество продаж по каждому продукту[span_13](start_span)[span_13](end_span).
CREATE PROCEDURE sp_Task10 AS BEGIN
    SELECT product_id, SUM(quantity) AS total_quantity, COUNT(*) AS sales_records FROM item GROUP BY product_id;
END;
GO

-- 11. Выбрать названия отделов фирмы и покупателей, расположенных в NEW YORK[span_14](start_span)[span_14](end_span).
CREATE PROCEDURE sp_Task11 AS BEGIN
    SELECT d.name AS department_name, c.name AS customer_name
    FROM department d
    JOIN location l ON d.location_id = l.location_id
    LEFT JOIN customer c ON c.city = 'New York'
    WHERE l.regional_group = 'NEW YORK';
END;
GO

-- 12. Выбрать названия покупателей и фамилии продавцов, которые их обслуживают[span_15](start_span)[span_15](end_span).
CREATE PROCEDURE sp_Task12 AS BEGIN
    SELECT c.name AS customer_name, e.last_name AS salesperson_name
    FROM customer c
    LEFT JOIN employee e ON c.salesperson_id = e.employee_id;
END;
GO

-- 13. Выбрать покупателей в порядке убывания суммарной прибыли, которая на них получена[span_16](start_span)[span_16](end_span).
CREATE PROCEDURE sp_Task13 AS BEGIN
    SELECT c.customer_id, c.name, SUM(i.total) AS total_revenue
    FROM customer c
    JOIN sales_order so ON c.customer_id = so.customer_id
    JOIN item i ON so.order_id = i.order_id
    GROUP BY c.customer_id, c.name
    ORDER BY total_revenue DESC;
END;
GO

-- 14. Выбрать код отдела, минимальную и максимальную зарплату клерков (CLERK) для тех отделов, в которых минимальная зарплата меньше 850[span_17](start_span)[span_17](end_span).
CREATE PROCEDURE sp_Task14 AS BEGIN
    SELECT e.department_id, MIN(e.salary) AS min_salary, MAX(e.salary) AS max_salary
    FROM employee e
    JOIN job j ON e.job_id = j.job_id
    WHERE j.[function] = 'CLERK'
    GROUP BY e.department_id
    HAVING MIN(e.salary) < 850;
END;
GO

-- 15. Для каждого отдела определить отношение средней заработной платы рядовых сотрудников к зарплате менеджера отдела[span_18](start_span)[span_18](end_span).
CREATE PROCEDURE sp_Task15 AS BEGIN
    SELECT 
        d.department_id, d.name,
        (SELECT AVG(salary) FROM employee WHERE department_id = d.department_id AND job_id <> 671) /
        NULLIF((SELECT TOP 1 salary FROM employee WHERE department_id = d.department_id AND job_id = 671), 0) AS ratio
    FROM department d;
END;
GO

-- 16. Выбрать тех сотрудников, которые с учетом комиссионных зарабатывают не меньше, чем их начальники[span_19](start_span)[span_19](end_span).
CREATE PROCEDURE sp_Task16 AS BEGIN
    SELECT e.*
    FROM employee e
    JOIN employee m ON e.manager_id = m.employee_id
    WHERE (e.salary + ISNULL(e.commission, 0)) >= (m.salary + ISNULL(m.commission, 0));
END;
GO

-- 17. Выбрать имена начальников тех отделов, которые занимаются продажами, и суммы обеспеченных ими продаж[span_20](start_span)[span_20](end_span).
CREATE PROCEDURE sp_Task17 AS BEGIN
    SELECT m.first_name, m.last_name, SUM(so.total) AS total_sales
    FROM employee m
    JOIN department d ON m.department_id = d.department_id
    JOIN customer c ON m.employee_id = c.salesperson_id
    JOIN sales_order so ON c.customer_id = so.customer_id
    WHERE d.name = 'SALES' AND m.job_id = 671
    GROUP BY m.employee_id, m.first_name, m.last_name;
END;
GO

-- 18. Выбрать названия и места размещения отделов, в которых работают продавцы[span_21](start_span)[span_21](end_span).
CREATE PROCEDURE sp_Task18 AS BEGIN
    SELECT DISTINCT d.name, l.regional_group
    FROM department d
    JOIN location l ON d.location_id = l.location_id
    JOIN employee e ON d.department_id = e.department_id
    JOIN job j ON e.job_id = j.job_id
    WHERE j.[function] = 'SALESPERSON';
END;
GO

-- 19. Выбрать список рядовых сотрудников (не менеджеров) с указанием отдела и разности между зарплатой сотрудника и средней зарплатой рядовых сотрудников по отделу[span_22](start_span)[span_22](end_span).
CREATE PROCEDURE sp_Task19 AS BEGIN
    SELECT 
        e.last_name, e.department_id,
        e.salary - (SELECT AVG(salary) FROM employee WHERE department_id = e.department_id AND job_id <> 671) AS salary_diff
    FROM employee e
    WHERE e.job_id <> 671;
END;
GO

-- 20. Выбрать среднюю сумму продаж, которая приходится на одного сотрудника, работающего в DALLAS[span_23](start_span)[span_23](end_span).
CREATE PROCEDURE sp_Task20 AS BEGIN
    SELECT 
        SUM(so.total) / NULLIF(COUNT(DISTINCT e.employee_id), 0) AS avg_sales_per_employee
    FROM employee e
    JOIN department d ON e.department_id = d.department_id
    JOIN location l ON d.location_id = l.location_id
    LEFT JOIN customer c ON e.employee_id = c.salesperson_id
    LEFT JOIN sales_order so ON c.customer_id = so.customer_id
    WHERE l.regional_group = 'DALLAS';
END;
GO

-- 21. Выбрать названия городов, в которых есть такие отделы, где никто из сотрудников не получает комиссионные[span_24](start_span)[span_24](end_span).
CREATE PROCEDURE sp_Task21 AS BEGIN
    SELECT DISTINCT l.regional_group
    FROM location l
    JOIN department d ON l.location_id = d.location_id
    JOIN employee e ON d.department_id = e.department_id
    GROUP BY l.regional_group, d.department_id
    HAVING SUM(CASE WHEN e.commission IS NOT NULL AND e.commission > 0 THEN 1 ELSE 0 END) = 0;
END;
GO

-- 22. Определить месяц, который был самым удачным для продавца по фамилии MARTIN[span_25](start_span)[span_25](end_span).
CREATE PROCEDURE sp_Task22 AS BEGIN
    SELECT TOP 1 FORMAT(so.order_date, 'yyyy-MM') AS best_month, SUM(so.total) AS total_sales
    FROM employee e
    JOIN customer c ON e.employee_id = c.salesperson_id
    JOIN sales_order so ON c.customer_id = so.customer_id
    WHERE e.last_name = 'MARTIN'
    GROUP BY FORMAT(so.order_date, 'yyyy-MM')
    ORDER BY total_sales DESC;
END;
GO

-- 23. Выбрать минимальную, максимальную и среднюю должностную зарплату для всех должностей, не учитывая тех менеджеров, которые подчинены непосредственно президенту[span_26](start_span)[span_26](end_span).
CREATE PROCEDURE sp_Task23 AS BEGIN
    SELECT j.[function], MIN(e.salary) AS min_sal, MAX(e.salary) AS max_sal, AVG(e.salary) AS avg_sal
    FROM employee e
    JOIN job j ON e.job_id = j.job_id
    WHERE NOT (e.job_id = 671 AND e.manager_id IN (SELECT employee_id FROM employee WHERE job_id = 673))
    GROUP BY j.job_id, j.[function];
END;
GO
-- 24. Выбрать самый "молодой" по составу сотрудников отдел фирмы[span_28](start_span)[span_28](end_span).
CREATE PROCEDURE sp_Task24 AS BEGIN
    SELECT TOP 1 d.department_id, d.name
    FROM department d
    JOIN employee e ON d.department_id = e.department_id
    GROUP BY d.department_id, d.name
    ORDER BY AVG(CAST(e.hire_date AS FLOAT)) DESC;
END;
GO

-- 25. Для каждого года выбрать тот месяц/месяцы, на который приходилось наибольшее число продаж[span_29](start_span)[span_29](end_span).
CREATE PROCEDURE sp_Task25 AS BEGIN
    WITH MonthlySales AS (
        SELECT YEAR(order_date) AS yr, MONTH(order_date) AS mth, COUNT(*) AS cnt,
               RANK() OVER(PARTITION BY YEAR(order_date) ORDER BY COUNT(*) DESC) AS rnk
        FROM sales_order
        GROUP BY YEAR(order_date), MONTH(order_date)
    )
    SELECT yr, mth, cnt FROM MonthlySales WHERE rnk = 1;
END;
GO

-- 26. Выбрать продукты, наиболее популярные в каждом штате (по количеству проданных экземпляров и по годам)[span_30](start_span)[span_30](end_span).
CREATE PROCEDURE sp_Task26 AS BEGIN
    WITH StateProductSales AS (
        SELECT c.state, YEAR(so.order_date) AS yr, p.product_id, p.description, SUM(i.quantity) AS total_qty,
               RANK() OVER(PARTITION BY c.state, YEAR(so.order_date) ORDER BY SUM(i.quantity) DESC) AS rnk
        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
        WHERE c.state IS NOT NULL
        GROUP BY c.state, YEAR(so.order_date), p.product_id, p.description
    )
    SELECT state, yr, description, total_qty FROM StateProductSales WHERE rnk = 1;
END;
GO

-- 27. Определить, на сколько процентов в среднем повысились цены на все товары за 1989г[span_31](start_span)[span_31](end_span).
CREATE PROCEDURE sp_Task27 AS BEGIN
    SELECT AVG((p_end.list_price - p_start.list_price) / p_start.list_price * 100) AS avg_increase_pct
    FROM product pr
    JOIN price p_start ON pr.product_id = p_start.product_id AND p_start.start_date <= '1989-01-01' AND (p_start.end_date >= '1989-01-01' OR p_start.end_date IS NULL)
    JOIN price p_end ON pr.product_id = p_end.product_id AND p_end.start_date <= '1989-12-31' AND (p_end.end_date >= '1989-12-31' OR p_end.end_date IS NULL);
END;
GO

-- 28. Определить общую сумму сделок, заключенных в 1990г. отделом, которым руководит BLAKE[span_32](start_span)[span_32](end_span).
CREATE PROCEDURE sp_Task28 AS BEGIN
    SELECT SUM(so.total) AS total_sales
    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 YEAR(so.order_date) = 1990 
      AND e.department_id = (SELECT department_id FROM employee WHERE last_name = 'BLAKE' AND job_id = 671);
END;
GO

-- 29. Определить покупателей, которые делают не менее трех закупок ежегодно[span_33](start_span)[span_33](end_span).
CREATE PROCEDURE sp_Task29 AS BEGIN
    SELECT customer_id
    FROM sales_order
    GROUP BY customer_id, YEAR(order_date)
    HAVING COUNT(*) >= 3;
END;
GO

-- 30. Для каждого отдела выбрать отношение зарплаты и комиссионных сотрудников отдела к объему продаж отдела[span_34](start_span)[span_34](end_span).
CREATE PROCEDURE sp_Task30 AS BEGIN
    SELECT 
        d.department_id, d.name,
        ISNULL((SELECT SUM(salary + ISNULL(commission, 0)) FROM employee WHERE department_id = d.department_id), 0) /
        NULLIF((SELECT SUM(so.total) 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.department_id = d.department_id), 0) AS ratio
    FROM department d;
END;
GO

-- 31. Выбрать список штатов в порядке уменьшения объема продаж (не менее 20 продаж)[span_35](start_span)[span_35](end_span).
CREATE PROCEDURE sp_Task31 AS BEGIN
    SELECT c.state, SUM(so.total) AS total_sales, COUNT(so.order_id) AS sales_count
    FROM customer c
    JOIN sales_order so ON c.customer_id = so.customer_id
    WHERE c.state IS NOT NULL
    GROUP BY c.state
    HAVING COUNT(so.order_id) >= 20
    ORDER BY total_sales DESC;
END;
GO

-- 32. Для каждого города, в котором расположен отдел фирмы, выбрать количество покупателей[span_36](start_span)[span_36](end_span).
CREATE PROCEDURE sp_Task32 AS BEGIN
    SELECT l.regional_group AS city, COUNT(c.customer_id) AS customer_count
    FROM location l
    LEFT JOIN customer c ON l.regional_group = c.city
    GROUP BY l.regional_group;
END;
GO

-- 33. Определить количество новых покупателей, появившихся в 1989г, и количество потерянных[span_37](start_span)[span_37](end_span).
CREATE PROCEDURE sp_Task33 AS BEGIN
    SELECT 
        (SELECT COUNT(DISTINCT customer_id) FROM sales_order WHERE YEAR(order_date) = 1989 AND customer_id NOT IN (SELECT customer_id FROM sales_order WHERE YEAR(order_date) < 1989)) AS new_customers_1989,
        (SELECT COUNT(DISTINCT customer_id) FROM sales_order WHERE YEAR(order_date) < 1989 AND customer_id NOT IN (SELECT customer_id FROM sales_order WHERE YEAR(order_date) = 1989)) AS lost_customers_1989;
END;
GO

-- 34. Вывести названия всех городов и количество работающих в каждом из них сотрудников[span_38](start_span)[span_38](end_span).
CREATE PROCEDURE sp_Task34 AS BEGIN
    SELECT l.regional_group AS city, COUNT(e.employee_id) AS employee_count
    FROM location l
    LEFT JOIN department d ON l.location_id = d.location_id
    LEFT JOIN employee e ON d.department_id = e.department_id
    GROUP BY l.regional_group;
END;
GO

-- 35. Выбрать код отдела и максимальную зарплату для тех отделов, где она меньше средней по другим отделам[span_39](start_span)[span_39](end_span).
CREATE PROCEDURE sp_Task35 AS BEGIN
    SELECT department_id, MAX(salary) AS max_sal
    FROM employee
    GROUP BY department_id
    HAVING MAX(salary) < (
        SELECT AVG(dept_max) FROM (
            SELECT MAX(salary) AS dept_max FROM employee GROUP BY department_id
        ) t
    );
END;
GO

-- 36. Вывести список тех отделов, для которых в 1989г было более 3 случаев задержки поставки более чем на 15 дней[span_40](start_span)[span_40](end_span).
CREATE PROCEDURE sp_Task36 AS BEGIN
    SELECT d.department_id, d.name
    FROM department d
    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
    WHERE YEAR(so.order_date) = 1989 AND DATEDIFF(DAY, so.order_date, so.ship_date) > 15
    GROUP BY d.department_id, d.name
    HAVING COUNT(*) > 3;
END;
GO

-- 37. Определить, какой продукт был наиболее популярен весной 1989г[span_41](start_span)[span_41](end_span).
CREATE PROCEDURE sp_Task37 AS BEGIN
    SELECT TOP 1 p.product_id, p.description, SUM(i.quantity) AS total_qty
    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 so.order_date BETWEEN '1989-03-01' AND '1989-05-31'
    GROUP BY p.product_id, p.description
    ORDER BY total_qty DESC;
END;
GO

-- 38. Определить штат, для которого средняя задержка между заключением сделки и поставкой является наибольшей[span_42](start_span)[span_42](end_span).
CREATE PROCEDURE sp_Task38 AS BEGIN
    SELECT TOP 1 c.state, AVG(CAST(DATEDIFF(DAY, so.order_date, so.ship_date) AS FLOAT)) AS avg_delay
    FROM customer c
    JOIN sales_order so ON c.customer_id = so.customer_id
    WHERE c.state IS NOT NULL AND so.ship_date IS NOT NULL
    GROUP BY c.state
    ORDER BY avg_delay DESC;
END;
GO

-- 39. Выбрать те сделки, в которых общая сумма сделки не совпадает с постатейной суммой[span_43](start_span)[span_43](end_span).
CREATE PROCEDURE sp_Task39 AS BEGIN
    SELECT so.order_id, so.total AS header_total, SUM(i.total) AS items_sum
    FROM sales_order so
    JOIN item i ON so.order_id = i.order_id
    GROUP BY so.order_id, so.total
    HAVING so.total <> SUM(i.total);
END;
GO

-- 40. Выбрать данные для графика: комиссионные продавца от суммы обеспеченных им продаж[span_44](start_span)[span_44](end_span).
CREATE PROCEDURE sp_Task40 AS BEGIN
    SELECT e.employee_id, e.last_name, e.commission, SUM(so.total) AS total_sales
    FROM employee e
    JOIN customer c ON e.employee_id = c.salesperson_id
    JOIN sales_order so ON c.customer_id = so.customer_id
    WHERE e.commission IS NOT NULL
    GROUP BY e.employee_id, e.last_name, e.commission;
END;
GO

-- 41. Выбрать данные для графика: сумма продажи от процента предоставленной скидки[span_45](start_span)[span_45](end_span).
CREATE PROCEDURE sp_Task41 AS BEGIN
    SELECT i.order_id, i.total AS item_total, 
           (pr.list_price - i.actual_price) / pr.list_price * 100 AS discount_pct
    FROM item i
    JOIN sales_order so ON i.order_id = so.order_id
    JOIN price pr ON i.product_id = pr.product_id AND so.order_date BETWEEN pr.start_date AND ISNULL(pr.end_date, GETDATE());
END;
GO

-- 42. Определить по годам, насколько объем продаж в летние месяцы отличается от среднемесячного[span_46](start_span)[span_46](end_span).
CREATE PROCEDURE sp_Task42 AS BEGIN
    WITH AnnualStats AS (
        SELECT YEAR(order_date) AS yr, SUM(total) / 12 AS monthly_avg FROM sales_order GROUP BY YEAR(order_date)
    ), SummerStats AS (
        SELECT YEAR(order_date) AS yr, SUM(total) / 3 AS summer_monthly_avg FROM sales_order WHERE MONTH(order_date) IN (6, 7, 8) GROUP BY YEAR(order_date)
    )
    SELECT a.yr, (s.summer_monthly_avg - a.monthly_avg) / a.monthly_avg * 100 AS diff_pct
    FROM AnnualStats a JOIN SummerStats s ON a.yr = s.yr;
END;
GO

-- 43. Вывести список менеджеров отделов SALES в порядке убывания эффективности за 1989 г[span_47](start_span)[span_47](end_span).
CREATE PROCEDURE sp_Task43 AS BEGIN
    SELECT m.employee_id, m.last_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) = 1989), 0) /
           NULLIF((SELECT SUM(salary) FROM employee WHERE department_id = d.department_id), 0) AS efficiency
    FROM department d
    JOIN employee m ON d.department_id = m.department_id AND m.job_id = 671
    WHERE d.name = 'SALES'
    ORDER BY efficiency DESC;
END;
GO

-- 44. Определить продукты, которые не продавались до 1990г[span_48](start_span)[span_48](end_span).
CREATE PROCEDURE sp_Task44 AS BEGIN
    SELECT * FROM product
    WHERE product_id NOT IN (
        SELECT DISTINCT i.product_id FROM item i JOIN sales_order so ON i.order_id = so.order_id WHERE so.order_date < '1990-01-01'
    );
END;
GO

-- 45. Определить, не хранятся ли в базе данные о покупателях, которые не совершили ни одной покупки[span_49](start_span)[span_49](end_span).
CREATE PROCEDURE sp_Task45 AS BEGIN
    SELECT * FROM customer WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM sales_order);
END;
GO
-- 46. Определить отделы, которые обслуживают покупателей, находящихся в штате Нью-Йорк (state = 'NY')[span_51](start_span)[span_51](end_span).
CREATE PROCEDURE sp_Task46 AS BEGIN
    SELECT DISTINCT d.department_id, d.name
    FROM department d
    JOIN employee e ON d.department_id = e.department_id
    JOIN customer c ON e.employee_id = c.salesperson_id
    WHERE c.state = 'NY';
END;
GO

-- 47. Определить менеджера, у которого в непосредственном подчинении наибольшее число служащих[span_52](start_span)[span_52](end_span).
CREATE PROCEDURE sp_Task47 AS BEGIN
    SELECT TOP 1 m.employee_id, m.last_name, COUNT(s.employee_id) AS sub_count
    FROM employee m
    JOIN employee s ON m.employee_id = s.manager_id
    GROUP BY m.employee_id, m.last_name
    ORDER BY sub_count DESC;
END;
GO

-- 48. Определить, в каких штатах любят покупать дорогие товары, а в каких - дешевые[span_53](start_span)[span_53](end_span).
CREATE PROCEDURE sp_Task48 AS BEGIN
    SELECT c.state, AVG(i.actual_price) AS avg_item_price
    FROM customer c
    JOIN sales_order so ON c.customer_id = so.customer_id
    JOIN item i ON so.order_id = i.order_id
    WHERE c.state IS NOT NULL
    GROUP BY c.state
    ORDER BY avg_item_price DESC;
END;
GO

-- 49. Определить "наименьшего общего" начальника служащих SMITH и ADAMS[span_54](start_span)[span_54](end_span).
CREATE PROCEDURE sp_Task49 AS BEGIN
    WITH SmithManagers AS (
        SELECT employee_id, last_name, 1 AS lvl FROM employee WHERE last_name = 'SMITH'
        UNION ALL
        SELECT e.employee_id, e.last_name, sm.lvl + 1 FROM employee e JOIN SmithManagers sm ON e.employee_id = sm.manager_id
    ), AdamsManagers AS (
        SELECT employee_id, last_name, 1 AS lvl FROM employee WHERE last_name = 'ADAMS'
        UNION ALL
        SELECT e.employee_id, e.last_name, am.lvl + 1 FROM employee e JOIN AdamsManagers am ON e.employee_id = am.manager_id
    )
    SELECT TOP 1 sm.last_name AS common_manager
    FROM SmithManagers sm
    JOIN AdamsManagers am ON sm.employee_id = am.employee_id
    ORDER BY sm.lvl ASC;
END;
GO

-- 50. Определить случаи продажи продуктов, не выставленных на тот момент в продажу (название продукта, дата, покупатель)[span_55](start_span)[span_55](end_span).
CREATE PROCEDURE sp_Task50 AS BEGIN
    SELECT p.description, so.order_date, c.name AS customer_name
    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
    WHERE NOT EXISTS (
        SELECT 1 FROM price pr 
        WHERE pr.product_id = i.product_id 
          AND so.order_date BETWEEN pr.start_date AND ISNULL(pr.end_date, '9999-12-31')
    );
END;
GO
Больше возможностей при регистрации:
