SQL Syntax Cheatsheet - Complete Guide

A comprehensive reference covering SQL from basic to advanced concepts, including syntax,
functions, and best practices.

Table of Contents
1. Basic SQL Commands
2. Data Types
3. Constraints
4. SELECT Statement
5. JOIN Operations
6. Aggregate Functions
7. GROUP BY & HAVING
8. Subqueries
9. Common Table Expressions (CTEs)
10. Window Functions
11. Indexes
12. Performance Tuning
13. Advanced SQL Features

Basic SQL Commands

CREATE DATABASE

CREATE DATABASE database_name;

CREATE TABLE

CREATE TABLE table_name (

column1 datatype constraints,
column2 datatype constraints,
column3 datatype constraints,
...
);

Example:
 CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
hire_date DATE,
salary DECIMAL(10,2)
);

INSERT Statement

-- Insert specific columns

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

-- Insert all columns

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

-- Insert multiple rows

INSERT INTO table_name (column1, column2, column3)
VALUES
(value1, value2, value3),
(value4, value5, value6),
(value7, value8, value9);

SELECT Statement

SELECT column1, column2, ...

FROM table_name
WHERE condition;

-- Select all columns

SELECT * FROM table_name;

UPDATE Statement

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

DELETE Statement

DELETE FROM table_name

WHERE condition;
 -- Delete all records (keep table structure)
DELETE FROM table_name;

DROP Statement

-- Drop table
DROP TABLE table_name;

-- Drop database
DROP DATABASE database_name;

Data Types

Numeric Data Types

INT, INTEGER -- Whole numbers (-2,147,483,648 to 2,147,483,647)

BIGINT -- Large integers (-9,223,372,036,854,775,808 to 9,223,372,036,854,7
SMALLINT -- Small integers (-32,768 to 32,767)
TINYINT -- Very small integers (0 to 255)
DECIMAL(p,s) -- Fixed-point numbers (p=precision, s=scale)
NUMERIC(p,s) -- Same as DECIMAL
FLOAT(p) -- Floating-point numbers
REAL -- Single-precision floating-point
DOUBLE PRECISION -- Double-precision floating-point

Character Data Types

CHAR(n) -- Fixed-length string (padded with spaces)

VARCHAR(n) -- Variable-length string
TEXT -- Large text data
NCHAR(n) -- Unicode fixed-length string
NVARCHAR(n) -- Unicode variable-length string
NTEXT -- Large Unicode text data

Date and Time Data Types

DATE -- Date (YYYY-MM-DD)

TIME -- Time (HH:MM:SS)
DATETIME -- Date and time
DATETIME2 -- Enhanced datetime (SQL Server)
TIMESTAMP -- Date and time with timezone
YEAR -- Year value
Other Data Types

BOOLEAN -- TRUE/FALSE values

BINARY(n) -- Fixed-length binary data
VARBINARY(n) -- Variable-length binary data
IMAGE -- Large binary data
UUID/UNIQUEIDENTIFIER -- Universally unique identifier
JSON -- JSON data (MySQL, PostgreSQL)
XML -- XML data

Constraints

PRIMARY KEY

-- Single column primary key

CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100)
);

-- Composite primary key

CREATE TABLE order_details (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id)
);

-- Add primary key to existing table

ALTER TABLE table_name
ADD PRIMARY KEY (column_name);

FOREIGN KEY

-- Create table with foreign key

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Add foreign key to existing table

ALTER TABLE orders
ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

-- Foreign key with cascade options

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
 ON DELETE CASCADE
ON UPDATE CASCADE;

NOT NULL

CREATE TABLE employees (

employee_id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL
);

UNIQUE

CREATE TABLE users (

user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);

-- Add unique constraint

ALTER TABLE users
ADD CONSTRAINT unique_email UNIQUE (email);

CHECK

CREATE TABLE employees (

employee_id INT PRIMARY KEY,
age INT CHECK (age >= 18 AND age <= 65),
salary DECIMAL(10,2) CHECK (salary > 0)
);

