subselect-pgsql-create-db.sql

CREATE DATABASE subquery
\c subquery

CREATE TABLE IF NOT EXISTS "user" (
   id INT PRIMARY KEY,
   name VARCHAR (50) NOT NULL
);


CREATE TABLE IF NOT EXISTS "location" (
   id INT PRIMARY KEY,
   user_id INT NOT NULL,
   name VARCHAR (50) NOT NULL,
   moved_on TIMESTAMP NOT NULL,
   CONSTRAINT fk_user
    FOREIGN KEY (user_id)
      REFERENCES "user" (id)
);

INSERT INTO "user" VALUES
  (1, 'tom'),
  (2, 'sally'),
  (3, 'ben');

INSERT INTO location VALUES
  (1, 1, 'New York', '2010-1-1 00:00:00'),
  (2, 1, 'Columbus', '2011-1-1 00:00:00'),
  (3, 1, 'Miami', '2012-1-1 00:00:00'),

  (4, 2, 'Miami', '2010-1-1 00:00:00'),
  (5, 2, 'Columbus', '2011-1-1 00:00:00'),

  (6, 3, 'Miami', '2010-1-1 00:00:00'),
  (7, 3, 'New York', '2011-1-1 00:00:00');