SQL Language Basics

 1-- Basic table
 2CREATE TABLE users (
 3    id SERIAL PRIMARY KEY,
 4    username VARCHAR(50) UNIQUE NOT NULL,
 5    email VARCHAR(100) UNIQUE NOT NULL,
 6    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 7);
 8
 9-- With constraints
10CREATE TABLE orders (
11    id SERIAL PRIMARY KEY,
12    user_id INTEGER NOT NULL,
13    total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
14    status VARCHAR(20) DEFAULT 'pending',
15    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
16    
17    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
18    CONSTRAINT valid_status CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'))
19);
20
21-- With indexes
22CREATE TABLE products (
23    id SERIAL PRIMARY KEY,
24    name VARCHAR(200) NOT NULL,
25    description TEXT,
26    price DECIMAL(10, 2) NOT NULL,
27    category VARCHAR(50),
28    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
29    
30    INDEX idx_category (category),
31    INDEX idx_price (price),
32    FULLTEXT INDEX idx_search (name, description)
33);

 1-- Add column
 2ALTER TABLE users ADD COLUMN phone VARCHAR(20);
 3
 4-- Modify column
 5ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(150);
 6
 7-- Drop column
 8ALTER TABLE users DROP COLUMN phone;
 9
10-- Add constraint
11ALTER TABLE users ADD CONSTRAINT email_format CHECK (email LIKE '%@%');
12
13-- Add foreign key
14ALTER TABLE orders ADD FOREIGN KEY (user_id) REFERENCES users(id);
15
16-- Add index
17CREATE INDEX idx_username ON users(username);
18
19-- Rename table
20ALTER TABLE users RENAME TO customers;

1-- Drop table
2DROP TABLE users;
3
4-- Drop if exists
5DROP TABLE IF EXISTS users;
6
7-- Drop with cascade (removes dependent objects)
8DROP TABLE users CASCADE;

 1-- Single row
 2INSERT INTO users (username, email) 
 3VALUES ('john_doe', 'john@example.com');
 4
 5-- Multiple rows
 6INSERT INTO users (username, email) VALUES
 7    ('alice', 'alice@example.com'),
 8    ('bob', 'bob@example.com'),
 9    ('charlie', 'charlie@example.com');
10
11-- Insert from SELECT
12INSERT INTO archived_users (username, email)
13SELECT username, email FROM users WHERE created_at < '2020-01-01';
14
15-- Insert with RETURNING (PostgreSQL)
16INSERT INTO users (username, email) 
17VALUES ('jane', 'jane@example.com')
18RETURNING id, created_at;
19
20-- Upsert (INSERT ... ON CONFLICT)
21INSERT INTO users (id, username, email)
22VALUES (1, 'john', 'john@example.com')
23ON CONFLICT (id) DO UPDATE SET
24    username = EXCLUDED.username,
25    email = EXCLUDED.email;

 1-- Basic SELECT
 2SELECT * FROM users;
 3
 4-- Specific columns
 5SELECT id, username, email FROM users;
 6
 7-- With WHERE
 8SELECT * FROM users WHERE created_at > '2023-01-01';
 9
10-- With multiple conditions
11SELECT * FROM users 
12WHERE created_at > '2023-01-01' 
13  AND (username LIKE 'john%' OR email LIKE '%@gmail.com');
14
15-- DISTINCT
16SELECT DISTINCT category FROM products;
17
18-- ORDER BY
19SELECT * FROM users ORDER BY created_at DESC;
20SELECT * FROM products ORDER BY price ASC, name;
21
22-- LIMIT and OFFSET (pagination)
23SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;
24
25-- Aggregate functions
26SELECT COUNT(*) FROM users;
27SELECT AVG(price) FROM products;
28SELECT SUM(total) FROM orders;
29SELECT MIN(price), MAX(price) FROM products;
30
31-- GROUP BY
32SELECT category, COUNT(*), AVG(price)
33FROM products
34GROUP BY category;
35
36-- HAVING (filter after GROUP BY)
37SELECT category, COUNT(*) as count
38FROM products
39GROUP BY category
40HAVING COUNT(*) > 5;

 1-- Update single column
 2UPDATE users SET email = 'newemail@example.com' WHERE id = 1;
 3
 4-- Update multiple columns
 5UPDATE users 
 6SET username = 'new_username', 
 7    email = 'new@example.com'
 8WHERE id = 1;
 9
10-- Update with calculation
11UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
12
13-- Update from another table
14UPDATE orders o
15SET status = 'completed'
16FROM users u
17WHERE o.user_id = u.id AND u.username = 'john';
18
19-- Update with RETURNING
20UPDATE users 
21SET email = 'updated@example.com' 
22WHERE id = 1
23RETURNING *;

 1-- Delete specific rows
 2DELETE FROM users WHERE id = 1;
 3
 4-- Delete with condition
 5DELETE FROM users WHERE created_at < '2020-01-01';
 6
 7-- Delete all rows (keep table structure)
 8DELETE FROM users;
 9
