For these exercises, we use the Employees Database. Your query must work for any set of data in the Employees Database.

1. Give the single statement to create a new employee with ID 12 and name Ron Neuman. All other values should be the default.

2. Give the single statement to assign the employee with ID 12 to the department with code ADMIN

3. Give the single statement to remove all employees from the project with ID ADT4MFIA.

4. Give the single statement to increase the revenue of all projects by 10% that have a project worked on by an employee with last name Hardware.

5. Give the single statement to assign the employee with ID 12 to the project(s) with revenue greater than $15,000. Each project should be assigned an equal portion of 100% time. For example, if there are 4 such projects, the employee with ID 12 should be assigned 25% time for each project.

6. Give the statement(s) to delete all employees of the Accounting department

7. Harry Hardwares twin brother, Igor, has decided to join the company. Hell work for the same department and make the same salary as his brother. Pick an unused ID. Give the single statement to create this new employee. You never know when somebody else might be changing Harrys salary, department, or last name; therefore, your statement must derive these values from the database. In other words, you cannot look up Harrys last name and use the literal value in the creation statement for Igor.

8. You have created a new department named Shipping with code SHPNG. The new department is a subdepartment of ADMIN. To handle this new department, you have hired a new employee named Ed Fex with ID 20 to manage the department. Give the statement(s) to add this new information to the database. Make up any unspecified data yourself.

9. Give the statement(s) to remove the Administration department. Any responsibilities that the Administration department has (e.g., being a super department, owning projects) should be transferred to the department with code CNSLT. Dont peek at the data.

departments projects projectid code deptcode FKe name managerid FK subdeptof FK description startdate enddate revenue workson projectidFK employeeidFK assignedtime employees employeeid firstname lastname deptcode FK salary Figure 1.5: Schema for the Employees Database