Loading W Code...
Complete SQL Reference with Examples
Master SQL from basic queries to advanced window functions and CTEs. Each command includes detailed syntax and practical examples.
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT
SELECT is the most fundamental SQL command used to retrieve data from one or more tables. Syntax: SELECT column1, column2 FROM table_name WHERE condition; Key Clauses: • SELECT: Specifies which columns to retrieve • FROM: Specifies the table(s) to query • WHERE: Filters rows based on conditions • DISTINCT: Removes duplicate rows • AS: Creates column aliases • LIMIT/TOP: Restricts number of rows returned
-- Select all columns
SELECT * FROM Employees;
-- Select specific columns
SELECT name, salary FROM Employees;
-- With WHERE clause
SELECT name, department FROM Employees WHERE salary > 50000;
-- Using DISTINCT
SELECT DISTINCT department FROM Employees;
-- Using aliases
SELECT name AS employee_name, salary * 12 AS annual_salary FROM Employees;
-- Multiple conditions
SELECT * FROM Employees
WHERE department = 'IT' AND salary > 60000;
-- BETWEEN and IN
SELECT * FROM Products WHERE price BETWEEN 100 AND 500;
SELECT * FROM Employees WHERE department IN ('IT', 'HR', 'Sales');
-- LIKE for pattern matching
SELECT * FROM Employees WHERE name LIKE 'J%'; -- Starts with J
SELECT * FROM Employees WHERE email LIKE '%@gmail.com'; -- Ends with
SELECT * FROM Employees WHERE name LIKE '_ohn'; -- Second char onwards is 'ohn'
-- ORDER BY
SELECT * FROM Employees ORDER BY salary DESC;
SELECT * FROM Employees ORDER BY department ASC, salary DESC;
-- LIMIT (MySQL) / TOP (SQL Server)
SELECT * FROM Employees ORDER BY salary DESC LIMIT 10;
-- SQL Server: SELECT TOP 10 * FROM Employees ORDER BY salary DESC;INSERT statement adds new rows to a table. Syntax: INSERT INTO table_name (columns) VALUES (values); Key Points: • Can insert single or multiple rows • Column order must match value order • Can omit column names if providing all values • Can insert from another table using SELECT • Auto-increment columns can be omitted
-- Insert single row (all columns)
INSERT INTO Employees VALUES (1, 'John Doe', 'IT', 75000, '2024-01-15');
-- Insert with specified columns
INSERT INTO Employees (name, department, salary)
VALUES ('Jane Smith', 'HR', 65000);
-- Insert multiple rows
INSERT INTO Employees (name, department, salary) VALUES
('Alice Brown', 'IT', 80000),
('Bob Wilson', 'Sales', 55000),
('Carol Davis', 'HR', 60000);
-- Insert from another table
INSERT INTO EmployeeBackup (name, department, salary)
SELECT name, department, salary FROM Employees WHERE department = 'IT';
-- Insert with DEFAULT values
INSERT INTO Products (name, price, stock)
VALUES ('Laptop', 999.99, DEFAULT);
-- Insert and get the auto-generated ID (MySQL)
INSERT INTO Orders (customer_id, total) VALUES (5, 150.00);
SELECT LAST_INSERT_ID();
-- PostgreSQL: INSERT ... RETURNING
INSERT INTO Orders (customer_id, total) VALUES (5, 150.00) RETURNING order_id;UPDATE statement modifies existing rows in a table. Syntax: UPDATE table_name SET column1 = value1 WHERE condition; Key Points: • Always use WHERE clause (unless updating all rows intentionally) • Can update multiple columns in one statement • Can use subqueries for values • Be careful: Without WHERE, ALL rows are updated!
-- Update single column
UPDATE Employees SET salary = 80000 WHERE emp_id = 101;
-- Update multiple columns
UPDATE Employees
SET salary = 85000, department = 'Senior IT'
WHERE emp_id = 101;
-- Update with calculation
UPDATE Products SET price = price * 1.10; -- 10% price increase
-- Update using CASE
UPDATE Employees SET salary =
CASE
WHEN department = 'IT' THEN salary * 1.15
WHEN department = 'HR' THEN salary * 1.10
ELSE salary * 1.05
END;
-- Update with subquery
UPDATE Employees
SET salary = (SELECT AVG(salary) FROM Employees WHERE department = 'IT')
WHERE emp_id = 102;
-- Update with JOIN (MySQL)
UPDATE Employees e
JOIN Departments d ON e.dept_id = d.dept_id
SET e.salary = e.salary * 1.10
WHERE d.dept_name = 'Engineering';
-- Safe update: Check rows first
SELECT * FROM Employees WHERE department = 'IT'; -- Preview
UPDATE Employees SET status = 'Active' WHERE department = 'IT';DELETE statement removes rows from a table. Syntax: DELETE FROM table_name WHERE condition; Key Points: • Always use WHERE clause (unless deleting all rows) • DELETE removes rows, TRUNCATE removes all rows faster • Can use subqueries in WHERE clause • Triggers fire on DELETE but not on TRUNCATE • DELETE can be rolled back, TRUNCATE often cannot
-- Delete specific rows
DELETE FROM Employees WHERE emp_id = 101;
-- Delete with multiple conditions
DELETE FROM Orders
WHERE status = 'Cancelled' AND order_date < '2024-01-01';
-- Delete using subquery
DELETE FROM Employees
WHERE dept_id IN (SELECT dept_id FROM Departments WHERE status = 'Closed');
-- Delete with JOIN (MySQL)
DELETE e FROM Employees e
JOIN Departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Obsolete';
-- Delete all rows (slower, logged, can rollback)
DELETE FROM TempTable;
-- TRUNCATE all rows (faster, minimal logging)
TRUNCATE TABLE TempTable;
-- Safe delete: Check rows first
SELECT COUNT(*) FROM Logs WHERE log_date < '2023-01-01'; -- Check count
DELETE FROM Logs WHERE log_date < '2023-01-01';
-- DELETE vs TRUNCATE vs DROP
-- DELETE: Removes rows, keeps structure, can rollback
-- TRUNCATE: Removes all rows, resets auto-increment, faster
-- DROP: Removes entire table including structureJOIN combines rows from two or more tables based on related columns. Types of JOINs: • INNER JOIN: Returns only matching rows from both tables • LEFT JOIN: All rows from left + matching from right (NULL if no match) • RIGHT JOIN: All rows from right + matching from left (NULL if no match) • FULL OUTER JOIN: All rows from both tables • CROSS JOIN: Cartesian product (every row with every row) • SELF JOIN: Table joined with itself
-- Sample Tables:
-- Employees: emp_id, name, dept_id
-- Departments: dept_id, dept_name
-- INNER JOIN (only matching rows)
SELECT e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id;
-- LEFT JOIN (all employees, even without department)
SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;
-- RIGHT JOIN (all departments, even without employees)
SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.dept_id = d.dept_id;
-- FULL OUTER JOIN (all from both)
SELECT e.name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d ON e.dept_id = d.dept_id;
-- CROSS JOIN (Cartesian product)
SELECT e.name, p.project_name
FROM Employees e
CROSS JOIN Projects p;
-- SELF JOIN (find employees and their managers)
SELECT e.name AS employee, m.name AS manager
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.emp_id;
-- Multiple JOINs
SELECT e.name, d.dept_name, p.project_name
FROM Employees e
JOIN Departments d ON e.dept_id = d.dept_id
JOIN Projects p ON e.project_id = p.project_id;
-- JOIN with WHERE
SELECT e.name, d.dept_name
FROM Employees e
JOIN Departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'IT' AND e.salary > 50000;Aggregate functions perform calculations on a set of values and return a single value. Common Functions: • COUNT(): Number of rows • SUM(): Total of numeric column • AVG(): Average value • MIN(): Smallest value • MAX(): Largest value • GROUP_CONCAT() / STRING_AGG(): Concatenate values GROUP BY: Groups rows with same values HAVING: Filters groups (like WHERE for aggregates)
-- Basic aggregations
SELECT COUNT(*) AS total_employees FROM Employees;
SELECT SUM(salary) AS total_salary FROM Employees;
SELECT AVG(salary) AS avg_salary FROM Employees;
SELECT MIN(salary) AS min_sal, MAX(salary) AS max_sal FROM Employees;
-- COUNT variations
SELECT COUNT(*) FROM Employees; -- All rows including NULL
SELECT COUNT(email) FROM Employees; -- Non-NULL values only
SELECT COUNT(DISTINCT department) FROM Employees; -- Unique values
-- GROUP BY
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM Employees
GROUP BY department;
-- GROUP BY multiple columns
SELECT department, job_title, AVG(salary)
FROM Employees
GROUP BY department, job_title;
-- HAVING (filter after grouping)
SELECT department, AVG(salary) AS avg_sal
FROM Employees
GROUP BY department
HAVING AVG(salary) > 60000;
-- WHERE vs HAVING
SELECT department, AVG(salary)
FROM Employees
WHERE status = 'Active' -- Filters rows BEFORE grouping
GROUP BY department
HAVING AVG(salary) > 50000; -- Filters groups AFTER grouping
-- Concatenate values (MySQL)
SELECT department, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ')
FROM Employees
GROUP BY department;
-- PostgreSQL: STRING_AGG
SELECT department, STRING_AGG(name, ', ' ORDER BY name)
FROM Employees
GROUP BY department;A subquery is a query nested inside another query. Can be used in SELECT, FROM, WHERE, or HAVING. Types: • Scalar Subquery: Returns single value • Row Subquery: Returns single row • Table Subquery: Returns table (used in FROM) • Correlated Subquery: References outer query (runs for each outer row) Key Operators: IN, NOT IN, EXISTS, NOT EXISTS, ANY, ALL
-- Scalar subquery in SELECT
SELECT name, salary,
(SELECT AVG(salary) FROM Employees) AS company_avg
FROM Employees;
-- Subquery in WHERE with IN
SELECT * FROM Employees
WHERE dept_id IN (SELECT dept_id FROM Departments WHERE location = 'NYC');
-- Subquery with comparison
SELECT * FROM Employees
WHERE salary > (SELECT AVG(salary) FROM Employees);
-- EXISTS (check if subquery returns any rows)
SELECT * FROM Departments d
WHERE EXISTS (SELECT 1 FROM Employees e WHERE e.dept_id = d.dept_id);
-- NOT EXISTS (departments with no employees)
SELECT * FROM Departments d
WHERE NOT EXISTS (SELECT 1 FROM Employees e WHERE e.dept_id = d.dept_id);
-- Correlated subquery (runs for each row)
SELECT e.name, e.salary
FROM Employees e
WHERE e.salary > (
SELECT AVG(salary) FROM Employees WHERE dept_id = e.dept_id
);
-- ANY and ALL
SELECT * FROM Employees
WHERE salary > ANY (SELECT salary FROM Employees WHERE department = 'IT');
SELECT * FROM Employees
WHERE salary > ALL (SELECT salary FROM Employees WHERE department = 'HR');
-- Subquery in FROM (derived table)
SELECT dept_summary.department, dept_summary.avg_sal
FROM (
SELECT department, AVG(salary) AS avg_sal
FROM Employees
GROUP BY department
) AS dept_summary
WHERE dept_summary.avg_sal > 60000;Window functions perform calculations across a set of rows related to the current row, without collapsing rows like GROUP BY. Components: • OVER(): Defines the window • PARTITION BY: Divides rows into groups • ORDER BY: Defines order within partition • ROWS/RANGE: Defines frame boundaries Common Functions: • ROW_NUMBER(): Sequential number • RANK(): Rank with gaps • DENSE_RANK(): Rank without gaps • LEAD()/LAG(): Access next/previous rows • FIRST_VALUE()/LAST_VALUE(): First/last in window • SUM()/AVG()/COUNT() OVER(): Running calculations
-- ROW_NUMBER: Unique sequential number
SELECT name, department, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM Employees;
-- ROW_NUMBER with PARTITION (restart numbering per department)
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM Employees;
-- RANK vs DENSE_RANK
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank, -- 1,2,2,4 (gaps)
DENSE_RANK() OVER (ORDER BY salary DESC) AS d_rank -- 1,2,2,3 (no gaps)
FROM Employees;
-- Top N per group (get top 3 earners per department)
SELECT * FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM Employees
) ranked WHERE rn <= 3;
-- LAG and LEAD (access adjacent rows)
SELECT name, salary,
LAG(salary, 1) OVER (ORDER BY emp_id) AS prev_salary,
LEAD(salary, 1) OVER (ORDER BY emp_id) AS next_salary
FROM Employees;
-- Running total
SELECT name, salary,
SUM(salary) OVER (ORDER BY emp_id) AS running_total
FROM Employees;
-- Running average
SELECT name, salary,
AVG(salary) OVER (ORDER BY emp_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM Employees;
-- Percentage of total
SELECT name, department, salary,
salary * 100.0 / SUM(salary) OVER () AS pct_of_total,
salary * 100.0 / SUM(salary) OVER (PARTITION BY department) AS pct_of_dept
FROM Employees;DDL commands define and modify database structure. Commands: • CREATE: Create new objects (database, table, index, view) • ALTER: Modify existing objects • DROP: Delete objects • TRUNCATE: Remove all data from table Constraints: • PRIMARY KEY: Unique identifier • FOREIGN KEY: Referential integrity • UNIQUE: No duplicates • NOT NULL: Cannot be empty • CHECK: Custom validation • DEFAULT: Default value
-- CREATE DATABASE
CREATE DATABASE CompanyDB;
USE CompanyDB;
-- CREATE TABLE with constraints
CREATE TABLE Employees (
emp_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE,
department VARCHAR(50) DEFAULT 'General',
salary DECIMAL(10,2) CHECK (salary > 0),
hire_date DATE DEFAULT CURRENT_DATE,
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES Employees(emp_id)
);
-- ALTER TABLE - Add column
ALTER TABLE Employees ADD phone VARCHAR(15);
-- ALTER TABLE - Modify column
ALTER TABLE Employees MODIFY COLUMN phone VARCHAR(20);
-- ALTER TABLE - Drop column
ALTER TABLE Employees DROP COLUMN phone;
-- ALTER TABLE - Add constraint
ALTER TABLE Employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);
-- ALTER TABLE - Rename column (MySQL 8+)
ALTER TABLE Employees RENAME COLUMN name TO full_name;
-- CREATE INDEX
CREATE INDEX idx_department ON Employees(department);
CREATE UNIQUE INDEX idx_email ON Employees(email);
-- DROP INDEX
DROP INDEX idx_department ON Employees;
-- DROP TABLE
DROP TABLE IF EXISTS TempTable;
-- TRUNCATE (faster than DELETE, resets auto-increment)
TRUNCATE TABLE Logs;
-- DROP vs TRUNCATE vs DELETE
-- DROP: Removes table + data + structure
-- TRUNCATE: Removes all data, keeps structure
-- DELETE: Removes specific rows, can use WHERECommon Table Expressions (CTEs) create temporary named result sets that simplify complex queries. CTE Benefits: • Improve readability • Enable recursive queries • Can be referenced multiple times • Better than subqueries for complex logic Other Advanced Features: • CASE WHEN: Conditional logic • COALESCE/NULLIF: NULL handling • UNION/INTERSECT/EXCEPT: Set operations
-- Simple CTE
WITH DeptSalary AS (
SELECT department, AVG(salary) AS avg_salary
FROM Employees
GROUP BY department
)
SELECT e.name, e.salary, ds.avg_salary
FROM Employees e
JOIN DeptSalary ds ON e.department = ds.department
WHERE e.salary > ds.avg_salary;
-- Multiple CTEs
WITH
HighEarners AS (
SELECT * FROM Employees WHERE salary > 80000
),
ITDept AS (
SELECT * FROM Employees WHERE department = 'IT'
)
SELECT * FROM HighEarners WHERE emp_id IN (SELECT emp_id FROM ITDept);
-- Recursive CTE (employee hierarchy)
WITH RECURSIVE EmpHierarchy AS (
-- Base case: top-level managers
SELECT emp_id, name, manager_id, 1 AS level
FROM Employees WHERE manager_id IS NULL
UNION ALL
-- Recursive case: employees under managers
SELECT e.emp_id, e.name, e.manager_id, eh.level + 1
FROM Employees e
JOIN EmpHierarchy eh ON e.manager_id = eh.emp_id
)
SELECT * FROM EmpHierarchy ORDER BY level, name;
-- CASE WHEN
SELECT name, salary,
CASE
WHEN salary >= 100000 THEN 'Senior'
WHEN salary >= 60000 THEN 'Mid-level'
ELSE 'Junior'
END AS level
FROM Employees;
-- COALESCE (first non-NULL value)
SELECT name, COALESCE(phone, email, 'No contact') AS contact
FROM Employees;
-- UNION (combine results, remove duplicates)
SELECT name, 'Employee' AS type FROM Employees
UNION
SELECT name, 'Customer' AS type FROM Customers;
-- UNION ALL (keep duplicates, faster)
SELECT department FROM Employees
UNION ALL
SELECT department FROM Contractors;
-- EXCEPT (in first but not in second)
SELECT email FROM AllUsers
EXCEPT
SELECT email FROM UnsubscribedUsers;Know the query execution order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY
Master JOINs - draw Venn diagrams to visualize INNER, LEFT, RIGHT, FULL
WHERE filters rows BEFORE grouping, HAVING filters AFTER grouping
Window functions (ROW_NUMBER, RANK, LEAD/LAG) are asked in senior interviews
Practice writing queries without IDE - interviewers often use whiteboard
Know when to use EXISTS vs IN, and correlated vs non-correlated subqueries