DROP TABLE IF EXISTS BOOKING;
DROP TABLE IF EXISTS [EVENT];
DROP TABLE IF EXISTS TOUR;
DROP TABLE IF EXISTS CLIENT;

GO

CREATE TABLE CLIENT(
    CLIENTID    INT,
    SURNAME     NVARCHAR(100) NOT NULL,
    GIVENNAME   NVARCHAR(100) NOT NULL,
    GENDER      NVARCHAR(1),
    CONSTRAINT PK_CLIENT PRIMARY KEY(CLIENTID),
    CONSTRAINT CHK_GENDER CHECK (GENDER IN('M','F','I'))
);

CREATE TABLE TOUR(
    TOURNAME        NVARCHAR(100),    
    [DESCRIPTION]   NVARCHAR(500),
    CONSTRAINT PK_TOUR PRIMARY KEY(TOURNAME)
);

CREATE TABLE EVENT(
    TOURNAME    NVARCHAR(100),
    EVENTMONTH  NVARCHAR(3),
    EVENTDAY    INT,
    EVENTYEAR   INT,
    EVENTFEE    MONEY NOT NULL,
    CONSTRAINT PK_EVENT PRIMARY KEY(TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR),
    CONSTRAINT FK_EVENT_TOUR FOREIGN KEY(TOURNAME) REFERENCES TOUR,
    CONSTRAINT CHK_EVENT_EVENTMONTH CHECK(EVENTMONTH IN('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')),
    CONSTRAINT CHK_EVENT_EVENTDAY CHECK(EVENTDAY >= 1 AND EVENTDAY <= 31),
    CONSTRAINT CHK_EVENT_EVENTYEAR CHECK(LEN(EVENTYEAR) = 4),
    CONSTRAINT CHK_EVENTFEE CHECK(EVENTFEE > 0)
);

CREATE TABLE BOOKING(
    CLIENTID INT,
    TOURNAME    NVARCHAR(100),
    EVENTMONTH  NVARCHAR(3),
    EVENTDAY    INT,
    EVENTYEAR   INT,
    PAYMENT     MONEY,
    DATEBOOKED DATE NOT NULL,
    CONSTRAINT PK_BOOKING PRIMARY KEY(CLIENTID, TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR),
    CONSTRAINT FK_BOOKING_EVENT FOREIGN KEY(TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR) REFERENCES [EVENT],
    CONSTRAINT FK_BOOKING_CLIENT FOREIGN KEY(CLIENTID) REFERENCES CLIENT,
    CONSTRAINT CHK_BOOKING_EVENTMONTH CHECK(EVENTMONTH IN('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')),
    CONSTRAINT CHK_BOOKING_EVENTDAY CHECK(EVENTDAY >= 1 AND EVENTDAY <= 31),
    CONSTRAINT CHK_BOOKING_EVENTYEAR CHECK(LEN(EVENTYEAR) = 4),
    CONSTRAINT CHK_PAYMENT CHECK(PAYMENT> 0)
);
GO

INSERT INTO CLIENT(CLIENTID, SURNAME, GIVENNAME, GENDER)VALUES
(1, 'Price','Taylor','M'),
(2, 'Gamble','Ellyse','F'),
(3, 'Tan', 'Tilly', 'F'),
(4, 'Onna', 'Myowney', 'I');

INSERT INTO TOUR (TOURNAME, [DESCRIPTION]) VALUES
('North',   'Tour of wineries and outlets of the Bedigo and Castlemaine region'),
('South',   'Tour of wineries and outlets of Mornington Penisula'),
('West',    'Tour of wineries and outlets of the Geelong and Otways region');

INSERT INTO EVENT (TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR, EVENTFEE) VALUES
('North', 'Jan', 9, 2022, 200),
('North', 'Feb', 13, 2022, 225),
('South', 'Jan', 9, 2022, 200),
('South', 'Jan', 16, 2022, 200),
('West', 'Jan', 29, 2022, 225),
('West', 'Feb', 12, 2022, 225);

INSERT INTO BOOKING (CLIENTID, TOURNAME, EVENTMONTH, EVENTDAY, EVENTYEAR, PAYMENT, DATEBOOKED) VALUES
(1, 'North', 'Jan', 9, 2022, 200, '2021/12/10'),
(2, 'North', 'Jan', 9, 2022, 200, '2022/01/03'),
(1, 'North', 'Feb', 13, 2022, 225, '2022/01/28'),
(2, 'North', 'Feb', 13, 2022, 125, '2022/01/14'),
(3, 'North', 'Feb', 13, 2022, 225, '2022/02/10'),
(1, 'South', 'Jan', 9, 2022, 200, '2021/12/10'),
(1, 'South', 'Jan', 16, 2022, 200, '2022/1/11'),
(2, 'South', 'Jan', 16, 2022, 200, '2021/12/18'),
(3, 'South', 'Jan', 16, 2022, 200, '2022/01/15'),
(2, 'West', 'Jan',  29, 2022, 225, '2022/01/17'),
(3, 'West', 'Jan',  29, 2022, 200, '2021/12/18');

SELECT TOURNAME, GENDER, COUNT(*) as 'num bookings' FROM BOOKING
JOIN CLIENT ON BOOKING.CLIENTID = CLIENT.CLIENTID
GROUP BY TOURNAME, GENDER ORDER BY TOURNAME DESC, GENDER;

Using the information above answer the following only in SQL code within a single line:

Provide a list of clients who still owe money for events they have booked for.
A client owes money if - the payment for one or more of their bookings is less than the event fee for the related event.

Your query should list the clients given name, surname and a calculated column named 'amount owing' (i.e event. eventfee - booking.payment )

you may to use some joins to make this work.

Your result set should have column headers like this givenname, surname, amount owing