Assignment 6

For the given database tables, write SQL queries for each of the questions below.

1. Retrieve the name of all senior students majoring in ‘cs’ (Computer Science).

• Notice that the class column can take one of the numbers (1, 2, 3, 4) that represents whether the student is a freshman, sophomore, junior, or senior, respectively.

1. Retrieve the numbers of the courses taught by Professor King in 2007 and 2008.
2. Retrieve all the grades of student number 8.
3. Retrieve all prerequisites of course CS3380.
4. Retrieve all the courses offered by the CS Department.
5. For each section taught by professor King, retrieve the course number, semester, year, and number of students that took the section.
6. Retrieve the current transcript for each sophomore student majoring in CS. A sophomore student has a class of 2. The transcript should show the student number, student name, course name, course number, course credits, semester & year at which the student took the course, and the obtained grade.
7. Retrieve all the courses taken by student whose number is 8 in the Fall of 2008.
8. Retrieve the names of all students that got an A in course CS1310.
9. Retrieve the total number of students in the database.
10. Retrieve the maximum number of credits for the database courses.
11. Retrieve the total number of courses that are worth three credits.
12. Retrieve the course numbers of all courses taught by professor Anderson
13. Retrieve the course names of all courses taught by professor Anderson.

• Hint: You will need to join the section and course tables since the section table contains the instructor & section number, while the course table contains the course name. The course number is the PK-FK linking the two tables.

1. Retrieve the total number of students that got an A grade in all sections of MATH2410.