Blog SQL Drills

blog.sql

CREATE TABLE users(
  id serial PRIMARY KEY,
  first_name text,
  last_name text,
  email_address text NOT NULL,
  screen_name text NOT NULL
);
CREATE TABLE posts(
  author_id int REFERENCES users,
  post_id serial PRIMARY KEY,
  title text,
  content text,
  published_time TIMESTAMP DEFAULT current_timestamp
);
CREATE TABLE comments(
  id serial PRIMARY KEY,
  author int REFERENCES users,
  parent_post int REFERENCES posts,
  comment_text text
);
CREATE TABLE tags (
  id serial PRIMARY KEY,
  tag text NOT NULL
);
CREATE TABLE post_tags (
  post_id int REFERENCES posts(id),
  tag_id int REFERENCES tag(id),
  PRIMARY KEY (post_id, tag_id)
);

INSERT INTO users
  (first_name, last_name, email_address, screen_name)
  VALUES ('Dakota', 'Bryant', 'bryant.dakota@gmail.com', 'dakotabryant'), ('TaChyla', 'Murray', 'tachyla.murray@gmail.com', 'tachylamurray') RETURNING id, first_name;

INSERT INTO posts
  (title, content, author_id)
  VALUES ('Post 1', 'This is a post', 3) RETURNING title, content, author_id;

INSERT INTO tags (tag)
  VALUES ('outdoors'), ('indoors'), ('dogs'), ('cats'), ('California'), ('Oklahoma') RETURNING tag;

INSERT INTO comments (author, parent_post, comment_text, external_comment) VALUES
  (2, 1, 'Hey man, this is a comment');