Hello, we are working on Subqueries versus Joins. Please assist with the followi

Question

Hello, we are working on Subqueries versus Joins. Please assist with the following question. I included the tables we are working on. Thank you.

Find the names of 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. Therefore, 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.” Then, construct the subquery—a list of departments that offer courses at the freshman level. Save the153 subquery as a view. 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. Check your result with the department lists you just generated. Redo the last part of the experiment with your view. You should get the same result.

SOL> DESCRIBE COURSE Name Nul1 Type COURSE NAME COURSE NUMBER CREDIT HOURS OFFERING DE VARCHAR2 (20) NOT NULL VARCHAR2 (8) NUMBER (38) VARCHAR2 (4) SQL> DESCRIBE section Name Null? Type SECTION_ID COURSE NUM SEMESTER YEAR INSTRUCTOR BLDG ROOM NOT NULL NUMBER (38) VARCHAR2 (8) VARCHAR2 (6) CHAR (2) VARCHAR2 (10) NUMBER(38) NUMBER (38) SQL> DESCRIBE grade Name Null? Type STUDENT NUMBER SECTION ID GRADE NOT NULL NUMBER (38) NOT NULL NUMBER (38) CHAR (1) SQL> DESCRIBE Student Name Nul1? Type STNO SNAME MAJOR CLASS BDATE NOT NULL NUMBER (38) VARCHAR2 (20) VARCHAR2 (4) NUMBER (38) DATE

Explanation / Answer

Hi,

Below are the steps that are asked to perform-

Ans-
Q74-
//This query is for junior course-
SELECT OFFERING_DEPT FROM COURSE WHERE SUBSTR(OFFERING_DEPT,4,1)='3';

Q153
//This query is for freshman level course. I am assuming code '1' for freshman level
SELECT OFFERING_DEPT FROM COURSE WHERE SUBSTR(OFFERING_DEPT,4,1)='1';

//Converting the query as view
create view vw1 as
SELECT OFFERING_DEPT FROM COURSE WHERE SUBSTR(OFFERING_DEPT,4,1)='1';

Merging the Q153 with Q74
SELECT OFFERING_DEPT FROM COURSE WHERE OFFERING_DEPT in(SELECT OFFERING_DEPT FROM COURSE WHERE SUBSTR(OFFERING_DEPT,4,1)='1')

Thanks and Regards,

Vinay Singh

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.