sqt_training_2.sql

---------------------------------------
-- SQL Training 2/4 hands-on session --
---------------------------------------

-- Customers
drop table if exists customers cascade;
create table customers (
  id serial primary key,
  name text not null
);

-- Drivers
drop table if exists drivers cascade;
create table drivers (
  id serial primary key,
  license text not null,
  name text not null
);

-- This is a simplification, not to have car_brands and car_models tables.
-- Take a look at the number of joind we already need to "reassemble" the data
-- in the original form. 
-- The idea is to show how particular situations may pull for different design
-- decisions. This one though a bit severe :p  would make sense if we _knew_
-- that it is unlikely we'd do a query to ask eg. "how many models each brand
-- has". If the business evolves to having this need, we can always migrate the
-- schema and data using the relevant ALTER, UPDATE, etc. statements.
drop type car_model_t cascade;
create type car_model_t as enum ('Ford Anglia', 'Peugeot Knight', 'Peugeot King');

-- Cars
drop table if exists cars cascade;
create table cars (
  id serial primary key,
  license_plate text not null,
  model car_model_t not null 
);

-- Rides
drop table if exists rides cascade;
create table rides (
  id serial primary key,
  date timestamptz not null,
  _from text not null,
  _to text not null,
  driver_id integer not null references drivers (id),
  car_id integer not null references cars (id),
  has_luggage boolean not null default false
);

drop table if exists ride_customers;
create table ride_customers (
  ride_id integer not null references rides (id),
  customer_id integer not null references customers (id)
);

----------------------------------------------------------------------------------------------------

insert into customers(name) values ('Hermione Granger'), ('Ron Weasley');
--  id |       name
-- ----+------------------
--   1 | Hermione Granger
--   2 | Ron Weasley

insert into drivers(name, license) values ('Ernest Prang', 'DL123456');
--  id | license  |     name
-- ----+----------+--------------
--   1 | DL123456 | Ernest Prang

insert into cars(license_plate, model) values ('123-ABC', 'Peugeot Knight');
--  id | license_plate |   model
-- ----+---------------+----------------
--   1 | 123-ABC       | Peugeot Knight

insert into rides(date, _from, _to, driver_id, car_id) values ('04/01/2022 02:55:00', 'Elephant H. Bar', 'Ron''s House', 1, 1);
--  id |          date          |      _from      |     _to     | driver_id | car_id | has_luggage
-- ----+------------------------+-----------------+-------------+-----------+--------+-------------
--   1 | 2022-04-01 02:55:00+02 | Elephant H. Bar | Ron's House |         1 |      1 | f

insert into ride_customers(customer_id, ride_id) values (1, 1), (2, 1);
--  ride_id | customer_id
-- ---------+-------------
--        1 |           1
--        1 |           2

----------------------------------------------------------------------------------------------------

-- This gives you the full data for the ride we've just inserted
select
  rides.id,
  rides.date,
  string_agg(customers.name, ', '),
  rides._from,
  rides._to,
  drivers.name,
  drivers.license,
  cars.license_plate,
  cars.model,
  has_luggage
from
  rides
  join drivers on rides.driver_id=drivers.id
  join cars on rides.car_id=cars.id
  join ride_customers on ride_customers.ride_id=rides.id
  join customers on ride_customers.customer_id=customers.id
group by
  -- we're actually only grouping by riders.id, but...
  rides.id,
  -- ...postgres doesn't know these other values are also unique
  -- within the group, so we just group by them too
  drivers.name,
  drivers.license,
  cars.license_plate,
  cars.model
;