SQL code

-- Dropping the tables so we can continuously run the code
DROP TABLE Venue;
DROP TABLE Coach;
DROP TABLE Achievement;
DROP TABLE Booking;
DROP TABLE TrainingItems;
DROP TABLE Client;

-- Creating the tables
CREATE TABLE Venue(
venueId VARCHAR(20) NOT NULL PRIMARY KEY,
vName CHAR(20) NOT NULL,
telephoneNumber INT NOT NULL,
vAddress VARCHAR(50) NOT NULL,
postcode INT NOT NULL
);

CREATE TABLE TrainingItems(
trainingItemsId VARCHAR(20) NOT NULL PRIMARY KEY,
tType VARCHAR(20) NOT NULL
);

CREATE TABLE Client(
clientId VARCHAR(20) NOT NULL PRIMARY KEY,
fName VARCHAR(20) NOT NULL,
lName VARCHAR(20) NOT NULL,
phoneNumber INT NOT NULL,
email VARCHAR(30) NOT NULL
);

CREATE TABLE Achievement(
achievementId VARCHAR(20) NOT NULL PRIMARY KEY,
aName CHAR(20) NOT NULL,
aType VARCHAR(20) NOT NULL,
aDateAchieved DATE NOT NULL
);

CREATE TABLE Booking(
bookingId VARCHAR(20) NOT NULL PRIMARY KEY,
dateOfBooking DATE NOT NULL,
startTime TIME NOT NULL,
endTime TIME NOT NULL,
totalHoursBooked INT NOT NULL,
totalCost MONEY NOT NULL,
totalPaid MONEY NOT NULL,
paymentStatus VARCHAR(20) NOT NULL,
completionStatus VARCHAR(20) NOT NULL,
travellingTime DECIMAL(10,2) NOT NULL,
clientId VARCHAR(20) FOREIGN KEY REFERENCES Client
);

CREATE TABLE Coach(
coachId VARCHAR(20) NOT NULL PRIMARY KEY,
cName CHAR(20) NOT NULL,
cEmail VARCHAR(30) NOT NULL,
cPhoneNumber INT NOT NULL,
cAddress VARCHAR(50) NOT NULL,
postcode INT NOT NULL,
achievementId VARCHAR(20) FOREIGN KEY REFERENCES Achievement,
bookingId VARCHAR(20) FOREIGN KEY REFERENCES Booking,
venueId VARCHAR(20) FOREIGN KEY REFERENCES Venue,
trainingItemsId VARCHAR(20) FOREIGN KEY REFERENCES trainingItems
);

-- Adding values into the tables
INSERT INTO Achievement
VALUES ('a1', 'Hollow Slaying', 'Lieutenant', '1/1/0487');
INSERT INTO Achievement
VALUES ('a2', 'Magic', 'PhD', '8/7/1867');
INSERT INTO Achievement
VALUES ('a3', 'Sharpshooting', 'PhD', '6/18/2015');
INSERT INTO Achievement
VALUES ('a4', 'Programming', 'PhD', '9/16/2017');

INSERT INTO Booking
VALUES ('b1', '10/18/2017', '10:00', '2', '1000', '300', 'Partial', 'Complete', '1.2', '1');
INSERT INTO Booking
VALUES ('b2', '10/17/2017', '11:00', '3', '500', '500', 'Paid', 'Incomplete', '0', '2');
INSERT INTO Booking
VALUES ('b3', '10/16/2017', '12:00', '4', '300', '0', 'Not Paid', 'Incomplete', '2', '3');
INSERT INTO Booking
VALUES ('b4', '10/15/2017', '13:00', '2', '100', '50', 'Partial', 'Complete', '0.5', '4');

INSERT INTO Venue
VALUES ('v1', 'Sokyoku', '29573817', 'Sokyoku Hill, Soul Society', '6728');
INSERT INTO Venue
VALUES ('v2', 'Kuoh Academy', '29573817', 'Kuoh, Japan', '9268');
INSERT INTO Venue
VALUES ('v3', 'Bonyari High', '29573817', 'Bonyari, Japan', '2245');
INSERT INTO Venue
VALUES ('v4', 'Blacktown Library', '29573817', 'Blacktown, Sydney', '2148');

INSERT INTO TrainingItems
VALUES ('t1', 'Slaying');
INSERT INTO TrainingItems
VALUES ('t2', 'Not Science');
INSERT INTO TrainingItems
VALUES ('t3', 'Sports');
INSERT INTO TrainingItems
VALUES ('t4', 'Science');

