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
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
List all document ids and receipt dates of documents.
assistant
SELECT document_id , receipt_date FROM Documents;
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
List the employees who have not showed up in any circulation history of documents. List the employee's name.
assistant
SELECT employee_name FROM Employees EXCEPT SELECT Employees.employee_name FROM Employees JOIN Circulation_History ON Circulation_History.employee_id = Employees.employee_id
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
List all employees in the circulation history of the document with id 1. List the employee's name.
assistant
SELECT Employees.employee_name FROM Employees JOIN Circulation_History ON Circulation_History.employee_id = Employees.employee_id WHERE Circulation_History.document_id = 1 ;