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 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
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
How many different product types are there?
assistant
SELECT count(DISTINCT product_type_code) FROM products_for_hire
human
What is the average amount due for all the payments?
assistant
SELECT avg(amount_due) FROM payments
human
What are the payment date of the payment with amount paid higher than 300 or with payment type is 'Check'
assistant
SELECT payment_date FROM payments WHERE amount_paid > 300 OR payment_type_code = 'Check'
human
What are all the distinct payment types?
assistant
SELECT DISTINCT payment_type_code FROM payments ;