CREATE TABLE Ref_Document_Types (
    Document_Type_Code TEXT PRIMARY KEY,
    Document_Type_Name TEXT,
    Document_Type_Description TEXT
);
CREATE TABLE Ref_Calendar (
    Calendar_Date TIME PRIMARY KEY,
    Day_Number NUMBER
);
CREATE TABLE Ref_Locations (
    Location_Code TEXT PRIMARY KEY,
    Location_Name TEXT,
    Location_Description TEXT
);
CREATE TABLE Roles (
    Role_Code TEXT PRIMARY KEY,
    Role_Name TEXT,
    Role_Description TEXT
);
CREATE TABLE All_Documents (
    Document_ID NUMBER PRIMARY KEY,
    Date_Stored TIME,
    Document_Type_Code TEXT,
    Document_Name TEXT,
    Document_Description TEXT,
    Other_Details TEXT,
    FOREIGN KEY ("Date_Stored") REFERENCES Ref_Calendar("Calendar_Date"),
    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,
    Gender_MFU TEXT,
    Date_of_Birth TIME,
    Other_Details TEXT,
    FOREIGN KEY ("Role_Code") REFERENCES Roles("Role_Code")
);
CREATE TABLE Document_Locations (
    Document_ID NUMBER PRIMARY KEY,
    Location_Code TEXT,
    Date_in_Location_From TIME,
    Date_in_Locaton_To TIME,
    FOREIGN KEY ("Document_ID") REFERENCES All_Documents("Document_ID"),
    FOREIGN KEY ("Date_in_Locaton_To") REFERENCES Ref_Calendar("Calendar_Date"),
    FOREIGN KEY ("Date_in_Location_From") REFERENCES Ref_Calendar("Calendar_Date"),
    FOREIGN KEY ("Location_Code") REFERENCES Ref_Locations("Location_Code")
);
CREATE TABLE Documents_to_be_Destroyed (
    Document_ID NUMBER PRIMARY KEY,
    Destruction_Authorised_by_Employee_ID NUMBER,
    Destroyed_by_Employee_ID NUMBER,
    Planned_Destruction_Date TIME,
    Actual_Destruction_Date TIME,
    Other_Details TEXT,
    FOREIGN KEY ("Document_ID") REFERENCES All_Documents("Document_ID"),
    FOREIGN KEY ("Actual_Destruction_Date") REFERENCES Ref_Calendar("Calendar_Date"),
    FOREIGN KEY ("Planned_Destruction_Date") REFERENCES Ref_Calendar("Calendar_Date"),
    FOREIGN KEY ("Destruction_Authorised_by_Employee_ID") REFERENCES Employees("Employee_ID"),
    FOREIGN KEY ("Destroyed_by_Employee_ID") REFERENCES Employees("Employee_ID")
);

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