Database: cre_Doc_Control_Systems

CREATE TABLE Ref_Document_Types (
document_type_code CHAR(15) NOT NULL,
document_type_description VARCHAR(255) NOT NULL,
PRIMARY KEY (document_type_code)
);
CREATE TABLE Roles (
role_code CHAR(15) NOT NULL,
role_description VARCHAR(255),
PRIMARY KEY (role_code)
);
CREATE TABLE Addresses (
address_id INTEGER NOT NULL,
address_details VARCHAR(255),
PRIMARY KEY (address_id)
);
CREATE TABLE Ref_Document_Status (
document_status_code CHAR(15) NOT NULL,
document_status_description VARCHAR(255) NOT NULL,
PRIMARY KEY (document_status_code)
);
CREATE TABLE Ref_Shipping_Agents (
shipping_agent_code CHAR(15) NOT NULL,
shipping_agent_name VARCHAR(255) NOT NULL,
shipping_agent_description VARCHAR(255) NOT NULL,
PRIMARY KEY (shipping_agent_code)
);
CREATE TABLE Documents (
document_id INTEGER NOT NULL,
document_status_code CHAR(15) NOT NULL,
document_type_code CHAR(15) NOT NULL,
shipping_agent_code CHAR(15),
receipt_date DATETIME,
receipt_number VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (document_id),
FOREIGN KEY (document_type_code) REFERENCES Ref_Document_Types (document_type_code),
FOREIGN KEY (document_status_code) REFERENCES Ref_Document_Status (document_status_code),
FOREIGN KEY (shipping_agent_code) REFERENCES Ref_Shipping_Agents (shipping_agent_code)
);
CREATE TABLE Employees (
employee_id INTEGER NOT NULL,
role_code CHAR(15) NOT NULL,
employee_name VARCHAR(255),
other_details VARCHAR(255),
PRIMARY KEY (employee_id),
FOREIGN KEY (role_code) REFERENCES Roles (role_code)
);
CREATE TABLE Document_Drafts (
document_id INTEGER NOT NULL,
draft_number INTEGER NOT NULL,
draft_details VARCHAR(255),
PRIMARY KEY (document_id, draft_number),
FOREIGN KEY (document_id) REFERENCES Documents (document_id)
);
CREATE TABLE Draft_Copies (
document_id INTEGER NOT NULL,
draft_number INTEGER NOT NULL,
copy_number INTEGER NOT NULL,
PRIMARY KEY (document_id, draft_number, copy_number),
FOREIGN KEY (document_id, draft_number) REFERENCES Document_Drafts (document_id,draft_number)
);
CREATE TABLE Circulation_History (
document_id INTEGER NOT NULL,
draft_number INTEGER NOT NULL,
copy_number INTEGER NOT NULL,
employee_id INTEGER NOT NULL,
PRIMARY KEY (document_id, draft_number, copy_number, employee_id),
FOREIGN KEY (document_id, draft_number, copy_number) REFERENCES Draft_Copies (document_id,draft_number,copy_number),
FOREIGN KEY (employee_id) REFERENCES Employees (employee_id)
);
CREATE TABLE Documents_Mailed (
document_id INTEGER NOT NULL,
mailed_to_address_id INTEGER NOT NULL,
mailing_date DATETIME,
PRIMARY KEY (document_id, mailed_to_address_id),
FOREIGN KEY (document_id) REFERENCES Documents (document_id),
FOREIGN KEY (mailed_to_address_id) REFERENCES Addresses (address_id)
);

Question: What document status codes do we have?
SQL: SELECT document_status_code FROM Ref_Document_Status;

Question: What is the description of document status code 'working'?
SQL: SELECT document_status_description FROM Ref_Document_Status WHERE document_status_code = "working";

Question: What document type codes do we have?
SQL: SELECT document_type_code FROM Ref_Document_Types;

Question: What is the description of document type 'Paper'?
SQL: SELECT document_type_description FROM Ref_Document_Types WHERE document_type_code = "Paper";

Question: What are the shipping agent names?
SQL: SELECT shipping_agent_name FROM Ref_Shipping_Agents;

Question: What is the shipping agent code of shipping agent UPS?
SQL: SELECT shipping_agent_code FROM Ref_Shipping_Agents WHERE shipping_agent_name = "UPS";

Question: What are all role codes?
SQL: SELECT role_code FROM ROLES;

Question: What is the description of role code ED?
SQL: SELECT role_description FROM ROLES WHERE role_code = "ED";

Question: How many employees do we have?
SQL: SELECT count(*) FROM Employees;

Question: What is the role of the employee named Koby?
SQL: SELECT T1.role_description FROM ROLES AS T1 JOIN Employees AS T2 ON T1.role_code = T2.role_code WHERE T2.employee_name = "Koby";

Question: List all document ids and receipt dates of documents.
SQL: SELECT document_id ,  receipt_date FROM Documents;

Question: How many employees does each role have? List role description, id and number of employees.
SQL: 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;

Question: List roles that have more than one employee. List the role description and number of employees.
SQL: 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;

Question: What is the document status description of the document with id 1?
SQL: 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;

Question: How many documents have the status code done?
SQL: SELECT count(*) FROM Documents WHERE document_status_code = "done";

Question: List the document type code for the document with the id 2.
SQL: SELECT document_type_code FROM Documents WHERE document_id = 2;

Question: List the document ids for any documents with the status code done and the type code paper.
SQL: SELECT document_id FROM Documents WHERE document_status_code = "done" AND document_type_code = "Paper";

Question: What is the name of the shipping agent of the document with id 2?
SQL: 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;

Question: How many documents were shipped by USPS?
SQL: 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";

Question: Which shipping agent shipped the most documents? List the shipping agent name and the number of documents.
SQL: 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;

Question: What is the receipt date of the document with id 3?
SQL: SELECT receipt_date FROM Documents WHERE document_id = 3;

Question: What address was the document with id 4 mailed to?
SQL: SELECT Addresses.address_details FROM Addresses JOIN Documents_Mailed ON Documents_Mailed.mailed_to_address_id = Addresses.address_id WHERE document_id = 4;

Question: What is the mail date of the document with id 7?
SQL: SELECT mailing_date FROM Documents_Mailed WHERE document_id = 7;

Question: List the document ids of documents with the status done and type Paper, which not shipped by the shipping agent named USPS.
SQL: SELECT document_id FROM Documents WHERE document_status_code  =  "done" AND document_type_code = "Paper" EXCEPT 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";

Question: List document id of documents status is done and document type is Paper and the document is shipped by shipping agent named USPS.
SQL: 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";

Question: What is draft detail of the document with id 7?
SQL: SELECT draft_details FROM Document_Drafts WHERE document_id = 7;

Question: How many draft copies does the document with id 2 have?
SQL: SELECT count(*) FROM Draft_Copies WHERE document_id = 2;

Question: Which document has the most draft copies? List its document id and number of draft copies.
SQL: SELECT document_id , count(copy_number) FROM Draft_Copies GROUP BY document_id ORDER BY count(copy_number) DESC LIMIT 1;

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;