MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Books (
    title VARCHAR(100) NOT NULL,
    author_1 VARCHAR(30) NOT NULL,
    author_2 VARCHAR(30), -- all these other "author" fields are for books with more than one author, but they can be NULL
    author_3 VARCHAR(30),
    author_4 VARCHAR(30),
    author_5 VARCHAR(30),
    author_6 VARCHAR(30),
    author_7 VARCHAR(30),
    author_8 VARCHAR(30),
    author_9 VARCHAR(30),
    author_10 VARCHAR(30),
    ISBN BIGINT NOT NULL, -- an ISBN can be between 10 and 13 num long, so not enough for an INT
    PRIMARY KEY(ISBN),
    stock INT NOT NULL
);
INSERT INTO Books VALUES ("Call of Cthulhu: Fantasy Roleplaying in the Worlds of HP Lovecraft", "Sandy Petersen", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780933635586, 2); -- Since I included the possibility of having multiple authors, I'm forced to write NULL for the empty fields, as I don't know how else to manage this (as of the time of me writing this)
INSERT INTO Books VALUES ("Vampire: The Masquerade", "Phil Brucato", "Tom Dowd", "Mark Rein-Hagen", "Richard E. Dansky", NULL, NULL, NULL, NULL, NULL, NULL, 9781565042490, 4);
INSERT INTO Books VALUES ("IT", "Stephen King", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780670813025, 27);
INSERT INTO Books VALUES ("Elantris", "Brandon Sanderson", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780765311771, 12);
INSERT INTO Books VALUES ("Watchmen", "Alan Moore", "Dave Gibson", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9781852860240, 15);
INSERT INTO Books VALUES ("Supergod", "Warren Ellis", "Garrie Gastonny", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9781592911004, 6);
INSERT INTO Books VALUES ("Necronomicon", "H. R. Giger", NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,  9780905664064, 8);
INSERT INTO Books VALUES ("The Fantastic Art of Frank Frazetta", "Frank Frazetta", "Betty Ballantine",  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9780684144160, 1);
INSERT INTO Books VALUES ("Trigun", "Yasuhiro Nightow",  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9783551767011, 2);
INSERT INTO Books VALUES ("Ghost in the Shell", "Masamune Shirow",  NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 9781569710814, 5);

CREATE TABLE Users (
    user_id MEDIUMINT AUTO_INCREMENT NOT NULL, -- MEDIUMINT because I don't think a library is going to have more than 8388607 users but it might have more than 32767 (SMALLINT)
    PRIMARY KEY(user_id),
    First_Name VARCHAR(15) NOT NULL,
    Last_Name VARCHAR(15) NOT NULL,
    Contact_Number VARCHAR(32) NOT NULL, -- Longest telephone number possible should be at max 15 num long, then there are the +, the country code, the area code and the possibility of other char like () - and so on
    City VARCHAR(85) NOT NULL, -- 85 is the longest name recorded for any place
    Address_1 VARCHAR(50) NOT NULL, -- Address shouldn't be more than 35-40, but just in case
    Address_2 VARCHAR(50), -- can be NULL, as it's not necessary to fill
    County VARCHAR(30) NOT NULL, -- longest county name in the UK is "Cambridgeshire and Isle of Ely", a total of 30 char
    Postcode CHAR(7) NOT NULL -- I chose a CHAR because postcodes a pretty consistent, being 5 to 7 char, so maybe it could save up memory?
);
INSERT INTO Users VALUES (4, "Bruce", "Wayne", 07893475629, "Gotham City", "1007, Mountain Drive", "Batcave", "Gotham", "NG8 2AE");
INSERT INTO Users VALUES (6, "Tony", "Stark", 09023853948, "Point Dume", "10880, Malibu Point", NULL, "Malibu", "MRVL616");
INSERT INTO Users VALUES (49, "Jessica", "Fletcher", 93829774, "Cabot Cove", "698, CAndlewood Lane", NULL, "Maine", "FN39F");
INSERT INTO Users VALUES (287, "Sherlock", "Holmes", 2984723987, "London", "221B, Baker Street", NULL, "Greater London", "OFN38");
INSERT INTO Users VALUES (2397, "Herman", "Munster", 2593528955, "Mockingbird Heights", "1313, Mockingbird Lane", NULL, "Los Angeles", "NF93FN");
INSERT INTO Users VALUES (29374, "Phileas", "Fogg", 234829384, "London", "7, Savile Row", "Burlington Gardens", "Greater London", "984NF");
INSERT INTO Users VALUES (37, "Spongebob", "Squarepants", 293829493, "Bikini Bottom", "124, Conch Street", NULL, "Pacific Ocean", "46BF9");
INSERT INTO Users VALUES (7, "Sweeny", "Todd", 0923492734, "London", "186, Fleet Street", NULL, "Greater London", "8DK92");
INSERT INTO Users VALUES (2, "Homer", "Simpson", 0234475923, "Springfield", "742, Evergreen Terrace", NULL, "Maine", "PE02SN");
INSERT INTO Users VALUES (73387, "Harry", "Potter", 02387426, "Little Whinging", "4, Privet Drive", "The Cupboard unda the Stairs", "Surrey", "FO9FH");


