SQL Commands

Essential SQL commands and query syntax reference.

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

SELECT * FROM posts LIMIT 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;

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

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;

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;