%load_ext sql
%sql postgresql://postgres:postgres@localhost

(psycopg2.OperationalError) connection to server at "localhost" (127.0.0.1), port 5432 failed: Connection refused
	Is the server running on that host and accepting TCP/IP connections?
connection to server at "localhost" (::1), port 5432 failed: Cannot assign requested address
	Is the server running on that host and accepting TCP/IP connections?

(Background on this error at: https://sqlalche.me/e/14/e3q8)
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

Create a DB.¶

%%sql
SELECT datname
    FROM pg_database;

 * postgresql://postgres:***@localhost
5 rows affected.

!createdb seven_dbs

Print DBs.

%%sql
SELECT datname
    FROM pg_database;

 * postgresql://postgres:***@localhost
5 rows affected.

%%sql
-- Print available tables.
SELECT table_schema, table_name
    FROM information_schema.tables
    WHERE table_type = 'BASE TABLE' AND
    table_schema NOT IN ('pg_catalog', 'information_schema', 'priv');

 * postgresql://postgres:***@localhost
1 rows affected.

Create table `countries`.¶

%%sql
DROP TABLE countries;
-- Create table `countries`.
CREATE TABLE countries (
    country_code CHAR(2) PRIMARY KEY,
    country_name TEXT UNIQUE)

 * postgresql://postgres:***@localhost
(psycopg2.errors.DependentObjectsStillExist) cannot drop table countries because other objects depend on it
DETAIL:  constraint cities_country_code_fkey on table cities depends on table countries
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

[SQL: DROP TABLE countries;]
(Background on this error at: https://sqlalche.me/e/14/2j85)

%%sql
-- Print the schema of this table.
SELECT * FROM Information_schema.Columns
    WHERE table_name = 'countries';

 * postgresql://postgres:***@localhost
2 rows affected.

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

 * postgresql://postgres:***@localhost
(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "countries_pkey"
DETAIL:  Key (country_code)=(us) already exists.

[SQL: INSERT INTO countries (country_code, country_name)
    VALUES
    ('us','United States'),
    ('mx','Mexico'),
    ('au','Australia'),
    ('gb','United Kingdom'),
    ('de','Germany'),
    ('ll','Loompaland');]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

%%sql
SELECT * FROM countries;

 * postgresql://postgres:***@localhost
5 rows affected.

%%sql
-- Try to insert a duplicate.
INSERT INTO countries
    VALUES ('uk','United Kingdom');

 * postgresql://postgres:***@localhost
(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "countries_country_name_key"
DETAIL:  Key (country_name)=(United Kingdom) already exists.

[SQL: -- Try to insert a duplicate.
INSERT INTO countries
    VALUES ('uk','United Kingdom');]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

%%sql
DELETE FROM countries
    WHERE country_code = 'll';
SELECT * FROM countries;

 * postgresql://postgres:***@localhost
0 rows affected.
5 rows affected.

Create table `cities`.¶

%%sql
DROP TABLE cities;
-- Add a `cities` table.
CREATE TABLE cities (
    -- No nulls in name.
    name text NOT NULL,
    -- No empty strings.
    postal_code VARCHAR(9) CHECK (postal_code <> ''),
    -- Foreign key.
    country_code CHAR(2) REFERENCES countries,
    -- Compound key.
    PRIMARY KEY (country_code, postal_code)
);

 * postgresql://postgres:***@localhost
Done.
Done.

[]

%%sql
-- Errors out because of referential integrity.
INSERT INTO cities
    VALUES ('Toronto', 'M4C1B5', 'ca');

 * postgresql://postgres:***@localhost
(psycopg2.errors.ForeignKeyViolation) insert or update on table "cities" violates foreign key constraint "cities_country_code_fkey"
DETAIL:  Key (country_code)=(ca) is not present in table "countries".

[SQL: -- Errors out because of referential integrity.
INSERT INTO cities
    VALUES ('Toronto', 'M4C1B5', 'ca');]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

%%sql
-- Valid insert (but the zip code is wrong).
INSERT INTO cities
    VALUES ('Portland', '87200', 'us');
SELECT * FROM cities;

 * postgresql://postgres:***@localhost
(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "cities_pkey"
DETAIL:  Key (country_code, postal_code)=(us, 87200) already exists.

[SQL: INSERT INTO cities
    VALUES ('Portland', '87200', 'us');]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

%%sql
-- Update the value in a relationship.
UPDATE cities
    SET postal_code = '97206'
    WHERE name = 'Portland';
SELECT * FROM cities;

 * postgresql://postgres:***@localhost
1 rows affected.
1 rows affected.

Join reads¶

%%sql
SELECT * FROM cities;

 * postgresql://postgres:***@localhost
1 rows affected.

%%sql
SELECT * FROM countries;

 * postgresql://postgres:***@localhost
6 rows affected.

%%sql
-- Show all the info from cities and the country name.
SELECT cities.*, countries.country_name
    FROM cities
    INNER JOIN countries
    ON cities.country_code = countries.country_code;

 * postgresql://postgres:***@localhost
1 rows affected.

%%sql
DROP TABLE venues;
--
CREATE TABLE venues (
    --
    venue_id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE,
    street_address TEXT,
    -- 2 values with one default.
    type char(7) CHECK (type IN ('public', 'private')) DEFAULT 'public',
    postal_code VARCHAR(9),
    country_code CHAR(2),
    -- The foreign key is compound.
    FOREIGN KEY (country_code, postal_code)
        REFERENCES cities (country_code, postal_code) MATCH FULL
);

 * postgresql://postgres:***@localhost
Done.
Done.

[]

%%sql
INSERT INTO venues (name, postal_code, country_code)
    VALUES ('Crystal Ballroom', '97206', 'us');

 * postgresql://postgres:***@localhost
1 rows affected.

[]

%%sql
SELECT * FROM venues;

 * postgresql://postgres:***@localhost
1 rows affected.

%%sql
-- Insert and return row.
INSERT INTO venues (name, postal_code, country_code)
    VALUES ('Voodoo Doughnut', '97206', 'us')
    -- Return the inserted value.
    RETURNING *;

 * postgresql://postgres:***@localhost
1 rows affected.

%%sql
-- Join venue and state.
SELECT v.venue_id, v.name, v.postal_code, c.name
    FROM venues v
    INNER JOIN cities c
    ON v.postal_code=c.postal_code AND v.country_code=c.country_code;

 * postgresql://postgres:***@localhost
2 rows affected.

Outer joins.¶

%%sql
DROP TABLE events;
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    starts TIMESTAMP,
    ends TIMESTAMP,
    venue_id INTEGER REFERENCES venues
);

 * postgresql://postgres:***@localhost
Done.
Done.

[]

%%sql
INSERT INTO events (title, starts, ends, venue_id)
    VALUES
    ('Fight club', '2018-02-15 17:30:00', '2018-02-15 19:30:00', 2),
    ('April Fools day', '2018-04-01 00:00:00', '2018-04-01 23:59:00', NULL),
    ('Christmas day', '2018-02-15 19:30:00', '2018-12-25 23:59:00', NULL)
    RETURNING *
;

 * postgresql://postgres:***@localhost
3 rows affected.

%%sql
SELECT * FROM events;

 * postgresql://postgres:***@localhost
3 rows affected.

%%sql
-- Outer join but venues have NULL value so they don't show up.
SELECT e.title, v.name
    FROM events e
    JOIN venues v
    ON e.venue_id = v.venue_id;

 * postgresql://postgres:***@localhost
1 rows affected.

%%sql
-- Left outer join.
SELECT e.title, v.name
    FROM events e
    LEFT JOIN venues v
    ON e.venue_id = v.venue_id;

 * postgresql://postgres:***@localhost
3 rows affected.

Indexing.¶

%%sql

%%sql

%%sql

%%sql

%%sql

%%sql

%%sql

%%sql

%%sql

%%sql

%%sql

%%sql

Create a DB.

Create a DB.¶

Create table countries.¶

Create table cities.¶

Join reads¶

Outer joins.¶

Indexing.¶

Create table `countries`.¶

Create table `cities`.¶