CIS-390: Design of Large Software Systems

Homework #4

Due: Friday, April 19


Exercises

For each exercise, write an SQL select statement to:

  1. Find the ssn, first name, and last name of each person in the database.
  2. Find the ssn, first name, and last name of people living in Boston, MA. The results should be sorted by ssn.
  3. Find the vin number, accident date, and damages for each accident that did not involve a driver.
  4. Find, for each accident involving a driver, the vin number, accident date, damages, and driver's name.
  5. Find, for each person in the database, their ssn, name, and number of vehicles they own. For full credit, the results should include people who own zero vehicles.

 


Database Access

You can practice writing SQL queries and test your answers online at http://moosehead.cis.umassd.edu/autos/query.

Alternatively, you can download a copy of MySQL Workbench from http://dev.mysql.com/downloads/tools/workbench/. To access my sample database using MySQL Workbench you will need the following information:

The cis390 account has read only access to the database, so you can execute queries, but you cannot do updates, etc.

 


Submissions

Turn in your answers by email (either inline or as an attachment) to pbergstein@umassd.edu. Please use the subject line: CIS-390 HW#4.