SELECT column1, column2 FROM table_name
WHERE condition
ORDER BY column1 ASC
LIMIT 10 OFFSET 20;

SELECT DISTINCT column1 FROM table_name;

SELECT * FROM table_name WHERE column1 BETWEEN 10 AND 20;
SELECT * FROM table_name WHERE column1 IN (1, 2, 3);
SELECT * FROM table_name WHERE column1 LIKE 'A%';
SELECT * FROM table_name WHERE column1 IS NULL;
SELECT * FROM table_name WHERE column1 IS NOT NULL;

SELECT a.*, b.* FROM a INNER JOIN b ON a.id = b.a_id;
SELECT a.*, b.* FROM a LEFT JOIN b ON a.id = b.a_id;
SELECT a.*, b.* FROM a RIGHT JOIN b ON a.id = b.a_id;
SELECT a.*, b.* FROM a FULL OUTER JOIN b ON a.id = b.a_id;
SELECT a.*, b.* FROM a CROSS JOIN b;
SELECT a.*, b.* FROM a NATURAL JOIN b;

SELECT department, COUNT(*), AVG(salary), MIN(salary), MAX(salary), SUM(salary)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY AVG(salary) DESC;

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'CA');

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

SELECT customer_id, (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

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

-- recursive CTE
WITH RECURSIVE subordinates AS (
  SELECT id, manager_id, name FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;

SELECT city FROM customers UNION SELECT city FROM suppliers;
SELECT city FROM customers UNION ALL SELECT city FROM suppliers;
SELECT city FROM customers INTERSECT SELECT city FROM suppliers;
SELECT city FROM customers EXCEPT SELECT city FROM suppliers;

INSERT INTO table_name (col1, col2) VALUES ('a', 1);
INSERT INTO table_name (col1, col2) VALUES ('a', 1), ('b', 2), ('c', 3);

UPDATE table_name SET col1 = 'new value' WHERE id = 1;

DELETE FROM table_name WHERE id = 1;

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total NUMERIC(10, 2) NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

ALTER TABLE orders ADD COLUMN notes TEXT;
ALTER TABLE orders DROP COLUMN notes;
ALTER TABLE orders ALTER COLUMN total SET DEFAULT 0;
ALTER TABLE orders RENAME TO customer_orders;

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
DROP TABLE orders;

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT before_credit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- ROLLBACK TO SAVEPOINT before_credit;  -- undo just the credit
-- RELEASE SAVEPOINT before_credit;      -- keep it, drop the checkpoint

COMMIT;
-- ROLLBACK;  -- discard everything since BEGIN

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

CREATE USER app_service WITH PASSWORD 'secret';
CREATE ROLE analyst;

GRANT SELECT ON orders TO analyst;
GRANT analyst TO app_service;
REVOKE SELECT ON orders FROM analyst;