Question

2) Consider the database schema on the relations (where key attributes have been underlined): PROJECTS(Number, Department, ProjectName) EMPLOYEES(Number, Surname, FirstName, Department) ALLOCATIONSEmplovee, Project, Function, Date) NOTE: relation ALLOCATIONS stores the registration number (attribute Employee) of employees that are assigned to a given project (attribute Project is the number of the project, not the name of the project), the function the employee has in that project (i.e., technician, manager, etc.) and the date when the employee has been assigned to that project. a) Write the SQL query that finds the project number of projects in the Administration Department. (3marks) (8marks) (14marks) b) Write the SQL query that finds the employee number, the employee surname and the name of projects they are assigned to. c Write the SQL query that retrieves all the following information for each employee number: the employee's surname and department - how many different projects he/she has been assigned to - how many different functions he/she has

Answer 1

a) Answer:

QUERY:

select Number,Department from PROJECTS where Department="Administration";

Explanation:

• In the above SQL query first the where clause is evaluated and it outputs the Number of the project belonging to the Administration Department.

b)Answer:

QUERY:

select Number,Surname,ProjectName from PROJECTS JOIN EMPLOYEES ON PROJECTS.Number=EMPLOYEES.Number;

Explanation:

• Here first we will perform the join operation on the first two tables based on the key Number (Project Number). Then Number,Surname, ProjectName is then retrieved from Joined table.

c)Answer:

QUERY:

select Surname,Department,count(Project), count(DISTINCT Function) from EMPLOYEES JOIN ALLOCATIONS ON EMPLOYEES.Number=ALLOCATIONS.Employee groupby EMPLOYEES.Number;

Explanation:

• Here first we join the EMPLOYEES AND ALLOCATIONS based on the Employee number.
• Then we will group the joined table using Employee number.
• After applting groupby clause , we will get groups like g1,g2,g3 .....gn.
• In each and every group count(Project) gives you the number of projects assigned to an employee and count(DISTINCT Function) gives you the count of different roles played by the employee in different projects.

Note: Please like the answer if you are satisfied with it. Thank you