10-- Truncate (faster, resets auto-increment)
11TRUNCATE TABLE users;
12
13-- Delete with RETURNING
14DELETE FROM users WHERE id = 1 RETURNING *;

 1-- INNER JOIN (only matching rows)
 2SELECT u.username, o.total
 3FROM users u
 4INNER JOIN orders o ON u.id = o.user_id;
 5
 6-- LEFT JOIN (all from left table)
 7SELECT u.username, o.total
 8FROM users u
 9LEFT JOIN orders o ON u.id = o.user_id;
10
11-- RIGHT JOIN (all from right table)
12SELECT u.username, o.total
13FROM users u
14RIGHT JOIN orders o ON u.id = o.user_id;
15
16-- FULL OUTER JOIN (all from both tables)
17SELECT u.username, o.total
18FROM users u
19FULL OUTER JOIN orders o ON u.id = o.user_id;
20
21-- Multiple joins
22SELECT u.username, o.total, p.name
23FROM users u
24INNER JOIN orders o ON u.id = o.user_id
25INNER JOIN order_items oi ON o.id = oi.order_id
26INNER JOIN products p ON oi.product_id = p.id;
27
28-- Self join
29SELECT e1.name AS employee, e2.name AS manager
30FROM employees e1
31LEFT JOIN employees e2 ON e1.manager_id = e2.id;

 1-- Subquery in WHERE
 2SELECT * FROM users
 3WHERE id IN (SELECT user_id FROM orders WHERE total > 100);
 4
 5-- Subquery in SELECT
 6SELECT username,
 7       (SELECT COUNT(*) FROM orders WHERE user_id = users.id) AS order_count
 8FROM users;
 9
10-- Subquery in FROM
11SELECT avg_price.category, avg_price.avg
12FROM (
13    SELECT category, AVG(price) as avg
14    FROM products
15    GROUP BY category
16) AS avg_price
17WHERE avg_price.avg > 50;
18
19-- EXISTS
20SELECT * FROM users u
21WHERE EXISTS (
22    SELECT 1 FROM orders o WHERE o.user_id = u.id
23);
24
25-- NOT EXISTS
26SELECT * FROM users u
27WHERE NOT EXISTS (
28    SELECT 1 FROM orders o WHERE o.user_id = u.id
29);

 1-- Basic CTE
 2WITH high_value_orders AS (
 3    SELECT user_id, SUM(total) as total_spent
 4    FROM orders
 5    WHERE total > 100
 6    GROUP BY user_id
 7)
 8SELECT u.username, hvo.total_spent
 9FROM users u
10INNER JOIN high_value_orders hvo ON u.id = hvo.user_id;
11
12-- Multiple CTEs
13WITH 
14    user_orders AS (
15        SELECT user_id, COUNT(*) as order_count
16        FROM orders
17        GROUP BY user_id
18    ),
19    user_spending AS (
20        SELECT user_id, SUM(total) as total_spent
21        FROM orders
22        GROUP BY user_id
23    )
24SELECT u.username, uo.order_count, us.total_spent
25FROM users u
26LEFT JOIN user_orders uo ON u.id = uo.user_id
27LEFT JOIN user_spending us ON u.id = us.user_id;
28
29-- Recursive CTE (hierarchy)
30WITH RECURSIVE employee_hierarchy AS (
31    -- Base case
32    SELECT id, name, manager_id, 1 as level
33    FROM employees
34    WHERE manager_id IS NULL
35    
36    UNION ALL
37    
38    -- Recursive case
39    SELECT e.id, e.name, e.manager_id, eh.level + 1
40    FROM employees e
41    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
42)
43SELECT * FROM employee_hierarchy ORDER BY level, name;

 1-- ROW_NUMBER
 2SELECT username, total,
 3       ROW_NUMBER() OVER (ORDER BY total DESC) as rank
 4FROM orders;
 5
 6-- RANK (with gaps)
 7SELECT username, total,
 8       RANK() OVER (ORDER BY total DESC) as rank
 9FROM orders;
10
11-- DENSE_RANK (no gaps)
12SELECT username, total,
13       DENSE_RANK() OVER (ORDER BY total DESC) as rank
14FROM orders;
15
16-- PARTITION BY
17SELECT category, name, price,
18       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_category
19FROM products;
20
21-- Running total
22SELECT date, amount,
23       SUM(amount) OVER (ORDER BY date) as running_total
24FROM transactions;
25
26-- Moving average
27SELECT date, price,
28       AVG(price) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7days
29FROM stock_prices;
30
31-- LAG and LEAD
32SELECT date, price,
33       LAG(price, 1) OVER (ORDER BY date) as prev_price,
34       LEAD(price, 1) OVER (ORDER BY date) as next_price
35FROM stock_prices;

 1-- Basic transaction
 2BEGIN;
 3    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 4    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 5COMMIT;
 6
 7-- Rollback on error
 8BEGIN;
 9    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
