diff options
| -rw-r--r-- | assignments/2/README.md | 11 | ||||
| -rw-r--r-- | assignments/2/run.sql | 22 |
2 files changed, 31 insertions, 2 deletions
diff --git a/assignments/2/README.md b/assignments/2/README.md index deaa85e..5d5e1ac 100644 --- a/assignments/2/README.md +++ b/assignments/2/README.md @@ -46,9 +46,16 @@ Answer the following questions (250 words max/question). Consider the following database: -Employee(emp-no, name, department, salary), ProjAssigned(emp-no, proj-no, worked-hours) +```text +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. * Define indexes on selected attributes to speed up your query, and justify your selections. * Write SQL queries to create the indexes you defined above. diff --git a/assignments/2/run.sql b/assignments/2/run.sql index e69de29..25101a8 100644 --- a/assignments/2/run.sql +++ b/assignments/2/run.sql @@ -0,0 +1,22 @@ +-- \echo '--- Question 4 ---' + +DROP TABLE IF EXISTS Employee; +DROP TABLE IF EXISTS ProjAssigned; + +CREATE TABLE Employee( + "emp-no" bigint primary key, + name varchar(255), + department varchar(255), + salary decimal +); + +CREATE TABLE ProjAssigned( + "emp-no" bigint, + "proj-no" bigint, + "worked-hours" integer +); + +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; |