7
DB Fiddle - SQL Database Playground
source link: https://www.db-fiddle.com/f/pDcD13Grye9Rycb2Pissei/4
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Fiddle Title
50 characters remaining.Fiddle Description
300 characters remaining.
Private Fiddle
PRO
This setting cannot be modified after saving the fiddle.
Off
OnUpgrade to PRO
50% OFF for Early Adopters
Schema SQL
xxxxxxxxxx
/*
--
--
--
--
-- 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`),
Query SQL
xxxxxxxxxx
/*
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 rentals: duration * rate + discount (discount is negative)
extra: amount
cancellations: 100.00
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
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK