diff options
| -rw-r--r-- | assignments/2/README.md | 21 | ||||
| -rw-r--r-- | assignments/2/run.sql | 62 |
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; |