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');

Using the information above answer the following only in SQL code select the number of bookings made for each tourname by each gender of client.
Order the results in  descending tourname order.
Your result set should have column headers like this tourname, gender, num bookings