SQL Queries, Schema Design & Database Indexing Guide

-- Create table with constraints
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Select active users sorted by registration date
SELECT id, email, created_at
FROM users
WHERE is_active = TRUE AND email LIKE '%@gmail.com'
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- INNER JOIN: Returns only rows with matches in both tables
SELECT u.email, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN: Returns all users, plus orders if they exist
SELECT u.email, COUNT(o.id) AS total_orders
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.email;

-- Create a B-Tree index on foreign key or search column
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

-- Analyze query execution plan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 452 ORDER BY created_at DESC;