Database: cre_Doc_Tracking_DB
Schema:
CREATE TABLE Ref_Document_Types (
Document_Type_Code CHAR(15) NOT NULL,
Document_Type_Name VARCHAR(255) NOT NULL,
Document_Type_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Document_Type_Code)
)

CREATE TABLE Ref_Calendar (
Calendar_Date DATETIME NOT NULL,
Day_Number INTEGER,
PRIMARY KEY (Calendar_Date)
)

CREATE TABLE Ref_Locations (
Location_Code CHAR(15) NOT NULL,
Location_Name VARCHAR(255) NOT NULL,
Location_Description VARCHAR(255) NOT NULL,
PRIMARY KEY (Location_Code)
)

CREATE TABLE Roles (
Role_Code CHAR(15) NOT NULL,
Role_Name VARCHAR(255),
Role_Description VARCHAR(255),
PRIMARY KEY (Role_Code)
)

CREATE TABLE All_Documents (
Document_ID INTEGER NOT NULL,
Date_Stored DATETIME,
Document_Type_Code CHAR(15) NOT NULL,
Document_Name CHAR(255),
Document_Description CHAR(255),
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Document_Type_Code) REFERENCES Ref_Document_Types (Document_Type_Code),
FOREIGN KEY (Date_Stored) REFERENCES Ref_Calendar (Calendar_Date)
)

CREATE TABLE Employees (
Employee_ID INTEGER NOT NULL,
Role_Code CHAR(15) NOT NULL,
Employee_Name VARCHAR(255),
Gender_MFU CHAR(1) NOT NULL,
Date_of_Birth DATETIME NOT NULL,
Other_Details VARCHAR(255),
PRIMARY KEY (Employee_ID),
FOREIGN KEY (Role_Code) REFERENCES Roles (Role_Code)
)

CREATE TABLE Document_Locations (
Document_ID INTEGER NOT NULL,
Location_Code CHAR(15) NOT NULL,
Date_in_Location_From DATETIME NOT NULL,
Date_in_Locaton_To DATETIME,
PRIMARY KEY (Document_ID, Location_Code, Date_in_Location_From),
FOREIGN KEY (Location_Code) REFERENCES Ref_Locations (Location_Code),
FOREIGN KEY (Date_in_Location_From) REFERENCES Ref_Calendar (Calendar_Date),
FOREIGN KEY (Date_in_Locaton_To) REFERENCES Ref_Calendar (Calendar_Date),
FOREIGN KEY (Document_ID) REFERENCES All_Documents (Document_ID)
)

CREATE TABLE Documents_to_be_Destroyed (
Document_ID INTEGER NOT NULL,
Destruction_Authorised_by_Employee_ID INTEGER,
Destroyed_by_Employee_ID INTEGER,
Planned_Destruction_Date DATETIME,
Actual_Destruction_Date DATETIME,
Other_Details VARCHAR(255),
PRIMARY KEY (Document_ID),
FOREIGN KEY (Destroyed_by_Employee_ID) REFERENCES Employees (Employee_ID),
FOREIGN KEY (Destruction_Authorised_by_Employee_ID) REFERENCES Employees (Employee_ID),
FOREIGN KEY (Planned_Destruction_Date) REFERENCES Ref_Calendar (Calendar_Date),
FOREIGN KEY (Actual_Destruction_Date) REFERENCES Ref_Calendar (Calendar_Date),
FOREIGN KEY (Document_ID) REFERENCES All_Documents (Document_ID)
)

Question: How many calendar items do we have?
SQL: SELECT count(*) FROM Ref_calendar

Question: Count the number of all the calendar items.
SQL: SELECT count(*) FROM Ref_calendar

Question: Show all calendar dates and day Numbers.
SQL: SELECT calendar_date ,  day_Number FROM Ref_calendar

Question: What are all the calendar dates and day Numbers?
SQL: SELECT calendar_date ,  day_Number FROM Ref_calendar

Question: Show the number of document types.
SQL: SELECT count(*) FROM Ref_document_types

