CSE 444 Homework 3

Objectives:
To understand and be able to manipulate SQL queries
Reading Assignments:
5.1-5.9
Number of points:
100; numbers 1 to 12 are each worth 8 points
Due date:
October 29 (but turn it in on the 27th to have it graded by the 29th)
Tools for the assignment

For this assignment, you will need the database we have created. This database is available in MS Access form at hw3.mdb

You should import this database into your project space and work on it there. Each group member should create their own views independently, so to avoid name conflicts, be sure to decide on a unique naming scheme.

Here are some better descriptions on how to use SQL Server

Answer the following questions on the data and schema provided. The schema was based on the schema given in homework one. For each answer, please provide
  1. The answers
  2. The SQL query you used
  3. Any assumptions you made (any assumptions that do not agree with the data are incorrect)
Please note that your query must return the answer of the question exactly; if the answer asks for a count of something, return that count, etc. Unless it's part of the query, we do not want duplicates in the answers. If you create a view, please include the view definition in your answer. The questions are (roughly) in order from least to most difficult.
  1. Which research areas are our faculty interested in?
  2. How many graduate students have offices in 428?
  3. What are the first and last names of the undergrads who have homepages on the students.washington.edu server?
  4. Give the first and last name of all faculty members who advise students who have offices in 224.
  5. What's the total number of undergraduates, graduates, and faculty members in the department?
  6. What is the average year that students entered the department? Note, you do not need to get SQL to give you an integer.
  7. What are the logins of all the people in our database have the first name "Brian"?
  8. Which first name(s) is most common in department members?
  9. How many students with the first name "David" entered the department for each since 1995? Note, you do not have to list years that had no "David"'s in them, but the years must be in descending order, and you must use the HAVING clause in your answer.
  10. Which first names are represented among exactly two of Faculty, Undergrads, and Grads? ( a name does not appear in one of the three tables of people, and appears at least once in each of the other two.)
  11. Give the login of all TAs for 142, and, if possible, give their URLs as well.
  12. Give an interesting query, English explanation, and answer that involves at least two joins that is not already in the assignment.
  13. [4 points] Please answer the following questions
    1. How long did it take you to complete this assignment?
    2. What did you like the best about this assignment?
    3. What did you like the least about this assignment?
    4. What helped you learn the best in this assignment?
    5. What distracted from your learning in this assignment?