7) Subqueries in SQL

This question is for SQL. Use the Student Course Database below the question to write the codes. Please answer in copyable code so that I may run it in my Oracle system.

Step 1 - Find the names of the departments that offer courses at the junior or senior levels (either one) but not at the freshman level. The course level is the first digit after the prefix; for example, AAAA3yyy is a junior course, and so on. Hint: Begin by creating the outer query - the names of departments that offer courses at the junior or senior levels. Save this query as q74.

Step 2 - Construct the subquery - a list of departments that offer courses at the freshman level. Save the subquery as a view.

Step 3 - Examine both lists of departments. When you have the outer query and the subquery results, recall the original query that you saved (q74) and add the subquery.

Step 5 - Check your result with the department lists you just generated.

Step 6 - Redo the last part of the experimt. You should get the same result.

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)