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 is the id of the product that is booked for 3 times?
assistant
SELECT product_id FROM products_booked GROUP BY product_id HAVING count(*) = 3
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 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
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
How many different product types are there?
assistant
SELECT count(DISTINCT product_type_code) FROM products_for_hire
human
What are the names of products whose availability equals to 1?
assistant
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 ;