PRAGMA foreign_keys = ON;

PRAGMA writable_schema = 1;
DELETE FROM sqlite_master WHERE type IN ("table", "index", "trigger");
PRAGMA writable_schema = 0;
VACUUM;

CREATE TABLE "employee" (
"Employee_ID" INT UNIQUE,
"Age" INT,
"City" TEXT,
PRIMARY KEY ("Employee_ID")
);

CREATE TABLE "hiring" (
"Shop_ID" INT,
"Employee_ID" INT,
"Start_from" TEXT,
PRIMARY KEY ("Employee_ID"),
FOREIGN KEY ("Shop_ID") REFERENCES "shop"("Shop_ID")
);

CREATE TABLE "shop" (
"Shop_ID" INT,
"Location" TEXT,
PRIMARY KEY ("Shop_ID")
);





INSERT INTO "employee" VALUES ("1", "25", "New York");
INSERT INTO "employee" VALUES ("2", "29", "New York");
INSERT INTO "employee" VALUES ("3", "22", "Los Angeles");
INSERT INTO "employee" VALUES ("4", "28", "Los Angeles");
INSERT INTO "employee" VALUES ("5", "35", "Chicago");
INSERT INTO "employee" VALUES ("6", "40", "Chicago");
INSERT INTO "employee" VALUES ("7", "27", "Houston");
INSERT INTO "employee" VALUES ("8", "31", "Houston");
INSERT INTO "employee" VALUES ("9", "24", "Phoenix");
INSERT INTO "employee" VALUES ("10", "26", "Phoenix");
INSERT INTO "employee" VALUES ("11", "29", "Philadelphia");
INSERT INTO "employee" VALUES ("12", "33", "Philadelphia");
INSERT INTO "employee" VALUES ("13", "21", "San Antonio");
INSERT INTO "employee" VALUES ("14", "22", "San Antonio");
INSERT INTO "employee" VALUES ("15", "45", "San Diego");
INSERT INTO "shop" VALUES ("101", "Downtown");
INSERT INTO "shop" VALUES ("102", "Uptown");
INSERT INTO "shop" VALUES ("103", "Midtown");
INSERT INTO "shop" VALUES ("104", "Suburb");
INSERT INTO "shop" VALUES ("105", "Industrial Area");
INSERT INTO "hiring" VALUES ("101", "1", "2023-01-15");
INSERT INTO "hiring" VALUES ("101", "2", "2023-02-20");
INSERT INTO "hiring" VALUES ("102", "3", "2023-03-10");
INSERT INTO "hiring" VALUES ("102", "4", "2023-04-05");
INSERT INTO "hiring" VALUES ("103", "5", "2022-12-01");
INSERT INTO "hiring" VALUES ("103", "6", "2022-11-15");
INSERT INTO "hiring" VALUES ("104", "7", "2023-05-01");
INSERT INTO "hiring" VALUES ("105", "8", "2023-06-01");
INSERT INTO "hiring" VALUES ("104", "9", "2023-07-01");
INSERT INTO "hiring" VALUES ("105", "10", "2023-08-01");
INSERT INTO "hiring" VALUES ("101", "11", "2023-09-01");
INSERT INTO "hiring" VALUES ("102", "12", "2023-10-01");
INSERT INTO "hiring" VALUES ("103", "13", "2023-11-01");
INSERT INTO "hiring" VALUES ("104", "14", "2023-12-01");
INSERT INTO "hiring" VALUES ("105", "15", "2024-01-01");