COMP 378 Lecture 4: Comp 378ass2qs4
30 Jun 2018
School
Department
Course
Professor
Question 4 (18 marks)
Consider the following database:
Employee(emp-no, name, department, salary), ProjAssigned(emp-no, proj-no, worked-hours)
a. 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.
Answer:
SELECT e.emp-no, e.name, e.salary, p.proj-no, SUM(p.worked-hours) AS
total_workedhours
FROM Employee e, ProjAssigned p
WHERE e.emp-no=p.emp-no AND e.salary > 66000
GROUP BY e.emp-no, e.name, e.salary, p.proj-no
HAVING SUM(p.worked-hours) > 0
ORDER BY e.emp-no, e.name, e.salary, p.proj-no ;
Using an explicit join:
SELECT e.emp-no, e.name, e.salary, p.proj-no, SUM(p.worked-hours) AS
total_workedhours
FROM Employee e
JOIN ProjAssigned p ON e.emp-no = p.emp-no
WHERE e.salary > 66000
GROUP BY e.emp-no, e.name, e.salary, p.proj-no
HAVING SUM(p.worked-hours) > 0
ORDER BY e.emp-no, e.name, e.salary, p.proj-no ;
b. Define indexes on selected attributes to speed up your query, and justify your selections
Answer:
Emp-no column has been chosen to index by as all searching is generally conducted based on
emp-no field on the table and it also appears in the WHERE and GROUPBY
clauses. Also this is the only field which is distinct in this table and thus would be
easier to index by. Index name as it is used by order by and is very common way to
retrieve data
Emp-no and proj-no together has been chosen as index columns in the seconds table
because generally all the searching will be conducted in relation to emp-no and proj-no.
They both also appear in the WHERE clause.
Index salary as it is used in where clause
c. Write SQL queries to create the indexes you defined above.
Answer:
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
Your list should be sorted by employee name. Select e. emp-no, e. name, e. salary, p. proj-no, sum(p. worked-hours) as total_workedhours. Order by e. emp-no, e. name, e. salary, p. proj-no : define indexes on selected attributes to speed up your query, and justify your selections. Emp-no column has been chosen to index by as all searching is generally conducted based on emp-no field on the table and it also appears in the where and groupby clauses. Also this is the only field which is distinct in this table and thus would be easier to index by. Index name as it is used by order by and is very common way to retrieve data. Emp-no and proj-no together has been chosen as index columns in the seconds table because generally all the searching will be conducted in relation to emp-no and proj-no. They both also appear in the where clause. Index salary as it is used in where clause: write sql queries to create the indexes you defined above.
