CREATE TABLE Discount_Coupons (
    coupon_id NUMBER PRIMARY KEY,
    date_issued TIME,
    coupon_amount NUMBER
);
CREATE TABLE Customers (
    customer_id NUMBER PRIMARY KEY,
    coupon_id NUMBER,
    good_or_bad_customer TEXT,
    first_name TEXT,
    last_name TEXT,
    gender_mf TEXT,
    date_became_customer TIME,
    date_last_hire TIME,
    FOREIGN KEY ("coupon_id") REFERENCES Discount_Coupons("coupon_id")
);
CREATE TABLE Bookings (
    booking_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    booking_status_code TEXT,
    returned_damaged_yn TEXT,
    booking_start_date TIME,
    booking_end_date TIME,
    count_hired TEXT,
    amount_payable NUMBER,
    amount_of_discount NUMBER,
    amount_outstanding NUMBER,
    amount_of_refund NUMBER,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id")
);
CREATE TABLE Products_for_Hire (
    product_id NUMBER PRIMARY KEY,
    product_type_code TEXT,
    daily_hire_cost NUMBER,
    product_name TEXT,
    product_description TEXT
);
CREATE TABLE Payments (
    payment_id NUMBER PRIMARY KEY,
    booking_id NUMBER,
    customer_id NUMBER,
    payment_type_code TEXT,
    amount_paid_in_full_yn TEXT,
    payment_date TIME,
    amount_due NUMBER,
    amount_paid NUMBER,
    FOREIGN KEY ("customer_id") REFERENCES Customers("customer_id"),
    FOREIGN KEY ("booking_id") REFERENCES Bookings("booking_id")
);
CREATE TABLE Products_Booked (
    booking_id NUMBER PRIMARY KEY,
    product_id NUMBER,
    returned_yn TEXT,
    returned_late_yn TEXT,
    booked_count NUMBER,
    booked_amount NUMBER,
    FOREIGN KEY ("product_id") REFERENCES Products_for_Hire("product_id"),
    FOREIGN KEY ("booking_id") REFERENCES Bookings("booking_id")
);
CREATE TABLE View_Product_Availability (
    product_id NUMBER,
    booking_id NUMBER,
    status_date TIME PRIMARY KEY,
    available_yn TEXT,
    FOREIGN KEY ("product_id") REFERENCES Products_for_Hire("product_id"),
    FOREIGN KEY ("booking_id") REFERENCES Bookings("booking_id")
);

Question: Are the customers holding coupons with amount 500 bad or good?
SQL: SELECT T1.good_or_bad_customer FROM customers AS T1 JOIN discount_coupons AS T2 ON T1.coupon_id  =  T2.coupon_id WHERE T2.coupon_amount  =  500

Question: How many bookings did each customer make? List the customer id, first name, and the count.
SQL: SELECT T1.customer_id ,  T1.first_name ,  count(*) FROM Customers AS T1 JOIN bookings AS T2 ON T1.customer_id  =  T2.customer_id GROUP BY T1.customer_id

Question: What is the maximum total amount paid by a customer? List the customer id and amount.
SQL: SELECT customer_id ,  sum(amount_paid) FROM Payments GROUP BY customer_id ORDER BY sum(amount_paid) DESC LIMIT 1

Question: What are the id and the amount of refund of the booking that incurred the most times of payments?
SQL: SELECT T1.booking_id ,  T1.amount_of_refund FROM Bookings AS T1 JOIN Payments AS T2 ON T1.booking_id  =  T2.booking_id GROUP BY T1.booking_id ORDER BY count(*) DESC LIMIT 1

Question: What is the id of the product that is booked for 3 times?
SQL: SELECT product_id FROM products_booked GROUP BY product_id HAVING count(*)  =  3

Question: What is the product description of the product booked with an amount of 102.76?
SQL: SELECT T2.product_description FROM products_booked AS T1 JOIN products_for_hire AS T2 ON T1.product_id  =  T2.product_id WHERE T1.booked_amount  =  102.76

Question: What are the start date and end date of the booking that has booked the product named 'Book collection A'?
SQL: SELECT T3.booking_start_date ,   T3.booking_end_date FROM Products_for_hire AS T1 JOIN products_booked AS T2 ON T1.product_id  =  T2.product_id JOIN bookings AS T3 ON T2.booking_id  =  T3.booking_id WHERE T1.product_name  =  'Book collection A'

Question: What are the names of products whose availability equals to 1?
SQL: SELECT T2.product_name FROM view_product_availability AS T1 JOIN products_for_hire AS T2 ON T1.product_id  =  T2.product_id WHERE T1.available_yn  =  1

Question: How many different product types are there?
SQL: SELECT count(DISTINCT product_type_code) FROM products_for_hire

Question: What are the first name, last name, and gender of all the good customers? Order by their last name.
SQL: SELECT first_name ,  last_name ,  gender_mf FROM customers WHERE good_or_bad_customer  =  'good' ORDER BY last_name

Question: What is the average amount due for all the payments?
SQL: SELECT avg(amount_due) FROM payments

Question: What are the maximum, minimum, and average booked count for the products booked?
SQL: SELECT max(booked_count) ,  min(booked_count) ,  avg(booked_count) FROM products_booked

Question: What are all the distinct payment types?
SQL: SELECT DISTINCT payment_type_code FROM payments

Question: What are the daily hire costs for the products with substring 'Book' in its name?
SQL: SELECT daily_hire_cost FROM Products_for_hire WHERE product_name LIKE '%Book%'

Question: How many products are never booked with amount higher than 200?
SQL: SELECT count(*) FROM Products_for_hire WHERE product_id NOT IN ( SELECT product_id FROM products_booked WHERE booked_amount  >  200 )

Question: What are the coupon amount of the coupons owned by both good and bad customers?
SQL: SELECT T1.coupon_amount FROM Discount_Coupons AS T1 JOIN customers AS T2 ON T1.coupon_id  =  T2.coupon_id WHERE T2.good_or_bad_customer  =  'good' INTERSECT SELECT T1.coupon_amount FROM Discount_Coupons AS T1 JOIN customers AS T2 ON T1.coupon_id  =  T2.coupon_id WHERE T2.good_or_bad_customer  =  'bad'

Question: What are the payment date of the payment with amount paid higher than 300 or with payment type is 'Check'
SQL: SELECT payment_date FROM payments WHERE amount_paid  >  300 OR payment_type_code  =  'Check'

Question: What are the names and descriptions of the products that are of 'Cutlery' type and have daily hire cost lower than 20?
SQL: SELECT product_name ,  product_description FROM products_for_hire WHERE product_type_code  =  'Cutlery' AND daily_hire_cost  <  20