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:

Using at least 1 full outer join, list all clients and all events (all columns)
Where clients have booked for an event join the client, booking and event data on the same line of the result set.

Where a client hasn't booked, or an event hasn't had any bookings make sure to list these with null values filling the columns for which there is no data.
Your result set should have column headers like this clientid, surname, givenname, gender, clientid, tourname, eventmonth, eventday, eventyear, payment, datebooked