10    -- If error occurs
11    ROLLBACK;
12
13-- Savepoints
14BEGIN;
15    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
16    SAVEPOINT my_savepoint;
17    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
18    -- Rollback to savepoint if needed
19    ROLLBACK TO my_savepoint;
20    -- Or commit
21COMMIT;
22
23-- Isolation levels
24SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
25SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
26SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 1-- Concatenation
 2SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
 3SELECT first_name || ' ' || last_name AS full_name FROM users;  -- PostgreSQL
 4
 5-- UPPER/LOWER
 6SELECT UPPER(username), LOWER(email) FROM users;
 7
 8-- SUBSTRING
 9SELECT SUBSTRING(email, 1, 10) FROM users;
10
11-- LENGTH
12SELECT username, LENGTH(username) FROM users;
13
14-- TRIM
15SELECT TRIM('  hello  ');
16SELECT LTRIM('  hello');
17SELECT RTRIM('hello  ');
18
19-- REPLACE
20SELECT REPLACE(email, '@gmail.com', '@example.com') FROM users;
21
22-- LIKE pattern matching
23SELECT * FROM users WHERE email LIKE '%@gmail.com';
24SELECT * FROM users WHERE username LIKE 'john%';
25SELECT * FROM users WHERE phone LIKE '555-____';  -- _ matches single char
26
27-- REGEXP (MySQL/PostgreSQL)
28SELECT * FROM users WHERE email ~ '^[a-z]+@gmail\.com$';  -- PostgreSQL
29SELECT * FROM users WHERE email REGEXP '^[a-z]+@gmail\\.com$';  -- MySQL

 1-- Current date/time
 2SELECT CURRENT_DATE;
 3SELECT CURRENT_TIME;
 4SELECT CURRENT_TIMESTAMP;
 5SELECT NOW();
 6
 7-- Date arithmetic
 8SELECT CURRENT_DATE + INTERVAL '7 days';  -- PostgreSQL
 9SELECT DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY);  -- MySQL
10
11-- Extract parts
12SELECT EXTRACT(YEAR FROM created_at) FROM users;
13SELECT EXTRACT(MONTH FROM created_at) FROM users;
14SELECT DATE_PART('year', created_at) FROM users;  -- PostgreSQL
15
16-- Format date
17SELECT TO_CHAR(created_at, 'YYYY-MM-DD') FROM users;  -- PostgreSQL
18SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM users;  -- MySQL
19
20-- Date difference
21SELECT AGE(CURRENT_DATE, created_at) FROM users;  -- PostgreSQL
22SELECT DATEDIFF(CURRENT_DATE, created_at) FROM users;  -- MySQL
23
24-- Truncate to period
25SELECT DATE_TRUNC('month', created_at) FROM users;  -- PostgreSQL

 1-- CASE
 2SELECT username,
 3       CASE 
 4           WHEN total > 1000 THEN 'VIP'
 5           WHEN total > 500 THEN 'Premium'
 6           ELSE 'Regular'
 7       END AS customer_tier
 8FROM users;
 9
10-- COALESCE (return first non-null)
11SELECT username, COALESCE(phone, email, 'No contact') AS contact FROM users;
12
13-- NULLIF (return NULL if equal)
14SELECT NULLIF(column_name, '') FROM table_name;
15
16-- IF (MySQL)
17SELECT IF(total > 100, 'High', 'Low') AS value_category FROM orders;

 1-- ✅ Use parameterized queries (prevent SQL injection)
 2-- Application code:
 3-- cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))
 4
 5-- ✅ Use indexes for frequently queried columns
 6CREATE INDEX idx_email ON users(email);
 7
 8-- ✅ Use EXPLAIN to analyze queries
 9EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
10EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
11
12-- ✅ Avoid SELECT *
13SELECT id, username, email FROM users;  -- Better
14
15-- ✅ Use transactions for multiple related operations
16BEGIN;
17    -- Multiple operations
18COMMIT;
19
20-- ✅ Use appropriate data types
21-- INT for integers, VARCHAR for variable strings, TEXT for long text
22-- DECIMAL for money, TIMESTAMP for dates
23
24-- ❌ Avoid N+1 queries
25-- Bad: Query in loop
26-- Good: Use JOIN or IN clause

 1-- Create
 2CREATE TABLE table_name (column type constraints);
 3
 4-- Read
 5SELECT columns FROM table WHERE condition;
 6
 7-- Update
 8UPDATE table SET column = value WHERE condition;
 9
10-- Delete
11DELETE FROM table WHERE condition;
12
13-- Join
14SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
15
16-- Aggregate
17SELECT COUNT(*), AVG(column) FROM table GROUP BY category;
18
19-- Subquery
20SELECT * FROM table WHERE id IN (SELECT id FROM other);
21
22-- Transaction
23BEGIN; ... COMMIT; or ROLLBACK;