SQL Blog App Challenge

blog-app-challenge.sql

CREATE TABLE users (
  id serial PRIMARY KEY,
  first_name text,
  last_name text,
  email text NOT NULL,
  screen_name text NOT NULL
  );

CREATE TABLE posts (
  id serial PRIMARY KEY,
  author_id int REFERENCES users ON DELETE CASCADE,
  title text NOT NULL,
  content text NOT NULL,
  published timestamp DEFAULT now()
  );

CREATE TABLE tags (
  id serial PRIMARY KEY,
  tag text NOT NULL
  );

CREATE TABLE post_tags (
 posts_id int REFERENCES posts(id) ON DELETE CASCADE,
 tags_id int REFERENCES tags(id) ON DELETE CASCADE,
 PRIMARY KEY (posts_id, tags_id)
 );

CREATE TABLE comments (
  id serial PRIMARY KEY,
  author_id int REFERENCES users ON DELETE CASCADE,
  post_id int REFERENCES posts ON DELETE CASCADE,
  content text NOT NULL
  );
  
INSERT INTO users (first_name, last_name, email, screen_name) VALUES 
  ('Mickey', 'Mouse', 'mickey@disney.com', 'mickeymouse'),
  ('Harry', 'Potter', 'hpotter@hogwarts.com', 'hpotter'),
  ('Leslie', 'Knope', 'lknop@pawnee.gov', 'knope4pres');
  
INSERT INTO posts (author_id, title, content) VALUES
  (1, 'Me and Walt', 'blah blah 1928 1955 blah blah'),
  (2, 'Life With the Muggles', 'blah blah Dursleys blah blah'),
  (3, 'Woman in Government', 'blah blah Pelosi, Michelle Obama, blah blah');
   
INSERT INTO tags (tag) VALUES ('life'), ('memories'), ('inspiration'), ('childhood');
  
INSERT INTO comments (post_id, author_id, content) VALUES
  (1, 2, 'Wicked'),
  (2, 3, 'Muggles > People from Eagleton'),
  (3, 1, 'Oh boy! I mean...');
  
INSERT INTO post_tags (posts_id, tags_id) VALUES
  (1, 1),
  (1, 2),
  (2, 1),
  (2, 2),
  (2, 4),
  (3, 3);