Какие бывают типы подзапросов?sql-10

SELECT product_name
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE is_active = 1
);

SELECT dept.name, emp_stats.avg_salary
FROM departments dept
JOIN (
    SELECT department_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
) emp_stats ON dept.id = emp_stats.department_id;

SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees) as company_avg_salary
FROM employees;

SELECT department_id, AVG(salary) as avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary)
    FROM employees
);

SELECT name
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

SELECT *
FROM products
WHERE (category_id, price) = (
    SELECT category_id, MAX(price)
    FROM products
    GROUP BY category_id
    LIMIT 1
);

SELECT name
FROM customers
WHERE id IN (
    SELECT customer_id
    FROM orders
    WHERE amount > 1000
);

SELECT *
FROM (
    SELECT department_id, COUNT(*) as emp_count
    FROM employees
    GROUP BY department_id
) dept_stats
WHERE emp_count > 5;

SELECT name
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

SELECT e.name
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

SELECT name
FROM employees
WHERE id IN (
    SELECT manager_id
    FROM departments
);

SELECT d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
    AND e.salary > 100000
);

SELECT name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary
    FROM employees
    WHERE department = 'Support'
);

WITH RECURSIVE org_hierarchy AS (
    -- Базовый случай
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивная часть
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN org_hierarchy h ON e.manager_id = h.id
)
SELECT * FROM org_hierarchy;

WITH regional_sales AS (
    SELECT region, SUM(amount) as total_sales
    FROM orders
    GROUP BY region
),
top_regions AS (
    SELECT region
    FROM regional_sales
    WHERE total_sales > 1000000
)
SELECT * FROM top_regions;