SQL Fundamentals: Master Database Queries

-- Create a users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT true
);

-- Create a posts table with foreign key
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    published_at TIMESTAMP,
    views INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create a comments table
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create an index for better query performance
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);

-- INSERT: Add new records
INSERT INTO users (username, email, password_hash)
VALUES ('john_doe', 'john@example.com', 'hashed_password_123');

-- Insert multiple rows
INSERT INTO users (username, email, password_hash)
VALUES 
    ('jane_smith', 'jane@example.com', 'hashed_password_456'),
    ('bob_wilson', 'bob@example.com', 'hashed_password_789');

-- SELECT: Query data
SELECT * FROM users;

SELECT username, email FROM users WHERE is_active = true;

SELECT * FROM users WHERE created_at > '2024-01-01';

-- ORDER BY and LIMIT
SELECT * FROM users 
ORDER BY created_at DESC 
LIMIT 10;

-- UPDATE: Modify existing records
UPDATE users 
SET email = 'newemail@example.com' 
WHERE username = 'john_doe';

UPDATE posts 
SET views = views + 1 
WHERE id = 1;

-- DELETE: Remove records
DELETE FROM users WHERE id = 5;

DELETE FROM posts WHERE created_at < '2023-01-01';

-- Comparison operators
SELECT * FROM posts WHERE views > 1000;
SELECT * FROM posts WHERE views BETWEEN 100 AND 1000;

-- String matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM posts WHERE title ILIKE '%react%'; -- Case insensitive

-- Multiple conditions
SELECT * FROM posts 
WHERE views > 500 
  AND published_at IS NOT NULL
  AND user_id IN (1, 2, 3);

-- OR conditions
SELECT * FROM users 
WHERE username = 'john_doe' 
   OR email = 'john@example.com';

-- NOT operator
SELECT * FROM posts WHERE published_at IS NOT NULL;

-- IN operator
SELECT * FROM users WHERE id IN (1, 5, 10, 15);

-- Date filtering
SELECT * FROM posts 
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';

-- NULL checks
SELECT * FROM posts WHERE published_at IS NULL;

-- INNER JOIN: Returns matching rows from both tables
SELECT 
    users.username,
    posts.title,
    posts.views
FROM users
INNER JOIN posts ON users.id = posts.user_id;

-- LEFT JOIN: Returns all rows from left table
SELECT 
    users.username,
    COUNT(posts.id) as post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id, users.username;

-- Multiple joins
SELECT 
    users.username,
    posts.title,
    comments.content,
    comments.created_at
FROM comments
INNER JOIN posts ON comments.post_id = posts.id
INNER JOIN users ON comments.user_id = users.id
WHERE posts.published_at IS NOT NULL
ORDER BY comments.created_at DESC;

-- Self join (e.g., for hierarchical data)
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    manager_id INTEGER REFERENCES employees(id)
);

SELECT 
    e.name as employee,
    m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) as total_users FROM users;

SELECT AVG(views) as avg_views FROM posts;

SELECT 
    MIN(created_at) as first_post,
    MAX(created_at) as latest_post
FROM posts;

-- GROUP BY: Aggregate by categories
SELECT 
    user_id,
    COUNT(*) as post_count,
    SUM(views) as total_views,
    AVG(views) as avg_views
FROM posts
GROUP BY user_id
ORDER BY total_views DESC;

-- HAVING: Filter grouped results
SELECT 
    user_id,
    COUNT(*) as post_count
FROM posts
GROUP BY user_id
HAVING COUNT(*) > 5;

-- Complex aggregation
SELECT 
    users.username,
    COUNT(DISTINCT posts.id) as post_count,
    COUNT(DISTINCT comments.id) as comment_count,
    SUM(posts.views) as total_views
FROM users
LEFT JOIN posts ON users.id = posts.user_id
LEFT JOIN comments ON users.id = comments.user_id
GROUP BY users.id, users.username
HAVING COUNT(DISTINCT posts.id) > 0
ORDER BY total_views DESC;

-- Subquery in WHERE clause
SELECT * FROM posts
WHERE user_id IN (
    SELECT id FROM users WHERE is_active = true
);

-- Subquery with comparison
SELECT * FROM posts
WHERE views > (
    SELECT AVG(views) FROM posts
);

-- Correlated subquery
SELECT 
    u.username,
    (SELECT COUNT(*) FROM posts p WHERE p.user_id = u.id) as post_count
FROM users u;

-- EXISTS operator
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM posts p 
    WHERE p.user_id = u.id 
    AND p.views > 1000
);

-- Subquery in FROM clause (derived table)
SELECT 
    avg_views_by_user.username,
    avg_views_by_user.avg_views
FROM (
    SELECT 
        users.username,
        AVG(posts.views) as avg_views
    FROM users
    JOIN posts ON users.id = posts.user_id
    GROUP BY users.id, users.username
) as avg_views_by_user
WHERE avg_views_by_user.avg_views > 500;

-- Common Table Expressions (CTEs)
WITH active_users AS (
    SELECT * FROM users WHERE is_active = true
),
popular_posts AS (
    SELECT * FROM posts WHERE views > 1000
)
SELECT 
    au.username,
    COUNT(pp.id) as popular_post_count
FROM active_users au
LEFT JOIN popular_posts pp ON au.id = pp.user_id
GROUP BY au.id, au.username;

-- Window functions
SELECT 
    username,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as row_num,
    RANK() OVER (ORDER BY created_at DESC) as rank
FROM users;

-- Partition by
SELECT 
    posts.title,
    posts.views,
    users.username,
    AVG(posts.views) OVER (PARTITION BY posts.user_id) as user_avg_views,
    RANK() OVER (PARTITION BY posts.user_id ORDER BY posts.views DESC) as rank_in_user_posts
FROM posts
JOIN users ON posts.user_id = users.id;

-- Running totals
SELECT 
    created_at::date as date,
    COUNT(*) as daily_posts,
    SUM(COUNT(*)) OVER (ORDER BY created_at::date) as cumulative_posts
FROM posts
GROUP BY created_at::date
ORDER BY date;

-- Create indexes for frequently queried columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_published_at ON posts(published_at);

-- Composite index
CREATE INDEX idx_posts_user_published ON posts(user_id, published_at);

-- Partial index (for specific conditions)
CREATE INDEX idx_active_users ON users(username) WHERE is_active = true;

-- Full-text search index
CREATE INDEX idx_posts_title_search ON posts USING gin(to_tsvector('english', title));

-- Analyze query performance
EXPLAIN ANALYZE
SELECT * FROM posts 
WHERE user_id = 1 
ORDER BY created_at DESC 
LIMIT 10;

-- View index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan as index_scans
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Drop unused indexes
DROP INDEX IF EXISTS idx_old_index;