-- Add check constraint

ALTER TABLE employees
ADD CONSTRAINT check_age CHECK (age >= 18);

DEFAULT

CREATE TABLE orders (

order_id INT PRIMARY KEY,
order_date DATE DEFAULT CURRENT_DATE,
status VARCHAR(20) DEFAULT 'pending'
);
SELECT Statement (Advanced)

Basic Syntax

SELECT [DISTINCT] column1, column2, ...

FROM table_name
WHERE condition
GROUP BY column1, column2, ...
HAVING condition
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]
LIMIT number;

WHERE Clause Operators

-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price BETWEEN 50 AND 200;
SELECT * FROM products WHERE category IN ('Electronics', 'Books');
SELECT * FROM products WHERE product_name LIKE 'Apple%';
SELECT * FROM products WHERE description IS NOT NULL;

-- Logical operators
SELECT * FROM products WHERE price > 100 AND category = 'Electronics';
SELECT * FROM products WHERE price > 500 OR category = 'Luxury';
SELECT * FROM products WHERE NOT category = 'Discontinued';

LIKE Pattern Matching

-- Wildcard characters
'%' -- Matches zero or more characters
'_' -- Matches exactly one character
'[]' -- Matches any single character within brackets
'[^]' -- Matches any character not within brackets

-- Examples
SELECT * FROM customers WHERE customer_name LIKE 'John%'; -- Starts with "John"
SELECT * FROM customers WHERE customer_name LIKE '%son'; -- Ends with "son"
SELECT * FROM customers WHERE customer_name LIKE '%and%'; -- Contains "and"
SELECT * FROM customers WHERE customer_name LIKE 'J_hn'; -- J, any char, hn
SELECT * FROM products WHERE product_code LIKE '[A-C]%'; -- Starts with A, B, or C

JOIN Operations
INNER JOIN

SELECT columns
FROM table1
INNER JOIN table2 ON [Link] = [Link];

-- Example
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

LEFT JOIN (LEFT OUTER JOIN)

SELECT columns
FROM table1
LEFT JOIN table2 ON [Link] = [Link];

-- Example
SELECT c.customer_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

RIGHT JOIN (RIGHT OUTER JOIN)

SELECT columns
FROM table1
RIGHT JOIN table2 ON [Link] = [Link];

-- 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

SELECT columns
FROM table1
FULL OUTER JOIN table2 ON [Link] = [Link];

-- Example
SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;
CROSS JOIN

SELECT columns
FROM table1
CROSS JOIN table2;

-- Example
SELECT p.product_name, c.category_name
FROM products p
CROSS JOIN categories c;

SELF JOIN

SELECT a.column1, b.column2

FROM table1 a, table1 b
WHERE condition;

-- Example: Find employees and their managers

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

Multiple Joins

SELECT o.order_id, c.customer_name, p.product_name, [Link]

FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_details od ON o.order_id = od.order_id
INNER JOIN products p ON od.product_id = p.product_id;

Aggregate Functions

Basic Aggregate Functions

COUNT(*) -- Count all rows

COUNT(column) -- Count non-NULL values
COUNT(DISTINCT column) -- Count unique non-NULL values
SUM(column) -- Sum of numeric values
AVG(column) -- Average of numeric values
MIN(column) -- Minimum value
MAX(column) -- Maximum value
Examples

-- Basic usage
SELECT COUNT(*) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(hire_date), MIN(hire_date) FROM employees;

-- With conditions
SELECT COUNT(*) FROM employees WHERE department = 'IT';
SELECT AVG(salary) FROM employees WHERE hire_date > '2020-01-01';

-- Multiple aggregates
SELECT
COUNT(*) as total_employees,
AVG(salary) as average_salary,
MAX(salary) as highest_salary,
MIN(salary) as lowest_salary
FROM employees;

GROUP BY & HAVING

GROUP BY Syntax

