Search for question
Question

Individual Exercise 1

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

Course_name

Intro to Computer Science

Data Structures

Name

Discrete Mathematics

Database

GRADE REPORT

PREREQUISITE

Section identifier

85

92

102

112

119

135

Student_number

17

8

Course_number

CS1310

Course_number

MATH2410

CS1310

17

17

CS3320

MATH2410

CS3380

CS3320

MATH2410

CS1310

CS3380

Student number

8

8

8

8

Course_number

CS3380

CS3380

CS3320

Class

1

2

Credit hours

Semester

Fall

Fall

Spring

Fall

Fall

Fall

4

4

3

3

Section_identifier

112

119

85

92

102

135

Major

CS

CS

MATH2410

CS1310

Figure 1.2 Example of a simple database

Year

07

Prerequisite_number

CS3320

07

08

08

08

08

Department

CS

CS

MATH

CS

Instructor

King

Anderson

Knuth

Chang

Anderson

Stone

Grade

B

с

A

A

B

A

Fig: 1