Add some indexes to see what produces the best explain

2 files changed, 26 insertions, 1 deletions

diff --git a/assignments/2/README.md b/assignments/2/README.md
index 5d5e1ac..9ddeda0 100644
--- a/assignments/2/README.md
+++ b/assignments/2/README.md
@@ -56,7 +56,20 @@ ProjAssigned(emp-no, proj-no, worked-hours)
   the projects they are assigned to,
   and the corresponding hours worked.
   * Your list should be sorted by employee name.
+
+```sql
+SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours"
+FROM Employee e
+INNER JOIN ProjAssigned pa ON pa."emp-no" = e."emp-no"
+WHERE e.salary > 66000
+ORDER BY e.name ASC;
+```
+
 * Define indexes on selected attributes to speed up your query, and justify your selections.
+
+```sql
+```
+
 * Write SQL queries to create the indexes you defined above.
 
 ## Question 5 (40 marks)
diff --git a/assignments/2/run.sql b/assignments/2/run.sql
index 09551d6..24f1317 100644
--- a/assignments/2/run.sql
+++ b/assignments/2/run.sql
@@ -1,5 +1,6 @@
 -- \echo '--- Question 4 ---'
 
+ALTER TABLE IF EXISTS ProjAssigned DROP CONSTRAINT employees_fk;
 DROP TABLE IF EXISTS Employee;
 DROP TABLE IF EXISTS ProjAssigned;
 
@@ -23,7 +24,18 @@ INSERT INTO Employee VALUES (3, 'C', 'C', 400000);
 INSERT INTO ProjAssigned VALUES (1, 1, 40);
 INSERT INTO ProjAssigned VALUES (2, 2, 80);
 
-SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours"
+EXPLAIN SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours"
+FROM Employee e
+INNER JOIN ProjAssigned pa ON pa."emp-no" = e."emp-no"
+WHERE e.salary > 66000
+ORDER BY e.name ASC;
+
+ALTER TABLE ProjAssigned ADD CONSTRAINT employees_fk FOREIGN KEY ("emp-no") REFERENCES Employee("emp-no");
+-- CREATE INDEX employee_names_idx ON Employee(name);
+-- CREATE INDEX employee_salary_idx ON Employee(salary);
+CREATE INDEX employee_salaries_idx ON Employee(name, salary);
+
+EXPLAIN SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours"
 FROM Employee e
 INNER JOIN ProjAssigned pa ON pa."emp-no" = e."emp-no"
 WHERE e.salary > 66000