SQL Cheat Sheet

Data Manipulation Language (DML) Commands

SELECT

Retrieves data from a database.

Syntax:

SELECT column1, column2 
FROM table_name;

Example:

SELECT first_name, last_name 
FROM customers;

INSERT

Adds new records to a table.

Syntax:

INSERT INTO table_name (column1, column2) 
VALUES (value1, value2);

Example:

INSERT INTO customers (first_name, last_name) 
VALUES ('Mary', 'Doe');

UPDATE

Modifies existing records in a table.

Syntax:

UPDATE table_name 
SET column1 = value1, column2 = value2 
WHERE condition;

Example:

UPDATE employees 
SET employee_name = 'John Doe', department = 'Marketing' 
WHERE employee_id = 1;

DELETE

Removes records from a table.

Syntax:

DELETE FROM table_name 
WHERE condition;

Example:

DELETE FROM employees 
WHERE employee_name = 'John Doe';

Data Definition Language (DDL) Commands

CREATE

Creates a new database and objects, such as a table, index, view, or stored procedure.

Syntax:

CREATE TABLE table_name (
    column1 datatype1,
    column2 datatype2,
    ...
);

Example:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

ALTER

Adds, deletes, or modifies columns in an existing table.

Syntax:

ALTER TABLE table_name 
ADD column_name datatype;

Example:

ALTER TABLE customers 
ADD email VARCHAR(100);

DROP

Drops an existing table in a database.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE customers;

TRUNCATE

Deletes the data inside a table, but not the table itself.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE customers;

Data Control Language (DCL) Commands

GRANT

Gives specific privileges to users or roles.

Syntax:

GRANT SELECT, INSERT ON table_name 
TO user_name;

Example:

GRANT SELECT, INSERT ON employees 
TO 'john_doe';

REVOKE

Takes away privileges previously granted to users or roles.

Syntax:

REVOKE SELECT, INSERT ON table_name 
FROM user_name;

Example:

REVOKE SELECT, INSERT ON employees 
FROM 'john_doe';

Query Clauses in SQL

DISTINCT

Removes duplicate values from the result set.

Syntax:

SELECT DISTINCT column1 FROM table_name;

Example:

SELECT DISTINCT department FROM employees;

LIMIT

Restricts the number of rows returned by the query (MySQL, PostgreSQL, SQLite).

Syntax:

SELECT * FROM table_name LIMIT 5;

Example:

SELECT name FROM students LIMIT 10;

OFFSET

Skips the first N rows before starting to return rows from the query.

Syntax:

SELECT * FROM table_name LIMIT 5 OFFSET 10;

Example:

SELECT name FROM students LIMIT 10 OFFSET 20;

FETCH FIRST

Limits the number of rows (used in Oracle 12c+, PostgreSQL, DB2).

Syntax:

SELECT * FROM table_name FETCH FIRST 5 ROWS ONLY;

Example:

SELECT name FROM employees FETCH FIRST 3 ROWS ONLY;

ROWNUM

A pseudo column in Oracle that limits rows in the result.

Syntax:

SELECT * FROM employees WHERE ROWNUM <= 5;

Example:

SELECT name FROM employees WHERE ROWNUM <= 3;

Querying Data Commands

WHERE

Filters rows based on a specified condition.

Syntax:

SELECT * FROM table_name 
WHERE condition;

Example:

SELECT * FROM customers 
WHERE age > 30;

ORDER BY

Sorts the result set in ascending or descending order.

Syntax:

SELECT * FROM table_name 
ORDER BY column_name ASC|DESC;

Example:

SELECT * FROM products 
ORDER BY price DESC;

GROUP BY

Groups rows based on values in a specified column. Often used with aggregate functions.

Syntax:

SELECT column_name, COUNT(*) 
FROM table_name 
GROUP BY column_name;

Example:

SELECT category, COUNT(*) 
FROM products 
GROUP BY category;

HAVING

Filters grouped results based on a specified condition.

Syntax:

SELECT column_name, COUNT(*) 
FROM table_name 
GROUP BY column_name 
HAVING condition;

Example:

SELECT category, COUNT(*) 
FROM products 
GROUP BY category 
HAVING COUNT(*) > 5;

Joining Commands

INNER JOIN

