SQL Exercises 2

Assume that you have the following tables in a database:

Workers
Name SS#
Married (Boolean - True/False)
Dept (Department Name)
Salary
BDay (Birth Day — Date Field)

Department
Name
ManagerName
Budget
Location

ProjectEmp
SS#
ProjNm (Project Name)

An employee can work on multiple projects, but belongs to only one department. All managers are employees. A manager cannot manage more than one department. A project can have multiple employees.

• Write a query to list out the Employee Name, Department Name, Manager Name and Project Names for employees who have salaries over $100,000 and are working on a project.
• Write a query to calculate how many married employees in the Sales department, are working on Project "Cart Wheel".
• Write a query to print out the names and salaries of married managers of employees working on project "Hope". The names and salaries must be of the married managers. The employees are working on Project "Hope", not necessarily the managers. You must use sub queries with no joins or Cartesian Products to do this.
• Write a query to print out the names of employees not currently assigned to any project.
• For employees that are working on multiple projects, write a query to print out the name of each employee, their salary, along with the number of projects they are working on.
• Write a query to calculate the average budget for departments that have employees working on Project "Dom". The answer is just one number. Even if there are multiple employees from a particular department working on "Dom", include the department only once while calculating the average.

Can only use Select, from, where, having, group by, Cartesian product, inner and outer join, sub queries(in, not in), aggregate functions, not, and, (whatever other basic functions like <, =)

All queries must be typed (double spaced). They must be in the given order. Every query must be done in SQL.