SELECT column1, aggregate_function(column2)

FROM table_name
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column1;

Examples

-- Group by single column

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department;

-- Group by multiple columns

SELECT department, job_title, AVG(salary) as avg_salary
FROM employees
GROUP BY department, job_title;

-- Group by with WHERE

SELECT department, COUNT(*) as employee_count
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department;
HAVING Clause

-- Filter groups after aggregation

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

-- Multiple conditions in HAVING

SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000 AND COUNT(*) > 3;

-- HAVING with WHERE

SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 45000;

Subqueries

Scalar Subqueries

-- Single value subquery

SELECT employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

IN Operator

SELECT employee_name
FROM employees
WHERE department_id IN (
SELECT department_id
FROM departments
WHERE location = 'New York'
);

NOT IN Operator

SELECT employee_name
FROM employees
WHERE department_id NOT IN (
SELECT department_id
FROM departments
 WHERE location = 'Remote'
);

EXISTS Operator

SELECT customer_name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);

NOT EXISTS Operator

SELECT customer_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);

ANY and ALL Operators

-- ANY: true if condition is met by any value

SELECT product_name
FROM products
WHERE price > ANY (
SELECT price
FROM products
WHERE category = 'Electronics'
);

-- ALL: true if condition is met by all values

SELECT product_name
FROM products
WHERE price > ALL (
SELECT price
FROM products
WHERE category = 'Books'
);
Correlated Subqueries

SELECT e1.employee_name, [Link]

FROM employees e1
WHERE [Link] > (
SELECT AVG([Link])
FROM employees e2
WHERE e2.department_id = e1.department_id
);

Common Table Expressions (CTEs)

Basic CTE Syntax

WITH cte_name AS (
SELECT columns
FROM table_name
WHERE condition
)
SELECT columns
FROM cte_name
WHERE condition;

Simple CTE Example

WITH department_avg AS (
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
)
SELECT [Link], d.avg_salary
FROM department_avg d
WHERE d.avg_salary > 50000;

Multiple CTEs

WITH
high_earners AS (
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 75000
),
department_info AS (
SELECT department_id, department_name, location
FROM departments
WHERE location = 'New York'
)
SELECT h.first_name, h.last_name, [Link], d.department_name
 FROM high_earners h
JOIN department_info d ON h.department_id = d.department_id;

Recursive CTEs

-- Employee hierarchy example

WITH employee_hierarchy AS (
-- Anchor member: top-level managers
SELECT employee_id, first_name, last_name, manager_id, 0 as level
FROM employees
WHERE manager_id IS NULL

UNION ALL

-- Recursive member
SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, [Link] + 1
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT employee_id, first_name, last_name, level
FROM employee_hierarchy
ORDER BY level, employee_id;

Window Functions

Basic Window Function Syntax

SELECT
column1,
window_function() OVER (
[PARTITION BY column1, column2, ...]
[ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...]
[ROWS|RANGE window_frame]
) AS alias
FROM table_name;

Ranking Functions

-- ROW_NUMBER: Unique sequential integers

SELECT
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as row_num
FROM employees;

-- RANK: Ranking with gaps for ties

SELECT
employee_name,
salary,
 RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- DENSE_RANK: Ranking without gaps for ties

SELECT
employee_name,
salary,
DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

-- NTILE: Divide rows into specified number of groups

SELECT
employee_name,
salary,
NTILE(4) OVER (ORDER BY salary DESC) as quartile
FROM employees;

Aggregate Window Functions

-- Running totals
SELECT
employee_name,
salary,
SUM(salary) OVER (ORDER BY hire_date) as running_total
FROM employees;

-- Moving averages
SELECT
employee_name,
salary,
AVG(salary) OVER (
ORDER BY hire_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as moving_avg_3_months
FROM employees;

-- Cumulative statistics
SELECT
employee_name,
salary,
COUNT(*) OVER (ORDER BY hire_date) as cumulative_hires,
AVG(salary) OVER (ORDER BY hire_date) as cumulative_avg_salary
FROM employees;

Partitioning

-- Ranking within groups

SELECT
employee_name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;
 -- Department statistics
SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) as dept_avg_salary,
salary - AVG(salary) OVER (PARTITION BY department) as salary_vs_dept_avg
FROM employees;

Offset Functions

-- LAG: Previous row value

SELECT
employee_name,
hire_date,
salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) as previous_salary
FROM employees;

