CSci 275 Assignment 4

Due: Thursday, 15 March 2018.

Given the following relational schema in the "cs" database:
 

Student ( ID, LName, FName, Address )
Enroll ( ID, CRN, Year, Grade )
Section ( CRN, Year, DeptCode, CourseNo, SNo, BCode, RoomNo )
Course ( DeptCode, CourseNo, CourseName )
Dept ( DeptCode, DeptName )
Building ( BCode, Building )

Create a spool file (see SQL*Plus Notes) showing the results of SQL queries for the following:
 

  1. List all of the student with last name of 'MacNeil', showing the ID, LName, and FName.
     
     
  2. List all of the students with last name of 'MacNeil' that have actually enrolled in a section of a course in 2017.
     
     
  3. List all of the students with last name of 'MacNeil' that have never enrolled in a section of a course.
     
     
  4. Find a class list of CSCI 275:20 in 2017, showing ID, Last Name, First Name, and Grade, sorting by name.
     
     
  5. Rumor has it that someone cheated on a midterm in 2018 in the Math seminar room, AX 23A. I need you to find all students enrolled in courses in that room in 2018. Please list them in order by course, then by name.
     
     
  6. Turns out the source of the rumor was the phrase 'I cheated' and the initials 'RB 2018' on a table in AX 23A. I need you to find all students enrolled in courses in that room in 2018 with initials 'RB'.
     
     
  7. Find the course section offering(s) with the highest failure rate (percentage), showing the department code, course number, section number, year, number of failures, and total number of students.
     
     
  8. For the students who enrolled in MATH 277 in 2017, determine the number of students and their average grade for those who have a MUSI (Music) course on their transcript, and the number of students and their average grade for those who do NOT have a MUSI course on their transcript.