INSERT INTO Client
VALUES ('1', 'Lirin', 'Kinamoto', '0758392010', 'captain@eliteenforcers.com');
INSERT INTO Client
VALUES ('2', 'Akeno', 'Himejima', '0867463529', 'best@girl.com');
INSERT INTO Client
VALUES ('3', 'Chitoge', 'Kirisaki', '0987654321', 'successor@beehive.com');
INSERT INTO Client
VALUES ('4', 'Hiten', 'Bhudia', '0584939587', 'goblinbarrel@gmail.com');

INSERT INTO Coach
VALUES ('c1', 'Akimei Izayumi', 'akimeiizayumi@gmail.com', '0293857382', '1 Kido St, Soul Society', '0014', 'a1', 'b1', 'v1', 't1');
INSERT INTO Coach
VALUES ('c2', 'Rosuvaise Wang', 'rosuvaise<3@gmail.com', '0858392948', '654 Demonic St, Valhalla', '0100', 'a2', 'b2', 'v2', 't2');
INSERT INTO Coach
VALUES ('c3', 'Seishiro Tsugumi', 'ojousama@gmail.com', '0843986043', '2 Black Tiger st, Bonyari', '0001', 'a3', 'b3', 'v3', 't3');
INSERT INTO Coach
VALUES ('c4', 'Dylan Wang', 'gibberish69@gmail.com', '0968472658', '4 Trash Can, Sydney', '6996', 'a4', 'b4', 'v4', 't4');

/*
-- Viewing everything from the tables
SELECT * FROM Venue;
SELECT * FROM Coach;
SELECT * FROM Achievement;
SELECT * FROM TrainingItems;
SELECT * FROM Booking;
SELECT * FROM Client;
*/

-- SQL question 2
--a
SELECT c.fName, c.lName, b.dateOfBooking
FROM Client c, Booking b
WHERE c.clientId = b.clientId
ORDER BY c.fName;

SELECT Client.fName, Client.lName, Booking.dateOfBooking
FROM Client
FULL OUTER JOIN Booking ON Client.clientId = Booking.clientId;

--b
SELECT c.cName, b.totalHoursBooked
FROM Coach c, Booking b
WHERE c.bookingId = b.bookingId
ORDER BY c.cName;

SELECT Coach.cName, Booking.totalHoursBooked
FROM Coach
FULL OUTER JOIN Booking ON Coach.bookingId = Booking.bookingId;

SELECT Coach.cName, sum(Booking.totalHoursBooked)
FROM Coach
INNER JOIN Booking ON Coach.bookingId = Booking.bookingId
GROUP BY Coach.cName;

-- (MOVE FOREIGN KEY FROM COACH TO BOOKING)
SELECT Coach.cName, sum(Booking.totalHoursBooked)
FROM Booking
INNER JOIN Coach ON Booking.BookingId = Coach.bookingId
GROUP BY Coach.cName;

--c
SELECT c.cName, b.dateOfBooking, v.vName, t.fName
FROM Coach c, Booking b, Venue v, Client t
WHERE c.bookingId = b.bookingId
AND c.venueId = v.venueId
AND t.clientId = b.bookingId
AND b.dateOfBooking = '10/1/2017'
ORDER BY c.cName;

-- (CHECK THE FOREIGN KEYS)
SELECT Coach.cName, Venue.vName, Booking.dateOfBooking, Client.fName
INNER JOIN Booking ON Client.clientId = Booking.clientId
INNER JOIN Coach ON CoachId = Booking.CoachId
INNER JOIN Venue ON Coach.venueId = Venue.venueId
WHERE CONVERT(DATE, Booking.startTime) = '10/1/2017';

--d
SELECT c.cName, b.startTime, v.vName, t.trainingItemsId
FROM Coach c, Booking b, Venue v, TrainingItems t
WHERE c.bookingId = b.bookingId
AND c.venueId = v.venueId
AND c.trainingItemsId = t.trainingItemsId
AND b.dateOfBooking = '10/1/2017'
AND b.startTime = '10:00'
ORDER BY c.cName;

-- (CHECK FOREIGN KEYS AND ADDED BOOKING.ENDTIME)
SELECT Coach.cName
FROM TrainingItems
INNER JOIN Client ON Client.trainingId = TrainingItems.trainingId
INNER JOIN Booking ON Booking.clientId = Client.clientId
INNER JOIN Coach ON Coach.coachId = Booking.coachId
WHERE TrainingItems.tType = 'Slaying'
AND CONVERT(DATE, Booking.startTime) = '10/1/2017'
AND (CONVERT(time, Booking.startTime) != '10:00'
AND CONVERT(TIME, booking.endTime) != '12:00');