Question

10- Specify the following queries in SQL on the database schema of Figure 2. a. Retrieve the names of all senior students majoring in ‘CS’ (Computer Science). b. Retrieve the names of all courses taught by Professor King in 2004 and 2005. c. For each section taught by Professor King, retrieve the course number, semester, year, and number of students who took the section. d. Retrieve the name and transcript of each senior student (Class =4) majoring in CS. A transcript includes course name, course number, credit hours, semester year and grade for each course completed by the student. e. Retrieve the names and major departments of all straight-A students (students who have a grade of A in all their courses.) f. Retrieve the names and major departments of all students who do not have a grade of A in any of their courses.

STUDENT Name Student number ClassMajor Smith 17 CS COURSE Course name Course_number Credit hours Department Intro to Computer Science CS1310 Data Structures Discrete Mathematics Database CS3320 MATH2410 CS3380 CS CS MATH CS 3 SECTION Section_identifier Course number Semester Year Instructor 85 92 102 112 119 135 MATH2410 CS1310 CS3320 MATH2410 CS1310 CS3380 04 King 04 Anderson 05 Knuth 05 Chang 05 Anderson 05 Stone Fall Spring Fall Fall GRADE REPORT Student number Section identifier Grade 112 119 85 92 102 135 17 17 PREREQUISITE rse number Prerequisite, number CS3380 CS3380 CS3320 CS3320 MATH2410 CS1310

Answer 1