SQL Syntax Cheat Sheet

A comprehensive SQL syntax reference covering commands, functions, operators, and concepts across MySQL, PostgreSQL, SQL Server, SQLite, and Oracle.

---

Table of Contents

1. Data Definition Language (DDL)
   • CREATE
   • ALTER
   • DROP
2. Data Manipulation Language (DML)
   • SELECT
   • INSERT
   • UPDATE
   • DELETE
3. Data Control Language (DCL)
   • GRANT
   • REVOKE
4. Transaction Control Language (TCL)
   • COMMIT
   • ROLLBACK
   • SAVEPOINT
5. Constraints
6. Operators
7. Functions
   • Aggregate Functions
   • String Functions
   • Date and Time Functions
8. Joins
   • INNER JOIN
   • LEFT JOIN
   • RIGHT JOIN
   • FULL OUTER JOIN
9. Subqueries
10. Views
11. Indexes
12. Stored Procedures and Functions
13. Common Clauses
    • WHERE
    • GROUP BY
    • HAVING
    • ORDER BY
    • LIMIT / TOP / FETCH
14. Data Types
15. Window Functions
16. Common Table Expressions (CTEs)
17. Database-Specific Syntax
18. Performance and Optimization
19. Common Mistakes and Fixes
20. Additional Resources

---

1. Data Definition Language (DDL)

CREATE

Create a Database

CREATE DATABASE database_name;

Create a Table

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    CreatedDate DATE DEFAULT CURRENT_DATE
);

ALTER

Add a Column

ALTER TABLE table_name
ADD column_name datatype [constraints];

Modify a Column

ALTER TABLE table_name
MODIFY COLUMN column_name new_datatype;

Drop a Column

ALTER TABLE table_name
DROP COLUMN column_name;

DROP

Drop a Table

DROP TABLE table_name;

Drop a Database

DROP DATABASE database_name;

---

2. Data Manipulation Language (DML)

SELECT

Basic Syntax

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

Example:

SELECT FirstName, LastName
FROM Customers
WHERE CreatedDate > '2023-01-01'
ORDER BY LastName ASC;

INSERT

Insert Into All Columns

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

Insert Into Specific Columns

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

Example:

INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('Alice', 'Smith', 'alice@example.com');

UPDATE

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

Example:

UPDATE Customers
SET Email = 'newemail@example.com'
WHERE CustomerID = 1;

DELETE

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Customers
WHERE CustomerID = 1;

---

3. Data Control Language (DCL)

GRANT

GRANT privilege_type ON object_name TO user [WITH GRANT OPTION];

Example:

GRANT SELECT, INSERT ON Customers TO 'db_user';

REVOKE

REVOKE privilege_type ON object_name FROM user;

Example:

REVOKE INSERT ON Customers FROM 'db_user';

---

4. Transaction Control Language (TCL)

COMMIT

COMMIT;

ROLLBACK

ROLLBACK;

SAVEPOINT

SAVEPOINT savepoint_name;

Rollback to Savepoint

ROLLBACK TO SAVEPOINT savepoint_name;

---

5. Constraints

• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Ensures all values in a column are unique.
• PRIMARY KEY: Uniquely identifies each record in a table.
• FOREIGN KEY: Ensures referential integrity between tables.
• CHECK: Ensures that all values in a column satisfy a specific condition.
• DEFAULT: Sets a default value for a column if none is specified.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

---

6. Operators

• Arithmetic Operators: +, -, *, /, %
• Comparison Operators: =, <> or !=, >, <, >=, <=
• Logical Operators: AND, OR, NOT
• Other Operators: BETWEEN, IN, LIKE, IS NULL, EXISTS

Example:

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 50;

SELECT * FROM Customers
WHERE Email LIKE '%@example.com';

---

7. Functions

Aggregate Functions

• COUNT(): Counts the number of rows.
• SUM(): Calculates the sum of a numeric column.
• AVG(): Calculates the average value.
• MIN(): Finds the minimum value.
• MAX(): Finds the maximum value.

