Write sql for assignment 2

2 files changed, 56 insertions, 27 deletions

diff --git a/assignments/2/README.md b/assignments/2/README.md
index abdc03e..fd6e92b 100644
--- a/assignments/2/README.md
+++ b/assignments/2/README.md
@@ -167,27 +167,30 @@ Employee(emp-no, name, department, salary)
 ProjAssigned(emp-no, proj-no, worked-hours)
 ```
 
-* Write one SELECT SQL query to list the numbers and names of all employees
-  with a salary greater than 66 000 who are assigned to projects,
-  the projects they are assigned to,
-  and the corresponding hours worked.
-  * Your list should be sorted by employee name.
+**Write one SELECT SQL query to list the numbers and names of all employees with a salary greater than 66 000 who are assigned to projects, 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
+WHERE e.salary > 66000.0
 ORDER BY e.name ASC;
 ```
 
-* Define indexes on selected attributes to speed up your query, and justify your selections.
+**Define indexes on selected attributes to speed up your query, and justify your selections.**
+
+* An index on the `emp-no` column on the `ProjAssigned` table will improve the join performance.
+* An index on the `salary` column will improve the filter performance.
+* An index on the `name` column will improve the order by performance.
+
+**Write SQL queries to create the indexes you defined above.**
 
 ```sql
+CREATE INDEX employees_idx ON ProjAssigned("emp-no");
+CREATE INDEX employee_salary_idx ON Employee(salary);
+CREATE INDEX employee_names_idx ON Employee(name ASC);
 ```
 
-* Write SQL queries to create the indexes you defined above.
-
 ## Question 5 (40 marks)
 
 Consider the following three relations:
diff --git a/assignments/2/run.sql b/assignments/2/run.sql
index 24f1317..ef50a67 100644
--- a/assignments/2/run.sql
+++ b/assignments/2/run.sql
@@ -1,8 +1,8 @@
 -- \echo '--- Question 4 ---'
+\set ON_ERROR_STOP on
 
-ALTER TABLE IF EXISTS ProjAssigned DROP CONSTRAINT employees_fk;
-DROP TABLE IF EXISTS Employee;
 DROP TABLE IF EXISTS ProjAssigned;
+DROP TABLE IF EXISTS Employee;
 
 CREATE TABLE Employee(
   "emp-no" bigint primary key,
@@ -12,31 +12,57 @@ CREATE TABLE Employee(
 );
 
 CREATE TABLE ProjAssigned(
-  "emp-no" bigint,
-  "proj-no" bigint,
+  "proj-no" bigint primary key,
+  "emp-no" bigint references Employee("emp-no"),
   "worked-hours" integer
 );
 
-INSERT INTO Employee VALUES (1, 'A', 'Ruby', 200000);
-INSERT INTO Employee VALUES (2, 'B', 'Golang', 300000);
-INSERT INTO Employee VALUES (3, 'C', 'C', 400000);
+INSERT INTO Employee VALUES (1, 'Tsuyoshi Garrett', 'X', 200000);
+INSERT INTO Employee VALUES (2, 'Peter Parker', 'X', 300000);
+INSERT INTO Employee VALUES (3, 'Diana Prince', 'X', 400000);
+INSERT INTO Employee VALUES (4, 'Tony Stark', 'X', 500000);
+
+INSERT INTO ProjAssigned VALUES (1, 3, 120);
+INSERT INTO ProjAssigned VALUES (2, 2, 40);
+INSERT INTO ProjAssigned VALUES (3, 1, 80);
+
+/*
+                                                        QUERY PLAN                                                    >
+---------------------------------------------------------------------------------------------------------------------->
+ Sort  (cost=68.22..69.62 rows=559 width=536) (actual time=0.021..0.021 rows=3 loops=1)
+   Sort Key: e.name
+   Sort Method: quicksort  Memory: 25kB
+   ->  Hash Join  (cost=11.16..42.71 rows=559 width=536) (actual time=0.015..0.016 rows=3 loops=1)
+         Hash Cond: (pa."emp-no" = e."emp-no")
+         ->  Seq Scan on projassigned pa  (cost=0.00..27.00 rows=1700 width=20) (actual time=0.003..0.003 rows=3 loops>
+         ->  Hash  (cost=10.88..10.88 rows=23 width=524) (actual time=0.010..0.010 rows=4 loops=1)
+               Buckets: 1024  Batches: 1  Memory Usage: 9kB
+               ->  Seq Scan on employee e  (cost=0.00..10.88 rows=23 width=524) (actual time=0.002..0.003 rows=4 loops>
+                     Filter: (salary > 66000.0)
+ Planning time: 0.053 ms
+ Execution time: 0.033 ms
+(12 rows)
+*/
 
-INSERT INTO ProjAssigned VALUES (1, 1, 40);
-INSERT INTO ProjAssigned VALUES (2, 2, 80);
+CREATE INDEX employees_idx ON ProjAssigned("emp-no");
+CREATE INDEX employee_salary_idx ON Employee(salary);
+CREATE INDEX employee_names_idx ON Employee(name ASC);
+-- CREATE INDEX employee_composite_idx ON Employee(salary, name ASC);
 
-EXPLAIN SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours"
+/*
+*/
+
+\d Employee;
+\d ProjAssigned;
+
+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
+WHERE e.salary > 66000.0
 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"
+EXPLAIN ANALYZE 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
+WHERE e.salary > 66000.0
 ORDER BY e.name ASC;