RDBMS

What is a Subquery?

A subquery is a query within another query. The outer query is the main query and the inner one the subquery. A normal subquery is executed first and its result is passed to the main query. Because a subquery usually returns a list of values, the IN operator is used very often.

select name, qual from faculty
where fcode in ( select fcode from course_faculty where ccode = 'ora' );

select * from batches
where fcode = ( select fcode from faculty where name = 'Kevin Loney' );   -- single value

select * from faculty
where fcode not in ( select fcode from batches
                     where months_between(sysdate, stdate) <= 3 );

Multiple, Nested and Multi-column Subqueries

• Multiple subqueries – a main query can receive values from more than one subquery (combined with AND / OR).
• Nested subqueries – a subquery can depend on another subquery. Oracle allows nesting up to 16 levels; more than 3 levels is not recommended for performance.
• Multi-column – a subquery may return several columns that are compared with several values: where (ccode, enddate - stdate) in (select ...).

select rollno, name, bcode from students
where bcode in ( select bcode from batches
                 where fcode in ( select fcode from faculty where qual like '%MS%' ) );

select * from batches
where (ccode, enddate - stdate) in
      ( select ccode, max(enddate - stdate) from batches group by ccode );

select ccode, name, fee from courses
where fee = ( select max(fee) from courses );

Subqueries in DML and DDL

update courses set fee = fee * 1.1
where ccode in ( select ccode from batches group by ccode having count(*) > 5 );

delete from batches where bcode not in ( select bcode from students );

update batches set fcode = ( select fcode from batches where bcode = 'b1' )
where bcode = 'b7';                                   -- subquery supplies the new value

create table new_batches as
select bcode, ccode, fcode, stdate, timing from batches where stdate > sysdate;

insert into new_batches
select bcode, ccode, fcode, stdate, timing from batches where stdate > sysdate;

• CREATE TABLE … AS SELECT creates the table with the structure of the query; only NOT NULL constraints are copied (other constraints such as the primary key are not).
• For INSERT … SELECT the order and type of columns must match the target table (or list the columns).
• The notes show how to “rename” a column by creating a new table with an alias, dropping the old table, using RENAME newproducts TO products and re-adding constraints.

New in Oracle8i

insert into students values ( (select max(rollno) + 1 from students),
                              'b7', 'Robert Lafore', 'm', sysdate, null, null );

select ccode, name, fee
from ( select * from courses order by fee desc )
where rownum < 3;                                     -- top-2 fees (top-N analysis)

Correlated Subquery

A correlated subquery receives a value from the main query, uses it (usually in its condition) and returns the result to the main query. It is recognised by a main-query column used inside the subquery, and it is executed once for each row of the main query.

select * from batches b1
where enddate - stdate >= ( select avg(enddate - stdate)
                            from batches
                            where ccode = b1.ccode );      -- longer than course average

select name, fee from courses c1
where 2 = ( select count(*) from courses where fee > c1.fee );   -- 3rd highest fee

EXISTS, NOT EXISTS, ANY and ALL

• EXISTS is true if the subquery returns any row; NOT EXISTS is true if it returns none. They do not compare values – what the subquery selects does not matter. They are used with correlated subqueries.
• ANY – the condition is true if it holds for any value in the list; ALL – it must hold for all values. Syntax: value operator ANY|ALL (list) with =, !=, >, >=, <, <=.

select ccode, name, fee from courses
where not exists ( select * from batches where ccode = courses.ccode );   -- no batch yet

select name from courses
where fee > any ( select fee from courses where duration = 25 );
-- same as: where fee > ( select min(fee) from courses where duration = 25 )

Chapter 11 Notes (PDF)

Question / Answer (15 questions)

Moderate-level questions based on the Chapter 11 notes (PDF). Click a question to see the answer.

MCQ Test : Check Your Understanding (5 questions)

Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.

Bibliography/References

The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.

1. Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
2. Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
3. Oracle Corporation. (n.d.). Oracle Database Development Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/adfns/
4. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.
5. Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson.