Consider the following SQL data de nition formaintaining information about emplo

Question

Consider the following SQL data de nition formaintaining information about employees at a hypothetical company.

CREATE TABLE emp
( num INTEGER NOT NULL,
name VARCHAR(20) NOT NULL,
dept VARCHAR(20) NOT NULL,
salary INTEGER NOT NULL,
boss INTEGER NOT NULL,
PRIMARY KEY (num),
FOREIGN KEY (boss) REFERENCES emp (num) );

You can assume that there is one president that has herself/himself as the boss, that all other
employees have a boss that is someone else and that there are no cycles in the boss hierarchy
for anyone other than the president. (A cycle would exist if, for example, Fred was the boss
of Mary and Mary was in turn the boss of Fred.) Translate each of the following queries on
this schema to SQL. In each case, also indicate if the query can be expressed in the relational
algebra.

A) The number and name of each employee, excluding the president, together with the
number and name of the employee's boss. The result should be sorted by the name of
the boss and then by the name of the employee.

B) The names of the departments with the highest average salary of their employees.

C) The numbers and names of employees who have the president as their boss, and that
have a salary among the lowest of those employees who do not have the president as
their boss.

Explanation / Answer

B). SELECT dept, avg(salary) as AVGSalary FROM emp e1 GROUP BY dept HAVING
AVGSalary > (select avg(salary) from emp e2 where e2.dept=e1.dept)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.