CSE 444, Winter 2003

Assignment #1: Due January 22, Wednesday

Objectives:

To understand and be able to manipulate SQL queries.

Number of points:

100 points

Tools for the assignment

For this assignment, you will need the database created according to the answers we got for the questionnaire. This database is available in MS Access form at questionaire.mdb. You should import this database into your personal account space and work on it there. To get started with MS SQL Server, please check the project support page. To import the database, please check the detailed instruction. With the database imported, you can do your work now! To start Query Analyzer for your homework, check here.

Questions:

[8 points for each question below and 4 points for your efforts to get started with SQL.]

Answer the following questions on the data and schema provided.

• Student (stuID, lastName, firstName, gender, day, month, year, birthplace, home, cell, fax, homepage)
• Address (stuID, street, city, state, zip)
• Email (stuID, email)
• Education (stuID, department, university)
• FavoriteCourse (stuID, courseNo, courseName)
• Sport (stuID, sport)
• Interest (stuID, interest)
• Ofriend (stuID, outFriend)
• Ifriend (stuID, inFriend)

For each answer, please provide

1. The SQL query you used
2. The answers
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 sports are our students interested in?
2. How many students are interested in traveling?
3. What are the first and last names of our students who have homepages on the students.washington.edu server?
4. Give the studentIDs of all students who are interested in course Programming Languages, and, if possible, give their homepage URLs as well.
5. Give the first and last names of all our students who do not study in the department of Computer Science and Engineering at University of Washington.
6. What is the average age of our students? To simplify, for a student who were born in 1975, we consider his/her age as 2003 - 1975 = 28.
7. What's the total number of people (inside or outside 444 class) who are friends of our students? Note: one person should be counted only once. We count people with the same name as a single person.
8. Give the first and last names of the student who have the most friends inside and outside the class in total.
9. Return all the pairs (a,b) of students where both a and b list the same person as being their friend (the friend can be either in 444 or outside the class). Note that each pair should be listed only once, i.e., don't list both (a,b) and (b,a). To simplify, for each pair you can give only studentID instead of student name.
10. How many students were born in WA state for each year since 1980? Note, you should not list years in which no one were born in WA; the years must be in descending order; and you must use the HAVING clause in your answer.
11. Which first name(s) is most common in our class?
12. Give an interesting query of your own that is not already in the assignment. The query should involve at least two joins, HAVING clause and aggregation operation. Give the English explanation and the answer.