-- LEAD: Next row value

SELECT
employee_name,
hire_date,
salary,
LEAD(salary, 1, 0) OVER (ORDER BY hire_date) as next_salary
FROM employees;

-- FIRST_VALUE and LAST_VALUE

SELECT
employee_name,
salary,
FIRST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY salary DESC
) as highest_dept_salary,
LAST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as lowest_dept_salary
FROM employees;

Window Frames

-- ROWS frame
SELECT
employee_name,
salary,
AVG(salary) OVER (
ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) as avg_salary_3_rows
 FROM employees;

-- RANGE frame
SELECT
employee_name,
hire_date,
salary,
SUM(salary) OVER (
ORDER BY hire_date
RANGE BETWEEN INTERVAL '30' DAY PRECEDING AND CURRENT ROW
) as salary_sum_last_30_days
FROM employees;

Indexes

Create Index

-- Basic index
CREATE INDEX index_name ON table_name (column1);

-- Composite index
CREATE INDEX index_name ON table_name (column1, column2, column3);

-- Unique index
CREATE UNIQUE INDEX index_name ON table_name (column1);

-- Partial index (with condition)

CREATE INDEX index_name ON table_name (column1) WHERE condition;

Index Types

-- Clustered index (SQL Server)

CREATE CLUSTERED INDEX index_name ON table_name (column1);

-- Non-clustered index
CREATE NONCLUSTERED INDEX index_name ON table_name (column1);

-- Covering index (includes additional columns)

CREATE INDEX index_name ON table_name (column1) INCLUDE (column2, column3);

-- Filtered index
CREATE INDEX index_name ON table_name (column1) WHERE column1 IS NOT NULL;

Drop Index

DROP INDEX index_name ON table_name;

-- Or in some databases:
DROP INDEX table_name.index_name;
Index Best Practices

-- Good for WHERE clauses

CREATE INDEX idx_customer_email ON customers (email);

-- Good for ORDER BY

CREATE INDEX idx_order_date ON orders (order_date DESC);

-- Good for JOIN conditions

CREATE INDEX idx_order_customer ON orders (customer_id);

-- Composite index for complex queries

CREATE INDEX idx_product_search ON products (category, price, availability);

Performance Tuning

Query Analysis

-- Execution plan
EXPLAIN SELECT * FROM table_name WHERE condition;
EXPLAIN ANALYZE SELECT * FROM table_name WHERE condition;

-- Query hints (SQL Server)

SELECT * FROM table_name WITH (INDEX(index_name)) WHERE condition;
SELECT * FROM table_name WITH (NOLOCK) WHERE condition;

Optimization Techniques

-- Use specific columns instead of *

SELECT column1, column2 FROM table_name;

-- Use WHERE instead of HAVING when possible

SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department;

-- Use EXISTS instead of IN for subqueries

SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

-- Use LIMIT to restrict results

SELECT * FROM large_table ORDER BY date DESC LIMIT 100;

-- Use UNION ALL instead of UNION when duplicates are acceptable

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;
Index Maintenance

-- Rebuild index
ALTER INDEX index_name ON table_name REBUILD;

-- Reorganize index
ALTER INDEX index_name ON table_name REORGANIZE;

-- Update statistics
UPDATE STATISTICS table_name;

-- Check index fragmentation

SELECT
OBJECT_NAME(object_id) as table_name,
index_id,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED');

Advanced SQL Features

CASE Statements

