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
What is the shipping agent code of shipping agent UPS?
assistant
SELECT shipping_agent_code FROM Ref_Shipping_Agents WHERE shipping_agent_name = 'UPS';
human
What document type codes do we have?
assistant
SELECT document_type_code FROM Ref_Document_Types;
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;
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
What are all role codes?
assistant
SELECT role_code FROM ROLES;
human
What is the description of role code ED?
assistant
SELECT role_description FROM ROLES WHERE role_code = "ED" ;