solution.txt

------------------------------------------------------------
1.Create Database and connect to it
------------------------------------------------------------
psql --username=freecodecamp --dbname=postgres;

CREATE DATABASE worldcup;

\c worldcup

------------------------------------------------------------
2. Create tables as required conditions
------------------------------------------------------------

CREATE TABLE teams (
    team_id SERIAL NOT NULL,
    name VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE games (
    game_id SERIAL NOT NULL,
    year INTEGER NOT NULL,
    round VARCHAR(20) NOT NULL,
    winner_id      INTEGER NOT NULL,
    opponent_id    INTEGER NOT NULL,
    winner_goals   INTEGER NOT NULL,
    opponent_goals INTEGER NOT NULL
);

------------------------------------------------------------
3. Primary Key and Foreign Key assignment
------------------------------------------------------------
ALTER TABLE teams ADD PRIMARY KEY (team_id)
ALTER TABLE games ADD PRIMARY KEY (game_id)
ALTER TABLE games ADD FOREIGN KEY (winner_id)   REFERENCES teams (team_id)
ALTER TABLE games ADD FOREIGN KEY (opponent_id) REFERENCES teams (team_id)

------------------------------------------------------------
4.Give executable permission to shell scipt files
------------------------------------------------------------
chmod +x insert_data.sh
chmod +x queries.sh

------------------------------------------------------------
5.Copy & Run shell script files (NOTE : insert data first)
------------------------------------------------------------
./insert_data.sh
./queries.sh

------------------------------------------------------------
6. Compact sql db queries into worldcup.sql file.
------------------------------------------------------------
pg_dump -cC --inserts -U freecodecamp worldcup > worldcup.sql