SQL Cheatsheet

CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE, age INT CHECK (age >= 0), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT TRUE ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id), amount DECIMAL(10,2), status VARCHAR(50) DEFAULT 'pending' );

ALTER TABLE users ADD COLUMN phone VARCHAR(20); ALTER TABLE users DROP COLUMN phone; ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(300); ALTER TABLE users RENAME COLUMN name TO full_name; ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email); DROP TABLE IF EXISTS temp_table;

INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 30); INSERT INTO users (name, email, age) VALUES ('Jane Smith', 'jane@example.com', 25), ('Bob Johnson', 'bob@example.com', 35); INSERT INTO users (name, email) SELECT name, email FROM temp_users WHERE active = true;

UPDATE users SET age = 31 WHERE id = 1; UPDATE users SET email = 'newemail@example.com', is_active = false WHERE age > 65; UPDATE users SET age = age + 1 WHERE birth_year = 1990;

DELETE FROM users WHERE id = 1; DELETE FROM users WHERE created_at < '2020-01-01'; DELETE FROM users WHERE status = 'inactive'; -- Delete all records (be careful!) DELETE FROM users; -- Truncate (faster, resets auto-increment) TRUNCATE TABLE users;

SELECT * FROM users; SELECT name, email FROM users; SELECT DISTINCT country FROM users; SELECT name AS user_name, email AS user_email FROM users; SELECT COUNT(*) FROM users; SELECT COUNT(*) as user_count FROM users WHERE age >= 18;

SELECT * FROM users WHERE age > 25; SELECT * FROM users WHERE name LIKE 'John%'; SELECT * FROM users WHERE email IN ('john@example.com', 'jane@example.com'); SELECT * FROM users WHERE age BETWEEN 18 AND 65; SELECT * FROM users WHERE created_at >= '2023-01-01'; SELECT * FROM users WHERE is_active = true AND age > 18 OR email LIKE '%.edu';

SELECT * FROM users ORDER BY name ASC; SELECT * FROM users ORDER BY age DESC, name ASC; SELECT * FROM users ORDER BY created_at DESC LIMIT 10; SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20; -- Skip first 20, get next 10

SELECT u.name, o.amount, o.status FROM users u INNER JOIN orders o ON u.id = o.user_id; SELECT u.name, COUNT(o.id) as order_count FROM users u INNER JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name;

SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id; SELECT u.name, COALESCE(SUM(o.amount), 0) as total_spent FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name;

SELECT u.name, o.amount FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id; SELECT COALESCE(u.name, 'No User') as user_name, COALESCE(o.amount, 0) as amount FROM users u FULL OUTER JOIN orders o ON u.id = o.user_id;

SELECT COUNT(*) as total_users, AVG(age) as average_age, MIN(age) as youngest, MAX(age) as oldest, SUM(balance) as total_balance FROM users; SELECT country, COUNT(*) as user_count, AVG(age) as avg_age FROM users GROUP BY country;

SELECT country, COUNT(*) as user_count FROM users GROUP BY country HAVING COUNT(*) > 10; 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.id, u.name HAVING COUNT(o.id) > 5; SELECT EXTRACT(YEAR FROM created_at) as year, COUNT(*) as user_count FROM users GROUP BY EXTRACT(YEAR FROM created_at) HAVING COUNT(*) > 100;

SELECT name, email FROM users WHERE id IN ( SELECT user_id FROM orders WHERE amount > 1000 ); SELECT name, email FROM users WHERE age > ( SELECT AVG(age) FROM users ); SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count FROM users u;

SELECT name FROM users UNION SELECT company_name FROM companies; SELECT name, CASE WHEN age < 18 THEN 'Minor' WHEN age BETWEEN 18 AND 65 THEN 'Adult' ELSE 'Senior' END as age_group FROM users;

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) as rank, RANK() OVER (ORDER BY salary DESC) as salary_rank, LAG(salary) OVER (ORDER BY hire_date) as prev_salary FROM employees; SELECT department, name, salary, AVG(salary) OVER (PARTITION BY department) as dept_avg FROM employees;

SELECT UPPER(name) as upper_name, LOWER(email) as lower_email, LENGTH(name) as name_length, SUBSTRING(email, 1, 5) as email_part, CONCAT(first_name, ' ', last_name) as full_name, REPLACE(email, 'old.com', 'new.com') as updated_email, TRIM(' text ') as trimmed_text FROM users;

SELECT created_at, DATE(created_at) as date_only, EXTRACT(YEAR FROM created_at) as year, EXTRACT(MONTH FROM created_at) as month, DATE_PART('year', created_at) as year_part, NOW() as current_time, CURRENT_DATE as today, created_at + INTERVAL '1 day' as tomorrow FROM users;