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
How many documents were shipped by USPS?
assistant
SELECT count(*) FROM Ref_Shipping_Agents JOIN Documents ON Documents.shipping_agent_code = Ref_Shipping_Agents.shipping_agent_code WHERE Ref_Shipping_Agents.shipping_agent_name = 'USPS';
human
List document id of documents status is done and document type is Paper and the document is shipped by shipping agent named USPS.
assistant
SELECT document_id FROM Documents WHERE document_status_code = 'done' AND document_type_code = 'Paper' INTERSECT SELECT document_id FROM Documents JOIN Ref_Shipping_Agents ON Documents.shipping_agent_code = Ref_Shipping_Agents.shipping_agent_code WHERE Ref_Shipping_Agents.shipping_agent_name = 'USPS';
human
Which shipping agent shipped the most documents? List the shipping agent name and the number of documents.
assistant
SELECT Ref_Shipping_Agents.shipping_agent_name , count(Documents.document_id) FROM Ref_Shipping_Agents JOIN Documents ON Documents.shipping_agent_code = Ref_Shipping_Agents.shipping_agent_code GROUP BY Ref_Shipping_Agents.shipping_agent_code ORDER BY count(Documents.document_id) DESC LIMIT 1;
human
What is the name of the shipping agent of the document with id 2?
assistant
SELECT Ref_Shipping_Agents.shipping_agent_name FROM Ref_Shipping_Agents JOIN Documents ON Documents.shipping_agent_code = Ref_Shipping_Agents.shipping_agent_code WHERE Documents.document_id = 2;
human
What are the shipping agent names?
assistant
SELECT shipping_agent_name FROM Ref_Shipping_Agents;
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" ;