SQL Basics for Projects

-- Line comment
/* Block comment
   spanning lines */

-- A complete SELECT statement (clauses must appear in this order):
SELECT column1, column2
FROM   table_name
WHERE  condition
GROUP  BY column1
HAVING aggregate_condition
ORDER  BY column1 ASC
LIMIT  10
OFFSET 20;

-- Correct NULL checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- Wrong — always returns no rows
SELECT * FROM users WHERE phone = NULL;

-- NULL propagates through arithmetic
SELECT NULL + 5;        -- NULL
SELECT NULL || 'text';  -- NULL (PostgreSQL)
SELECT COALESCE(NULL, 'default');  -- 'default'
SELECT NULLIF(5, 5);    -- NULL (returns NULL when args are equal)
SELECT NULLIF(5, 3);    -- 5

-- NULL in aggregate functions: ignored except COUNT(*)
SELECT AVG(salary) FROM employees;  -- NULLs excluded from average
SELECT COUNT(*) FROM employees;      -- counts all rows including NULL
SELECT COUNT(salary) FROM employees; -- excludes NULL salaries

WHERE a = 1 AND b = 2
WHERE a = 1 OR  b = 2
WHERE NOT (a = 1)

-- Operator precedence: NOT > AND > OR
-- Always use parentheses to be explicit
WHERE (a = 1 OR a = 2) AND b > 0

SELECT 10 + 3,   -- 13
       10 - 3,   -- 7
       10 * 3,   -- 30
       10 / 3,   -- 3  (integer division in most dialects)
       10.0 / 3, -- 3.333...
       10 % 3,   -- 1  (modulo)
       10 ^ 2;   -- 100 (PostgreSQL; use POWER(10,2) elsewhere)

-- Concatenation
SELECT 'Hello' || ' ' || 'World';        -- standard
SELECT CONCAT('Hello', ' ', 'World');    -- MySQL / SQL Server

-- LIKE pattern matching (case-insensitive in MySQL/SQLite by default)
WHERE name LIKE 'A%'       -- starts with A
WHERE name LIKE '%son'     -- ends with son
WHERE name LIKE '%art%'    -- contains art
WHERE name LIKE 'J_n'      -- J, any one char, n
WHERE name NOT LIKE 'X%'

-- ILIKE — case-insensitive LIKE (PostgreSQL)
WHERE name ILIKE 'alice%'

-- CAST (standard)
SELECT CAST('123' AS INTEGER);
SELECT CAST(price AS TEXT);
SELECT CAST('2024-01-01' AS DATE);

-- :: shorthand (PostgreSQL)
SELECT '123'::INTEGER;
SELECT price::TEXT;

-- CONVERT (MySQL / SQL Server)
SELECT CONVERT(INT, '123');        -- SQL Server
SELECT CONVERT('123', UNSIGNED);   -- MySQL

BEGIN;                          -- start transaction (also: START TRANSACTION)
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;                         -- make changes permanent

-- On error:
ROLLBACK;                       -- undo all changes in the transaction

-- Savepoints
BEGIN;
  INSERT INTO orders VALUES (1, 'pending');
  SAVEPOINT before_payment;
  INSERT INTO payments VALUES (1, 100);
  ROLLBACK TO before_payment;   -- undo only from savepoint
COMMIT;

-- Isolation levels (SET before BEGIN or after BEGIN)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- Single-line comment (SQL standard)
# Single-line comment (MySQL only)

/*
   Multi-line
   block comment
*/

SELECT id /*, name */ FROM users;  -- inline block comment

-- Unquoted identifiers are case-insensitive and must start with a letter/underscore
SELECT user_id FROM orders;

-- Quoted identifiers preserve case and allow reserved words / spaces
SELECT "User ID" FROM "Order Details";   -- standard SQL and PostgreSQL
SELECT `user_id` FROM `orders`;          -- MySQL backtick quoting
SELECT [user id] FROM [order details];   -- SQL Server bracket quoting

-- Reserved word used as column name
SELECT "select" FROM my_table;