SQL Mastery — Basic থেকে Advanced

JOIN Types

-- INNER (intersection)
SELECT o.id, c.name FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT (all from left)
SELECT c.name, COUNT(o.id) FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

Subquery

SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.cid=c.id) AS cnt
FROM customers c;

SELECT * FROM customers c WHERE EXISTS
  (SELECT 1 FROM orders WHERE cid=c.id);

CTE ⭐

WITH high_value AS (
    SELECT customer_id, SUM(total) AS spent FROM orders
    GROUP BY customer_id HAVING SUM(total) > 100000
)
SELECT c.name, h.spent FROM customers c
JOIN high_value h ON c.id = h.customer_id;

-- Recursive (hierarchy)
WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, o.lvl+1
    FROM employees e JOIN org o ON e.manager_id = o.id
) SELECT * FROM org;

Window Functions ⭐

SELECT name, salary, dept,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
  RANK() OVER (ORDER BY salary DESC) AS rnk,
  SUM(salary) OVER (PARTITION BY dept) AS dept_total,
  LAG(salary) OVER (ORDER BY id) AS prev_sal
FROM employees;

Stored Procedure

CREATE PROCEDURE sp_GetOrders @CustId INT AS
BEGIN
  SELECT * FROM orders WHERE customer_id = @CustId;
END;

View, Trigger

CREATE VIEW v_active AS SELECT * FROM products WHERE deleted_at IS NULL;

CREATE TRIGGER trg_audit AFTER UPDATE ON orders
FOR EACH ROW INSERT INTO audit_log VALUES (NEW.id, NOW());