Q1:A) For each department, list all the employees who are working in the department with the employee?s first and last name and first and last name of his or her immediate supervisor. Include all the departments and all the employees (including who do not have any supervisor). List the result in the order of each department number and first name of each employee.

B) List all the employees (including who do not have any supervisor) but do not list the departments that no employee is working for in the output.

C) Get SSN and the last name of married female employees who work on three or more projects.

D) List the name of employees who is working for ?Research? department and are married but have no children.

E) Get the last name of married employees who only have daughters.

F) Give the last name and ssn of those employees who work in any project(s) where there are more female than male employees.

Transcribed Image Text:

Referential integrity constraints displayed on the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Essn Dependent_name Sex Bdate Relationship Dno Referential integrity constraints displayed on the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Essn Dependent_name Sex Bdate Relationship Dno

Answer rating: 100% (QA)

Q1A SELECT dDnumber AS DepartmentNumber eLname AS EmployeeLastName eFname AS EmployeeFirstName sLnam... View the full answer