Returns rows with matching values in both tables.

Syntax:

SELECT * FROM table1 
INNER JOIN table2 ON table1.column = table2.column;

Example:

SELECT * FROM customers
INNER JOIN orders ON customers.customers.id = orders.customer;

LEFT JOIN

Returns all rows from the left table and matching rows from the right table.

Syntax:

SELECT * FROM table1 
LEFT JOIN table2 ON table1.column = table2.column;

Example:

SELECT * FROM students
LEFT JOIN departments ON students.students.id = departments.student_id;

RIGHT JOIN

Returns all rows from the right table and matching rows from the left table.

Syntax:

SELECT * FROM table1 
RIGHT JOIN table2 ON table1.column = table2.column;

Example:

SELECT * FROM students
RIGHT JOIN departments ON students.students.id = departments.student_id;

FULL JOIN

Returns all rows when there is a match in either the left or right table.

Syntax:

SELECT * FROM table1 
FULL JOIN table2 ON table1.column = table2.column;

Example:

SELECT * FROM employees 
FULL JOIN departments ON employees.employee_id = departments.employee_id;

CROSS JOIN

Combines every row from the first table with every row from the second table.

Syntax:

SELECT * FROM table1 
CROSS JOIN table2;

Example:

SELECT * FROM employees 
CROSS JOIN departments;

SELF JOIN

A SELF JOIN is a regular join but the table is joined with itself. It is used to compare rows within the same table.

Syntax:

SELECT A.column_name, B.column_name 
FROM table_name A
JOIN table_name B ON A.common_column = B.common_column;

Example:

SELECT E1.Name AS Employee, E2.Name AS Manager
FROM Employees E1
JOIN Employees E2 ON E1.Manager_ID = E2.Employee_ID;

Subqueries in SQL

IN

Determines whether a value matches any value in a subquery result.

Syntax:

SELECT column(s) FROM table 
WHERE value IN (subquery);

Example:

SELECT * FROM customers 
WHERE city IN (SELECT city FROM suppliers);

ANY

Compares a value to any value returned by a subquery.

Syntax:

SELECT column(s) FROM table 
WHERE value < ANY (subquery);

Example:

SELECT * FROM products 
WHERE price < ANY (SELECT unit_price FROM supplier_products);

ALL

Compares a value to all values returned by a subquery.

Syntax:

SELECT column(s) FROM table 
WHERE value > ALL (subquery);

Example:

SELECT * FROM orders 
WHERE order_amount > ALL (SELECT total_amount FROM previous_orders);

Aggregate Functions

COUNT()

Counts the number of rows or non-null values in a specified column.

Syntax:

SELECT COUNT(column_name) 
FROM table_name;

Example:

SELECT COUNT(age) FROM employees;

SUM()

Calculates the sum of all values in a specified column.

Syntax:

SELECT SUM(column_name) 
FROM table_name;

Example:

SELECT SUM(revenue) FROM sales;

AVG()

Calculates the average (mean) of all values in a specified column.

Syntax:

SELECT AVG(column_name) 
FROM table_name;

Example:

SELECT AVG(price) FROM products;

MIN()

Returns the minimum (lowest) value in a specified column.

Syntax:

SELECT MIN(column_name) 
FROM table_name;

Example:

SELECT MIN(price) FROM products;

MAX()

Returns the maximum (highest) value in a specified column.

Syntax:

SELECT MAX(column_name) 
FROM table_name;

Example:

SELECT MAX(price) FROM products;

String Functions

CONCAT()

Concatenates two or more strings into a single string.

Syntax:

SELECT CONCAT(string1, string2, ...) AS concatenated_string FROM table_name;

Example:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

SUBSTRING()/SUBSTR()

Extracts a substring from a string.

Syntax:

SELECT SUBSTRING(string FROM start_position [FOR length]) AS substring FROM table_name;

Example:

SELECT SUBSTRING(product_name FROM 1 FOR 5) AS substring FROM products;

CHAR_LENGTH()/LENGTH()

Returns the length (number of characters) of a string.

Syntax:

SELECT CHAR_LENGTH(string) AS length FROM table_name;

Example:

SELECT CHAR_LENGTH(product_name) AS length FROM products;

UPPER()

