(r2) CSC210S2026Lab3 < CenterForComputing

Tags: view all tags
---++SQL Lab 3
This lab uses the enrollment data.<br /><br />

Only turn in the sql statements (not the results of the query). You may test your queries out online. (But remember, just because you get the right data back, that does not mean your query is correct!). Turn in an electronic copy or your .sql file. Include your name and label each question (using comments).<br /><br />
Once again, you may assume you know your id number and you may use yourself or another student of your choice (as long as that student has enough data).<br><br>
For the questions that specify "a student" and possibly "a given semester", you may pick the student and the semester but use the same ones for all those types of questions.
Questions:<br />
<b>Part A (using only nested queries and NO joins)</b>
   1 Display the title of each course a student was enrolled in during a given one semester.
   1 Display the student id numbers of each of the students that shared a class with a student a given semester  (no duplications).
   1 Repeat the previous question but display the students name (no duplications).
   1 Limit the previous response to those students in the same department as a student
   1 Limit the same response to those students that have not shared the department.
   1 Display the names of the students that shared a class a student a given semester and have ever shared a department with that student
   1 Display the names of the students that shared a class with a student a given semester but have never shared a department with that student.
   1 Display the names of the students that shared a class with a given student you last semester and currently share a major with you.
   1 Display the names of the students that shared a class with you last semester or have ever 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. Do not use aggregation. Ignore the course numbers ending in T (or just use the first three characters of a course number).
*Part B (Primary work cannot be done using 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 for 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 C (use any method you wish)*
   1 The names of the instructors that have taught the largest number of different students last semester. Do not include Kristen Richmond. (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 (last fall) less courses than the average number of courses taught by any instructor last fall (i.e. multiple sections of the same course don't count). Order by the count and then the last name for extra credit.
Topic revision: r2 - 2026-01-21 - CathyBareiss