-- Basic SELECT
SELECT * FROM users;
SELECT name, email FROM users;
-- DISTINCT values
SELECT DISTINCT country FROM users;
-- WHERE clause
SELECT * FROM users WHERE age > 18;
SELECT * FROM users WHERE country = 'USA' AND age >= 21;
SELECT * FROM users WHERE country IN ('USA', 'Canada', 'UK');
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE name LIKE 'John%';
SELECT * FROM users WHERE age BETWEEN 18 AND 65;
SELECT * FROM users WHERE middle_name IS NULL;
SELECT * FROM users WHERE middle_name IS NOT NULL;
-- ORDER BY
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY created_at DESC;
SELECT * FROM users ORDER BY country, name;
-- LIMIT & OFFSET
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20; -- Pagination
-- COUNT
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT country) FROM users;
SELECT country, COUNT(*) FROM users GROUP BY country;
-- SUM, AVG, MIN, MAX
SELECT SUM(amount) FROM orders;
SELECT AVG(age) FROM users;
SELECT MIN(price), MAX(price) FROM products;
-- GROUP BY with HAVING
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100;
-- Multiple aggregations
SELECT
category,
COUNT(*) as count,
AVG(price) as avg_price,
SUM(quantity) as total_quantity
FROM products
GROUP BY category;
-- INNER JOIN
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;
-- LEFT JOIN (all from left table)
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- RIGHT JOIN (all from right table)
SELECT users.name, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;
-- FULL OUTER JOIN
SELECT users.name, orders.total
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;
-- Multiple JOINs
SELECT
u.name,
o.id as order_id,
p.name as product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
-- Self JOIN
SELECT e.name, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Subquery in WHERE
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
-- Subquery in FROM
SELECT avg_orders.country, avg_orders.avg_total
FROM (
SELECT country, AVG(total) as avg_total
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY country
) avg_orders
WHERE avg_orders.avg_total > 500;
-- Correlated subquery
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.id AND o.total > 1000
);
-- Scalar subquery
SELECT
name,
(SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count
FROM users;
-- INSERT
INSERT INTO users (name, email) VALUES ('John', 'john@example.com');
-- Multiple INSERT
INSERT INTO users (name, email) VALUES
('John', 'john@example.com'),
('Jane', 'jane@example.com');
-- INSERT from SELECT
INSERT INTO archive_users
SELECT * FROM users WHERE created_at < '2023-01-01';
-- UPDATE
UPDATE users SET name = 'Johnny' WHERE id = 1;
UPDATE users SET
name = 'Johnny',
updated_at = NOW()
WHERE id = 1;
-- DELETE
DELETE FROM users WHERE id = 1;
DELETE FROM users WHERE created_at < '2020-01-01';
-- UPSERT (PostgreSQL)
INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com')
ON CONFLICT (id) DO UPDATE SET
name = EXCLUDED.name,
email = EXCLUDED.email;
-- UPSERT (MySQL)
INSERT INTO users (id, name, email)
VALUES (1, 'John', 'john@example.com')
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email);
-- CREATE TABLE
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
age INT CHECK (age >= 0),
country VARCHAR(50) DEFAULT 'USA',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- CREATE TABLE with foreign key
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
total DECIMAL(10, 2) NOT NULL,
status VARCHAR(20) DEFAULT 'pending'
);
-- ALTER TABLE
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ADD CONSTRAINT email_check CHECK (email LIKE '%@%');
-- DROP TABLE
DROP TABLE IF EXISTS users;
-- TRUNCATE (delete all rows)
TRUNCATE TABLE logs;
-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_country_age ON users(country, age);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
-- Drop index
DROP INDEX idx_users_email;
-- Partial index (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- Basic CTE
WITH active_users AS (
SELECT * FROM users WHERE active = true
)
SELECT * FROM active_users WHERE country = 'USA';
-- Multiple CTEs
WITH
active_users AS (
SELECT * FROM users WHERE active = true
),
user_orders AS (
SELECT user_id, COUNT(*) as order_count
FROM orders
GROUP BY user_id
)
SELECT u.name, uo.order_count
FROM active_users u
JOIN user_orders uo ON u.id = uo.user_id;
-- Recursive CTE (for hierarchical data)
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id, 0 as level
FROM employees
WHERE id = 1
UNION ALL
SELECT e.id, e.name, e.manager_id, s.level + 1
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;
-- ROW_NUMBER
SELECT
name,
country,
ROW_NUMBER() OVER (PARTITION BY country ORDER BY created_at) as row_num
FROM users;
-- RANK and DENSE_RANK
SELECT
name,
score,
RANK() OVER (ORDER BY score DESC) as rank,
DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM players;
-- Running total
SELECT
date,
amount,
SUM(amount) OVER (ORDER BY date) as running_total
FROM transactions;
-- Moving average
SELECT
date,
amount,
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM transactions;
-- LAG and LEAD
SELECT
date,
amount,
LAG(amount) OVER (ORDER BY date) as prev_amount,
LEAD(amount) OVER (ORDER BY date) as next_amount
FROM transactions;
-- String functions
CONCAT(first_name, ' ', last_name)
UPPER(name)
LOWER(name)
LENGTH(name)
TRIM(name)
SUBSTRING(name, 1, 3)
REPLACE(name, 'old', 'new')
-- Date functions
NOW()
CURRENT_DATE
DATE_PART('year', created_at)
DATE_TRUNC('month', created_at)
created_at + INTERVAL '1 day'
AGE(birth_date)
-- NULL handling
COALESCE(middle_name, '')
NULLIF(value, 0)
-- CASE expression
SELECT
name,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END as age_group
FROM users;