CREATE TABLE Loaned (
    user_id_loaned MEDIUMINT AUTO_INCREMENT NOT NULL,
    ISBN_loaned BIGINT NOT NULL,
    FOREIGN KEY (user_id_loaned) REFERENCES Users(user_id),
    FOREIGN KEY (ISBN_loaned) REFERENCES Books(ISBN),    
    date_lent DATE NOT NULL,
    due_date DATE NOT NULL
);
INSERT INTO Loaned VALUES (49, 9780684144160, "1991-03-29", "1991-05-29");
INSERT INTO Loaned VALUES (287, 9780933635586, "1827-08-17", "1827-10-17");
INSERT INTO Loaned VALUES (2397, 9780670813025, "2022-05-10", "2022-07-10");
INSERT INTO Loaned VALUES (37, 9780670813025, "2022-04-19", "2022-06-19");
INSERT INTO Loaned VALUES (2397, 9780905664064, "2022-03-21", "2022-05-21");
INSERT INTO Loaned VALUES (29374, 9781592911004, "2022-05-16", "2022-07-16");

SELECT * FROM Users; 
SELECT * FROM Books; 
SELECT * FROM Loaned; 


user_id
First_Name
Last_Name
Contact_Number
City
Address_1
Address_2
County
Postcode


2
Homer
Simpson
234475923
Springfield
742, Evergreen Terrace
null
Maine
PE02SN

4
Bruce
Wayne
7893475629
Gotham City
1007, Mountain Drive
Batcave
Gotham
NG8 2AE

6
Tony
Stark
9023853948
Point Dume
10880, Malibu Point
null
Malibu
MRVL616

7
Sweeny
Todd
923492734
London
186, Fleet Street
null
Greater London
8DK92

37
Spongebob
Squarepants
293829493
Bikini Bottom
124, Conch Street
null
Pacific Ocean
46BF9

49
Jessica
Fletcher
93829774
Cabot Cove
698, CAndlewood Lane
null
Maine
FN39F

287
Sherlock
Holmes
2984723987
London
221B, Baker Street
null
Greater London
OFN38

2397
Herman
Munster
2593528955
Mockingbird Heights
1313, Mockingbird Lane
null
Los Angeles
NF93FN

29374
Phileas
Fogg
234829384
London
7, Savile Row
Burlington Gardens
Greater London
984NF

73387
Harry
Potter
2387426
Little Whinging
4, Privet Drive
The Cupboard unda the Stairs
Surrey
FO9FH




title
author_1
author_2
author_3
author_4
author_5
author_6
author_7
author_8
author_9
author_10
ISBN
stock


IT
Stephen King
null
null
null
null
null
null
null
null
null
9780670813025
27

The Fantastic Art of Frank Frazetta
Frank Frazetta
Betty Ballantine
null
null
null
null
null
null
null
null
9780684144160
1

Elantris
Brandon Sanderson
null
null
null
null
null
null
null
null
null
9780765311771
12

Necronomicon
H. R. Giger
null
null
null
null
null
null
null
null
null
9780905664064
8

Call of Cthulhu: Fantasy Roleplaying in the Worlds of HP Lovecraft
Sandy Petersen
null
null
null
null
null
null
null
null
null
9780933635586
2

Vampire: The Masquerade
Phil Brucato
Tom Dowd
Mark Rein-Hagen
Richard E. Dansky
null
null
null
null
null
null
9781565042490
4

Ghost in the Shell
Masamune Shirow
null
null
null
null
null
null
null
null
null
9781569710814
5

Supergod
Warren Ellis
Garrie Gastonny
null
null
null
null
null
null
null
null
9781592911004
6

Watchmen
Alan Moore
Dave Gibson
null
null
null
null
null
null
null
null
9781852860240
15

