SQL Cheat Sheet

A complete reference of SQL commands with examples and usage instructions. Find the command you need using the search bar or browse by category.

🗃️ What is SQL?

SQL (Structured Query Language) is a standardized programming language designed for managing and manipulating relational databases. Created in the 1970s at IBM by Donald Chamberlin and Raymond Boyce, SQL has become the universal language for database operations across virtually all database management systems.

📈 Key Features

  • Declarative: Describe what you want, not how to get it
  • Standardized: Works across different database systems
  • Powerful: Handle complex data operations efficiently
  • Scalable: Manage databases from small to enterprise-scale
  • ACID Compliant: Ensures data integrity and consistency

👥 Who Uses SQL?

  • Data Analysts: Extract insights from business data
  • Software Developers: Build data-driven applications
  • Database Administrators: Manage and optimize databases
  • Business Intelligence: Create reports and dashboards
  • Data Scientists: Prepare and analyze datasets

🚀 Why Learn SQL?

Essential Skills:

  • Most in-demand technical skill for data professionals
  • Required for virtually all data-related roles
  • Foundation for advanced analytics and data science

Business Impact:

  • Make data-driven decisions quickly
  • Automate repetitive data tasks
  • Uncover hidden business insights

🔧 SQL Core Operations (CRUD)

CREATE

Add new data

READ

Query existing data

UPDATE

Modify existing data

DELETE

Remove data

🌐 Popular SQL Database Systems

Open Source:

  • MySQL - Web applications
  • PostgreSQL - Advanced features
  • SQLite - Lightweight, embedded

Commercial:

  • Oracle Database - Enterprise
  • Microsoft SQL Server - Windows ecosystem
  • IBM Db2 - Mainframe/enterprise

Cloud:

  • Amazon RDS - AWS managed
  • Google Cloud SQL - GCP managed
  • Azure SQL - Microsoft managed

🎯 Getting Started with SQL

1. Choose a Database: Start with SQLite (no setup) or MySQL (widely used)
2. Practice Environment: Use online tools like SQLiteBrowser, phpMyAdmin, or DB Fiddle
3. Start Simple: Begin with SELECT statements to query existing data
4. Build Complexity: Progress to JOINs, subqueries, and data modification
5. Real Projects: Apply skills to actual datasets and business problems
37 commands found
Filter by category:

SELECT

Basic

Retrieve data from one or more tables

Syntax:

SELECT column1, column2 FROM table_name WHERE condition

Examples:

SELECT * FROM users; Select all columns from users table
SELECT name, email FROM users; Select specific columns
SELECT * FROM users WHERE age > 25; Select with condition
SELECT COUNT(*) FROM users; Count total rows

Notes:

The most fundamental SQL command for querying data

INSERT

Basic

Add new rows to a table

Syntax:

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

Examples:

INSERT INTO users (name, email) VALUES ('John', 'john@email.com'); Insert single row
INSERT INTO users VALUES ('Jane', 'jane@email.com', 30); Insert without specifying columns
INSERT INTO users (name, email) VALUES ('Bob', 'bob@email.com'), ('Alice', 'alice@email.com'); Insert multiple rows

Notes:

Use specific column names for better maintainability

UPDATE

Basic

Modify existing data in a table

Syntax:

UPDATE table_name SET column1 = value1 WHERE condition

Examples:

UPDATE users SET age = 31 WHERE name = 'John'; Update specific row
UPDATE users SET email = 'newemail@domain.com', age = age + 1; Update multiple columns
UPDATE users SET status = 'active' WHERE created_date > '2023-01-01'; Update with date condition

Notes:

Always use WHERE clause to avoid updating all rows accidentally

DELETE

Basic

Remove rows from a table

Syntax:

DELETE FROM table_name WHERE condition

Examples:

DELETE FROM users WHERE id = 5; Delete specific row
DELETE FROM users WHERE age < 18; Delete multiple rows
DELETE FROM users; Delete all rows (use with caution)