Converts all characters in a string to uppercase.

Syntax:

SELECT UPPER(string) AS uppercase_string FROM table_name;

Example:

SELECT UPPER(first_name) AS uppercase_first_name FROM employees;

LOWER()

Converts all characters in a string to lowercase.

Syntax:

SELECT LOWER(string) AS lowercase_string FROM table_name;

Example:

SELECT LOWER(last_name) AS lowercase_last_name FROM employees;

TRIM()

Removes specified prefixes or suffixes (or whitespace by default) from a string.

Syntax:

SELECT TRIM([LEADING | TRAILING | BOTH] characters FROM string) AS trimmed_string FROM table_name;

Example:

SELECT TRIM(TRAILING ' ' FROM full_name) AS trimmed_full_name FROM customers;

LEFT()

Returns a specified number of characters from the left of a string.

Syntax:

SELECT LEFT(string, num_characters) AS left_string FROM table_name;

Example:

SELECT LEFT(product_name, 5) AS left_product_name FROM products;

RIGHT()

Returns a specified number of characters from the right of a string.

Syntax:

SELECT RIGHT(string, num_characters) AS right_string FROM table_name;

Example:

SELECT RIGHT(order_number, 4) AS right_order_number FROM orders;

REPLACE()

Replaces occurrences of a substring within a string.

Syntax:

SELECT REPLACE(string, old_substring, new_substring) AS replaced_string FROM table_name;

Example:

SELECT REPLACE(description, 'old_string', 'new_string') AS replaced_description FROM product_descriptions;

Date and Time Functions

CURRENT_DATE()

Returns the current date.

Syntax:

SELECT CURRENT_DATE() AS current_date;

CURRENT_TIME()

Returns the current time.

Syntax:

SELECT CURRENT_TIME() AS current_time;

CURRENT_TIMESTAMP()

Returns the current date and time.

Syntax:

SELECT CURRENT_TIMESTAMP() AS current_timestamp;

Extract Year and Month from a Date

In MySQL, you can extract the year and month separately using YEAR() and MONTH(), or combine them using DATE_FORMAT().

Syntax:

-- Separate year and month
SELECT YEAR(date_column) AS year_part, MONTH(date_column) AS month_part;

-- Combined year and month (e.g., '2024-07')
SELECT DATE_FORMAT(date_column, '%Y-%m') AS year_month;

Example:

SELECT YEAR('2024-07-12') AS year_part, MONTH('2024-07-12') AS month_part;

SELECT DATE_FORMAT('2024-07-12', '%Y-%m') AS year_month;

DATE_ADD()/DATE_SUB()

Adds or subtracts a specified number of days, months, or years to/from a date.

Syntax:

SELECT DATE_ADD(date_expression, INTERVAL value unit) AS new_date;

DATE_ADD Example:

SELECT DATE_ADD('2024-04-11', INTERVAL 1 DAY) AS new_date;

DATE_SUB Example:

SELECT DATE_SUB('2024-04-11', INTERVAL 1 DAY) AS new_date;

EXTRACT()

Extracts a specific part (e.g., year, month, day) from a date or time.

Syntax:

SELECT EXTRACT(part FROM date_expression) AS extracted_part;

Example:

SELECT EXTRACT(YEAR FROM '2024-04-11') AS extracted_part;

TO_CHAR()

Converts a date or time to a specified format.

Syntax:

SELECT TO_CHAR(date_expression, 'format') AS formatted_date;

Example:

SELECT TO_CHAR('2024-04-11', 'YYYY-MM-DD') AS formatted_date;

TIMESTAMPDIFF()

Calculates the difference between two timestamps in a specified unit (e.g., days, hours, minutes).

Syntax:

SELECT TIMESTAMPDIFF(unit, timestamp1, timestamp2) AS difference;

Example:

SELECT TIMESTAMPDIFF(DAY, '2024-04-10', '2024-04-11') AS difference;

DATEDIFF()

Calculates the difference in days between two dates.

Syntax:

SELECT DATEDIFF(date1, date2) AS difference_in_days;

Example:

SELECT DATEDIFF('2024-04-11', '2024-04-10') AS difference_in_days;

Conditional Expressions

CASE Statement

Allows you to perform conditional logic within a query.

