Question: Let consider the given relation schema for a COMPANY database
Employee (Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
Department (Dname, Dnumber, Mgr_ssn, Mgr_start_date)
Dept_locations (Dnumber, Dlocation)
Project (Pname, Pnumber, Plocation, Dnum)
Works_on (Ssn, Pno, Hours)
Dependent (Ssn, Dependent_name, Sex, Bdate, Relationship)
Write down a query in the SQL for the given:
a) Determine the names of employees in department 5 who work more than 10 hrs per week on the project named 'ProductX'.
b) Determine the names of employees who encompass a dependent with the name similar as the first name of that employee.
c) Determine the names of employees who are supervised by employee with first name 'Franklin’ and last name ‘Wong'.
d) For each and every project, find out the project names and the total hrs per week (spent by all employees) on the project.
e) Find out the names of employees who work on each and every project.
f) For each department, determine the department name and the average salary of employees working in department.