Notes:

Always use WHERE clause unless you really want to delete all rows

CREATE DATABASE

Database

Create a new database

Syntax:

CREATE DATABASE database_name

Examples:

CREATE DATABASE myapp; Create a new database
CREATE DATABASE IF NOT EXISTS myapp; Create only if doesn't exist
CREATE DATABASE myapp CHARACTER SET utf8mb4; Create with specific character set

Notes:

Database names should follow naming conventions

DROP DATABASE

Database

Delete an entire database

Syntax:

DROP DATABASE database_name

Examples:

DROP DATABASE myapp; Delete database permanently
DROP DATABASE IF EXISTS myapp; Delete only if exists

Notes:

This permanently deletes all data - use with extreme caution

USE

Database

Select a database to work with

Syntax:

USE database_name

Examples:

USE myapp; Switch to myapp database
USE information_schema; Switch to system database

Notes:

Must select a database before performing table operations

SHOW DATABASES

Database

List all databases

Syntax:

SHOW DATABASES

Examples:

SHOW DATABASES; List all available databases
SHOW DATABASES LIKE 'app%'; List databases matching pattern

Notes:

Useful for exploring available databases

CREATE TABLE

Tables

Create a new table

Syntax:

CREATE TABLE table_name (column1 datatype, column2 datatype)

Examples:

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255)); Create simple table
CREATE TABLE posts (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255), content TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP); Create table with auto-increment and timestamp
CREATE TABLE IF NOT EXISTS categories (id INT PRIMARY KEY, name VARCHAR(50) UNIQUE); Create only if doesn't exist

Notes:

Define appropriate data types and constraints for each column

ALTER TABLE

Tables

Modify an existing table structure

Syntax:

ALTER TABLE table_name ADD/DROP/MODIFY column_name datatype

Examples:

ALTER TABLE users ADD phone VARCHAR(20); Add new column
ALTER TABLE users DROP COLUMN phone; Remove column
ALTER TABLE users MODIFY email VARCHAR(320); Change column type
ALTER TABLE users RENAME TO customers; Rename table

Notes:

Be careful when modifying tables with existing data

DROP TABLE

Tables

Delete a table and all its data

Syntax:

DROP TABLE table_name

Examples:

DROP TABLE users; Delete table permanently
DROP TABLE IF EXISTS temp_table; Delete only if exists

Notes:

This permanently deletes the table and all data

DESCRIBE

Tables

Show table structure

Syntax:

DESCRIBE table_name

Examples:

DESCRIBE users; Show column details
DESC users; Short form of DESCRIBE
SHOW COLUMNS FROM users; Alternative syntax

Notes:

Useful for understanding table schema

INNER JOIN

Joins

Return rows that have matching values in both tables

Syntax:

SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column

Examples:

SELECT u.name, p.title FROM users u INNER JOIN posts p ON u.id = p.user_id; Join users with their posts
SELECT * FROM orders o INNER JOIN customers c ON o.customer_id = c.id; Join orders with customer details

Notes:

Only returns rows where the join condition is met in both tables

LEFT JOIN

Joins

Return all rows from left table and matching rows from right table

Syntax:

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column

Examples:

SELECT u.name, p.title FROM users u LEFT JOIN posts p ON u.id = p.user_id; All users, with posts if they exist
SELECT c.name, COUNT(o.id) FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id; Customer order counts

Notes:

Includes all rows from the left table, even if no match in right table

RIGHT JOIN

Joins

Return all rows from right table and matching rows from left table

Syntax:

SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column

Examples:

SELECT u.name, p.title FROM users u RIGHT JOIN posts p ON u.id = p.user_id; All posts, with user info if available

Notes:

Less commonly used than LEFT JOIN

FULL OUTER JOIN

Joins

Return rows when there is a match in either table

Syntax:

SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column

Examples:

