CSC210S2024Lab3 < CenterForComputing

---++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).
Questions:<br />
<b>Part A (using only nested queries and NO joins)</b>
   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 and have ever shared a major with you.
   1 Display the names of the students that shared a class with you last semester but have never shared a major with you.
   1 Display the names of the students that shared a class with 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.
This topic: CenterForComputing > WebHome > Syllabi > SyllabiSpring2024 > CSC210SylS2024 > CSC210S2024Lab3
Topic revision: r3 - 2024-02-15 - CathyBareiss