Purpose of homework: To become familiar with the foundations of relational database querying.
Problem 1: SQL Queries About Employment. [100 pts]
1. Find the names and ages of each employee who works in both the Hardware department and the Software department.
2. For each department with more than 10 full-time-equivalent employees (i.e., where the part-time and full-time employees add up to at least that many full-time employees), print the did together with the number of employees that work in that department. Note: for a full-time employee the pct_time=100 and for a part-time employee the pct_time<100. Full-time equivalent employees means that the sum of their pct_time is at least 100).
3. Retrieve the name of each employee whose salary exceeds the budget for all of the departments that they work in.
4. Find the managerids of managers who manage only departments with budgets greater than $1 million.
5. Find the enames of managers who manage the departments with the largest budgets.
Create SQL statements for each of the 5 queries. Explicitly state any assumptions that you are making when designing your queries.
You need to run the SQL queries in this homework on ORACLE. This will help to you to increase your confidence that your design is syntactically correct (compiles without errors) and semantically correct (does what you expect it to do on some data set).
For this, you need to run your SQL queries against a common data set that we will provide to you. Show the queries and the resulting output tables for each of your queries on our sample data (to be provided on the course webpage).
In addition, you need to design an additional second test data set of your own, explain what you are aiming to test, and undertake testing of your solution to verify that it indeed correct.