SELECT u.name, p.title FROM users u FULL OUTER JOIN posts p ON u.id = p.user_id; All users and all posts

Notes:

Not supported in all databases (e.g., MySQL)

COUNT

Functions

Count the number of rows

Syntax:

COUNT(column_name) or COUNT(*)

Examples:

SELECT COUNT(*) FROM users; Count all rows
SELECT COUNT(email) FROM users; Count non-null emails
SELECT status, COUNT(*) FROM users GROUP BY status; Count by group

Notes:

COUNT(*) includes NULL values, COUNT(column) excludes them

SUM

Functions

Calculate the sum of numeric values

Syntax:

SUM(column_name)

Examples:

SELECT SUM(amount) FROM orders; Total order amount
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id; Sum by customer

Notes:

Only works with numeric columns

AVG

Functions

Calculate the average of numeric values

Syntax:

AVG(column_name)

Examples:

SELECT AVG(age) FROM users; Average user age
SELECT department, AVG(salary) FROM employees GROUP BY department; Average salary by department

Notes:

Automatically excludes NULL values from calculation

MIN/MAX

Functions

Find minimum or maximum values

Syntax:

MIN(column_name) or MAX(column_name)

Examples:

SELECT MIN(price), MAX(price) FROM products; Price range
SELECT MIN(created_date) FROM users; First user registration

Notes:

Works with numeric, date, and string columns

CONCAT

Functions

Concatenate strings

Syntax:

CONCAT(string1, string2, ...)

Examples:

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users; Combine first and last name
SELECT CONCAT('Hello, ', name, '!') FROM users; Create greeting message

Notes:

Use CONCAT_WS for separator-based concatenation

SUBSTRING

Functions

Extract part of a string

Syntax:

SUBSTRING(string, start, length)

Examples:

SELECT SUBSTRING(email, 1, POSITION('@' IN email) - 1) AS username FROM users; Extract username from email
SELECT SUBSTRING(phone, 1, 3) AS area_code FROM contacts; Extract area code

Notes:

Position starts from 1, not 0

UPPER/LOWER

Functions

Convert string case

Syntax:

UPPER(string) or LOWER(string)

Examples:

SELECT UPPER(name) FROM users; Convert to uppercase
SELECT LOWER(email) FROM users; Convert to lowercase

Notes:

Useful for case-insensitive comparisons

WHERE

Filtering

Filter rows based on conditions

Syntax:

WHERE condition

Examples:

SELECT * FROM users WHERE age >= 18; Numeric comparison
SELECT * FROM users WHERE name LIKE 'John%'; Pattern matching
SELECT * FROM users WHERE email IS NOT NULL; NULL check
SELECT * FROM users WHERE age BETWEEN 25 AND 35; Range condition

Notes:

Use appropriate operators: =, !=, <, >, <=, >=, LIKE, IN, BETWEEN

ORDER BY

Filtering

Sort query results

Syntax:

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]

Examples:

SELECT * FROM users ORDER BY name; Sort by name (ascending)
SELECT * FROM users ORDER BY age DESC; Sort by age (descending)
SELECT * FROM users ORDER BY last_name, first_name; Sort by multiple columns

Notes:

ASC is default, use DESC for descending order

GROUP BY

Filtering

Group rows that have the same values

Syntax:

GROUP BY column1, column2

Examples:

SELECT department, COUNT(*) FROM employees GROUP BY department; Count employees by department
SELECT status, AVG(age) FROM users GROUP BY status; Average age by status

Notes:

Must use aggregate functions with non-grouped columns

HAVING

Filtering

Filter groups based on aggregate conditions

Syntax:

HAVING condition

Examples:

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5; Departments with more than 5 employees
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id HAVING SUM(amount) > 1000; High-value customers

Notes:

Use HAVING for aggregate conditions, WHERE for row conditions

LIMIT

Filtering

Limit the number of rows returned

Syntax:

LIMIT number [OFFSET number]