Question: How many document types are there?
SQL: SELECT count(*) FROM Ref_document_types

Question: List all document type codes and document type names.
SQL: SELECT document_type_code ,  document_type_name FROM Ref_document_types

Question: What are all the document type codes and document type names?
SQL: SELECT document_type_code ,  document_type_name FROM Ref_document_types

Question: What is the name and description for document type code RV?
SQL: SELECT document_type_name ,  document_type_description FROM Ref_document_types WHERE document_type_code  =  "RV"

Question: Give me the name and description of the document type code RV.
SQL: SELECT document_type_name ,  document_type_description FROM Ref_document_types WHERE document_type_code  =  "RV"

Question: What is the document type code for document type "Paper"?
SQL: SELECT document_type_code FROM Ref_document_types WHERE document_type_name  =  "Paper"

Question: Find the code of the document type "Paper".
SQL: SELECT document_type_code FROM Ref_document_types WHERE document_type_name  =  "Paper"

Question: Show the number of documents with document type code CV or BK.
SQL: SELECT count(*) FROM All_documents WHERE document_type_code  =  "CV" OR document_type_code  =  "BK"

Question: How many documents have document type code CV or BK?
SQL: SELECT count(*) FROM All_documents WHERE document_type_code  =  "CV" OR document_type_code  =  "BK"

Question: What is the date when the document "Marry CV" was stored?
SQL: SELECT date_stored FROM All_documents WHERE Document_name  =  "Marry CV"

Question: When was the document named "Marry CV" stored? Give me the date.
SQL: SELECT date_stored FROM All_documents WHERE Document_name  =  "Marry CV"

Question: What is the day Number and date of all the documents?
SQL: SELECT T2.day_Number ,  T1.Date_Stored FROM All_documents AS T1 JOIN Ref_calendar AS T2 ON T1.date_stored  =  T2.calendar_date

Question: Return the day Number and stored date for all the documents.
SQL: SELECT T2.day_Number ,  T1.Date_Stored FROM All_documents AS T1 JOIN Ref_calendar AS T2 ON T1.date_stored  =  T2.calendar_date

Question: What is the document type name for the document with name "How to read a book"?
SQL: SELECT T2.document_type_name FROM All_documents AS T1 JOIN Ref_document_types AS T2 ON T1.document_type_code  =  T2.document_type_code WHERE T1.document_name  =  "How to read a book"

Question: Find the document type name of the document named "How to read a book".
SQL: SELECT T2.document_type_name FROM All_documents AS T1 JOIN Ref_document_types AS T2 ON T1.document_type_code  =  T2.document_type_code WHERE T1.document_name  =  "How to read a book"

Question: Show the number of locations.
SQL: SELECT count(*) FROM Ref_locations

Question: How many locations are listed in the database?
SQL: SELECT count(*) FROM Ref_locations

Question: List all location codes and location names.
SQL: SELECT location_code ,  location_name FROM Ref_locations

Question: What are all the location codes and location names?
SQL: SELECT location_code ,  location_name FROM Ref_locations

Question: What are the name and description for location code x?
SQL: SELECT location_name ,  location_description FROM Ref_locations WHERE location_code  =  "x"

Question: Give me the name and description of the location with code x.
SQL: SELECT location_name ,  location_description FROM Ref_locations WHERE location_code  =  "x"

Question: What is the location code for the country "Canada"?
SQL: SELECT location_code FROM Ref_locations WHERE location_name  =  "Canada"

Question: Show the location code of the country "Canada".
SQL: SELECT location_code FROM Ref_locations WHERE location_name  =  "Canada"

Question: How many roles are there?
SQL: SELECT count(*) FROM ROLES

Question: Count the total number of roles listed.
SQL: SELECT count(*) FROM ROLES

Question: List all role codes, role names, and role descriptions.
SQL: SELECT role_code ,  role_name ,  role_description FROM ROLES

