Fall Semester 2002

Solutions are listed in some color

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
    The answer is, of course:

    Chicago Bulbs
    Los Angeles Lakers
    Indiana Pacers
    Philadelphia 76ers

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

    SELECT [Marina Name] 
FROM Marina, Technician
    The answer for this one is

    Indiana Pacers
    Indiana Pacers
    Los Angeles Lakers
    Los Angeles Lakers
    Chicago Bulbs
    Chicago Bulbs
    Philadelphia 76ers
    Philadelphia 76ers

    The reason is, of course, the cartesian product created.

  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]
    The answer for this one is

    Indiana Pacers
    Los Angeles Lakers
    Chicago Bulbs
    Philadelphia 76ers

    A natural join results through an adequate selection on the cartesian product.

  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'
    The answer for this one is

    Indiana Pacers
    Chicago Bulbs
    Philadelphia 76ers

    Only Bird's teams are shown.

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

    The answer for this one is 
    SELECT [Tech Name], [Marina Name]
FROM Technician, Marina
WHERE Technician.[Tech Number] = Marina.[Tech Number];
    This is almost (but not entirely) the query from no. 3.

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

    The answer for this one is 
    SELECT [Tech Name], COUNT(*), 
FROM Technician, Marina
WHERE Technician.[Tech Number] = Marina.[Tech Number]
GROUP BY [Tech Name];
    This one builds on the query from no. 5.

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

    The answer for this one is 
    SELECT [Tech Name], COUNT(*), 
FROM Technician, Marina
WHERE Technician.[Tech Number] = Marina.[Tech Number]
GROUP BY [Tech Name]
HAVING [Tech Name] = 'Larry Bird';
    Again, this one builds on the query before it.

  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'
    This will be a selection on a cartesian records that has eight records (four for Bird, and four for Jackson). One for Bird and three for Jackson would be eliminated in a natural join, but we don't have a natural join here. So the four records for Bird will be selected, giving the same answer as question no. 3, as illustrated below.

    Indiana Pacers
    Los Angeles Lakers
    Chicago Bulbs
    Philadelphia 76ers

  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).

    The answer for this one is 
    SELECT Name, AVG(Grade) 
FROM Grades
GROUP BY Name
    This is a very basic query.

  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).

    The answer for this one is 
    SELECT Name, MIN(Grade), MAX(Grade), MAX(GRade) - MIN(Grade) 
FROM Grades
GROUP BY Name
    Same basic query, with calculated fields.

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

    The answer for this one is 
    SELECT AVG(Grade)
FROM Grades
    Simple, but useful in 12 (below).

  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).

    The answer for this one is 
    SELECT Name
FROM Grades
GROUP BY Name
HAVING AVG(Grade) > ( SELECT AVG(Grade) 
                      FROM Grades 
                    )
    Nested queries as discussed in class last Thursday.

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

    (Name, Date) is one option.

    If we are allowed to change the structure of the table (nobody said we were) then adding a new column, to count the records and serve as a key, would also be an acceptable solution.

    The original question was asking this: create the Grades table with the columns indicated above. Enter the data. What was your key when you created the table (this table, as indicated above).

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