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.

Now write queries in the relational algebra on the same schema to answer each of the following.

A) The numbers, names and departments of employees that are not the boss of any other
employees.

B) The numbers, names and departments of employees that have the highest saleries in their
respective departments.

Explanation / Answer

b)
SELECT DISTINCT ON (d.id)
d.id AS department_id, d.name AS department
,e.id AS employee_id, e.name AS employee, e.salary
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
ORDER BY d.id, e.salary DESC;

(or)

ELECT d.name AS department, e.employee, e.salary
FROM departments d
LEFT JOIN (SELECT name AS employee, salary, department_id ,rank() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnkFROM employees e)
e ON e.department_id = d.department_id AND e.rnk = 1;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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