-- WRONG: Procedural thinking (cursor)
DECLARE cursor_emp CURSOR FOR SELECT * FROM employees;
WHILE @@FETCH_STATUS = 0
BEGIN
  UPDATE employees SET salary = salary * 1.1 WHERE CURRENT OF cursor_emp;
  FETCH NEXT FROM cursor_emp;
END

-- RIGHT: Set-based thinking
UPDATE employees SET salary = salary * 1.1;

-- Better than adjacency list for read-heavy trees
CREATE TABLE categories (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  lft INT NOT NULL,  -- left boundary
  rgt INT NOT NULL   -- right boundary
);

-- Find all descendants
SELECT child.*
FROM categories parent
JOIN categories child ON child.lft BETWEEN parent.lft AND parent.rgt
WHERE parent.id = 1;

-- Find path to root
SELECT parent.*
FROM categories child
JOIN categories parent ON child.lft BETWEEN parent.lft AND parent.rgt
WHERE child.id = 10
ORDER BY parent.lft;

-- Valid time modeling
CREATE TABLE employee_salaries (
  employee_id INT,
  salary DECIMAL(10,2),
  valid_from DATE,
  valid_to DATE,
  CONSTRAINT no_overlap CHECK (valid_from < valid_to),
  CONSTRAINT no_gaps -- use triggers or application logic
);

-- Current salary
SELECT salary FROM employee_salaries
WHERE employee_id = 1
AND CURRENT_DATE BETWEEN valid_from AND valid_to;

-- AVOID: EAV anti-pattern
CREATE TABLE attributes (
  entity_id INT,
  attribute_name VARCHAR(50),
  attribute_value VARCHAR(255)
);

-- PREFER: Proper table design
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2),
  weight DECIMAL(8,2)
);

-- NULL is not a value, it's a marker for missing information
-- Three-valued logic: TRUE, FALSE, UNKNOWN

-- WRONG: Comparing to NULL
WHERE column = NULL    -- Always UNKNOWN
WHERE column <> NULL   -- Always UNKNOWN

-- RIGHT: Testing for NULL
WHERE column IS NULL
WHERE column IS NOT NULL

-- COALESCE for defaults
SELECT COALESCE(nickname, first_name, 'Unknown') AS display_name

-- SLOW: OR can prevent index usage
SELECT * FROM orders WHERE customer_id = 1 OR product_id = 2;

-- FAST: UNION ALL (if result sets are disjoint)
SELECT * FROM orders WHERE customer_id = 1
UNION ALL
SELECT * FROM orders WHERE product_id = 2 AND customer_id <> 1;

-- Find gaps in a sequence
SELECT curr.id + 1 AS gap_start,
       (SELECT MIN(id) FROM sequence WHERE id > curr.id) - 1 AS gap_end
FROM sequence curr
WHERE NOT EXISTS (SELECT 1 FROM sequence WHERE id = curr.id + 1)
AND curr.id < (SELECT MAX(id) FROM sequence);

-- Find islands (consecutive sequences)
WITH numbered AS (
  SELECT id, id - ROW_NUMBER() OVER (ORDER BY id) AS grp
  FROM sequence
)
SELECT MIN(id) AS island_start, MAX(id) AS island_end
FROM numbered
GROUP BY grp;

-- Window function (modern SQL)
SELECT
  transaction_date,
  amount,
  SUM(amount) OVER (ORDER BY transaction_date) AS running_total
FROM transactions;