Example:

SELECT COUNT(*) FROM Orders;

SELECT CustomerID, SUM(TotalAmount) as TotalSpent
FROM Orders
GROUP BY CustomerID;

String Functions

• UPPER(string): Converts to uppercase.
• LOWER(string): Converts to lowercase.
• SUBSTRING(string, start, length): Extracts a substring.
• TRIM(string): Removes whitespace.
• CONCAT(string1, string2, ...): Concatenates strings.

Example:

SELECT UPPER(FirstName), LOWER(LastName)
FROM Customers;

Date and Time Functions

• NOW(): Returns current date and time.
• CURDATE(): Returns current date.
• DATE_ADD(date, INTERVAL value unit): Adds a time interval to a date.
• DATEDIFF(date1, date2): Returns the difference between two dates.

Example:

SELECT NOW();

SELECT DATE_ADD(CURDATE(), INTERVAL 7 DAY);

---

8. Joins

INNER JOIN

Returns records with matching values in both tables.

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

Example:

SELECT Orders.OrderID, Customers.FirstName, Customers.LastName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

LEFT JOIN

Returns all records from the left table, and matched records from the right table.

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

RIGHT JOIN

Returns all records from the right table, and matched records from the left table.

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

FULL OUTER JOIN

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

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

Writing complex JOINs is error-prone. Describe your tables and relationships in English, and AI2SQL generates the correct JOIN for you.

---

9. Subqueries

A query nested inside another query.

SELECT column1
FROM table1
WHERE column2 = (SELECT column FROM table2 WHERE condition);

Example:

SELECT FirstName, LastName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE TotalAmount > 1000);

Nested subqueries getting complicated? Just describe what data you need. AI2SQL handles the nesting automatically.

---

10. Views

A virtual table based on the result set of an SQL statement.

Create a View

CREATE VIEW view_name AS
SELECT columns
FROM table
WHERE condition;

Example:

CREATE VIEW HighValueOrders AS
SELECT OrderID, CustomerID, TotalAmount
FROM Orders
WHERE TotalAmount > 1000;

---

11. Indexes

Used to speed up the retrieval of data.

Create an Index

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Create a Unique Index

CREATE UNIQUE INDEX index_name
ON table_name (column);

---

12. Stored Procedures and Functions

Stored Procedure

A set of SQL statements that can be stored and executed on the database server.

Create a Stored Procedure

CREATE PROCEDURE procedure_name (parameters)
BEGIN
    -- SQL statements
END;

Example:

CREATE PROCEDURE GetCustomerOrders(IN custID INT)
BEGIN
    SELECT * FROM Orders WHERE CustomerID = custID;
END;

User-Defined Function

A function that returns a single value.

Create a Function

CREATE FUNCTION function_name (parameters)
RETURNS datatype
BEGIN
    DECLARE variable datatype;
    -- SQL statements
    RETURN variable;
END;

Skip the boilerplate. Describe your procedure's logic in plain English and AI2SQL writes the full stored procedure.

---

13. Common Clauses

WHERE

Filters records that meet specific conditions.

SELECT * FROM table_name
WHERE condition;

GROUP BY

Groups rows that have the same values in specified columns.

SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1;

HAVING

Filters groups according to specified conditions.

SELECT column1, AGG_FUNC(column2)
FROM table_name
GROUP BY column1
HAVING condition;

ORDER BY

Sorts the result set.

SELECT * FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC];

LIMIT / TOP / FETCH

Limits the number of records returned.

• MySQL, PostgreSQL, SQLite

  SELECT * FROM table_name
  LIMIT number OFFSET offset;
  

• SQL Server

  SELECT TOP number * FROM table_name;
  

• Oracle

  SELECT * FROM table_name
  FETCH FIRST number ROWS ONLY;
  

Tired of looking up syntax differences between databases? AI2SQL generates the right syntax for your specific database.

---

14. Data Types

Numeric

• INT
• DECIMAL(p, s)
• FLOAT
• DOUBLE

String

• CHAR(n)
• VARCHAR(n)
• TEXT

