7-2

Please write the SQL for the following qustion using the Student Course Database listed below the question. Please also answer the 'WHY' at the end of the question.

1) Find all students who took a course offered by the accounting department. List the student name and student number, the course name, and the grade in that course. (Hint: Begin with Department_to_major and use an appropriate WHERE.) Note that this task cannot be done with a multilevel subquery. Why?

The Student Course Database

Student

stno NOT NULL NUMBER (3)

PRIMARY KEY NOT NULL

sname VARCHAR2(20)

major CHAR(4)

class NUMBER(1)

bdate DATE

Grade_report

student_number NOT NULL NUMBER(3)

section_id NOT NULL NUMBER(6)

grade CHAR(1)

PRIMARY KEY (student_number, section_id)

Section

section_id NOT NULL NUMBER(6)

PRIMARY KEY NOT NULL

course_num CHAR(8)

semester VARCHAR2(6)

year CHAR(2)

instructor CHAR(10)

bldg. NUMBER(3)

room NUMBER(3)

Department_to_major

dcode NOT NULL CHAR(4)

PRIMARY KEY NOT NULL

dname CHAR(20)

Course

course_name CHAR(20)

course_number NOT NULL CHAR(8)

PRIMARY KEY NOT NULL

credit_hours NUMBER(2)

offering_dept CHAR(4)

Room

bldg NOT NULL NUMBER(3)

room NOT NULL NUMBER(3)

capacity NUMBER(4)

ohead CHAR(1)

PRIMARY KEY (bldg., room)

Prereq

course_number CHAR(8)

prereq CHAR(8)

PRIMARY KEY (course_number,prereq)