Question: What are all the role codes, role names, and role descriptions?
SQL: SELECT role_code ,  role_name ,  role_description FROM ROLES

Question: What are the name and description for role code "MG"?
SQL: SELECT role_name ,  role_description FROM ROLES WHERE role_code  =  "MG"

Question: Find the name and description of the role with code "MG".
SQL: SELECT role_name ,  role_description FROM ROLES WHERE role_code  =  "MG"

Question: Show the description for role name "Proof Reader".
SQL: SELECT role_description FROM ROLES WHERE role_name  =  "Proof Reader"

Question: What is the description of the role named "Proof Reader"?
SQL: SELECT role_description FROM ROLES WHERE role_name  =  "Proof Reader"

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

Question: Find the number of employees we have.
SQL: SELECT count(*) FROM Employees

Question: Show the name, role code, and date of birth for the employee with name 'Armani'.
SQL: SELECT employee_name ,  role_code ,  date_of_birth FROM Employees WHERE employee_Name  =  'Armani'

Question: What are the name, role code, and date of birth of the employee named 'Armani'?
SQL: SELECT employee_name ,  role_code ,  date_of_birth FROM Employees WHERE employee_Name  =  'Armani'

Question: What is the id for the employee called Ebba?
SQL: SELECT employee_ID FROM Employees WHERE employee_name  =  "Ebba"

Question: Show the id of the employee named Ebba.
SQL: SELECT employee_ID FROM Employees WHERE employee_name  =  "Ebba"

Question: Show the names of all the employees with role "HR".
SQL: SELECT employee_name FROM Employees WHERE role_code  =  "HR"

Question: Which employees have the role with code "HR"? Find their names.
SQL: SELECT employee_name FROM Employees WHERE role_code  =  "HR"

Question: Show all role codes and the number of employees in each role.
SQL: SELECT role_code ,  count(*) FROM Employees GROUP BY role_code

Question: What is the code of each role and the number of employees in each role?
SQL: SELECT role_code ,  count(*) FROM Employees GROUP BY role_code

Question: What is the role code with the largest number of employees?
SQL: SELECT role_code FROM Employees GROUP BY role_code ORDER BY count(*) DESC LIMIT 1

Question: Find the code of the role that have the most employees.
SQL: SELECT role_code FROM Employees GROUP BY role_code ORDER BY count(*) DESC LIMIT 1

Question: Show all role codes with at least 3 employees.
SQL: SELECT role_code FROM Employees GROUP BY role_code HAVING count(*)  >=  3

Question: What are the roles with three or more employees? Give me the role codes.
SQL: SELECT role_code FROM Employees GROUP BY role_code HAVING count(*)  >=  3

Question: Show the role code with the least employees.
SQL: SELECT role_code FROM Employees GROUP BY role_code ORDER BY count(*) ASC LIMIT 1

Question: What is the role with the smallest number of employees? Find the role codes.
SQL: SELECT role_code FROM Employees GROUP BY role_code ORDER BY count(*) ASC LIMIT 1

Question: What is the role name and role description for employee called Ebba?
SQL: SELECT T2.role_name ,  T2.role_description FROM Employees AS T1 JOIN ROLES AS T2 ON T1.role_code  =  T2.role_code WHERE T1.employee_name  =  "Ebba"

Question: Show the name and description of the role played by the employee named Ebba.
SQL: SELECT T2.role_name ,  T2.role_description FROM Employees AS T1 JOIN ROLES AS T2 ON T1.role_code  =  T2.role_code WHERE T1.employee_name  =  "Ebba"

Question: Show the names of employees with role name Editor.
SQL: SELECT T1.employee_name FROM Employees AS T1 JOIN ROLES AS T2 ON T1.role_code  =  T2.role_code WHERE T2.role_name  =  "Editor"

Question: Find the names of all the employees whose the role name is "Editor".
SQL: SELECT T1.employee_name FROM Employees AS T1 JOIN ROLES AS T2 ON T1.role_code  =  T2.role_code WHERE T2.role_name  =  "Editor"

