QUESTION 16

Given the following relational schema, where primary keys are underlined and foreign keys are marked with #:

Employee (employeeID, empFName, empLName, empDateBirth, address, #DepartmentID)

Department (departmentID, departmentName, location)

Project (projectID, projectName, place, startYear, endYear, #DepartmentID)

Work-on (#employeeID, #projectID, nbHours)

a) Express the following queries in SQL:

i. Find the date of birth and the address of the employee whose first name is Ahmed and last name is Maashani.

ii. List the first names and addresses of the employees who are working in the department named Research , ordered by first names in descending order.

iii. Change to 650 the number of hours that is required to finish the project having the id P10.

iv. Add the following column to the table project: sponsor varchar2(50) .

v. Find the number of projects that are going to finish in 2021.

vi. Find the number of projects assigned to each employee along with maximum number of hours spent.

vii. For each department, display its name along with the number of employees in which they are working.

b) Create a view named employees_MuscatProject that contains all the details of employees who worked on the projects, which took place in Muscat and finished in 2020.