
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 "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")
)


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 "Ref_Calendar" (
	"Calendar_Date" DATETIME NOT NULL, 
	"Day_Number" INTEGER, 
	PRIMARY KEY ("Calendar_Date")
)


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_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")
)

/*
2 rows from All_Documents table:
Document_ID	Date_Stored	Document_Type_Code	Document_Name	Document_Description	Other_Details
7	1976-06-15 03:40:06	CV	Robin CV	None	None
11	1986-10-14 17:53:39	CV	Marry CV	None	None
*/


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")
)

/*
2 rows from Document_Locations table:
Document_ID	Location_Code	Date_in_Location_From	Date_in_Locaton_To
7	e	2017-01-06 23:17:22	2008-06-08 12:45:38
11	x	2017-01-06 23:17:22	2012-07-03 09:48:46
*/


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")
)

/*
2 rows from Documents_to_be_Destroyed table:
Document_ID	Destruction_Authorised_by_Employee_ID	Destroyed_by_Employee_ID	Planned_Destruction_Date	Actual_Destruction_Date	Other_Details
7	156	138	1988-02-01 14:41:52	2017-01-06 23:17:22	None
11	55	173	2010-11-26 19:22:50	1986-10-14 17:53:39	None
*/


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")
)

/*
2 rows from Employees table:
Employee_ID	Role_Code	Employee_Name	Gender_MFU	Date_of_Birth	Other_Details
25	HR	Leo		1973-02-15 17:16:00	None
30	MG	Ebba		1979-09-20 12:50:15	None
*/


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

/*
2 rows from Ref_Calendar table:
Calendar_Date	Day_Number
1972-03-31 09:47:22	5
1976-06-15 03:40:06	7
*/


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")
)

/*
2 rows from Ref_Document_Types table:
Document_Type_Code	Document_Type_Name	Document_Type_Description
CV	CV	
BK	Book	
*/


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")
)

/*
2 rows from Ref_Locations table:
Location_Code	Location_Name	Location_Description
b	Brazil	
c	Canada	
*/


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

/*
2 rows from Roles table:
Role_Code	Role_Name	Role_Description
MG	Manager	Vero harum corrupti odit ipsa vero et odio. Iste et recusandae temporibus maxime. Magni aspernatur f
ED	Editor	Itaque dolor ut nemo rerum vitae provident. Vel laborum ipsum velit sint. Et est omnis dignissimos.
*/
Show the id of each employee and the number of document destruction authorised by that employee.
SELECT Destruction_Authorised_by_Employee_ID ,  count(*) FROM Documents_to_be_destroyed GROUP BY Destruction_Authorised_by_Employee_ID
[{'Destruction_Authorised_by_Employee_ID': 30, 'count(*)': 1}, {'Destruction_Authorised_by_Employee_ID': 38, 'count(*)': 1}, {'Destruction_Authorised_by_Employee_ID': 55, 'count(*)': 3}, {'Destruction_Authorised_by_Employee_ID': 123, 'count(*)': 1}, {'Destruction_Authorised_by_Employee_ID': 156, 'count(*)': 2}, {'Destruction_Authorised_by_Employee_ID': 183, 'count(*)': 3}]