Multiple Table SQL Lab

Overview

1. Use the data in the course, etc. tables
2. Turn in a soft copy (a .sql file with just the sql statements and comments) Be sure to label each statement/answer
3. When dealing with majors, you only need deal with major1. If you want to add major2, that is a lot of work and is extra credit

Questions

• Part 1 (using only joins)
  1. Display the title of each course you were enrolled in last semester
  2. Display the student id numbers of each of the students that you shared a class with last semester (no duplications)
  3. Display the names of each of the students that you shared a class with last semester (no duplications)
  4. Display the all the rooms associated with the classes you took last spring
  5. Find the names of all the instructors teaching in the Religion and Philosophy department last year.
  6. Find the names of all the instructors who have taught a course with the same course code as anyone who taught ENGL 101 last fall.
  7. Display your schedule from last fall
  8. Display the name, email, and major of all students currently majoring in one major of your choice.
• Part 2 (using only nested queries and NO joins)
  1. Display the title of each course you were enrolled in last semester
  2. Display the student id numbers of each of the students that you shared a class with last semester (no duplications)
  3. Display the names of each of the students that you shared a class with last semester (no duplications)
  4. Display the names of the students that shared a class with you last semester but have not shared a major with you.
  5. Display the names of the students that shared a class with you last semester and have shared a major with you.
  6. Display the names of the students that shared a class with you last semester or have shared a major with you.
  7. Display the all the rooms associated with the classes you took last spring
  8. Find the names of all the instructors teaching in the Religion and Philosophy department last year.
  9. Find the names of the students that have taken every 100 level music theory (MUTH) course offered.
• Part 3 (Cannot use a join or a nested query)
  1. Display the ids of all people (students and instructors) associated with philosophy courses (PHIL) last semester.
  2. Display the ids of all students who are ready of but have not taken ENGL 102 (i.e. have taken ENGL 101)
  3. Display the ids of all students who have taken both ENGL 101 and THEO 110
• Part 4
  1. The names of the instructors that have taught the largest number of students last semester (extra credit: for each semester)
  2. The names of the students who have taken the largest load last spring
  3. The names of the instructors who have taught less than the average number of classes taught by any instructor last fall