Fall Semester 2002

This is the A114/I111 Midterm Exam for the Fall 2002 semester. The exam is open book, and open notes. You have 50 minutes to complete it. Turn in the answers on the extra paper provided and keep the actual exam, as there's a second part to it: tomorrow in lab you should turn in the answers to these questions after having checked your answers in Access. Should you find any mistakes in what you turned in today, you can improve your grade by turning in a corrected version tomorrow, with any explanations of what you missed during the written test, and why. Best of luck and do very well!
  1. Consider a database that stores information about technicians and marinas that they serve. The database contains two tables: one for Technicians and the other one for the Marinas.

    First, here's the table of marinas (names are made up)

    Marina Number Marina Name Tech Number
    Pac Indiana Pacers 1
    Lak Los Angeles Lakers 2
    Bul Chicago Bulbs 1
    Phi Philadelphia 76ers 1

    And here are the technicians:

    Tech Number Tech Name
    1 Larry Bird
    2 Phil Jackson

    (Please refer to this database for this and the next few questions).

    What's the result of running the following query against this database: 

    SELECT [Marina Name] 
FROM Marina

  2. What's the result of running the following query against this database: 

    SELECT [Marina Name] 
FROM Marina, Technician

  3. What's the result of running the following query against this database: 

    SELECT [Marina Name] 
FROM Marina, Technician
WHERE Technician.[Tech Number] = Marina.[Tech Number]

  4. What's the result of running the following query against this database: 

    SELECT [Marina Name] 
FROM Marina, Technician
WHERE Technician.[Tech Number] = Marina.[Tech Number]
  AND [Tech Name] = 'Larry Bird'

  5. Write an SQL query that lists the technicians' names and the marinas they service.

  6. Write an SQL query that lists the technicians' names and the number of marinas they service.

  7. Write an SQL query that lists the number of marinas serviced by technician Larry Bird.

  8. What do you think the result will be if you run this query against the database, and why? 
    SELECT [Marina Name]
FROM Marina, Technician 
WHERE [Tech Name] = 'Larry Bird'

  9. For the remaining questions please refer to the following table (called Grades):

    NameDateGrade
    Larry 10/1/2003 10
    Michael 10/3/2003 8
    Larry 10/5/2003 6
    Larry 10/7/2003 7
    Michael 10/7/2003 10
    Michael 10/10/2003 8

    Write an SQL query that lists the players and their average grades.
    (The answer is: Larry 7.66, Michael 8.66).

  10. Write an SQL query that lists the players and their lowest and highest grades as well as, for each player, the difference between their highest and lowest grade.
    (For Larry that's (6, 10, 4) and for Michael it's (8, 10, 2) for verification purposes).

  11. Write an SQL query that calculates the average of all grades ever assigned (it's 49/6 = 8.16).

  12. Write an SQL query that lists the players whose average grade is above the average grade as calculated in the previous question (only Michael is).

  13. What, do you think, could be a key for the Grades table?

A114/I111 Midterm Exam October 1 2002, 2:30-3:20pm TH A201