PostgreSQL

Create user postgres to access PostgreSQL
Create table for countries: Add data for countries: Create table for cities: Add data for cities: Create table for venues: Add data for venues Create table for events: Add data for events
 * sudo -u postgres psql
 * CREATE TABLE countries (
 * country_code char(2) PRIMARY KEY,
 * country_name text UNIQUE
 * INSERT INTO countries (country_code, country_name)
 * VALUES ('us','United States'), ('mx','Mexico'), ('au','Australia'), ('gb','United Kingdom'),('de','Germany'),('ee','Estonia');
 * 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)
 * INSERT INTO cities
 * VALUES ('Portland','97206','us');
 * CREATE TABLE venues (
 * venue_id SERIAL PRIMARY KEY,
 * name varchar(255),
 * street_address text,
 * type char(7) CHECK (type in ('public','private')) DEFAULT 'public',
 * active boolean DEFAULT TRUE,
 * 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, street_address, postal_code, country_code)
 * VALUES ('Crystal Ballroom','97206','us');
 * INSERT INTO venues (name, postal_code, country_code)
 * VALUES ('Voodoo Doughnut','97206','us')
 * INSERT INTO venues (name, street_address, postal_code, country_code)
 * VALUES ('My Place', 'Penn Avenue', 'NW11 0LR', 'gb');
 * CREATE TABLE events (
 * events_id SERIAL PRIMARY KEY,
 * title text,
 * starts timestamp,
 * ends timestamp,
 * venue_id integer,
 * FOREIGN KEY (venue_id)
 * REFERENCES venues (venue_id)
 * 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),
 * ('Moby', '2018-02-06 21:00', '2018-02-06 23:00', (SELECT venue_id FROM venues
 * WHERE name = 'Crystal Ballroom')),
 * ('Wedding', '2018-02-26 21:00','2018-02-26 23:00', 2),
 * ('Dinner with Mom', '2018-02-26 18:00:00','2018-02-26 20:30:00', 3),
 * ('Valentine''s Day','2018-02-14 00:00:00', '2018-02-14 23:59:00', NULL);

Create countries table with columns 'country code', and 'country name'

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

Insert data into countries table

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

Delete 'll' from countries table

 * DELETE FROM countries
 * WHERE country_code = 'll';

Create cities table with columns 'name', 'postal code', and 'country code'
Display tables Insert data into cities Update cities Join 'countries' and 'cities' tables. Will display table of cities with country_name Create 'venues' table with columns 'venue id', 'name', 'street address', 'type', 'postal code', and 'country code' Insert data into table 'venues' Display venue id, venue name, and city name insert data for 'Voodoo Doughnut' into 'Venues' and return 'Venue ID' Create 'events' table Insert data into table 'events' Return 'event title' and 'venue name'
 * 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)
 * SELECT *
 * FROM countries;
 * SELECT *
 * FROM cities;
 * INSERT INTO cities
 * VALUES ('Portland','87200','us');
 * UPDATE cities
 * SET postal_code = '97206'
 * WHERE name = 'Portland';
 * SELECT cities.*, country_name
 * FROM cities INNER JOIN countries
 * ON cities.country_code = countries.country_code;
 * 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','97206','us');
 * 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;
 * INSERT INTO venues (name, postal_code, country_code)
 * VALUES ('Voodoo Doughnut','97206','us') RETURNING
 * venue_id;
 * CREATE TABLE events (
 * events_id SERIAL PRIMARY KEY,
 * title text,
 * starts timestamp,
 * ends timestamp,
 * active boolean DEFAULT TRUE,
 * venue_id integer,
 * FOREIGN KEY (venue_id)
 * REFERENCES venues (venue_id)
 * INSERT INTO events (title, starts, ends, venue_id, event_id)
 * VALUES ('FIGHT CLUB', '2018-02-15 17:30:00', '2018-02-15 19:30:00', '2', '1');
 * INSERT INTO events (title, starts, ends, venue_id, event_id)
 * VALUES ('April Fools Day','2018-04-01 00:00:00','2018-04-01 23:59:00', '2', '2');
 * INSERT INTO events (title, starts, ends, venue_id, event_id)
 * VALUES ('Christmas Day', '2018-02-15 19:30:00', '2018-12-25 23:59:00', '2', '3');
 * SELECT e.title, v.name
 * FROM events e LEFT JOIN venues v
 * ON e.venue_id = v.venue_id;