Examples:

SELECT * FROM users LIMIT 10; Get first 10 users
SELECT * FROM users ORDER BY created_date DESC LIMIT 5; Get 5 newest users
SELECT * FROM users LIMIT 10 OFFSET 20; Skip 20 rows, then get 10

Notes:

Useful for pagination and performance optimization

Subquery

Advanced

Query within another query

Syntax:

SELECT ... WHERE column IN (SELECT ...)

Examples:

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders); Users who have orders
SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products); Products above average price

Notes:

Can be used in SELECT, WHERE, and FROM clauses

UNION

Advanced

Combine results from multiple queries

Syntax:

SELECT ... UNION SELECT ...

Examples:

SELECT name FROM customers UNION SELECT name FROM suppliers; All names from both tables
SELECT 'Customer' as type, name FROM customers UNION SELECT 'Supplier', name FROM suppliers; Combined with type indicator

Notes:

UNION removes duplicates, use UNION ALL to keep them

CASE

Advanced

Conditional logic in SQL

Syntax:

CASE WHEN condition THEN result ELSE result END

Examples:

SELECT name, CASE WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END as category FROM users; Categorize by age
SELECT name, CASE status WHEN 'active' THEN 'Active User' WHEN 'inactive' THEN 'Inactive User' ELSE 'Unknown' END FROM users; Status labels

Notes:

Useful for creating computed columns and conditional logic

CREATE INDEX

Performance

Create an index to improve query performance

Syntax:

CREATE INDEX index_name ON table_name (column1, column2)

Examples:

CREATE INDEX idx_email ON users (email); Index on email column
CREATE INDEX idx_name_age ON users (last_name, first_name); Composite index
CREATE UNIQUE INDEX idx_username ON users (username); Unique index

Notes:

Indexes speed up queries but slow down writes

EXPLAIN

Performance

Show query execution plan

Syntax:

EXPLAIN SELECT ...

Examples:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com'; Analyze query performance
EXPLAIN ANALYZE SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id; Detailed execution analysis

Notes:

Essential for query optimization and performance tuning

PRIMARY KEY

Constraints

Define a primary key constraint

Syntax:

column_name datatype PRIMARY KEY

Examples:

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100)); Single column primary key
CREATE TABLE order_items (order_id INT, product_id INT, PRIMARY KEY (order_id, product_id)); Composite primary key

Notes:

Ensures uniqueness and creates clustered index

FOREIGN KEY

Constraints

Define a foreign key constraint

Syntax:

FOREIGN KEY (column) REFERENCES table(column)

Examples:

CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id)); Basic foreign key
ALTER TABLE posts ADD FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE; Add foreign key with cascade delete

Notes:

Maintains referential integrity between tables

UNIQUE

Constraints

Ensure column values are unique

Syntax:

column_name datatype UNIQUE

Examples:

CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) UNIQUE); Unique email constraint
ALTER TABLE users ADD CONSTRAINT uk_username UNIQUE (username); Add unique constraint

Notes:

Prevents duplicate values in specified columns

NOT NULL

Constraints

Ensure column cannot be empty

Syntax:

column_name datatype NOT NULL

Examples:

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100) NOT NULL); Required name field
ALTER TABLE users MODIFY email VARCHAR(255) NOT NULL; Make existing column required

Notes:

Prevents NULL values in specified columns

SQL Best Practices

Query Optimization

  • Use SELECT with specific columns instead of SELECT *
  • Always use WHERE clauses to limit results
  • Create indexes on frequently queried columns
  • Use LIMIT for large datasets
  • Avoid functions in WHERE clauses
  • Use EXPLAIN to analyze query performance

Data Safety

  • Always backup before running DROP or DELETE
  • Test queries on small datasets first
  • Use transactions for multiple related operations
  • Include WHERE clauses in UPDATE and DELETE
  • Use appropriate data types and constraints
  • Normalize your database design