02 — DQL: SELECT, JOINs, Subqueries, Set Operators, Window Functions, PIVOT

SELECT  [TOP (n) [PERCENT] [WITH TIES]]
        select_list
FROM    table_source
[WHERE  search_condition]
[GROUP BY [ALL | ROLLUP | CUBE | GROUPING SETS] grouping_columns]
[HAVING  aggregate_condition]
[ORDER BY  sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST]]
[OFFSET n ROWS FETCH NEXT m ROWS ONLY];

-- Equivalent when no aggregates are needed
SELECT DISTINCT department_id FROM employees;
SELECT department_id FROM employees GROUP BY department_id;

-- Only GROUP BY can aggregate
SELECT department_id, COUNT(*) AS head_count
FROM   employees
GROUP BY department_id
HAVING COUNT(*) > 5;

-- ROLLUP: hierarchical subtotals (year → quarter → month)
SELECT YEAR(order_date)  AS yr,
       MONTH(order_date) AS mo,
       SUM(amount)       AS total
FROM   orders
GROUP BY ROLLUP(YEAR(order_date), MONTH(order_date));

-- CUBE: all combinations of grouping columns
SELECT region, product_category, SUM(amount)
FROM   sales
GROUP BY CUBE(region, product_category);

-- GROUPING SETS: explicit control
SELECT region, product_category, SUM(amount)
FROM   sales
GROUP BY GROUPING SETS
(
    (region, product_category),  -- detail
    (region),                    -- region subtotal
    ()                           -- grand total
);

-- Standard inner join
SELECT e.last_name, d.department_name
FROM   employees   AS e
INNER JOIN departments AS d ON e.department_id = d.department_id;

-- Left join — keep employees even with no department
SELECT e.last_name, d.department_name
FROM   employees   AS e
LEFT  JOIN departments AS d ON e.department_id = d.department_id;

-- Full outer — surface unmatched rows from either side
SELECT e.last_name, d.department_name
FROM   employees   AS e
FULL OUTER JOIN departments AS d ON e.department_id = d.department_id;

-- Cross join — all combinations (use with care)
SELECT c.color, s.size
FROM   colors AS c
CROSS JOIN sizes AS s;

-- Range join: assign salary band
SELECT e.last_name, b.band_name
FROM   employees    AS e
JOIN   salary_bands AS b
    ON e.salary BETWEEN b.low AND b.high;

-- Bad: implicit join syntax (ANSI-89) — still valid SQL Server but avoid
SELECT e.last_name, d.department_name
FROM   employees e, departments d
WHERE  e.department_id = d.department_id;

-- SQL Server 2022+: IS DISTINCT FROM / IS NOT DISTINCT FROM
SELECT * FROM a JOIN b ON a.col IS NOT DISTINCT FROM b.col;

-- Pre-2022 equivalent
SELECT * FROM a JOIN b
    ON (a.col = b.col OR (a.col IS NULL AND b.col IS NULL));

SELECT department_id,
       (SELECT department_name FROM departments d WHERE d.department_id = e.department_id)
           AS dept_name
FROM   employees AS e;

-- Employees in departments that have a budget > 100000
SELECT last_name
FROM   employees
WHERE  department_id IN (SELECT department_id FROM departments WHERE budget > 100000);

-- Safe alternative
SELECT last_name
FROM   employees e
WHERE  NOT EXISTS (
    SELECT 1 FROM departments d
    WHERE  d.department_id = e.department_id
    AND    d.budget <= 100000
);

SELECT last_name
FROM   employees AS e
WHERE  EXISTS (
    SELECT 1
    FROM   orders AS o
    WHERE  o.employee_id = e.employee_id
);

SELECT dept_name, avg_salary
FROM (
    SELECT d.department_name AS dept_name,
           AVG(e.salary)     AS avg_salary
    FROM   employees   AS e
    JOIN   departments AS d ON e.department_id = d.department_id
    GROUP BY d.department_name
) AS dept_avg
WHERE avg_salary > 80000;

-- CROSS APPLY: call an inline TVF per employee
SELECT e.last_name, t.total_sales
FROM   employees AS e
CROSS APPLY dbo.fn_EmployeeSales(e.employee_id) AS t;

-- OUTER APPLY: top N per group (classic pattern)
SELECT c.customer_name, r.order_id, r.order_date
FROM   customers AS c
OUTER APPLY (
    SELECT TOP (3) o.order_id, o.order_date
    FROM   orders AS o
    WHERE  o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
) AS r;

-- Window function alternative for top-N per group
WITH ranked AS (
    SELECT c.customer_name,
           o.order_id,
           o.order_date,
           ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC) AS rn
    FROM   orders    AS o
    JOIN   customers AS c ON c.customer_id = o.customer_id
)
SELECT customer_name, order_id, order_date
FROM   ranked
WHERE  rn <= 3;

-- UNION: combine active and archived customers
SELECT customer_id, name FROM customers_active
UNION
SELECT customer_id, name FROM customers_archived;

-- UNION ALL: faster than UNION when duplicates are acceptable or impossible
SELECT product_id FROM orders_2023
UNION ALL
SELECT product_id FROM orders_2024;

-- INTERSECT: products ordered in both years
SELECT product_id FROM orders_2023
INTERSECT
SELECT product_id FROM orders_2024;

-- EXCEPT: products ordered in 2023 but not 2024
SELECT product_id FROM orders_2023
EXCEPT
SELECT product_id FROM orders_2024;

