SQL Cheatsheet

Essential SQL commands, queries, and operations for database management.

Useful SQL Resources

W3Schools SQL Tutorial - Comprehensive SQL tutorial with examples
Mode SQL Tutorial - In-depth SQL for analysts
SQL Fiddle - Test and share SQL queries online
PostgreSQL Documentation - Official PostgreSQL docs

Database Operations

CREATE DATABASE database_name;

Create a new database

DROP DATABASE database_name;

Delete a database

USE database_name;

Select a database to work with

SHOW DATABASES;

List all databases

BACKUP DATABASE db TO DISK = 'path';

Create a backup of a database

Table Operations

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype
);

Create a new table

DROP TABLE table_name;

Delete a table

ALTER TABLE table_name ADD column_name datatype;

Add a column to a table

ALTER TABLE table_name DROP COLUMN column_name;

Remove a column from a table

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Change data type of a column

TRUNCATE TABLE table_name;

Remove all data from a table but keep structure

DESCRIBE table_name;

Show table structure

Data Manipulation

INSERT INTO table_name VALUES (value1, value2);

Insert new row with all values

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

Insert new row with specific columns

UPDATE table_name SET column1 = value1 WHERE condition;

Update existing data in a table

DELETE FROM table_name WHERE condition;

Delete rows from a table

SELECT * FROM table_name;

Select all columns from a table

SELECT column1, column2 FROM table_name;

Select specific columns

SELECT DISTINCT column FROM table_name;

Select unique values from a column

Query Filters

SELECT * FROM table_name WHERE condition;

Filter records that meet a condition

SELECT * FROM table_name WHERE column LIKE pattern;

Filter with pattern matching

SELECT * FROM table_name WHERE column IN (value1, value2);

Match any value in a list

SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;

Values within a range

SELECT * FROM table_name WHERE condition1 AND condition2;

Multiple conditions with AND

SELECT * FROM table_name WHERE condition1 OR condition2;

Multiple conditions with OR

SELECT * FROM table_name WHERE NOT condition;

Negate a condition

Sorting and Grouping

SELECT * FROM table_name ORDER BY column ASC;

Sort results in ascending order

SELECT * FROM table_name ORDER BY column DESC;

Sort results in descending order

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

Group rows and count occurrences

SELECT * FROM table_name GROUP BY column HAVING condition;

Filter groups with HAVING clause

SELECT * FROM table_name LIMIT number;

Limit the number of results returned

Joins

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

Inner join - matching rows from both tables

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

Left join - all rows from left table with matching rows from right

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

Right join - all rows from right table with matching rows from left

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

Full join - all rows when match in either table

SELECT * FROM table1 CROSS JOIN table2;

Cross join - Cartesian product of both tables

Aggregate Functions

SELECT COUNT(column) FROM table_name;

Count number of rows

SELECT SUM(column) FROM table_name;

Calculate sum of column values

SELECT AVG(column) FROM table_name;

Calculate average of column values

SELECT MIN(column) FROM table_name;

Find minimum value in column

SELECT MAX(column) FROM table_name;

Find maximum value in column

SELECT ROUND(column, decimals) FROM table_name;

Round numeric values

Subqueries

SELECT * FROM table_name 
WHERE column = (SELECT column FROM table2 WHERE condition);

Subquery in WHERE clause

SELECT column, (SELECT COUNT(*) FROM table2 WHERE table2.id = table1.id) 
FROM table1;

Subquery in SELECT clause

SELECT * FROM (SELECT * FROM table_name WHERE condition) AS subquery;

Subquery in FROM clause (derived table)

SQL Cheatsheet: Essential Database Commands