-- Simple CASE
SELECT
employee_name,
salary,
CASE
WHEN salary > 80000 THEN 'High'
WHEN salary > 50000 THEN 'Medium'
ELSE 'Low'
END as salary_category
FROM employees;

-- CASE in WHERE clause

SELECT * FROM products
WHERE CASE
WHEN category = 'Electronics' THEN price > 500
WHEN category = 'Books' THEN price > 20
ELSE price > 100
END;

COALESCE and ISNULL

-- COALESCE: Return first non-null value

SELECT
employee_name,
COALESCE(phone_mobile, phone_home, 'No phone') as contact_phone
FROM employees;

-- ISNULL (SQL Server): Replace NULL with specified value

 SELECT
employee_name,
ISNULL(middle_name, '') as middle_name
FROM employees;

String Functions

-- Common string functions

SELECT
UPPER(first_name) as first_name_upper,
LOWER(last_name) as last_name_lower,
LENGTH(email) as email_length,
SUBSTRING(phone, 1, 3) as area_code,
CONCAT(first_name, ' ', last_name) as full_name,
TRIM(address) as clean_address,
REPLACE(phone, '-', '') as clean_phone
FROM employees;

Date Functions

-- Date arithmetic and formatting

SELECT
hire_date,
CURRENT_DATE as today,
CURRENT_DATE - hire_date as days_employed,
EXTRACT(YEAR FROM hire_date) as hire_year,
DATE_FORMAT(hire_date, '%Y-%m') as hire_month,
DATE_ADD(hire_date, INTERVAL 1 YEAR) as anniversary
FROM employees;

Pivot Operations

-- Pivot example (SQL Server)

SELECT *
FROM (
SELECT department, job_title, salary
FROM employees
) source_table
PIVOT (
AVG(salary)
FOR job_title IN ([Manager], [Developer], [Analyst])
) as pivot_table;
Stored Procedures (SQL Server/MySQL)

-- Create stored procedure

CREATE PROCEDURE GetEmployeesByDepartment
@DepartmentName NVARCHAR(50)
AS
BEGIN
SELECT employee_id, first_name, last_name, salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = @DepartmentName;
END;

-- Execute stored procedure

EXEC GetEmployeesByDepartment @DepartmentName = 'IT';

Views

-- Create view
CREATE VIEW employee_summary AS
SELECT
e.employee_id,
e.first_name,
e.last_name,
d.department_name,
[Link]
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Use view
SELECT * FROM employee_summary WHERE department_name = 'IT';

-- Drop view
DROP VIEW employee_summary;

Transactions

-- Begin transaction
BEGIN TRANSACTION;

-- Perform operations
INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe');
UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT';

-- Commit or rollback
COMMIT;
-- OR
ROLLBACK;

-- Savepoints
BEGIN TRANSACTION;
INSERT INTO table1 VALUES (1, 'A');
 SAVEPOINT sp1;
INSERT INTO table1 VALUES (2, 'B');
ROLLBACK TO sp1;
COMMIT;

Best Practices and Tips

Query Writing Best Practices

1. Use specific column names instead of SELECT *
2. Filter early with WHERE clauses
3. Use appropriate data types for better performance
4. Normalize your database to reduce redundancy
5. Use indexes strategically on frequently queried columns
6. Avoid functions in WHERE clauses on indexed columns
7. Use EXISTS instead of IN for subqueries when possible
8. Consider partitioning for very large tables
9. Regular maintenance of indexes and statistics
10. Test query performance with realistic data volumes

Common Mistakes to Avoid

Using SELECT * in production code
Missing WHERE clauses in UPDATE/DELETE statements
Not using proper indexes
Ignoring NULL values in comparisons
Using functions on indexed columns in WHERE clauses
Creating too many indexes (impacts INSERT/UPDATE performance)
Not handling transactions properly
Using cursors when set-based operations are possible
This cheatsheet covers the essential SQL syntax and concepts from basic to advanced levels.
Keep it handy for quick reference during development and troubleshooting!