a) Specify the following queries in SQL on the database schema of Figure 1.2. a. Retrieve...

  

Transcribed Image Text:

a) Specify the following queries in SQL on the database schema of Figure 1.2. a. Retrieve the name of each course and the instructor who taught that course. If an instructor taught a course more than one time, remove the redundant information. b. For each section of those courses offered by CS department, retrieve the course number, semester, year, and number of students who took the section. c. For each student who took more than 2 sections, retrieve the name, student number, major of the student and the number of sections taken by the student. STUDENT COURSE SECTION Smith Brown Name Course name Intro to Computer Science Data Structures Discrete Mathematics Database GRADE REPORT PREREQUISITE Section identifier Student number 85 92 102 112 119 135 17 8 Course_number CS1310 CS3320 17 17 8 8 8 8 MATH2410 CS3380 Course_number MATH2410 CS1310 CS3320 MATH2410 CS1310 CS3380 Student number Course_number CS3380 CS3380 CS3320 Class 1 2 Credit hours 4 4 Semester Fall Fall Spring Fall Fall Fall 112 119 85 92 102 135 3 Section identifier Major cs CS 3 Figure 1.2 Example of a simple database Year 07 07 08 08 08 08 Prerequisite_number CS3320 MATH2410 CS1310 Department CS CS MATH CS с A Grade B <<< Instructor King Anderson A Knuth Chang Anderson Stone a) Specify the following queries in SQL on the database schema of Figure 1.2. a. Retrieve the name of each course and the instructor who taught that course. If an instructor taught a course more than one time, remove the redundant information. b. For each section of those courses offered by CS department, retrieve the course number, semester, year, and number of students who took the section. c. For each student who took more than 2 sections, retrieve the name, student number, major of the student and the number of sections taken by the student. STUDENT COURSE SECTION Smith Brown Name Course name Intro to Computer Science Data Structures Discrete Mathematics Database GRADE REPORT PREREQUISITE Section identifier Student number 85 92 102 112 119 135 17 8 Course_number CS1310 CS3320 17 17 8 8 8 8 MATH2410 CS3380 Course_number MATH2410 CS1310 CS3320 MATH2410 CS1310 CS3380 Student number Course_number CS3380 CS3380 CS3320 Class 1 2 Credit hours 4 4 Semester Fall Fall Spring Fall Fall Fall 112 119 85 92 102 135 3 Section identifier Major cs CS 3 Figure 1.2 Example of a simple database Year 07 07 08 08 08 08 Prerequisite_number CS3320 MATH2410 CS1310 Department CS CS MATH CS с A Grade B <<< Instructor King Anderson A Knuth Chang Anderson Stone

Answer rating: 100% (QA)

a SELECT DISTINCT COURSEcoursename INSTRUCTORname FROM COURSE INNER JOIN SECT... View the full answer