turns-00003.parquet:50402
c8ada9abb048b2067c562327degenerate_repetitionAbsentFinal dense release
USER
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 SQL related question, answer only in sql code
Part 1:
Write a query that lists the clientid of all female clients.
Part 2:
Write a query that uses your query from part 1 as a sub query.
The query must list all the columns for bookings (booking table only) which were made by female clients.
This query must use the IN operator.
Your result set should have column headers like this clientid, tourname, eventmonth, eventday, eventyear, payment, datebooked
ASSISTANT
Part 1: SELECT CLIENTID FROM CLIENT WHERE GENDER = 'F'; Part 2: SELECT * FROM BOOKING WHERE CLIENTID IN (SELECT CLIENTID FROM CLIENT WHERE GENDER = 'F');