CS 4250: Database Management Systems

Spring 2020 - Homework 2

Due 3/20/2020 (Friday), at midnight

This is an individual assignment. All work must be your own. You should not look at any other student's work (in whole or in part, on paper or on screen), nor allow anyone else to look at yours, during the course of this assignment.

You may turn in hardcopy (typed, submitted during class time on the day before the due date) or email a plain text, MS Word or PDF document to the instructor with subject line "cs4250,hw2" (by midnight).

(Typed homework, in all cases. Under no circumstances should you email me a photograph of handwritten scribbles.)

Consider the following relations:

Engineer (engineerID: integer, lname: string, fname: string, jobtitle: string, email: string, proEngineerExamTitle: string, proEngineerExamPassDate: date, salary: integer, vacationDaysAccumulated: integer)
Project (projectID: integer, name: string, startDate: date, endDate: date, customerID: integer, monthlyBudget: integer, totalBudget: integer, managerID: integer)
ProjectEngineers (projectID: integer, engineerID: integer, hoursPerWeek: integer)
Customer (customerID: integer, name: string, address: string, phone: char(10), contactName: string, contactPhone: char(10))
BordersTrip (tripID: integer, engineerID: integer, country: string, town: string, startDate: date, stopDate: date, charityName: string, donation: integer, description: string)

The relations capture some basic information about an engineering consultant company. Engineers work on Projects for Customers. Engineers also go on Trips for Engineers Without Borders USA activities, as a way to give back to the world and their own communities.

Engineers have job titles, and some engineers have passed Professional Engineers exams in some specific field of engineering (civil, chemical, fire protection, etc).
Projects are performed for and paid by Customers, and customerID in Project is a foreign key referencing Customer. Projects are managed by Engineers, and managerID is a foreign key referencing Engineer:engineerID.
ProjectEngineers keeps track of which engineers work on which projects. Both key fields are foreign keys to the appropriate tables. hoursPerWeek keeps track of how many of the engineer's weekly hours are assigned to this project.
Customers are companies. Some employee at each customer company will be the official contact for that company.
BordersTrip records which engineers (engineerID is a foreign key referencing Engineer) are scheduled to go on trips for Engineers without Borders USA, when and where. The table also records how much money the company might have donated to a charity involved in the trip. (EWB frequently partners with charities when developing and deploying charitable projects.)

You may make the simplifying assumption that comparisons on "date" or "timestamp" fields will work with basic =, <, >, etc, boolean operators. This is not the case in many DBMS products; for real work you would need to consult the documentation for your particular DBMS software.

Write the following queries in SQL.

  1. Find the average engineer salary.
  2. Find the engineer identifiers (IDs) and their last and first names for all engineers where their job title is "Senior Project Manager".
  3. Find the names and total budgets of projects, where the customer paying for the project is "World Health Organization".
  4. Find the engineer identifiers, country and town names for Engineers Without Borders trips where the description of the trip has the word "water" anywhere in it. Or the description has "clean" at the start but might have any one letter where the 'a' in "clean" is, and the description might have anything after the clean-like word. ("clean sanitation" or "cleen houses" or "cleining coffee beans", and so on)

  5. Find the last and first names of engineers who manage some project, and the names of the projects those engineers manage.
  6. Find the names of projects and the names of the customers paying for those projects, where the total budgets of the projects are larger than the average total budget over all projects.
  7. Find the names of projects and the names of the customers paying for those projects, where the project start date is after "1 March 2020". The resulting names should be alphabetized by customer name.
  8. For each engineer job title, where the Professional Engineer Exam title that engineer took is "Fire Protection", print out minimum and maximum salaries engineers with that title earn.
  9. Find and list pairs of trip identifiers for Engineers Without Borders trips, and the two towns and one country those trips went to. Both trips must have been to the same country to be listed together. Alphabetize the list by the name of the country.
  10. For each engineer working on projects, print out the engineer identifier and how many projects that engineer is working on.
  11. Find the engineer last name and job titles, and the hours a week each engineer works on a project, and the project names and the names and addresses of the customers the projects are performed for, where the hours a week the engineer works on that project is more than 5.
  12. For each project that has more than 10 engineers assigned to it, list the project identifier, the project name, the number of engineers assigned to the project, and the sum of the number of hours per week of all the engineers assigned to the project.