==========================================================================
 COSC6340
 Getting Started on the Course Server
==========================================================================

Your account:   dbt01, dbt02, ... dbt10   (you were given ONE of these)
Server:         52.22.24.16
Password:       given to you separately - do not share it


==========================================================================
 1. LOGGING IN
==========================================================================

WINDOWS
  Open PowerShell  (Start menu -> type "powershell" -> Enter)

MAC / LINUX
  Open Terminal

Then type (replace dbt01 with YOUR account):

  ssh dbt01@52.22.24.16

The first time only, you will see:

  Are you sure you want to continue connecting (yes/no)?

Type  yes  and press Enter.

Then it asks for your password.

  *** NOTHING APPEARS ON SCREEN WHILE YOU TYPE THE PASSWORD ***
  No dots, no stars. This is normal. Type it and press Enter.

Paste tip:  right-click in PowerShell, or Cmd+V on Mac.
            Ctrl+V does NOT work in most terminals.

When you are in, you will see a prompt like:

  dbt01@ip-10-0-1-196:~$

To leave the server at any time:

  exit


==========================================================================
 2. WHERE AM I?
==========================================================================

  whoami          who am I logged in as
  pwd             print the folder I am currently in
  ls              list files here
  ls -la          list everything, including hidden files
  cd foldername   go into a folder
  cd ..           go up one level
  cd ~            go back to my home folder

Your home folder is /home/COSC6340/dbt01 (with your own number).
Only you can read it. Nobody else in the class can see your work.


==========================================================================
 3. TWO DATABASES - THIS IS IMPORTANT
==========================================================================

  psql                    -> YOUR OWN database.
                             Empty at first. You can create, insert,
                             update, delete, and break anything.
                             Nobody else can see it.

  psql -d cosc3380        -> SHARED practice database.
                             Contains the airline data.
                             READ-ONLY. You can SELECT but not change it.

To leave psql and go back to the shell:   \q


==========================================================================
 4. FIRST LOOK AT THE AIRLINE SCHEMA
==========================================================================

  psql -d cosc3380

Now you are at a prompt that looks like:   cosc3380=>

Try these:

  \dn                     list the schemas in this database
  \dt airline.*           list the tables in the airline schema
  \d airline.flights      show the structure of one table
  \d airline.tickets      show another one

NOTE: plain  \dt  will NOT show the airline tables.
      You must write  \dt airline.*
      This is because the tables live in a schema called "airline",
      not in the default schema.


==========================================================================
 5. THE TABLES
==========================================================================

  aircrafts        aircraft_code, model, range
  airports         airport_code, airport_name, city, coordinates, timezone
  flights          flight_id, flight_no, scheduled_departure,
                   scheduled_arrival, departure_airport, arrival_airport,
                   status, aircraft_code, actual_departure, actual_arrival
  seats            aircraft_code, seat_no, fare_conditions
  bookings         book_ref, book_date, total_amount
  tickets          ticket_no, book_ref, passenger_id, passenger_name,
                   phone, email
  flighttickets    ticket_no, flight_id, fare_conditions, amount
  boardingpasses   ticket_no, flight_id, boarding_no, seat_no

Relationships:
  flights        -> aircrafts   (aircraft_code)
  flights        -> airports    (departure_airport, arrival_airport)
  seats          -> aircrafts   (aircraft_code)
  tickets        -> bookings    (book_ref)
  flighttickets  -> tickets, flights
  boardingpasses -> flighttickets


==========================================================================
 6. WRITING QUERIES - EVERY TABLE NEEDS THE "airline." PREFIX
==========================================================================

  SELECT * FROM airline.aircrafts;

If you get tired of typing "airline." before every table, run this once
per session:

  SET search_path TO airline, public;

After that, this works:

  SELECT * FROM aircrafts;

The setting disappears when you quit psql. Run it again next time.

REMINDER: every SQL statement ends with a semicolon ;
If psql shows a prompt like  cosc3380->  it is waiting for your semicolon.


==========================================================================
 7. EXAMPLE QUERIES - TRY THESE ONE AT A TIME
==========================================================================

-- Simple SELECT
SELECT * FROM airline.aircrafts;

-- Pick columns, limit rows
SELECT flight_no, status FROM airline.flights LIMIT 10;

-- Filter
SELECT model, range FROM airline.aircrafts WHERE range > 6000;

-- Sort
SELECT model, range FROM airline.aircrafts ORDER BY range DESC;

-- Count
SELECT COUNT(*) FROM airline.flights;

-- Count per group
SELECT status, COUNT(*)
FROM airline.flights
GROUP BY status;

-- Join two tables
SELECT f.flight_no, a.model
FROM airline.flights f
JOIN airline.aircrafts a ON f.aircraft_code = a.aircraft_code
LIMIT 10;

-- Join the same table twice (departure and arrival airports)
SELECT f.flight_no,
       dep.city AS from_city,
       arr.city AS to_city
FROM airline.flights f
JOIN airline.airports dep ON f.departure_airport = dep.airport_code
JOIN airline.airports arr ON f.arrival_airport   = arr.airport_code
LIMIT 10;

