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: 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