Difference: CSC210S2026Lab3 (1 vs. 4)

1. The names of the students who have taken the largest load in a during the fall of 2006. Include the number of hours the student took that semester.
2. The department with the highest average salary of its faculty

1. Limit the same response to those students that have shared the class or the department.
2. Display the distinct names of the instructors in the same department as Arinb (but not Arinb)
3. Display the courses that do not have a prerequisite. You must use exists.
4. Display the all the building, room combinations associated with the classes a student took a given semester.
5. Find the names of all the instructors who did not teach a given semester. (You choose the semester)
6. Find the names of the students have had a class in all rooms that have been used in Main. You may use joins in the subqueries, but you must use subqueries and you cannot use count.

1. Display the ids of all people (students and instructors) associated directly with the computer science department
2. Display the ids of all students who took a course during the fall and spring of 2009
3. Display the ids of all students who took a course during the fall of 2009 but not the spring.

1. The names of the instructors that have taught the largest number of different students in a given semester
2. The names of the students who have taken the largest load in a given semester
3. The names of the instructors who have taught (in a given semester) less courses than the average number of courses taught by any instructor in the same semester (i.e. multiple sections of the same course don't count). Order by the count and then the name for extra credit.

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.