SQL Lab 2

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. BE sure to include your name and label each question (using commnets).

Unless otherwise specified, you may use your "id number" in the query.

Questions:
Part A (using aggregation and/or functions)

1. Display the courses you have taken (code, number, section, semester) ordered by when they were taken. Assume that there is at least three years of data to be dealt with. (You may assume you know the student id number and if you wish you can pick which student you want as long as it has sufficient data.)
2. Display the number of distinct courses taught fall 2006 using the takes table;.
3. Display the number of instructors teaching the second section of a course in fall of 2006
4. Display the number of instructors teaching the second section of a course each semester that is in the database ordered by when they were taught.
5. Display the day (including the time) of each reservation of a boat. NOTE: this is using the sailors data, not the enrollment data. Also be aware what is stored in the data affects what results are seen.
6. Display the max course number and min course number offered by each semester and year
7. Display the number of students take have taken the same course more than twice.
8. Extra Credit: For each student, classify them based upon how often they have retaken a course: at most 1 -> standard, 2 -> learned lesson, 3 -> stubborn, > 4 -> struggling
9. List the codes for the courses that have at least one class with an enrollment greater than 300 during the year of 2003
10. Display all students names sorted alphabetically by last name then first name. You may assume that each student only has two names listed.

1. Display the title of each course you were enrolled in last semester
2. Display the all the rooms associated with the classes a student took in a specific semester (you get to pick which semester and which student)
3. Find the names of all the instructors teaching Cybernetics department during the academic year of fall 2007.
4. Display a student's schedule with enough data to find the classes, etc. ordered by the semester taken.
5. Display the name, total credits, and major of all students advised by a faculty member in a department of your choice with the "newest" students first.
6. For student 77588, display the student id numbers of each of the students that shared a class with him/her during the semester of Spring 2008 (no duplications)
7. Repeat the previous question but display the names instead (no duplications)