Syntax:

SELECT
    column1,
    column2,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE default_result
    END AS alias
FROM table_name;

Example:

SELECT
    order_id,
    total_amount,
    CASE
        WHEN total_amount > 1000 THEN 'High Value Order'
        WHEN total_amount > 500 THEN 'Medium Value Order'
        ELSE 'Low Value Order'
    END AS order_status
FROM orders;

IF() Function

Evaluates a condition and returns a value based on the evaluation.

Syntax:

SELECT IF(condition, true_value, false_value) AS alias FROM table_name;

Example:

SELECT
    name,
    age,
    IF(age > 50, 'Senior', 'Junior') AS employee_category
FROM employees;

COALESCE() Function

Returns the first non-null value from a list of values.

Syntax:

SELECT COALESCE(value1, value2, ...) AS alias FROM table_name;

Example:

SELECT
    COALESCE(first_name, middle_name) AS preferred_name
FROM employees;

NULLIF() Function

Returns null if two specified expressions are equal.

Syntax:

SELECT NULLIF(expression1, expression2) AS alias FROM table_name;

Example:

SELECT
    NULLIF(total_amount, discounted_amount) AS diff_amount
FROM orders;

Set Operations

UNION

Combines the result sets of two or more SELECT statements into a single result set.

Syntax:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Example:

SELECT first_name, last_name FROM customers
UNION
SELECT first_name, last_name FROM employees;

INTERSECT

Returns the common rows that appear in both result sets.

Syntax:

SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

Example:

SELECT first_name, last_name FROM customers
INTERSECT
SELECT first_name, last_name FROM employees;

EXCEPT

Returns the distinct rows from the left result set that are not present in the right result set.

Syntax:

SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

Example:

SELECT first_name, last_name FROM customers
EXCEPT
SELECT first_name, last_name FROM employees;

Transaction Control Commands

COMMIT

Saves all the changes made during the current transaction and makes them permanent.

Syntax:

COMMIT;

Example:

BEGIN TRANSACTION;
INSERT INTO employees (name, age) VALUES ('Alice', 30);
UPDATE products SET price = 25.00 WHERE category = 'Electronics';
COMMIT;

ROLLBACK

Undoes all the changes made during the current transaction and discards them.

Syntax:

ROLLBACK;

Example:

BEGIN TRANSACTION;
INSERT INTO employees (name, age) VALUES ('Bob', 35);
UPDATE products SET price = 30.00 WHERE category = 'Electronics';
ROLLBACK;

SAVEPOINT

Sets a point within a transaction to which you can later roll back.

Syntax:

SAVEPOINT savepoint_name;

Example:

BEGIN TRANSACTION;
INSERT INTO employees (name, age) VALUES ('Carol', 28);
SAVEPOINT before_update;
UPDATE products SET price = 40.00 WHERE category = 'Electronics';
SAVEPOINT after_update;
DELETE FROM customers WHERE age > 60;
ROLLBACK TO before_update;
COMMIT;

ROLLBACK TO SAVEPOINT

Rolls back to a specific savepoint within a transaction.

Syntax:

ROLLBACK TO SAVEPOINT savepoint_name;

Example:

BEGIN TRANSACTION;
INSERT INTO employees (name, age) VALUES ('David', 42);
SAVEPOINT before_update;
UPDATE products SET price = 50.00 WHERE category = 'Electronics';
SAVEPOINT after_update;
DELETE FROM customers WHERE age > 60;
ROLLBACK TO SAVEPOINT before_update;
COMMIT;

SET TRANSACTION

Configures properties for the current transaction, such as isolation level and transaction mode.

Syntax:

SET TRANSACTION [ISOLATION LEVEL { READ COMMITTED | SERIALIZABLE }]

Example:

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO employees (name, age) VALUES ('Emily', 35);
UPDATE products SET price = 60.00 WHERE category = 'Electronics';
COMMIT;

Table of Contents

Data Manipulation Language (DML) Commands

Data Definition Language (DDL) Commands

Data Control Language (DCL) Commands

Query Clauses in SQL

Querying Data Commands

Joining Commands

Subqueries in SQL

Aggregate Functions

String Functions

Date and Time Functions

Conditional Expressions

Set Operations

Transaction Control Commands