human
Schema:
Customers (customer_id, customer_details)
Properties (property_id, property_type_code, property_address, other_details)
Residents (resident_id, property_id, date_moved_in, date_moved_out, other_details)
Organizations (organization_id, parent_organization_id, organization_details)
Services (service_id, organization_id, service_type_code, service_details)
Residents_Services (resident_id, service_id, date_moved_in, property_id, date_requested, date_provided, other_details)
Things (thing_id, organization_id, Type_of_Thing_Code, service_type_code, service_details)
Customer_Events (Customer_Event_ID, customer_id, date_moved_in, property_id, resident_id, thing_id)
Customer_Event_Notes (Customer_Event_Note_ID, Customer_Event_ID, service_type_code, resident_id, property_id, date_moved_in)
Timed_Status_of_Things (thing_id, Date_and_Date, Status_of_Thing_Code)
Timed_Locations_of_Things (thing_id, Date_and_Time, Location_Code)
Please answer with SQL.
assistant
Sure, I understand.
human
What are the distinct id and type of the thing that has the status 'Close' or has a status record before the date '2017-06-19 02:59:21'
assistant
SELECT DISTINCT T2.thing_id , T2.Type_of_Thing_Code FROM Timed_Status_of_Things AS T1 JOIN Things AS T2 ON T1.thing_id = T2.thing_id WHERE T1.Status_of_Thing_Code = 'Close' OR T1.Date_and_Date < '2017-06-19 02:59:21'
human
How many residents does each property have? List property id and resident count.
assistant
SELECT T1.property_id , count(*) FROM properties AS T1 JOIN residents AS T2 ON T1.property_id = T2.property_id GROUP BY T1.property_id
human
How many services has each resident requested? List the resident id, details, and the count in descending order of the count.
assistant
SELECT T1.resident_id , T1.other_details , count(*) FROM Residents AS T1 JOIN Residents_Services AS T2 ON T1.resident_id = T2.resident_id GROUP BY T1.resident_id ORDER BY count(*) DESC
human
How many distinct locations have the things with service detail 'Unsatisfied' been located in?
assistant
SELECT count(DISTINCT T2.Location_Code) FROM Things AS T1 JOIN Timed_Locations_of_Things AS T2 ON T1.thing_id = T2.thing_id WHERE T1.service_details = 'Unsatisfied'
human
How many different status codes of things are there?
assistant
SELECT count(DISTINCT Status_of_Thing_Code) FROM Timed_Status_of_Things
human
What are the resident details containing the substring 'Miss'?
assistant
SELECT other_details FROM Residents WHERE other_details LIKE "%Miss%" ;