CREATE TABLE Ref_Document_Types (
    document_type_code TEXT PRIMARY KEY,
    document_type_description TEXT
);
CREATE TABLE Roles (
    role_code TEXT PRIMARY KEY,
    role_description TEXT
);
CREATE TABLE Addresses (
    address_id NUMBER PRIMARY KEY,
    address_details TEXT
);
CREATE TABLE Ref_Document_Status (
    document_status_code TEXT PRIMARY KEY,
    document_status_description TEXT
);
CREATE TABLE Ref_Shipping_Agents (
    shipping_agent_code TEXT PRIMARY KEY,
    shipping_agent_name TEXT,
    shipping_agent_description TEXT
);
CREATE TABLE Documents (
    document_id NUMBER PRIMARY KEY,
    document_status_code TEXT,
    document_type_code TEXT,
    shipping_agent_code TEXT,
    receipt_date TIME,
    receipt_number TEXT,
    other_details TEXT,
    FOREIGN KEY ("shipping_agent_code") REFERENCES Ref_Shipping_Agents("shipping_agent_code"),
    FOREIGN KEY ("document_status_code") REFERENCES Ref_Document_Status("document_status_code"),
    FOREIGN KEY ("document_type_code") REFERENCES Ref_Document_Types("document_type_code")
);
CREATE TABLE Employees (
    employee_id NUMBER PRIMARY KEY,
    role_code TEXT,
    employee_name TEXT,
    other_details TEXT,
    FOREIGN KEY ("role_code") REFERENCES Roles("role_code")
);
CREATE TABLE Document_Drafts (
    document_id NUMBER PRIMARY KEY,
    draft_number NUMBER,
    draft_details TEXT,
    FOREIGN KEY ("document_id") REFERENCES Documents("document_id")
);
CREATE TABLE Draft_Copies (
    document_id NUMBER PRIMARY KEY,
    draft_number NUMBER,
    copy_number NUMBER,
    FOREIGN KEY ("document_id") REFERENCES Document_Drafts("document_id"),
    FOREIGN KEY ("draft_number") REFERENCES Document_Drafts("draft_number")
);
CREATE TABLE Circulation_History (
    document_id NUMBER PRIMARY KEY,
    draft_number NUMBER,
    copy_number NUMBER,
    employee_id NUMBER,
    FOREIGN KEY ("employee_id") REFERENCES Employees("employee_id"),
    FOREIGN KEY ("document_id") REFERENCES Draft_Copies("document_id"),
    FOREIGN KEY ("draft_number") REFERENCES Draft_Copies("draft_number"),
    FOREIGN KEY ("copy_number") REFERENCES Draft_Copies("copy_number")
);
CREATE TABLE Documents_Mailed (
    document_id NUMBER PRIMARY KEY,
    mailed_to_address_id NUMBER,
    mailing_date TIME,
    FOREIGN KEY ("mailed_to_address_id") REFERENCES Addresses("address_id"),
    FOREIGN KEY ("document_id") REFERENCES Documents("document_id")
);

Question: Which documents have more than 1 draft copies? List document id and number of draft copies.
SQL: SELECT document_id , count(*) FROM Draft_Copies GROUP BY document_id HAVING count(*)  >  1;

Question: List all employees in the circulation history of the document with id 1. List the employee's name.
SQL: SELECT Employees.employee_name FROM Employees JOIN Circulation_History ON Circulation_History.employee_id = Employees.employee_id WHERE Circulation_History.document_id = 1;

Question: List the employees who have not showed up in any circulation history of documents. List the employee's name.
SQL: SELECT employee_name FROM Employees EXCEPT SELECT Employees.employee_name FROM Employees JOIN Circulation_History ON Circulation_History.employee_id = Employees.employee_id

Question: Which employee has showed up in most circulation history documents. List the employee's name and the number of drafts and copies.
SQL: 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;

Question: 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.
SQL: SELECT document_id ,  count(DISTINCT employee_id) FROM Circulation_History GROUP BY document_id;