SQL Syntax Reference

Basic Querying

Command Description Syntax Example
AS (Aliases) Gives columns or tables a temporary name for readability. SELECT col AS alias
FROM table;		 SELECT employee_name AS "Staff Name"
FROM employees;
SELECT TOP / LIMIT Restricts the number of rows returned. SELECT TOP (n) | LIMIT n; SELECT * FROM sales_logs
ORDER BY date DESC LIMIT 10;

Filtering

Command Description Syntax Example
AND / OR Used to filter records based on more than one condition. SELECT col
FROM table
WHERE cond1
AND|OR cond2;		 SELECT * FROM employees
WHERE department = 'Sales'
AND salary > 50000;
BETWEEN Filters values within a given range (inclusive). WHERE col
BETWEEN v1 AND v2;		 SELECT * FROM orders
WHERE order_date
BETWEEN '2024-01-01' AND '2024-03-31';
LIKE / ILIKE Pattern matching. % is a wildcard. ILIKE is case-insensitive. WHERE col LIKE 'pattern'; WHERE email LIKE '%@gmail.com';
ANY / ALL "ANY" returns true if any subquery values meet the condition; "ALL" requires all to meet it. SELECT col
FROM table
WHERE col operator ANY|ALL
(subquery);		 SELECT name FROM products
WHERE price > ANY
(SELECT price FROM orders WHERE quantity > 10);

Conditional Logic

Command Description Syntax Example
CASE Handles conditional logic (IF-THEN-ELSE). CASE
  WHEN cond1 THEN res1
  ELSE resN
END;		 SELECT product_name,
CASE WHEN stock < 10 THEN 'Low Stock'
ELSE 'In Stock'
END AS "Status"
FROM warehouse;

String Functions

Command Description Syntax Example
CONCAT Joins multiple strings into one. CONCAT(s1, s2, ...); SELECT CONCAT(first_name, ' ', last_name) AS "Full Name"
FROM employees;

Date Functions

Command Description Syntax Example
EXTRACT Extracts a portion of a date (Year, Month, Day, etc.). SELECT EXTRACT(part FROM col); SELECT EXTRACT(month FROM hire_date) AS "Hire Month"
FROM employees;

Aggregation

Command Description Syntax Example
AVG() Returns the average value of a numeric column. SELECT AVG(col)
FROM table;		 SELECT AVG(unit_price)
FROM inventory;
COUNT Returns the number of rows matching the criteria. SELECT COUNT(col) FROM table; SELECT COUNT(customer_id) FROM orders;
MAX() / MIN() Returns the largest or smallest value. SELECT MAX|MIN(col) FROM table; SELECT MAX(salary) FROM employees;

Grouping

Command Description Syntax Example
GROUP BY Groups rows sharing the same values into summary rows. SELECT col, aggregate(col)
FROM table
GROUP BY col;		 SELECT region, SUM(sales_total)
FROM store_data GROUP BY region;
HAVING Filters groups (used instead of WHERE for aggregates). SELECT col FROM table
GROUP BY col
HAVING condition;		 SELECT category, COUNT(*)
FROM products GROUP BY category
HAVING COUNT(*) > 20;

Joins

Command Description Syntax Example
JOINS Combines rows from tables based on a related column. SELECT * FROM T1
JOIN T2 ON T1.id = T2.id;		 SELECT orders.id, customers.name
FROM orders
INNER JOIN customers ON orders.cust_id = customers.id;

Math Operations

Command Description Syntax Example
Math (+, -, *, /) Performs calculations on numeric data. SELECT col1 * col2; SELECT (price * quantity) AS "Total Revenue"
FROM order_items;

Data Definition (DDL)

Command Description Syntax Example
CREATE TABLE Creates a new table in the database. CREATE TABLE table (
  col1 data_type,
  col2 data_type
);		 CREATE TABLE inventory (
  item_id INT,
  item_name VARCHAR(100)
);
ALTER TABLE Adds, deletes, or modifies columns in an existing table. ALTER TABLE table
ADD|DROP|ALTER COLUMN col datatype;		 ALTER TABLE customers
ADD email_address VARCHAR(255);

Data Manipulation (DML)

Command Description Syntax Example
UPDATE Modifies existing records in a table. UPDATE table
SET col = val
WHERE condition;		 UPDATE products SET price = 19.99
WHERE sku = 'A100';
DELETE Deletes specific rows. Caution: Omitting WHERE deletes all data. DELETE FROM table
WHERE condition;		 DELETE FROM customers
WHERE last_login < '2020-01-01';