Complete SQL Reference Guide
Your comprehensive quick reference for all essential SQL commands, functions, and syntax. Perfect for beginners learning SQL and experienced developers who need a quick lookup.
Quick Navigation
Basic SELECT Operations
SELECT All Columns
Retrieve all columns from a table
Syntax:
SELECT * FROM table_name;Example:
SELECT * FROM employees;SELECT Specific Columns
Retrieve specific columns
Syntax:
SELECT column1, column2 FROM table_name;Example:
SELECT first_name, last_name FROM employees;SELECT with WHERE
Filter rows based on conditions
Syntax:
SELECT * FROM table_name WHERE condition;Example:
SELECT * FROM employees WHERE salary > 50000;SELECT DISTINCT
Get unique values only
Syntax:
SELECT DISTINCT column_name FROM table_name;Example:
SELECT DISTINCT department FROM employees;Filtering and Sorting
WHERE with AND/OR
Multiple conditions
Syntax:
SELECT * FROM table WHERE condition1 AND/OR condition2;Example:
SELECT * FROM employees WHERE salary > 50000 AND department = 'IT';WHERE with IN
Match any value in a list
Syntax:
SELECT * FROM table WHERE column IN (value1, value2);Example:
SELECT * FROM employees WHERE department IN ('IT', 'HR');WHERE with LIKE
Pattern matching with wildcards
Syntax:
SELECT * FROM table WHERE column LIKE pattern;Example:
SELECT * FROM employees WHERE first_name LIKE 'J%';ORDER BY
Sort results ascending or descending
Syntax:
SELECT * FROM table ORDER BY column ASC/DESC;Example:
SELECT * FROM employees ORDER BY salary DESC;LIMIT
Limit number of results
Syntax:
SELECT * FROM table LIMIT number;Example:
SELECT * FROM employees LIMIT 10;Aggregate Functions
COUNT
Count number of rows
Syntax:
SELECT COUNT(*) FROM table_name;Example:
SELECT COUNT(*) FROM employees WHERE department = 'IT';SUM
Calculate total sum
Syntax:
SELECT SUM(column_name) FROM table_name;Example:
SELECT SUM(salary) FROM employees;AVG
Calculate average value
Syntax:
SELECT AVG(column_name) FROM table_name;Example:
SELECT AVG(salary) FROM employees;MIN/MAX
Find minimum and maximum values
Syntax:
SELECT MIN(column), MAX(column) FROM table;Example:
SELECT MIN(salary), MAX(salary) FROM employees;GROUP BY
Group rows and apply aggregate functions
Syntax:
SELECT column, COUNT(*) FROM table GROUP BY column;Example:
SELECT department, COUNT(*) FROM employees GROUP BY department;HAVING
Filter groups after GROUP BY
Syntax:
SELECT column, COUNT(*) FROM table GROUP BY column HAVING condition;Example:
SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;JOIN Operations
INNER JOIN
Return rows that have matching values in both tables
Syntax:
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;Example:
SELECT e.first_name, d.department_name FROM employees e INNER JOIN departments d ON e.department_id = d.department_id;LEFT JOIN
Return all rows from left table and matching rows from right table
Syntax:
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;Example:
SELECT e.first_name, d.department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.department_id;RIGHT JOIN
Return all rows from right table and matching rows from left table
Syntax:
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;Example:
SELECT e.first_name, d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id = d.department_id;FULL OUTER JOIN
Return all rows when there's a match in either table
Syntax:
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;Example:
SELECT e.first_name, d.department_name FROM employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id;Data Modification
INSERT
Add new rows to a table
Syntax:
INSERT INTO table (column1, column2) VALUES (value1, value2);Example:
INSERT INTO employees (first_name, last_name, salary) VALUES ('John', 'Doe', 55000);UPDATE
Modify existing rows
Syntax:
UPDATE table SET column1 = value1 WHERE condition;Example:
UPDATE employees SET salary = 60000 WHERE employee_id = 1;DELETE
Remove rows from a table
Syntax:
DELETE FROM table WHERE condition;Example:
DELETE FROM employees WHERE employee_id = 1;Advanced Functions
CASE Statement
Conditional logic in SQL
Syntax:
SELECT CASE WHEN condition THEN result ELSE alternative END FROM table;Example:
SELECT first_name, CASE WHEN salary > 50000 THEN 'High' ELSE 'Standard' END as salary_level FROM employees;Subquery
Query within another query
Syntax:
SELECT * FROM table WHERE column IN (SELECT column FROM other_table);Example:
SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location = 'New York');UNION
Combine results from multiple queries
Syntax:
SELECT column FROM table1 UNION SELECT column FROM table2;Example:
SELECT first_name FROM employees UNION SELECT customer_name FROM customers;EXISTS
Check if subquery returns any rows
Syntax:
SELECT * FROM table1 WHERE EXISTS (SELECT 1 FROM table2 WHERE condition);Example:
SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE departments.department_id = employees.department_id);String Functions
CONCAT
Concatenate strings
Syntax:
SELECT CONCAT(string1, string2) FROM table;Example:
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM employees;SUBSTRING
Extract part of a string
Syntax:
SELECT SUBSTRING(column, start, length) FROM table;Example:
SELECT SUBSTRING(first_name, 1, 3) FROM employees;UPPER/LOWER
Convert to uppercase or lowercase
Syntax:
SELECT UPPER(column), LOWER(column) FROM table;Example:
SELECT UPPER(first_name), LOWER(last_name) FROM employees;LENGTH
Get string length
Syntax:
SELECT LENGTH(column) FROM table;Example:
SELECT first_name, LENGTH(first_name) FROM employees;Date Functions
Current Date/Time
Get current date and time
Syntax:
SELECT NOW(), CURRENT_DATE, CURRENT_TIME;Example:
SELECT NOW() as current_timestamp;Date Formatting
Format date display
Syntax:
SELECT DATE_FORMAT(date_column, format) FROM table;Example:
SELECT DATE_FORMAT(hire_date, '%Y-%m-%d') FROM employees;Date Arithmetic
Add or subtract time from dates
Syntax:
SELECT DATE_ADD(date, INTERVAL value unit) FROM table;Example:
SELECT DATE_ADD(hire_date, INTERVAL 1 YEAR) FROM employees;Date Difference
Calculate difference between dates
Syntax:
SELECT DATEDIFF(date1, date2) FROM table;Example:
SELECT DATEDIFF(CURRENT_DATE, hire_date) as days_employed FROM employees;Window Functions
ROW_NUMBER
Assign row numbers
Syntax:
SELECT ROW_NUMBER() OVER (ORDER BY column) FROM table;Example:
SELECT first_name, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank FROM employees;RANK
Rank rows with ties
Syntax:
SELECT RANK() OVER (ORDER BY column) FROM table;Example:
SELECT first_name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;LAG/LEAD
Access previous or next row value
Syntax:
SELECT LAG(column, 1) OVER (ORDER BY column) FROM table;Example:
SELECT first_name, salary, LAG(salary, 1) OVER (ORDER BY hire_date) as prev_salary FROM employees;Ready to Practice These Commands?
Now that you have the syntax reference, practice these SQL commands with our interactive exercises across 60+ real-world scenarios.
For Beginners
Start with basic SELECT statements and gradually work your way up to complex JOINs and functions.
Data Manipulation
Master INSERT, UPDATE, and DELETE operations for modifying database records.
Practice SQL Online
Apply what you've learned with hands-on practice exercises across multiple schemas.
Master SQL Functions & Aggregations
Learn aggregate functions for data analysis:
Master SQL JOIN operations: