human
Schema:
Discount_Coupons (coupon_id, date_issued, coupon_amount)
Customers (customer_id, coupon_id, good_or_bad_customer, first_name, last_name, gender_mf, date_became_customer, date_last_hire)
Bookings (booking_id, customer_id, booking_status_code, returned_damaged_yn, booking_start_date, booking_end_date, count_hired, amount_payable, amount_of_discount, amount_outstanding, amount_of_refund)
Products_for_Hire (product_id, product_type_code, daily_hire_cost, product_name, product_description)
Payments (payment_id, booking_id, customer_id, payment_type_code, amount_paid_in_full_yn, payment_date, amount_due, amount_paid)
Products_Booked (booking_id, product_id, returned_yn, returned_late_yn, booked_count, booked_amount)
View_Product_Availability (product_id, booking_id, status_date, available_yn)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the id and the amount of refund of the booking that incurred the most times of payments?
assistant
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
human
What is the product description of the product booked with an amount of 102.76?
assistant
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
human
What are the maximum, minimum, and average booked count for the products booked?
assistant
SELECT max(booked_count) , min(booked_count) , avg(booked_count) FROM products_booked
human
How many products are never booked with amount higher than 200?
assistant
SELECT count(*) FROM Products_for_hire WHERE product_id NOT IN ( SELECT product_id FROM products_booked WHERE booked_amount > 200 )
human
What are the coupon amount of the coupons owned by both good and bad customers?
assistant
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'
human
Are the customers holding coupons with amount 500 bad or good?
assistant
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 ;