Date and Time

• DATE
• TIME
• DATETIME
• TIMESTAMP

Binary

• BINARY
• VARBINARY
• BLOB

Boolean

• BOOLEAN or BIT

---

15. Window Functions

-- ROW_NUMBER
SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;

-- RANK and DENSE_RANK
SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) as rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

-- Running total
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;

-- LAG and LEAD
SELECT date, revenue,
       LAG(revenue, 1) OVER (ORDER BY date) as prev_day,
       revenue - LAG(revenue, 1) OVER (ORDER BY date) as daily_change
FROM daily_sales;

---

16. Common Table Expressions (CTEs)

-- Basic CTE
WITH active_customers AS (
    SELECT CustomerID, FirstName, LastName
    FROM Customers
    WHERE LastOrderDate > DATE_SUB(NOW(), INTERVAL 90 DAY)
)
SELECT * FROM active_customers;

-- Recursive CTE (org chart)
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

---

17. Database-Specific Syntax

PostgreSQL

-- UPSERT
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- JSON queries
SELECT data->>'name' as name FROM events WHERE data->>'type' = 'signup';

-- Array operations
SELECT * FROM posts WHERE tags @> ARRAY['sql', 'tutorial'];

MySQL

-- UPSERT
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- JSON queries
SELECT JSON_EXTRACT(data, '$.name') FROM events;

-- Full-text search
SELECT * FROM articles WHERE MATCH(title, body) AGAINST('sql tutorial');

SQL Server

-- MERGE (UPSERT)
MERGE INTO users AS target
USING (VALUES ('a@b.com', 'Alice')) AS source (email, name)
ON target.email = source.email
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (email, name) VALUES (source.email, source.name);

-- STRING_AGG
SELECT department, STRING_AGG(name, ', ') as team
FROM employees GROUP BY department;

---

18. Performance and Optimization

-- EXPLAIN query plan
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- Index hints (MySQL)
SELECT * FROM orders USE INDEX (idx_customer) WHERE customer_id = 42;

-- Avoid SELECT *
SELECT id, name, email FROM users WHERE active = 1;  -- faster than SELECT *

-- Use EXISTS instead of IN for large datasets
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

---

19. Common Mistakes and Fixes

-- WRONG: NULL comparison
SELECT * FROM users WHERE status = NULL;
-- CORRECT:
SELECT * FROM users WHERE status IS NULL;

-- WRONG: GROUP BY with non-aggregated columns
SELECT name, department, COUNT(*) FROM employees GROUP BY department;
-- CORRECT:
SELECT department, COUNT(*) FROM employees GROUP BY department;

-- WRONG: Using HAVING instead of WHERE
SELECT * FROM orders HAVING total > 100;
-- CORRECT:
SELECT * FROM orders WHERE total > 100;

-- WRONG: Cartesian join (missing JOIN condition)
SELECT * FROM orders, customers;
-- CORRECT:
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id;

---

20. Additional Resources

AI-Powered SQL Tools

• AI2SQL - Generate SQL queries from plain English descriptions. Supports MySQL, PostgreSQL, SQL Server, SQLite, and Oracle. No SQL knowledge required.

Official Documentation

• MySQL Documentation
• PostgreSQL Documentation
• SQL Server Documentation
• SQLite Documentation

Online Tutorials

• W3Schools SQL Tutorial
• TutorialsPoint SQL Tutorial

Practice Platforms

• SQLBolt
• HackerRank SQL Challenges
• LeetCode Database Problems

---

How to Use This Cheat Sheet

• Learning: Use this guide as a starting point to learn SQL syntax and concepts.
• Reference: Quickly look up syntax for SQL commands and functions.
• Contribution: Feel free to contribute by suggesting improvements or adding new sections.

License

This cheat sheet is released under the MIT License.

---

Note: SQL syntax may vary slightly between different database systems (e.g., MySQL, PostgreSQL, SQL Server, Oracle). Always refer to the official documentation of the database you are using for exact syntax and additional features.

---