Database: products_for_hire

CREATE TABLE `Discount_Coupons` (
`coupon_id` INTEGER PRIMARY KEY,
`date_issued` DATETIME,
`coupon_amount` DECIMAL(19,4)
);
CREATE TABLE `Customers` (
`customer_id` INTEGER PRIMARY KEY,
`coupon_id` INTEGER NOT NULL,
`good_or_bad_customer` VARCHAR(4),
`first_name` VARCHAR(80),
`last_name` VARCHAR(80),
`gender_mf` VARCHAR(1),
`date_became_customer` DATETIME,
`date_last_hire` DATETIME,
FOREIGN KEY (`coupon_id` ) REFERENCES `Discount_Coupons`(`coupon_id` )
);
CREATE TABLE `Bookings` (
`booking_id` INTEGER PRIMARY KEY ,
`customer_id` INTEGER NOT NULL,
`booking_status_code` VARCHAR(10) NOT NULL,
`returned_damaged_yn` VARCHAR(40),
`booking_start_date` DATETIME,
`booking_end_date` DATETIME,
`count_hired` VARCHAR(40),
`amount_payable` DECIMAL(19,4),
`amount_of_discount` DECIMAL(19,4),
`amount_outstanding` DECIMAL(19,4),
`amount_of_refund` DECIMAL(19,4),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Products_for_Hire` (
`product_id` INTEGER PRIMARY KEY,
`product_type_code` VARCHAR(15) NOT NULL,
`daily_hire_cost` DECIMAL(19,4),
`product_name` VARCHAR(80),
`product_description` VARCHAR(255)
);
CREATE TABLE `Payments` (
`payment_id` INTEGER PRIMARY KEY,
`booking_id` INTEGER,
`customer_id` INTEGER NOT NULL,
`payment_type_code` VARCHAR(15) NOT NULL,
`amount_paid_in_full_yn` VARCHAR(1),
`payment_date` DATETIME,
`amount_due` DECIMAL(19,4),
`amount_paid` DECIMAL(19,4),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`customer_id` ) REFERENCES `Customers`(`customer_id` )
);
CREATE TABLE `Products_Booked` (
`booking_id` INTEGER NOT NULL,
`product_id` INTEGER NOT NULL,
`returned_yn` VARCHAR(1),
`returned_late_yn` VARCHAR(1),
`booked_count` INTEGER,
`booked_amount` FLOAT NULL,
PRIMARY KEY (`booking_id`, `product_id`)
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_id` )
);
CREATE TABLE `View_Product_Availability` (
`product_id` INTEGER NOT NULL,
`booking_id` INTEGER NOT NULL,
`status_date` DATETIME PRIMARY KEY,
`available_yn` VARCHAR(1),
FOREIGN KEY (`booking_id` ) REFERENCES `Bookings`(`booking_id` ),
FOREIGN KEY (`product_id` ) REFERENCES `Products_for_Hire`(`product_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