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.

Academic Honesty!
It is not our intention to break the school's academic policy. Projects posted are only used as a reference and should not be submitted as is. We are not held liable for any misuse of the solutions. Please see the frequently asked questions page for further questions and inquiries.
Kindly fill out the form. Please provide a valid email address and we'll get back to you in less than 24 hours. We will be sending an invoice through PayPal upon confirmation. We are a non profit organization however we need an amount to keep this organization running, and to be able to complete our research and development.