human
Schema:
Services (Service_ID, Service_Type_Code)
Participants (Participant_ID, Participant_Type_Code, Participant_Details)
Events (Event_ID, Service_ID, Event_Details)
Participants_in_Events (Event_ID, Participant_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
What are all the distinct participant ids who attended any events?
assistant
SELECT count(DISTINCT participant_id) FROM participants_in_Events
human
How many events have each participants attended? List the participant id, type and the number.
assistant
SELECT T1.Participant_ID , T1.Participant_Type_Code , count(*) FROM Participants AS T1 JOIN Participants_in_Events AS T2 ON T1.Participant_ID = T2.Participant_ID GROUP BY T1.Participant_ID
human
What are the ids and details of events that have more than one participants?
assistant
SELECT T1.event_id , T1.event_details FROM EVENTS AS T1 JOIN Participants_in_Events AS T2 ON T1.Event_ID = T2.Event_ID GROUP BY T1.Event_ID HAVING count(*) > 1
human
what are the event details of the services that have the type code 'Marriage'?
assistant
SELECT T1.event_details FROM EVENTS AS T1 JOIN Services AS T2 ON T1.Service_ID = T2.Service_ID WHERE T2.Service_Type_Code = 'Marriage'
human
Which services type had both successful and failure event details?
assistant
SELECT T1.service_type_code FROM services AS T1 JOIN EVENTS AS T2 ON T1.service_id = T2.service_id WHERE T2.event_details = 'Success' INTERSECT SELECT T1.service_type_code FROM services AS T1 JOIN EVENTS AS T2 ON T1.service_id = T2.service_id WHERE T2.event_details = 'Fail'
human
List the service id and details for the events.
assistant
SELECT service_id , event_details FROM EVENTS ;