Using groups and set functions in Relational Algebra queries

Query 1:
For each department, list the department name and the highest salary paid to an employee in that department
Relations needed:
Employee (salary, fname, lname, dno)

Department (dname, dnumber)
Query plan:
Combine the employee and department information

Form groups based on common dname value

Find the maximum salary value in each group

Using groups and set functions in Relational Algebra queries

Query 1:
For each department, list the department name and the highest salary paid to an employee in that department
Sample solution: (with sample input to make query concrete)

Using groups and set functions in Relational Algebra queries

Query 1:
For each department, list the department name and the highest salary paid to an employee in that department
Step 1: combine (= join) the input relations:

Using groups and set functions in Relational Algebra queries

Query 1:
For each department, list the department name and the highest salary paid to an employee in that department
Step 2: Form groups with common dname and find max salary in each group:

Query 2: Find fname and lname of all employees who work on more projects than 'John Smith'

Relations needed:
Works_on ((essn, pno) --- projects worked on by employee)
Employee (ssn, fname, lname)

Query plan:

Query 2: Find fname and lname of all employees who work on more projects than 'John Smith'

Query 2: Find fname and lname of all employees who work on more projects than 'John Smith'

Step 2: Find the number of projects worked on by each employee:

Query 2: Find fname and lname of all employees who work on more projects than 'John Smith'