Difference: CSC210S2026Lab3 (1 vs. 2)

1. Display the title of each course a student was enrolled in during a given one semester.
2. Display the student id numbers of each of the students that shared a class with a student a given semester (no duplications).

1. Limit the previous response to those students in the same department as a student

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
2. 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.
3. Display the names of the students that shared a class with a given student you last semester and currently share a major with you.

SQL Lab 3

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).

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). Questions:
Part A (using only nested queries and NO joins)

1. Display the title of each course one student (of your choice) was enrolled in during one semester (your choice).
2. Display the student id numbers of each of the students that shared a class with one specific student (your choice) a specific semester (your choice) (no duplications).
3. Repeat the previous question but display the students name (no duplications).
4. Limit the previous response to those students in the same department as your specific student
5. Limit the same response to those students that have not shared the department.
6. Display the names of the students that shared a class with you last semester and have ever shared a major with you.
7. Display the names of the students that shared a class with you last semester but have never shared a major with you.
8. Display the names of the students that shared a class with you last semester and currently share a major with you.
9. Display the names of the students that shared a class with you last semester or have ever shared a major with you.
10. Display the all the rooms associated with the classes you took last spring
11. Find the names of all the instructors teaching in the Religion and Philosophy department last year.
12. 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).

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 for 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

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)
2. The names of the students who have taken the largest load last spring
3. 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.