---

CREATE PROCEDURE sp_Task01 AS BEGIN
    SELECT last_name FROM employee WHERE salary > 1500;
END;

CREATE PROCEDURE sp_Task02 AS BEGIN
    SELECT last_name FROM employee WHERE job_id = 670 AND salary < 1500;
END;

CREATE PROCEDURE sp_Task03 AS BEGIN
    SELECT last_name FROM employee WHERE salary > 1500 OR (salary + ISNULL(commission, 0)) > 2000;
END;

CREATE PROCEDURE sp_Task04 AS BEGIN
    SELECT * FROM employee WHERE salary BETWEEN 1100 AND 2000;
END;

CREATE PROCEDURE sp_Task05 AS BEGIN
    SELECT last_name, first_name FROM employee WHERE first_name LIKE 'MA%';
END;

CREATE PROCEDURE sp_Task06 AS BEGIN
    SELECT last_name FROM employee WHERE commission IS NOT NULL AND commission > 0;
END;

CREATE PROCEDURE sp_Task07 AS BEGIN
    SELECT COUNT(*) AS total_employees FROM employee;
END;

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;

CREATE PROCEDURE sp_Task09 AS BEGIN
    SELECT MAX(salary) - MIN(salary) AS salary_diff FROM employee;
END;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

CREATE PROCEDURE sp_Task29 AS BEGIN
    SELECT customer_id
    FROM sales_order
    GROUP BY customer_id, YEAR(order_date)
    HAVING COUNT(*) >= 3;
END;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

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;

CREATE PROCEDURE sp_Task45 AS BEGIN
    SELECT * FROM customer WHERE customer_id NOT IN (SELECT DISTINCT customer_id FROM sales_order);
END;

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;

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;

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;

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;

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;