5: Specify the following queries on the COMPANY database in SQL. Give the query results.
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 dept name and the number of *male* employees in each department having an average employee salary of more than $30,000 (rather than *all* employees, as in part a). Give an SQL query.
6: Specify the following queries in SQL for the UNIVERSITY database. For this exercise, an A- is *not* the same as an A.
a. Retrieve the names and major departments of all straight-A students (students who have a grade of A in all their courses).
b. Retrieve the names and major departments of all students who do not have a grade of A in any of their courses.
7: In SQL, specify the following queries on the COMPANY database using the concept of nested queries and concepts described in this chapter.
a. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees.
b. Retrieve the names of all employees whose supervisors supervisor has '888665555' for Ssn .
c. Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company.
8: Specify the following views in SQL on the COMPANY database schema shown in Figure 3.5/5.5.
a. A view that has the department name, manager name, and manager salary for every department.
b. A view that has the employee name, supervisor name, and employee salary for each employee who works in the 'Research' department.
c. 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.
d. 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.