Question: Show the employee ids for all employees with role name "Human Resource" or "Manager".
SQL: SELECT T1.employee_id FROM Employees AS T1 JOIN ROLES AS T2 ON T1.role_code  =  T2.role_code WHERE T2.role_name  =  "Human Resource" OR T2.role_name  =  "Manager"

Question: What are the employee ids of the employees whose role name is "Human Resource" or "Manager"?
SQL: SELECT T1.employee_id FROM Employees AS T1 JOIN ROLES AS T2 ON T1.role_code  =  T2.role_code WHERE T2.role_name  =  "Human Resource" OR T2.role_name  =  "Manager"

Question: What are the different location codes for documents?
SQL: SELECT DISTINCT location_code FROM Document_locations

Question: Give me all the distinct location codes for documents.
SQL: SELECT DISTINCT location_code FROM Document_locations

Question: Show the location name for document "Robin CV".
SQL: SELECT T3.location_name FROM All_documents AS T1 JOIN Document_locations AS T2 ON T1.document_id  =  T2.document_id JOIN Ref_locations AS T3 ON T2.location_code  =  T3.location_code WHERE T1.document_name  =  "Robin CV"

Question: What is the location name of the document "Robin CV"?
SQL: SELECT T3.location_name FROM All_documents AS T1 JOIN Document_locations AS T2 ON T1.document_id  =  T2.document_id JOIN Ref_locations AS T3 ON T2.location_code  =  T3.location_code WHERE T1.document_name  =  "Robin CV"

Question: Show the location code, the starting date and ending data in that location for all the documents.
SQL: SELECT location_code ,  date_in_location_from ,  date_in_locaton_to FROM Document_locations

Question: What are each document's location code, and starting date and ending data in that location?
SQL: SELECT location_code ,  date_in_location_from ,  date_in_locaton_to FROM Document_locations

Question: What is "the date in location from" and "the date in location to" for the document with name "Robin CV"?
SQL: SELECT T1.date_in_location_from ,  T1.date_in_locaton_to FROM Document_locations AS T1 JOIN All_documents AS T2 ON T1.document_id  =  T2.document_id WHERE T2.document_name  =  "Robin CV"

Question: Find the starting date and ending data in location for the document named "Robin CV".
SQL: SELECT T1.date_in_location_from ,  T1.date_in_locaton_to FROM Document_locations AS T1 JOIN All_documents AS T2 ON T1.document_id  =  T2.document_id WHERE T2.document_name  =  "Robin CV"

Question: Show the location codes and the number of documents in each location.
SQL: SELECT location_code ,  count(*) FROM Document_locations GROUP BY location_code

Question: What is the code of each location and the number of documents in that location?
SQL: SELECT location_code ,  count(*) FROM Document_locations GROUP BY location_code

Question: What is the location code with the most documents?
SQL: SELECT location_code FROM Document_locations GROUP BY location_code ORDER BY count(*) DESC LIMIT 1

Question: Find the code of the location with the largest number of documents.
SQL: SELECT location_code FROM Document_locations GROUP BY location_code ORDER BY count(*) DESC LIMIT 1

Question: Show the location codes with at least 3 documents.
SQL: SELECT location_code FROM Document_locations GROUP BY location_code HAVING count(*)  >=  3

Question: What are the codes of the locations with at least three documents?
SQL: SELECT location_code FROM Document_locations GROUP BY location_code HAVING count(*)  >=  3

Question: Show the location name and code with the least documents.
SQL: SELECT T2.location_name ,  T1.location_code FROM Document_locations AS T1 JOIN Ref_locations AS T2 ON T1.location_code  =  T2.location_code GROUP BY T1.location_code ORDER BY count(*) ASC LIMIT 1

Question: What are the name and code of the location with the smallest number of documents?
SQL: SELECT T2.location_name ,  T1.location_code FROM Document_locations AS T1 JOIN Ref_locations AS T2 ON T1.location_code  =  T2.location_code GROUP BY T1.location_code ORDER BY count(*) ASC LIMIT 1

