2. For the Company Projects DB, (see Appendix), write SQL queries to answer perform each of the following actions. a) For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. b) Suppose that we want the number of male employees in each department making more than $30,000, rather than all employees. Can we specify this query in SQL? Why or why not? c) Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. d) Retrieve the names of all employees whose supervisors supervisor has 888665555 for Ssn. e) Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company. f) A view that has the department name, manager name, and manager salary for every department. g) A view that has the employee name, supervisor name, and employee salary for each employee who works in the Research department. h) A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project. i) A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it. j) Retrieve the names of all employees who do not have supervisors. k) Retrieve the names of employees who have no dependents. l) Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project. m) List the names of all employees who have a dependent with the same first name as themselves. n) Find the names of all employees.

3. Do the following problems. a. Choose 3 questions from Problem (1) and reformulate these using Relational Algebra queries. b. Choose 3 questions from Problem (2) and reformulate these using Relational Algebra based queries.

Appendix

1. Pizza Delivery Shop DB design. Relational schemas. Customer(CustomerPhoneKey [PK], LastName, FirstName, Address1, Address2, City, State, Zip). CustomerOrder(OrderKey [PK], OrderDate, OrderTime, CustomerPhoneKey [FK], EmployeeKey[FK]). Employee(EmployeeKey [PK], LastName, FirstName, HireDate). Product(ProductKey [PK], ProductName, UnitSize, UnitPrice). OrderDetail(OrderDetailKey [PK], OrderKey [FK], ProductKey [FK], OrderDetailQuantity, OrderDetailPriceCharged).

2. Company Projects DB. Relational schemas: Employee(Fname, Minit, Lname, Ssn [PK], Bdate, Address, Sex, Salary, Super_ssn, Dno). Department(Dname, Dnumber [PK], Mgr_ssn, Mgr_start_date). DepartmentLocation(Dnumber [PK], Dlocation [PK]). Project(Pname, Pnumber [PK], Plocation, Dnum). WorksOn(Essn [PK], Pno [FK], Hours). Dependent(Essn [FK], Dependent_name [PK], Sex, Bdate, Relationship).