Standard SQL (Structured Query Language) works across MySQL, PostgreSQL, Oracle, SQL Server, and SQLite. Master these statements and you can work with any relational database. SQL is case-insensitive for keywords — UPPERCASE by convention.
| Statement / Feature | Syntax | Description |
|---|---|---|
| SELECT | SELECT col1, col2 FROM table WHERE cond ORDER BY col LIMIT n | Retrieve rows from one or more tables |
| SELECT * | SELECT * FROM table | Select all columns — avoid in production |
| SELECT DISTINCT | SELECT DISTINCT col FROM table | Return unique values only |
| WHERE | WHERE col = val AND col2 > val2 | Filter rows by condition |
| AND / OR / NOT | WHERE a = 1 AND b = 2 OR NOT c = 3 | Combine conditions |
| BETWEEN | WHERE age BETWEEN 18 AND 65 | Inclusive range filter |
| IN | WHERE status IN ('active','pending') | Match any value in list |
| NOT IN | WHERE id NOT IN (1,2,3) | Exclude values in list |
| LIKE | WHERE name LIKE 'A%' / '%son' / '_ob' | Pattern match: % = any chars, _ = one char |
| IS NULL | WHERE notes IS NULL / IS NOT NULL | Test for null values |
| ORDER BY | ORDER BY score DESC, name ASC | Sort results — ASC default |
| LIMIT / TOP | LIMIT 10 OFFSET 20 / TOP 10 | Paginate results (MySQL/PG: LIMIT; SQL Server: TOP) |
| INSERT INTO | INSERT INTO table (col1,col2) VALUES (v1,v2) | Add one row |
| INSERT multiple | INSERT INTO table (col1,col2) VALUES (v1,v2),(v3,v4) | Add multiple rows |
| UPDATE | UPDATE table SET col=val WHERE condition | Modify existing rows — ALWAYS use WHERE! |
| DELETE | DELETE FROM table WHERE condition | Remove rows — ALWAYS use WHERE! |
| TRUNCATE | TRUNCATE TABLE tablename | Delete all rows fast (no WHERE, no rollback) |
| JOIN (INNER) | SELECT * FROM a JOIN b ON a.id = b.a_id | Return rows matching in both tables |
| LEFT JOIN | SELECT * FROM a LEFT JOIN b ON a.id = b.a_id | All rows from left + matching from right |
| RIGHT JOIN | SELECT * FROM a RIGHT JOIN b ON a.id = b.a_id | All rows from right + matching from left |
| FULL OUTER JOIN | SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id | All rows from both tables |
| SELF JOIN | SELECT a.name, b.name FROM emp a JOIN emp b ON a.mgr_id = b.id | Join table to itself |
| CROSS JOIN | SELECT * FROM a CROSS JOIN b | Cartesian product — every combination |
| GROUP BY | GROUP BY col | Group rows for aggregate functions |
| HAVING | HAVING COUNT(*) > 5 | Filter groups (like WHERE but for aggregates) |
| UNION | SELECT ... UNION SELECT ... | Combine result sets (removes duplicates) |
| UNION ALL | SELECT ... UNION ALL SELECT ... | Combine result sets (keeps duplicates) |
| INTERSECT | SELECT ... INTERSECT SELECT ... | Rows in both result sets |
| EXCEPT / MINUS | SELECT ... EXCEPT SELECT ... | Rows in first but not second |
| Subquery | SELECT * FROM t WHERE id IN (SELECT id FROM t2) | Nested query |
| Correlated subquery | WHERE salary > (SELECT AVG(salary) FROM emp e2 WHERE e2.dept=e1.dept) | Subquery referencing outer query |
| WITH (CTE) | WITH cte AS (SELECT ...) SELECT * FROM cte | Common Table Expression — named subquery |
| Recursive CTE | WITH RECURSIVE cte AS (base UNION ALL recursive_step) | Hierarchical/recursive queries |
| CASE | CASE WHEN score>=90 THEN 'A' WHEN score>=80 THEN 'B' ELSE 'C' END | Conditional expression |
| COALESCE | COALESCE(col1, col2, 'default') | Return first non-null value |
| NULLIF | NULLIF(a, b) | Return NULL if a=b, else return a |
| CAST | CAST(col AS INTEGER) / CAST(col AS VARCHAR(50)) | Convert data type |
| CONCAT | CONCAT(first,' ',last) / first || ' ' || last | Concatenate strings |
| ALIAS | SELECT col AS alias, table AS t | Rename column or table in output |
-- Core SQL Examples — works on MySQL, PostgreSQL, SQLite
-- ── Schema ───────────────────────────────────────────────────
CREATE TABLE departments (
dept_id INTEGER PRIMARY KEY,
dept_name VARCHAR(100) NOT NULL,
budget DECIMAL(12,2) DEFAULT 0.00
);
CREATE TABLE employees (
emp_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
dept_id INTEGER REFERENCES departments(dept_id),
salary DECIMAL(10,2) NOT NULL,
hire_date DATE NOT NULL,
status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active','inactive','terminated')),
manager_id INTEGER REFERENCES employees(emp_id)
);
-- ── INSERT ───────────────────────────────────────────────────
INSERT INTO departments VALUES (1,'Engineering',500000),(2,'Marketing',200000),(3,'HR',150000);
INSERT INTO employees (emp_id,name,email,dept_id,salary,hire_date) VALUES
(1,'Alice Smith', 'alice@mwu.com',1,95000,'2020-03-01'),
(2,'Bob Jones', 'bob@mwu.com', 1,87000,'2021-06-15'),
(3,'Carol Davis', 'carol@mwu.com',2,72000,'2019-11-01'),
(4,'Dave Wilson', 'dave@mwu.com', 3,68000,'2022-01-10'),
(5,'Eve Martinez', 'eve@mwu.com', 1,102000,'2018-07-20');
-- ── SELECT with filters ──────────────────────────────────────
SELECT name, salary, hire_date
FROM employees
WHERE salary > 80000
AND status = 'active'
ORDER BY salary DESC;
-- ── JOINs ───────────────────────────────────────────────────
SELECT e.name, e.salary, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.status = 'active'
ORDER BY d.dept_name, e.salary DESC;
-- ── GROUP BY with HAVING ─────────────────────────────────────
SELECT d.dept_name,
COUNT(e.emp_id) AS headcount,
AVG(e.salary) AS avg_salary,
MAX(e.salary) AS max_salary,
MIN(e.hire_date) AS oldest_hire
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_id, d.dept_name
HAVING COUNT(e.emp_id) > 0
ORDER BY avg_salary DESC;
-- ── CASE expression ──────────────────────────────────────────
SELECT name, salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 80000 THEN 'Mid-level'
ELSE 'Junior'
END AS level
FROM employees ORDER BY salary DESC;
-- ── Subquery ─────────────────────────────────────────────────
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;
-- ── CTE ──────────────────────────────────────────────────────
WITH dept_stats AS (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary, ds.avg_sal,
ROUND((e.salary - ds.avg_sal) / ds.avg_sal * 100, 1) AS pct_above_avg
FROM employees e
JOIN dept_stats ds ON e.dept_id = ds.dept_id
ORDER BY pct_above_avg DESC;
-- ── UPDATE / DELETE ──────────────────────────────────────────
UPDATE employees SET salary = salary * 1.05 WHERE dept_id = 1;
DELETE FROM employees WHERE status = 'terminated' AND hire_date < '2020-01-01';