Exercise for /r/SQL | DB Fiddle - SQL Database Playground

/*

--

--

--

--

-- Read the introduction over there first >>>>>>>>>>>

--

--

--

--

-- This is just schema information and populating data

--

--

--

--

--

*/

CREATE TABLE IF NOT EXISTS `customers` (

    `customer_id`   INT(6) UNSIGNED NOT NULL,

    `name`          VARCHAR(100)    NOT NULL,

    `join_date`     DATE            NOT NULL,

    PRIMARY KEY(`customer_id`)

) DEFAULT CHARSET=utf8

;

CREATE TABLE IF NOT EXISTS `default_rates` (

    `rate_id`       INT(6) UNSIGNED NOT NULL,

    `daily_rate`    DECIMAL(10, 2)  NOT NULL,

    PRIMARY KEY(`rate_id`)

) DEFAULT CHARSET=utf8

;

CREATE TABLE IF NOT EXISTS `cars` (

    `car_id`        INT(6) UNSIGNED NOT NULL,

    `brand`         VARCHAR(50)     NOT NULL,

    `model`         VARCHAR(50)     NOT NULL,

    `model_year`    INT(6) UNSIGNED NULL,

    `plate`         VARCHAR(10)     NOT NULL,

    `rate_id`       INT(6) UNSIGNED NOT NULL,

    PRIMARY KEY(`car_id`),

    CONSTRAINT `fk_car_rate` FOREIGN KEY(`rate_id`) REFERENCES default_rates(`rate_id`)

) DEFAULT CHARSET=utf8

;

CREATE TABLE IF NOT EXISTS `bookings` (

    `booking_id`    INT(6) UNSIGNED NOT NULL,

    `out_date`      DATE            NOT NULL,

    `ret_date`      DATE            NOT NULL,

    `customer_id`   INT(6) UNSIGNED NOT NULL,

    `car_id`        INT(6) UNSIGNED NOT NULL,

    `discount`      DECIMAL(10, 2)  NOT NULL DEFAULT 0.00, -- Singular, one off discount, not on rate

    `is_cancelled`  BOOLEAN         NOT NULL DEFAULT FALSE,

    PRIMARY KEY(`booking_id`),

/*

  Introduction:

  Lord Johnson, owner of a collection of luxury vehicles, has been renting them out

  Unfortunately, his love of vehicles did not extend to databases

  Details have been well recorded, so you don't need to clean the data

  "Just get it done" says Johnson, heading to his yacht with his latest mistress

  "But I only have select and insert permissions!" You exclaim, too late

​

  Task 1:

  Normally, a customer will remember to cancel a booking if they want to cancel or it

  Unfortunately, this has been handled manually and imperfectly

  Georg Becker complained he was charged for booking id 15, which was superceded by #16

  A customer cannot rent more than one car at once or double rent one car

  As per usual, nobody bothered to design in cancellation checks, so it's fallen to you

  When a newer booking overlaps with an older one, the older is considered cancelled

  Affected customers will need a grovelling apology where the booking was not cancelled!

  Report

    booking id which should be considered cancelled

    customer name

    car brand & model

    start date

    return date

    amount

  Remember you can happily return a car in the morning and rent another one that afternoon

​

  Task 2:

  A stern letter with red print from the tax man sits on your desk, demanding ALL INVOICES

  You eye the large stack of handwritten "invoices" and think "perhaps not"

  There exists already an invoices table, but it sits empty

  Invoices are dated for the end of the month and cover all charges in that month

    invoice_id      sequential, increasing with date, customer_id

    line_id         sequential per invoice, with one charge per line

    invoice_date    the end of the month in question

    customer_id     because the accountant wants to check

    customer_name   because the postal service needs it

    booking_id      in case the customer wants to check

    extra_id        as booking_id, but helps with tracing things back