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 inner joins, select the given name, surname, tour name, tour description, date booked and event fee for every booking with a tour month of Jan.

You'll need to join every table to get all the required columns. 

Your result set should have column headers like this givenname, surname, tourname, datebooked, eventfee, description