SQL Beast Logo
Syntax Manual

SQL Cheat Sheet Index

Browse through categorized templates covering standard SQL syntax. Copy snippets, check dialect optimizations, or study CTE and transaction structures.

SELECT All Columns

StandardBasics

Retrieves all available fields and rows from a target database table.

SELECT * FROM employees;

SELECT Specific Columns

StandardBasics

Retrieves only specified projections from the table, minimizing network load.

SELECT employee_id, first_name, salary FROM employees;

Filter with WHERE

StandardBasics

Filters row returns matching a specific logical condition.

SELECT * FROM employees WHERE salary > 75000 AND department = 'Engineering';

Create Table

PostgreSQLData Definition (DDL)

Establishes a new database relation with structured fields and constraint checks.

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100) CHECK (email LIKE '%@%'), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Add Column

StandardData Definition (DDL)

Mutates an existing schema structure to append a new nullable data field.

ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

Drop Table

StandardData Definition (DDL)

Destroys an active database table along with all its recorded indexes and rows.

DROP TABLE IF EXISTS archived_users;

Insert Record

StandardData Modification (DML)

Appends a new structured row values entry into a target relation.

INSERT INTO users (username, email) VALUES ('octocat', 'git@github.com');

Update Record

StandardData Modification (DML)

Modifies active fields in records matching a filtering index condition. WARNING: Always use WHERE!

UPDATE users SET status = 'active' WHERE user_id = 42;

Delete Record

StandardData Modification (DML)

Safely removes row objects matching criteria from the relation. WARNING: Always use WHERE!

DELETE FROM users WHERE last_login < NOW() - INTERVAL 1 YEAR;

INNER JOIN

StandardJoins

Combines two tables, returning rows only where the join key matches in both tables.

SELECT orders.order_id, customers.company_name FROM orders INNER JOIN customers ON orders.customer_id = customers.id;

LEFT OUTER JOIN

StandardJoins

Returns all rows from the left table, and matching rows from the right table. Fills NULLs if unmatched.

SELECT employees.last_name, departments.dept_name FROM employees LEFT JOIN departments ON employees.dept_id = departments.id;

GROUP BY & COUNT

StandardGrouping

Aggregates matching records together and performs arithmetic counting calculations.

SELECT department, COUNT(employee_id) AS total_staff FROM employees GROUP BY department ORDER BY total_staff DESC;

Filter Aggregates with HAVING

StandardGrouping

Filters aggregated grouping scopes (WHERE executes before aggregations, HAVING executes after).

SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department HAVING AVG(salary) > 85000;

Common Table Expressions (CTE)

StandardAdvanced (CTE/Window)

Encapsulates complex intermediate subqueries into readable sequential scopes.

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ) SELECT region, total_sales FROM regional_sales WHERE total_sales > 50000;

Window Function: ROW_NUMBER()

PostgreSQLAdvanced (CTE/Window)

Assigns ranks sequentially to partitioned subsets without collapsing matching rows.

SELECT employee_id, salary, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept FROM employees;

Create Standard Index

StandardIndexes

Constructs a B-Tree search index to accelerate lookups on high-selectivity columns.

CREATE INDEX idx_users_email ON users (email);

Create Composite Index

StandardIndexes

Establishes a single multi-column index. Ideal for composite equality filter parameters.

CREATE INDEX idx_orders_status_date ON orders (status, order_date DESC);

Transaction Block (ACID)

StandardTransactions

Ensures relational execution sequences either fully complete or safely rollback together.

BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE account_id = 1; UPDATE accounts SET balance = balance + 100 WHERE account_id = 2; COMMIT;