Episode #16 | SQL Files With PostgreSQL


Friday, 02 Nov 2018 Kudzai Nyandoro 12:33 Minutes

Description:

So far we have learned how to use PostgreSQL to create a SQL database, table, and insert data using the PostgreSQL console. In this episode, we're going to simplify things a little by using SQL files.

Notes:

Create a file and name it create_california_rivers.sql or a name of your choice. Type or copy and paste the code below.

-- Delete the table if it exists
DROP TABLE california_rivers;

-- Create a table for California rivers
CREATE TABLE california_rivers(
  river_id          SERIAL PRIMARY KEY,
  name              VARCHAR(50) NOT NULL,
  class             VARCHAR(7)  NOT NULL,
  section           VARCHAR(50) NOT NULL,
  put_in            VARCHAR(50) NOT NULL,
  take_out          VARCHAR(50) NOT NULL,
  distance_miles    INTEGER     NOT NULL,
  season            VARCHAR(50) DEFAULT 'Spring, Summer',
  ideal_flow        INTEGER     DEFAULT 1500
);

Create a file and name it insert_california_rivers.sql or a name of your choice. Type or copy and paste the code below.

-- Clear table data

DELETE FROM california_rivers;

-- Insert California Rivers data

INSERT INTO california_rivers(name, class, section, put_in, take_out, distance_miles, season, ideal_flow)
VALUES('Trinity', '4 - 5', 'Burnt Ranch Gorge', 'Cedar Flat Bridge', 'Hawkins Bar Bridge', 9, 'Spring, Summer', DEFAULT);

INSERT INTO california_rivers(name, class, section, put_in, take_out, distance_miles, season, ideal_flow)
VALUES('South Fork American', '3 - 4', 'Chilli Bar', 'Route 193', 'Coloma', 6, 'Spring, Summer, Fall', 2000);

INSERT INTO california_rivers(name, class, section, put_in, take_out, distance_miles, season, ideal_flow)
VALUES('Merced', '3 - 5', 'El Portal to Briceburg', 'El Portal', 'Briceburg', 16, 'Spring', DEFAULT);

INSERT INTO california_rivers(name, class, section, put_in, take_out, distance_miles, season, ideal_flow)
VALUES('Kings', '3 - 4', 'Banzai Run', 'Garnet Dike', 'Kirch Flat', 10, 'Spring, Summer', 2500);

INSERT INTO california_rivers(name, class, section, put_in, take_out, distance_miles, season, ideal_flow)
VALUES('Kern', '5', 'Headwaters', 'Junction Meador', 'Kern Forks', 37, 'Spring', DEFAULT);

INSERT INTO california_rivers(name, class, section, put_in, take_out, distance_miles, season, ideal_flow)
VALUES('San Joaquin', '5', 'Chawanakee Gorge', 'Dam Base', 'Redinger Lake', 8, 'Spring, Summer', 600);

-- View all all inserted data

SELECT * FROM california_rivers;

Go to your terminal and enter into the rivers database

$ psql rivers

Create the table

rivers=# \i create_california_rivers.sql

Insert the data

rivers=# \i insert_california_rivers.sql

Use the SQL SELECT query to view all data

rivers=# SELECT * FROM california_rivers;

View all river ID's

rivers=# SELECT river_id FROM california_rivers;

View all river names

rivers=# SELECT name FROM california_rivers;

View only the river names, put in and take out areas

rivers=# SELECT name, put_in, take_out FROM california_rivers;

View all river trips with a distance less than 20 miles

rivers=# SELECT * FROM california_rivers
WHERE distance_miles < 20;

View all river trips with a distance greater than 20 miles

rivers=# SELECT * FROM california_rivers
WHERE distance_miles > 20;