Trigun
Yasuhiro Nightow
null
null
null
null
null
null
null
null
null
9783551767011
2




user_id_loaned
ISBN_loaned
date_lent
due_date


49
9780684144160
1991-03-29
1991-05-29

287
9780933635586
1827-08-17
1827-10-17

2397
9780670813025
2022-05-10
2022-07-10

37
9780670813025
2022-04-19
2022-06-19

2397
9780905664064
2022-03-21
2022-05-21

29374
9781592911004
2022-05-16
2022-07-16


SELECT l.user_id_loaned, 
       u.First_Name, 
       u.Last_Name, 
       b.title, 
       l.ISBN_Loaned 
FROM Users u 
INNER JOIN Loaned l ON u.user_id = l.user_id_loaned
INNER JOIN Books b ON b.ISBN = l.ISBN_loaned


user_id_loaned
First_Name
Last_Name
title
ISBN_Loaned


2397
Herman
Munster
IT
9780670813025

37
Spongebob
Squarepants
IT
9780670813025

49
Jessica
Fletcher
The Fantastic Art of Frank Frazetta
9780684144160

2397
Herman
Munster
Necronomicon
9780905664064

287
Sherlock
Holmes
Call of Cthulhu: Fantasy Roleplaying in the Worlds of HP Lovecraft
9780933635586

29374
Phileas
Fogg
Supergod
9781592911004

title	author_1	author_2	author_3	author_4	author_5	author_6	author_7	author_8	author_9	author_10	ISBN	stock
IT	Stephen King	null	null	null	null	null	null	null	null	null	9780670813025	27
The Fantastic Art of Frank Frazetta	Frank Frazetta	Betty Ballantine	null	null	null	null	null	null	null	null	9780684144160	1
Elantris	Brandon Sanderson	null	null	null	null	null	null	null	null	null	9780765311771	12
Necronomicon	H. R. Giger	null	null	null	null	null	null	null	null	null	9780905664064	8
Call of Cthulhu: Fantasy Roleplaying in the Worlds of HP Lovecraft	Sandy Petersen	null	null	null	null	null	null	null	null	null	9780933635586	2
Vampire: The Masquerade	Phil Brucato	Tom Dowd	Mark Rein-Hagen	Richard E. Dansky	null	null	null	null	null	null	9781565042490	4
Ghost in the Shell	Masamune Shirow	null	null	null	null	null	null	null	null	null	9781569710814	5
Supergod	Warren Ellis	Garrie Gastonny	null	null	null	null	null	null	null	null	9781592911004	6
Watchmen	Alan Moore	Dave Gibson	null	null	null	null	null	null	null	null	9781852860240	15
Trigun	Yasuhiro Nightow	null	null	null	null	null	null	null	null	null	9783551767011	2

user_id	First_Name	Last_Name	Contact_Number	City	Address_1	Address_2	County	Postcode
2	Homer	Simpson	234475923	Springfield	742, Evergreen Terrace	null	Maine	PE02SN
4	Bruce	Wayne	7893475629	Gotham City	1007, Mountain Drive	Batcave	Gotham	NG8 2AE
6	Tony	Stark	9023853948	Point Dume	10880, Malibu Point	null	Malibu	MRVL616
7	Sweeny	Todd	923492734	London	186, Fleet Street	null	Greater London	8DK92
37	Spongebob	Squarepants	293829493	Bikini Bottom	124, Conch Street	null	Pacific Ocean	46BF9
49	Jessica	Fletcher	93829774	Cabot Cove	698, CAndlewood Lane	null	Maine	FN39F
287	Sherlock	Holmes	2984723987	London	221B, Baker Street	null	Greater London	OFN38
2397	Herman	Munster	2593528955	Mockingbird Heights	1313, Mockingbird Lane	null	Los Angeles	NF93FN
29374	Phileas	Fogg	234829384	London	7, Savile Row	Burlington Gardens	Greater London	984NF
73387	Harry	Potter	2387426	Little Whinging	4, Privet Drive	The Cupboard unda the Stairs	Surrey	FO9FH

user_id_loaned	ISBN_loaned	date_lent	due_date
49	9780684144160	1991-03-29	1991-05-29
287	9780933635586	1827-08-17	1827-10-17
2397	9780670813025	2022-05-10	2022-07-10
37	9780670813025	2022-04-19	2022-06-19
2397	9780905664064	2022-03-21	2022-05-21
29374	9781592911004	2022-05-16	2022-07-16