CSC330S2020MultipleSQLLab < CenterForComputing

Tags: view all tags
---++ Multiple Table SQL Lab
---+++ Overview

   1. Use the data in the course, etc. tables
   1. Turn in a soft copy (a .sql file with just the sql statements and comments)  Be sure to label each statement/answer
   1. 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
      1 Display the student id numbers of each of the students that you shared a class with last semester (no duplications)
      1 Display the names of each of the students that you shared a class with last semester (no duplications)
      1 Display the all the rooms associated with the classes you took last spring
      1 Find the names of all the instructors teaching in the Religion and Philosophy department last year.
      1 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.
      1 Display your schedule from last fall
      1 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
      1 Display the student id numbers of each of the students that you shared a class with last semester (no duplications)
      1 Display the names of each of the students that you shared a class with last semester (no duplications)
      1 Display the names of the students that shared a class with you last semester but have not shared a major with you.
      1 Display the names of the students that shared a class with you last semester and have shared a major with you.
      1 Display the names of the students that shared a class with you last semester or have shared a major with you.
      1 Display the all the rooms associated with the classes you took last spring
      1 Find the names of all the instructors teaching in the Religion and Philosophy department last year.
      1 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.
      1 Display the ids of all students who are ready of but have not taken ENGL 102 (i.e. have taken ENGL 101)
      1 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)
      1 The names of the students who have taken the largest load last spring
      1 The names of the instructors who have taught less than the average number of classes taught by any instructor last fall
Topic revision: r1 - 2021-10-19 - cathybareiss