SQL Lab 2
This lab uses the enrollment data.
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.
Unless otherwise specified, you may use your "id number" in the query.
Part A (using aggregation and/or functions)
- 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.)
- Display the number of distinct Biblical literature courses taught last semester.
- Display the number of instructors teaching English composition courses last semester.
- Display the number of instructors teaching English composition courses each semester that is in the database ordered by when they were taught. Don't worry if the summer session is out of order -> extra credit to fix that!
- Display the time of day (military time -> starting and ending time) that each sociology course was taught last semester. Don't forgot to identify the time with the course
- Display the max course number and min course number offered by each "code" last semester
- Display the number of times each student has changed his/her major.
- For each student, classify them based upon how often they changed their major: 0 -> stable, 1-2 -> typical, 3-4 -> searching, >= 5 -> unsure
- List the codes for the courses that have at least one class with an enrollment greater than 50 last semester.
- Display all students names sorted alphabetically by last name then first name. You may assume that each student only has two names listed.
Part B (using only joins)
- Display the title of each course you were enrolled in last semester
- Display the all the rooms associated with the classes you took last spring
- Find the names of all the instructors teaching in the Religion and Philosophy department last year.
- Display your schedule from last fall
- Display the name, email, and major of all students majoring last spring in one major of your choice.
- Display the student id numbers of each of the students that you shared a class with last semester (no duplications)
- Display the names of each of the students that you shared a class with last semester (no duplications)
- Find the names of all the instructors who have taught a course with the same course code as anyone who taught ENGL 101 last fall.