human
Schema:
Problem_Category_Codes (problem_category_code, problem_category_description)
Problem_Log (problem_log_id, assigned_to_staff_id, problem_id, problem_category_code, problem_status_code, log_entry_date, log_entry_description, log_entry_fix, other_log_details)
Problem_Status_Codes (problem_status_code, problem_status_description)
Product (product_id, product_name, product_details)
Staff (staff_id, staff_first_name, staff_last_name, other_staff_details)
Problems (problem_id, product_id, closure_authorised_by_staff_id, reported_by_staff_id, date_problem_reported, date_problem_closed, problem_description, other_problem_details)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the ids of the problems reported before the date of any problem reported by Lysanne Turcotte?
assistant
SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported < ( SELECT min(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = 'Lysanne' AND T4.staff_last_name = 'Turcotte' )
human
What are the ids of the problems reported after the date of any problems reported by Rylan Homenick?
assistant
SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE date_problem_reported > ( SELECT max(date_problem_reported) FROM problems AS T3 JOIN staff AS T4 ON T3.reported_by_staff_id = T4.staff_id WHERE T4.staff_first_name = 'Rylan' AND T4.staff_last_name = 'Homenick' )
human
Find the ids of the problems that are reported by the staff whose last name is Bosco.
assistant
SELECT T1.problem_id FROM problems AS T1 JOIN staff AS T2 ON T1.reported_by_staff_id = T2.staff_id WHERE T2.staff_last_name = 'Bosco'
human
For each product that has problems, find the number of problems reported after 1986-11-13 and the product id?
assistant
SELECT count(*) , T2.product_id FROM problems AS T1 JOIN product AS T2 ON T1.product_id = T2.product_id WHERE T1.date_problem_reported > '1986-11-13' GROUP BY T2.product_id
human
What are the ids of the problems which are reported before 1978-06-26?
assistant
SELECT problem_id FROM problems WHERE date_problem_reported < '1978-06-26'
human
Find the ids of the problems reported after 1978-06-26.
assistant
SELECT problem_id FROM problems WHERE date_problem_reported > "1978-06-26" ;