---

Practicing queries in Relational Algebra

---

1. Find fname and lname of employees who earn more than 'John Smith'

   Relations that contain the information to answer the query: Employee (fname, lname, salary) Sample data to help you formulate the query: fname lname salary ------ -------- --------- John Smith 40000.00 Frankl Wong 40000.00 Alicia Zelaya 25000.00 Jennif Wallace 43000.00 Ramesh Narayan 38000.00 Joyce English 25000.00 Ahmad Jabbar 25000.00 James Borg 55000.00 Query plan: Find John Smith and his salary Use John Smith's salary value to find qualifying employees

   Solution: (with a sample database to illustrate the steps)

   ---

   ---

   ---

   ---

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

   Relations needed: Employee (salary, fname, lname, ssn) Works_on ((essn, pno) --- projects worked on by employee)            Query plan: Find the number of projects worked on by John Smith Find the number of projects worked on by each employee Find SSN of employees who work on more projects than John Smith Find the fname and lname of these employees

   Solution:

   ---

   ---

   ---

   ---

3. Find fname and lname of employees who have 2 or more dependents.

   Relations needed: Employee Dependent            Query plan: Find the number of dependents for each employee Find the SSN of employees with ≥ 2 dependents Find the fname and lname of these employees

   Solution: (with a sample database to illsutrate the steps)

   ---

   ---

   ---

   ---

4. Find fname and lname of employees who have 2 or more dependents of the same sex
   • Query plan:

     We must count that number of members in groups organized by common values in Employee SSN (dependent belongs to him/her) Sex !!! Groups with ≥ 2 members form the solution sets

     Solution:

   ---

   ---

   ---

   ---

5. Find fname and lname of the employees who have the most number of dependents.

   Relations needed: Employee Dependent            Query plan: Find the number of dependents for each employee Find the maximum of the number of dependents over all employees Find the SSN of employee(s) who has the maximum dependents Find the fname and lname of these employees

   Solution:

   Note: the following construct is illegal in Relational Algebra

   H1 = essn ℱ count(dep_name) (dependent) // H1 = ( essn, #dependents-of-this-essn) H2 = ℱ max(count) (H1) // H2 = (max) Note that: H2 is a relation This is illegal: H3 = σ H1.count = H2 (H1) Because: H1.count is an attribute (i.e., a single value) H2 is a relation (i.e., a set of values) You can compare an attribute value against another attribute value or a constant - in both cases, they are one single value. You cannot compare an attribute with a relation.

   ---

   ---

   ---

   ---

6. Find fname and lname of the employee(s) in the Research department who earn the highest salary in the Research department.

   Relations needed: Employee (salary, fname, lname, dno) Department (dname, dnumber)            Query plan: Find the employees in the Research department Find the maximum of the salary of these employees Find the SSN of the employee who earn maximum salary. Find the fname and lname of these employees

   Solution:

   ---

   ---

   ---

   ---

7. 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

   Solution:

   ---

   ---

   ---

   ---

8. For each department, show the name (of the department), number of employees, minimum salary paid to some employee in the department and maximum salary paid to some employee in the department.

   Sample output: dname #Employees min(salary) max(salary) --------------- --------------------- ----------- ----------- Administration 3 25000.00 43000.00 Headquarters 1 55000.00 55000.00 Research 4 25000.00 40000.00 Relations needed: Department (dname, dnumber)            Employee (salary, fname, lname, dno) Query plan: Join the information in the Department and Employee tables Group the tuples by common dname value Count the members in each group Find the min(salary) in each group Find the max(salary) in each group

   Solution: