SQL Commands

Essential SQL commands and query syntax reference.

Data Query

SELECT

Retrieve data from tables

SELECT * FROM users WHERE age > 18;

SELECT DISTINCT

Retrieve unique values

SELECT DISTINCT country FROM customers;

WHERE

Filter records

SELECT * FROM products WHERE price > 100;

ORDER BY

Sort results

SELECT * FROM users ORDER BY name ASC;

LIMIT

Limit number of results (MySQL, PostgreSQL, SQLite)

SELECT * FROM posts LIMIT 10;

TOP

Limit number of results (SQL Server)

SELECT TOP 10 * FROM posts;

ROWNUM

Limit number of results (Oracle)

SELECT * FROM posts WHERE ROWNUM <= 10;

GROUP BY

Group rows by column

SELECT country, COUNT(*) FROM users GROUP BY country;

HAVING

Filter grouped results

SELECT country, COUNT(*) FROM users GROUP BY country HAVING COUNT(*) > 5;

Data Manipulation

INSERT INTO

Add new records

INSERT INTO users (name, email) VALUES ('John', 'john@example.com');

UPDATE

Modify existing records

UPDATE users SET email = 'new@email.com' WHERE id = 1;

DELETE

Remove records

DELETE FROM users WHERE id = 1;

TRUNCATE

Remove all records from table

TRUNCATE TABLE logs;

MERGE

Upsert operation (SQL Server, Oracle)

MERGE INTO target USING source ON target.id = source.id WHEN MATCHED THEN UPDATE SET name = source.name;

UPSERT

Insert or update (SQLite 3.24+)

INSERT INTO users (id, name) VALUES (1, 'John') ON CONFLICT(id) DO UPDATE SET name = 'John';

ON DUPLICATE KEY UPDATE

Upsert operation (MySQL)

INSERT INTO users (id, name) VALUES (1, 'John') ON DUPLICATE KEY UPDATE name = 'John';

Table Operations

CREATE TABLE

Create new table

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));

ALTER TABLE

Modify table structure

ALTER TABLE users ADD COLUMN age INT;

DROP TABLE

Delete table

DROP TABLE old_users;

CREATE INDEX

Create index for performance

CREATE INDEX idx_email ON users(email);

AUTO_INCREMENT

Auto-incrementing column (MySQL)

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY);

SERIAL

Auto-incrementing column (PostgreSQL)

CREATE TABLE users (id SERIAL PRIMARY KEY);

IDENTITY

Auto-incrementing column (SQL Server)

CREATE TABLE users (id INT IDENTITY(1,1) PRIMARY KEY);

SEQUENCE

Auto-incrementing sequence (Oracle)

CREATE SEQUENCE user_seq START WITH 1;

Joins

INNER JOIN

Match records in both tables

SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.id;

LEFT JOIN

All from left table, matching from right

SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;

RIGHT JOIN

All from right table, matching from left

SELECT * FROM orders RIGHT JOIN users ON orders.user_id = users.id;

FULL JOIN

All records from both tables

SELECT * FROM table1 FULL JOIN table2 ON table1.id = table2.id;

CROSS JOIN

Cartesian product of tables

SELECT * FROM table1 CROSS JOIN table2;

Aggregate Functions

COUNT()

Count number of rows

SELECT COUNT(*) FROM users;

SUM()

Sum of numeric values

SELECT SUM(amount) FROM orders;

AVG()

Average of values

SELECT AVG(price) FROM products;

MIN()

Minimum value

SELECT MIN(age) FROM users;

MAX()

Maximum value

SELECT MAX(salary) FROM employees;

GROUP_CONCAT()

Concatenate grouped values (MySQL)

SELECT GROUP_CONCAT(name) FROM users GROUP BY country;

STRING_AGG()

Concatenate grouped values (PostgreSQL, SQL Server)

SELECT STRING_AGG(name, ', ') FROM users GROUP BY country;

LISTAGG()

Concatenate grouped values (Oracle)

SELECT LISTAGG(name, ', ') FROM users GROUP BY country;

String Functions

CONCAT()

Concatenate strings

SELECT CONCAT(first_name, ' ', last_name) FROM users;

LENGTH() / LEN()

String length

SELECT LENGTH(name) FROM users;

UPPER()

Convert to uppercase

SELECT UPPER(name) FROM users;

LOWER()

Convert to lowercase

SELECT LOWER(email) FROM users;

SUBSTRING()

Extract substring

SELECT SUBSTRING(name, 1, 3) FROM users;

TRIM()

Remove whitespace

SELECT TRIM(name) FROM users;

REPLACE()

Replace substring

SELECT REPLACE(email, 'old.com', 'new.com') FROM users;

Date & Time Functions

NOW() / GETDATE()

Current date and time

SELECT NOW();

CURDATE() / CURRENT_DATE

Current date

SELECT CURDATE();

DATE_ADD()

Add interval to date (MySQL)

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

DATEADD()

Add interval to date (SQL Server)

SELECT DATEADD(day, 7, GETDATE());

INTERVAL

Add interval to date (PostgreSQL)

SELECT NOW() + INTERVAL '7 days';

DATE_FORMAT()

Format date (MySQL)

SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM users;

TO_CHAR()

Format date (PostgreSQL, Oracle)

SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM users;

FORMAT()

Format date (SQL Server)

SELECT FORMAT(created_at, 'yyyy-MM-dd') FROM users;

Transaction Control

BEGIN / START TRANSACTION

Start transaction

START TRANSACTION;

COMMIT

Save transaction changes

COMMIT;

ROLLBACK

Undo transaction changes

ROLLBACK;

SAVEPOINT

Set savepoint in transaction

SAVEPOINT sp1;

Database-Specific Features

SHOW DATABASES

List databases (MySQL)

SHOW DATABASES;

SHOW TABLES

List tables (MySQL)

SHOW TABLES;

DESCRIBE

Show table structure (MySQL)

DESCRIBE users;

\dt

List tables (PostgreSQL)

\dt

\d table_name

Show table structure (PostgreSQL)

\d users

SELECT name FROM sys.databases

List databases (SQL Server)

SELECT name FROM sys.databases;

SELECT * FROM INFORMATION_SCHEMA.TABLES

List tables (SQL Server, PostgreSQL, MySQL)

SELECT * FROM INFORMATION_SCHEMA.TABLES;

PRAGMA table_info

Show table structure (SQLite)

PRAGMA table_info(users);

SELECT * FROM USER_TABLES

List tables (Oracle)

SELECT * FROM USER_TABLES;