CS457 Syllabus & Progress


Practicing queries in Relational Algebra

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

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





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

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





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





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

    Solution:

    Note: the following construct is illegal in Relational Algebra





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

    Solution:





  6. For each department, list the department name and the highest salary paid to an employee in that department

    Solution:





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





  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.

    Solution: