SQL File Viewer

text-green-text-cyan-300">400">"text-zinc-text-cyan-300">500 italic">-- Create users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(text-cyan-300">50) NOT NULL UNIQUE,
  email VARCHAR(text-cyan-300">255) NOT NULL UNIQUE,
  password_hash VARCHAR(text-cyan-300">255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

text-green-text-cyan-300">400">"text-zinc-text-cyan-300">500 italic">-- Create posts table
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title VARCHAR(text-cyan-300">255) NOT NULL,
  body TEXT,
  published BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT NOW()
);

text-green-text-cyan-300">400">"text-zinc-text-cyan-300">500 italic">-- Seed users
INSERT INTO users (username, email, password_hash) VALUES
  (text-green-text-cyan-300">400">'alice', text-green-text-cyan-300">400">'alice@example.com', text-green-text-cyan-300">400">'$2b$text-cyan-300">10$abc123'),
  (text-green-text-cyan-300">400">'bob', text-green-text-cyan-300">400">'bob@example.com', text-green-text-cyan-300">400">'$2b$text-cyan-300">10$def456');

text-green-text-cyan-300">400">"text-zinc-text-cyan-300">500 italic">-- Query example
SELECT u.username, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON p.user_id = u.id
GROUP BY u.username
ORDER BY post_count DESC;