In this session we will do queries on the Registrations DB (zipped). Write SQL queries in MS Access to display the following data. Please save each query indicating the exercise number.

Create the following queries using join clauses.

1. List of faculty names and the courses they taught.
2. List of faculty names from the Operations division and the names of the courses they taught, ordered by faculty name.
3. Names of non-MMOR students that have taken BAMS 580.

Let’s use queries to prepare a template that could be used for getting transcripts of students grades. [clarified Nov 7]

4. List of MMOR student names and the course ID, year, term and final grade, of their registered courses.
5. The same as 4, including course name and department
*6. The same as 5, including faculty name
*7. The same as 6, ordered by course year, term and student name
8. The same as 7, only for courses with ID starting with BAMS.

Joins can be useful to compare tuples of the same table. Use joins on the same table to find the following information:

9. Pairs of students admitted to the MMOR program having the same age. Show in a single row each student’s name, age and admission year; only unique combinations of students. [note Nov 7: in class we used the <> operator to exclude “a=a” pairings; use a different operator to make sure that you get only one of “a=b” and “b=a”]
*10. Pairs of students registered in the same course, term and year, having the same age.

Now let’s use nested queries (or join clauses or auxiliary tables) to obtain the following information

11. Students that have not taken BAMS 580.
*12. Students that took BAMS 521 and didn’t take BAMS 522.
*13. List of students and faculty that haven’t been together in a course.

Return the following information using aggregation queries. Make sure to name the aggregation field.

14. Total number of students registered in all courses. (This is the same as the sum of the number of students registered in each course.)
15. Total number of students per course, year and term
*16. Same as previous plus final grades’ average and standard deviation.
*17. Student name, total number of courses they were registered for, and average grade.
*18. Course ID, year and term and name of student with the maximum final grade
*19. For each course-year-term, students with the maximum and minimum final grades (in a single row)
*20. Number of students in BAMS 580 broken down by final grade groups (0-10,11-20, …, 91-100)