To Midterm Guide

IT 240 -- Midterm, Part 2

1. Download the Access database file MT-part2.mdb (this is the one with students, sports, pets, and siblings in it).
    
2. Five Queries:
   1. There are a total of 43 sports listed in the tblSports table. Each student was asked to list (select) the sports that they had participated in.
      By looking at the tblStudentSports, you can see which students participated in which sports. For example, 17 students selected swimming, 6 selected cycling, and 3 selected Gymnastics. For a given sport, SportCount = number of students that selected that sport. Write a query that lists each sport together with its SportCount. List in descending order by SportCount. Your result should include every sport with it's count (0 if no one selected it). Name this query SportCountAll.  [Hint: A Left Join might be helpful, reference Join-queries.htm.]
      

   2. Write a query that lists all sports selected by at least one student. There should be no duplicates in your list. Sort by Sport. Name this query SportsSelected.  [Hint: You may find using DISTINCT helpful, reference Sql-distinct.htm.]
      

   3. List all students that own a dog and selected cycling as a sport (again, no duplicates). Name this query DogCycling.
      

   4. Find the average Year of all dog owning students that have a female sibling still living at home. Year is a field in the tblStudents table. Two decimal place accuracy required on your answer. Name this query AverageYear.

   5. For each Year, for each Type of pet, find the number of students that own that type of pet. Name this query YearxType.  [Hint: Think Crosstab, reference crosstab-example.htm.]
       

3. Requirements:
   • Your five queries in part 2 above, must have the specified names.
     
   • Your database file must be called smith-MTP2.mdb, where Smith is your last name.
   • Double check that your database file opens properly and contains your five queries.
   • Just before you submit your database, open it one final time.  Click on:  Tools | Database Utilities | Compact and Repair Database...
     Now close your database.  This step can save quite a bit of space.
   • Submit your smith-MTP2.mdb file using COLweb.
   • Late submissions will NOT be accepted.