-- Aggregate with a join
SELECT a.model, COUNT(*) AS num_flights
FROM airline.flights f
JOIN airline.aircrafts a ON f.aircraft_code = a.aircraft_code
GROUP BY a.model
ORDER BY num_flights DESC;

-- HAVING - filter the groups, not the rows
SELECT departure_airport, COUNT(*) AS departures
FROM airline.flights
GROUP BY departure_airport
HAVING COUNT(*) > 500
ORDER BY departures DESC;

-- Subquery
SELECT flight_no
FROM airline.flights
WHERE aircraft_code IN (
    SELECT aircraft_code FROM airline.aircrafts WHERE range > 8000
)
LIMIT 10;

-- Working with timestamps: actual delay in minutes
SELECT flight_no,
       actual_departure - scheduled_departure AS delay
FROM airline.flights
WHERE actual_departure IS NOT NULL
ORDER BY delay DESC
LIMIT 10;

-- Five-table join: which passenger sat in which seat
SELECT t.passenger_name, f.flight_no, bp.seat_no, ft.fare_conditions
FROM airline.tickets t
JOIN airline.flighttickets ft  ON t.ticket_no = ft.ticket_no
JOIN airline.flights f         ON ft.flight_id = f.flight_id
JOIN airline.boardingpasses bp ON bp.ticket_no = ft.ticket_no
                              AND bp.flight_id = ft.flight_id
LIMIT 10;

-- Look at the query plan
EXPLAIN SELECT * FROM airline.flights WHERE flight_no = 'PG0405';

-- Query plan with real timings
EXPLAIN ANALYZE SELECT * FROM airline.flights WHERE flight_no = 'PG0405';


==========================================================================
 8. YOU CANNOT MODIFY THE SHARED DATA
==========================================================================

This will fail, and that is intentional:

  DELETE FROM airline.flights;
  ERROR:  permission denied for table flights

The shared copy stays clean for all 10 teams.

TO EXPERIMENT WITH WRITES, MAKE YOUR OWN COPY:

Quit psql first (\q), then from the shell:

  pg_dump -n airline cosc3380 | psql -d $USER

That copies the entire airline schema into YOUR OWN database.
Now you can do whatever you want with it:

  psql
  SELECT COUNT(*) FROM airline.flights;
  DELETE FROM airline.flights WHERE status = 'Cancelled';
  UPDATE airline.aircrafts SET range = 99999 WHERE aircraft_code = '773';

If you destroy your copy, just run the pg_dump command again.


==========================================================================
 9. USEFUL psql COMMANDS
==========================================================================

  \?              list all backslash commands
  \h SELECT       SQL syntax help for a statement
  \l              list all databases
  \dn             list schemas
  \dt airline.*   list tables in the airline schema
  \d tablename    describe a table (columns, keys, indexes)
  \di             list indexes
  \conninfo       which database am I connected to?
  \c dbname       switch to another database
  \x              toggle expanded output (good for wide rows)
  \timing         show how long each query takes
  \e              open your last query in an editor
  \q              quit

Two worth turning on immediately:

  \x on           makes wide results readable
  \timing on      shows query time - you will need this for the course


==========================================================================
 10. SAVING YOUR WORK
==========================================================================

IMPORTANT: tables you create live INSIDE PostgreSQL, not in your folder.
If you only create tables and never export them, your homework folder
will be EMPTY when it is collected.

Export your database to a file:

  pg_dump dbt01 > ~/myanswers.sql

Run a saved .sql file:

  psql -d cosc3380 -f myqueries.sql

Save query output to a file:

  psql -d cosc3380 -c "SELECT * FROM airline.aircrafts;" > output.txt

Write files with nano:

  nano myqueries.sql
     type your SQL
     Ctrl+O   then Enter    to save
     Ctrl+X                 to exit


==========================================================================
 11. COMMON PROBLEMS
==========================================================================

"permission denied for table ..."
    You are in cosc3380, which is read-only. Make your own copy
    (section 8) if you want to modify data.

"relation ... does not exist"
    Either you forgot the airline. prefix, or you are connected to the
    wrong database. Check with \conninfo

"schema airline does not exist"
    You are in your own database. The airline schema is only in
    cosc3380. Use:  psql -d cosc3380

The prompt says  cosc3380->  instead of  cosc3380=>
    You forgot the semicolon. Type ;  and press Enter.

psql seems frozen
    Press Ctrl+C to cancel the current query.

"Permission denied (publickey,password)" when connecting
    Wrong password, or wrong username. Check your account number.

Terminal is full of junk output
    Type  clear  and press Enter.


==========================================================================
 12. QUICK REFERENCE
==========================================================================

  ssh dbt01@52.22.24.16                     log in
  psql                                      your own database
  psql -d cosc3380                          shared airline data
  \dt airline.*                             list airline tables
  \d airline.flights                        describe a table
  SET search_path TO airline, public;       skip the airline. prefix
  pg_dump -n airline cosc3380 | psql -d $USER   your own writable copy
  pg_dump dbt01 > ~/myanswers.sql           export your work
  \q                                        quit psql
  exit                                      log out of the server

==========================================================================