human
Schema:
Ref_Document_Types (document_type_code, document_type_description)
Roles (role_code, role_description)
Addresses (address_id, address_details)
Ref_Document_Status (document_status_code, document_status_description)
Ref_Shipping_Agents (shipping_agent_code, shipping_agent_name, shipping_agent_description)
Documents (document_id, document_status_code, document_type_code, shipping_agent_code, receipt_date, receipt_number, other_details)
Employees (employee_id, role_code, employee_name, other_details)
Document_Drafts (document_id, draft_number, draft_details)
Draft_Copies (document_id, draft_number, copy_number)
Circulation_History (document_id, draft_number, copy_number, employee_id)
Documents_Mailed (document_id, mailed_to_address_id, mailing_date)
Please answer with SQL.
assistant
Sure, I understand.
human
Which employee has showed up in most circulation history documents. List the employee's name and the number of drafts and copies.
assistant
SELECT Employees.employee_name , count(*) FROM Employees JOIN Circulation_History ON Circulation_History.employee_id = Employees.employee_id GROUP BY Circulation_History.document_id , Circulation_History.draft_number , Circulation_History.copy_number ORDER BY count(*) DESC LIMIT 1;
human
What are all role codes?
assistant
SELECT role_code FROM ROLES;
human
For each document, list the number of employees who have showed up in the circulation history of that document. List the document ids and number of employees.
assistant
SELECT document_id , count(DISTINCT employee_id) FROM Circulation_History GROUP BY document_id;
human
How many employees do we have?
assistant
SELECT count(*) FROM Employees;
human
List roles that have more than one employee. List the role description and number of employees.
assistant
SELECT Roles.role_description , count(Employees.employee_id) FROM ROLES JOIN Employees ON Employees.role_code = Roles.role_code GROUP BY Employees.role_code HAVING count(Employees.employee_id) > 1;
human
How many employees does each role have? List role description, id and number of employees.
assistant
SELECT T1.role_description , T2.role_code , COUNT( * ) FROM ROLES AS T1 JOIN Employees AS T2 ON T1.role_code = T2.role_code GROUP BY T2.role_code ;