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
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 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 start date and end date of the booking that has booked the product named 'Book collection A'?
assistant
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'
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
What are the names and descriptions of the products that are of 'Cutlery' type and have daily hire cost lower than 20?
assistant
SELECT product_name , product_description FROM products_for_hire WHERE product_type_code = 'Cutlery' AND daily_hire_cost < 20
human
What are the daily hire costs for the products with substring 'Book' in its name?
assistant
SELECT daily_hire_cost FROM Products_for_hire WHERE product_name LIKE "%Book%" ;