Question: What are the names of the employees who authorised the destruction and the employees who destroyed the corresponding documents?
SQL: SELECT T2.employee_name ,  T3.employee_name FROM Documents_to_be_destroyed AS T1 JOIN Employees AS T2 ON T1.Destruction_Authorised_by_Employee_ID = T2.employee_id JOIN Employees AS T3 ON T1.Destroyed_by_Employee_ID = T3.employee_id;

Question: List the names of the employees who authorized the destruction of documents and the employees who destroyed the corresponding documents.
SQL: SELECT T2.employee_name ,  T3.employee_name FROM Documents_to_be_destroyed AS T1 JOIN Employees AS T2 ON T1.Destruction_Authorised_by_Employee_ID = T2.employee_id JOIN Employees AS T3 ON T1.Destroyed_by_Employee_ID = T3.employee_id;

Question: Show the id of each employee and the number of document destruction authorised by that employee.
SQL: SELECT Destruction_Authorised_by_Employee_ID ,  count(*) FROM Documents_to_be_destroyed GROUP BY Destruction_Authorised_by_Employee_ID

Question: What are the id of each employee and the number of document destruction authorised by that employee?
SQL: SELECT Destruction_Authorised_by_Employee_ID ,  count(*) FROM Documents_to_be_destroyed GROUP BY Destruction_Authorised_by_Employee_ID

Question: Show the employee ids and the number of documents destroyed by each employee.
SQL: SELECT Destroyed_by_Employee_ID ,  count(*) FROM Documents_to_be_destroyed GROUP BY Destroyed_by_Employee_ID

Question: What are the id of each employee and the number of document destroyed by that employee?
SQL: SELECT Destroyed_by_Employee_ID ,  count(*) FROM Documents_to_be_destroyed GROUP BY Destroyed_by_Employee_ID

Question: Show the ids of the employees who don't authorize destruction for any document.
SQL: SELECT employee_id FROM Employees EXCEPT SELECT Destruction_Authorised_by_Employee_ID FROM Documents_to_be_destroyed

Question: Which employees do not authorize destruction for any document? Give me their employee ids.
SQL: SELECT employee_id FROM Employees EXCEPT SELECT Destruction_Authorised_by_Employee_ID FROM Documents_to_be_destroyed

Question: Show the ids of all employees who have authorized destruction.
SQL: SELECT DISTINCT Destruction_Authorised_by_Employee_ID FROM Documents_to_be_destroyed

Question: What are the ids of all the employees who authorize document destruction?
SQL: SELECT DISTINCT Destruction_Authorised_by_Employee_ID FROM Documents_to_be_destroyed

Question: Show the ids of all employees who have destroyed a document.
SQL: SELECT DISTINCT Destroyed_by_Employee_ID FROM Documents_to_be_destroyed

Question: What are the ids of all the employees who have destroyed documents?
SQL: SELECT DISTINCT Destroyed_by_Employee_ID FROM Documents_to_be_destroyed

Question: Show the ids of all employees who don't destroy any document.
SQL: SELECT employee_id FROM Employees EXCEPT SELECT Destroyed_by_Employee_ID FROM Documents_to_be_destroyed

Question: Which employees do not destroy any document? Find their employee ids.
SQL: SELECT employee_id FROM Employees EXCEPT SELECT Destroyed_by_Employee_ID FROM Documents_to_be_destroyed

Question: Show the ids of all employees who have either destroyed a document or made an authorization to do this.
SQL: SELECT Destroyed_by_Employee_ID FROM Documents_to_be_destroyed UNION SELECT Destruction_Authorised_by_Employee_ID FROM Documents_to_be_destroyed

Question: Which employees have either destroyed a document or made an authorization to do so? Return their employee ids.
SQL: SELECT Destroyed_by_Employee_ID FROM Documents_to_be_destroyed UNION SELECT Destruction_Authorised_by_Employee_ID FROM Documents_to_be_destroyed