7つのデータベース 7つの世界 2章 PostgreSQL 1日目 ref: http://qiita.com/items/18847b18646a1965c398

file0.sql

CREATE TABLE countries (
  country_code char(2) PRIMARY KEY,
  country_name text UNIQUE
);

file1.sql

INSERT INTO countries (country_code, country_name)
VALUES ('us', 'Unitate States'), ('mx', 'Mexico'), ('au', 'Australia'), ('gb', 'United Kingdom'), ('de', 'Germany'),('ll', 'Loompaland');

file10.sql

INSERT INTO venues (name, postal_code, country_code) VALUES ('Voodoo Donuts', '97205', 'us') RETURNING venue_id;

file11.txt

 venue_id 
----------
        2
(1 row)

INSERT 0 1

file12.sql

CREATE TABLE events (
  event_id SERIAL PRIMARY KEY,
  title varchar(255),
  starts timestamp,
  ends timestamp,
  venue_id integer REFERENCES venues 
);

file13.sql

INSERT INTO events (title, starts, ends, venue_id)
 VALUES ('LARP Club', '2012-02-15 17:30:00', '2012-02-15 19:30:00', 2),
  ('April Fools Day', '2012-04-01 00:00:00', '2012-04-01 23:59:00', NULL),
  ('Christmas Day', '2012-12-25 00:00:00', '2012-12-25 23:59:00', NULL);

file14.sql

SELECT e.title, v.name 
FROM events e JOIN venues v
  ON e.venue_id = v.venue_id;

file15.txt

   title   |     name      
-----------+---------------
 LARP Club | Voodoo Donuts
(1 row)

file16.sql

SELECT e.title, v.name 
FROM events e LEFT JOIN venues v
  ON e.venue_id = v.venue_id;

file17.txt

      title      |     name      
-----------------+---------------
 LARP Club       | Voodoo Donuts
 April Fools Day | 
 Christmas Day   | 
(3 rows)

file18.sql

CREATE INDEX events_title ON events USING hash (title);
CREATE INDEX events_start ON events USING btree(starts);

file19.sql

\di

file2.sql

DELETE FROM countries WHERE country_code='ll';

file20.txt

                         List of relations
 Schema |            Name            | Type  |  Owner   |   Table   
--------+----------------------------+-------+----------+-----------
 public | cities_pkey                | index | postgres | cities
 public | countries_country_name_key | index | postgres | countries
 public | countries_pkey             | index | postgres | countries
 public | events_pkey                | index | postgres | events
 public | events_start               | index | postgres | events
 public | events_title               | index | postgres | events
 public | venues_pkey                | index | postgres | venues
(7 rows)

file21.sql

SELECT * FROM pg_class WHERE relname = 'events';

file22.sql

SELECT c.country_name, e.title 
FROM events e
INNER JOIN venues v ON e.venue_id = v.venue_id
INNER JOIN countries c ON v.country_code = c.country_code;

file23.sql

ALTER TABLE venues ADD COLUMN active boolean DEFAULT TRUE;
SELECT * FROM venues;

file3.sql

CREATE TABLE cities (
  name text NOT NULL,
  postal_code varchar(9) CHECK (postal_code <> ''),
  country_code char(2) REFERENCES countries,
  PRIMARY KEY (country_code, postal_code)
);

file4.sql

INSERT INTO cities VALUES ('Portland', '87200', 'us');
UPDATE cities SET postal_code='97205' WHERE name = 'Portland';

file5.sql

SELECT cities.*, country_name
 FROM cities INNER JOIN countries
 ON cities.country_code = countries.country_code;

file6.txt

   name   | postal_code | country_code |  country_name  
----------+-------------+--------------+----------------
 Portland | 97205       | us           | Unitate States
(1 row)

file7.sql

CREATE TABLE venues (
  venue_id SERIAL PRIMARY KEY,
  name varchar(255),
  street_address text,
  type char(7) CHECK (type in ('public', 'private')) DEFAULT 'public',
  postal_code varchar(9),
  country_code char(2),
  FOREIGN KEY(country_code, postal_code)
    REFERENCES cities (country_code, postal_code) MATCH FULL
);
INSERT INTO venues (name, postal_code, country_code)
 VALUES ('Crystal Ballroom', '97205', 'us');

file8.sql

SELECT v.venue_id, v.name, c.name 
FROM venues v INNER JOIN cities c
  ON v.postal_code = c.postal_code AND v.country_code = c.country_code;

file9.txt

 venue_id |       name       |   name   
----------+------------------+----------
        1 | Crystal Ballroom | Portland
(1 row)