Module 28 — Portfolio Project: Blog Database

-- 1. Create the Database
CREATE DATABASE blog_db;
\c blog_db
 
-- 2. Users Table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255

-- Insert Users
INSERT INTO users (username, email, password_hash, role) VALUES
    ('alice_dev', 'alice@example.com', 'hash_123', 'admin'),
    ('bob_codes', 'bob@example.com', 'hash_456', 'author');
 
--

SELECT
    posts.title,
    users.username AS author,
    categories.name AS category,
    posts.published_at
FROM posts
JOIN users ON posts.user_id = users.id
JOIN categories ON posts.category_id = categories.id
WHERE posts

SELECT
    posts.title,
    COUNT(comments.id) AS total_comments
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id, posts.title
ORDER BY total_comments DESC;

SELECT
    users.username,
    COUNT(posts.id) AS post_count,
    MAX(posts.published_at) AS latest_post
FROM users
LEFT JOIN posts ON users.id = posts.user_id AND posts.published = true
GROUP BY users

-- Find all posts with the tag 'sql'
SELECT posts.title
FROM posts
JOIN post_tags ON posts.id = post_tags.post_id
JOIN tags ON post_tags.tag_id = tags.id
WHERE tags.name = 'sql';

-- Speed up looking up posts by their URL slug
CREATE INDEX idx_posts_slug ON posts(slug);
 
-- Speed up filtering by published date
CREATE INDEX idx_posts_published ON posts(published_at) WHERE published = true;
 
-- Speed up comment lookups by post
CREATE INDEX idx_comments_post_id ON comments(post_id);
 
-- Speed up tag lookups
CREATE INDEX idx_post_tags_post_id ON