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 document has the most draft copies? List its document id and number of draft copies.
assistant
SELECT document_id , count(copy_number) FROM Draft_Copies GROUP BY document_id ORDER BY count(copy_number) DESC LIMIT 1;
human
What is the mail date of the document with id 7?
assistant
SELECT mailing_date FROM Documents_Mailed WHERE document_id = 7;
human
Which documents have more than 1 draft copies? List document id and number of draft copies.
assistant
SELECT document_id , count(*) FROM Draft_Copies GROUP BY document_id HAVING count(*) > 1;
human
How many draft copies does the document with id 2 have?
assistant
SELECT count(*) FROM Draft_Copies WHERE document_id = 2;
human
What is the document status description of the document with id 1?
assistant
SELECT Ref_Document_Status.document_status_description FROM Ref_Document_Status JOIN Documents ON Documents.document_status_code = Ref_Document_Status.document_status_code WHERE Documents.document_id = 1;
human
What is draft detail of the document with id 7?
assistant
SELECT draft_details FROM Document_Drafts WHERE document_id = 7 ;