test.sql

SQL

DROP USER testdb;
CREATE USER testdb;
ALTER DATABASE testdb OWNER to testdb;

CREATE TABLE companys (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    company_id INTEGER references companys(id),
    age INTEGER,
    created DATE
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    price INTEGER
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER references users(id),
    created DATE
);


CREATE TABLE order_products (
    product_id INTEGER references products(id),
    order_id INTEGER references orders(id)
);

INSERT INTO companys(name) VALUES
  ('ACME Company'),
  ('Thinkful'),
  ('Uber'),
  ('LinkedIn'),
  ('Facebook'),
  ('Google'),
  ('Apple'),
  ('Reddit');

INSERT INTO users(first_name, last_name, company_id, age, created) VALUES
  ('James', 'Johnson', 1, 28, '2015-01-10'),
  ('James', 'Schildkrout', 3, 28, '2015-01-10'),
  ('Bob', 'Smith', 1, 20, '2015-01-10'),
  ('Sally', 'Scott', 1, 40, '2015-03-30'),
  ('Michael', 'Douglas', 2, 20, '2015-03-20'),
  ('Michael', 'Apple', 3, 28, '2013-11-10'),
  ('Bob', 'Green', 4, 20, '2014-01-10'),
  ('Dan', 'Green', 5, 40, '2014-01-10'),
  ('Dan', 'Blue', null, 30, '2015-03-10'),
  ('Sam', 'Blue', null, 80, '2015-02-10');

INSERT INTO products(name, price) VALUES
  ('Green Box', 45),
  ('Blue Box', 23),
  ('Red Box', 18),
  ('Yellow Box', 10),
  ('Black Box', 12);

INSERT INTO orders(user_id, created) VALUES
  (1, '2014-01-01'),
  (1, '2015-01-01'),
  (2, '2015-03-01'),
  (3, '2014-04-01'),
  (3, '2014-08-01'),
  (4, '2014-09-01');

INSERT INTO order_products(product_id, order_id) VALUES
  (1, 2),
  (1, 1),
  (1, 3),
  (2, 1),
  (3, 4),
  (3, 5),
  (4, 2),
  (5, 1),
  (5, 3);