- sql: "-- Complete Blog Application Schema\n\n-- Users table\nDROP TABLE IF EXISTS users;\nCREATE TABLE users (\n id INTEGER PRIMARY KEY,\n username TEXT NOT NULL UNIQUE,\n email TEXT UNIQUE,\n password_hash TEXT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Insert sample users\nINSERT INTO users (username, email, password_hash, created_at) VALUES\n ('alice', 'alice@example.com', 'hash1', '2022-01-10'),\n ('bob', 'bob@example.com', 'hash2', '2022-01-15'),\n ('carol', 'carol@example.com', 'hash3', '2022-02-20');\n\n-- Posts table\nDROP TABLE IF EXISTS posts;\nCREATE TABLE posts (\n id INTEGER PRIMARY KEY,\n user_id INTEGER NOT NULL,\n title TEXT NOT NULL,\n content TEXT NOT NULL,\n published BOOLEAN DEFAULT 0,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE\n);\n\n-- Insert sample posts\nINSERT INTO posts (user_id, title, content, published, created_at) VALUES\n (1, 'First Post', 'This is my first post content', 1, '2022-01-12'),\n (1, 'Second Post', 'This is another post by Alice', 1, '2022-01-18'),\n (2, 'Hello World', 'Bob\\'s first post content', 1, '2022-01-20'),\n (3, 'Introduction', 'Hello from Carol', 1, '2022-02-25'),\n (2, 'Draft Post', 'This is a draft', 0, '2022-02-28');\n\n-- Comments table\nDROP TABLE IF EXISTS comments;\nCREATE TABLE comments (\n id INTEGER PRIMARY KEY,\n post_id INTEGER NOT NULL,\n user_id INTEGER NOT NULL,\n content TEXT NOT NULL,\n created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,\n FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE\n);\n\n-- Insert sample comments\nINSERT INTO comments (post_id, user_id, content, created_at) VALUES\n (1, 2, 'Great post!', '2022-01-13'),\n (1, 3, 'I agree with Bob', '2022-01-14'),\n (3, 1, 'Welcome Bob!', '2022-01-21'),\n (4, 2, 'Nice to meet you Carol', '2022-02-26');\n\n-- Query: Show posts with comment counts\nSELECT \n p.id, \n p.title, \n u.username as author,\n COUNT(c.id) as comment_count\nFROM posts p\nJOIN users u ON p.user_id = u.id\nLEFT JOIN comments c ON c.post_id = p.id\nWHERE p.published = 1\nGROUP BY p.id\nORDER BY p.created_at DESC;"
0 commit comments