Basic Queries
-- Select all columns
SELECT * FROM users;
-- Select specific columns with filter
SELECT name, email FROM users WHERE active = true;
-- Order and limit
SELECT * FROM products ORDER BY price DESC LIMIT 10;
-- Distinct values
SELECT DISTINCT category FROM products;
-- Count
SELECT COUNT(*) FROM users WHERE role = 'admin';
Filtering
WHERE age > 25
WHERE name LIKE '%smith%' -- Contains
WHERE name LIKE 'J%' -- Starts with J
WHERE status IN ('active', 'pending')
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31'
WHERE email IS NOT NULL
WHERE age > 18 AND role = 'user'
WHERE city = 'Paris' OR city = 'London'
JOINs
-- INNER JOIN — matching rows only
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- LEFT JOIN — all from left + matching from right
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- Multiple joins
SELECT u.name, p.name, o.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;
Aggregation
SELECT category, COUNT(*) AS count, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY avg_price DESC;
Data Modification
-- Insert
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
-- Update
UPDATE users SET active = false WHERE last_login < '2025-01-01';
-- Delete
DELETE FROM users WHERE id = 42;
-- Upsert (PostgreSQL)
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;
