Your task in this assignment is to write a set of SQL queries on a set of tables I supply.
1. First, download the script file company.sql from the course web site and run it in SQLDeveloper to construct a database instance containing five tables: EMPLOYEE, DEPARTMENT, PROJECT, WORKSON, and DEPENDENT.
Inspect the tables and their schemas in SQLDeveloper so that you understand the structure of the database. I recommend that you sketch the schema of the database, including all primary keys and foreign keys, before you write any queries.
2. In a separate .sql file (do not modify company.sql), write a script that contains the following eight SQL queries (in this order):
1. Give the names of all male employees whose direct supervisor has SSN 333445555.
2. Give the SSNs of all employees in who work at least 12 hours per week on some project located in Houston.
3. Give the average salary of all employees in the Research department
4. For each project, give the project name and the total hours per week (by all employees) spent on that project. List the projects in descending order by the total hours per week spent on the project.
5. Give the SSNs and names of all employees who do not work on any project.
6. Give the first names only of all employees in department 4 who have at least one dependent.
7. Give the last names of all employees whose supervisor’s supervisor has SSN 121231234. List the last names in alphabetical order.
8. For each department whose average salary is more than $33,000, give the department name, how many employees are in that department, and the average salary for the department.