SELECT customer_id, name FROM customers_active
UNION ALL
SELECT customer_id, name FROM customers_archived
ORDER BY name;

function_name(...) OVER (
    [PARTITION BY partition_expression [, ...]]
    [ORDER BY     sort_expression      [ASC | DESC] [, ...]]
    [ROWS | RANGE BETWEEN frame_start AND frame_end]
)

-- ROWS: running sum, each row counted once
SELECT order_id,
       amount,
       SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM   orders;

-- RANGE: running sum including all rows with the same order_date as current row
SELECT order_id,
       amount,
       SUM(amount) OVER (ORDER BY order_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_range
FROM   orders;

-- Sliding 7-day moving average
SELECT order_date,
       AVG(daily_total) OVER (
           ORDER BY order_date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS moving_avg_7d
FROM   daily_sales;

SELECT
    last_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk,
    NTILE(4)     OVER (PARTITION BY department_id ORDER BY salary DESC) AS quartile
FROM employees;

SELECT
    order_date,
    amount,
    LAG (amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount,
    LEAD(amount, 1, 0) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_amount
FROM orders;

-- FIRST_VALUE / LAST_VALUE: value from first or last row in window frame
SELECT
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (PARTITION BY customer_id ORDER BY order_date
                              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS first_order,
    LAST_VALUE (amount) OVER (PARTITION BY customer_id ORDER BY order_date
                              ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS last_order
FROM orders;

-- Percentage of department total
SELECT
    last_name,
    salary,
    SUM(salary) OVER (PARTITION BY department_id)                         AS dept_total,
    ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department_id), 2) AS pct_of_dept
FROM employees;

SELECT
    last_name,
    salary,
    NTILE(4) OVER (ORDER BY salary) AS salary_quartile
FROM employees;

-- Monthly sales per product as columns
SELECT *
FROM (
    SELECT product_id,
           DATENAME(MONTH, order_date) AS month_name,
           amount
    FROM   orders
) AS src
PIVOT (
    SUM(amount)
    FOR month_name IN ([January], [February], [March], [April],
                       [May], [June], [July], [August],
                       [September], [October], [November], [December])
) AS pvt;

-- Dynamic PIVOT pattern
DECLARE @cols  NVARCHAR(MAX),
        @sql   NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME(month_name), ', ')
FROM (SELECT DISTINCT DATENAME(MONTH, order_date) AS month_name FROM orders) AS t;

SET @sql = N'
SELECT *
FROM (
    SELECT product_id, DATENAME(MONTH, order_date) AS month_name, amount
    FROM   orders
) AS src
PIVOT (SUM(amount) FOR month_name IN (' + @cols + N')) AS pvt;';

EXEC sp_executesql @sql;

-- Column-per-quarter → row-per-quarter
SELECT product_id, quarter, sales
FROM (
    SELECT product_id, q1, q2, q3, q4
    FROM   quarterly_sales
) AS src
UNPIVOT (
    sales FOR quarter IN (q1, q2, q3, q4)
) AS unpvt;

SELECT product_id, quarter, sales
FROM   quarterly_sales AS s
CROSS APPLY (VALUES ('q1', s.q1), ('q2', s.q2), ('q3', s.q3), ('q4', s.q4))
             AS t(quarter, sales)
WHERE  t.sales IS NOT NULL;  -- remove WHERE to keep NULLs

-- Page 3 of 20-row pages (rows 41–60)
SELECT product_id, product_name, price
FROM   products
ORDER  BY product_name
OFFSET 40 ROWS
FETCH  NEXT 20 ROWS ONLY;

DECLARE @page_number  INT = 3,
        @page_size    INT = 20;

SELECT product_id, product_name, price
FROM   products
ORDER  BY product_name
OFFSET (@page_number - 1) * @page_size ROWS
FETCH  NEXT @page_size ROWS ONLY;

-- Keyset pagination: more efficient for deep pages
SELECT TOP (20) product_id, product_name, price
FROM   products
WHERE  product_name > @last_seen_name   -- last name from previous page
    OR (product_name = @last_seen_name AND product_id > @last_seen_id)
ORDER  BY product_name, product_id;

-- Top 10 by salary
SELECT TOP (10) last_name, salary
FROM   employees
ORDER  BY salary DESC;

-- Top 10 percent
SELECT TOP (10) PERCENT last_name, salary
FROM   employees
ORDER  BY salary DESC;

-- WITH TIES: include all rows that tie at the boundary
SELECT TOP (10) WITH TIES last_name, salary
FROM   employees
ORDER  BY salary DESC;

-- Bad: function on indexed column defeats the index
WHERE CONVERT(VARCHAR, order_date, 101) = '01/15/2024'

-- Good: apply the conversion to the constant
WHERE order_date = CONVERT(DATE, '01/15/2024', 101)

-- May cause a scan if OR spans different columns
WHERE department_id = 5 OR manager_id = 10

-- Rewrite as UNION ALL (each branch can use its own index)
SELECT * FROM employees WHERE department_id = 5
UNION ALL
SELECT * FROM employees WHERE manager_id = 10
    AND department_id <> 5;  -- avoid duplicates if needed

-- Bad: counts all rows when only presence matters
IF (SELECT COUNT(*) FROM orders WHERE customer_id = @id) > 0

-- Good: short-circuits on first match
IF EXISTS (SELECT 1 FROM orders WHERE customer_id = @id)