How to Use Simple Subqueries

A simple subquery is a select statement that is contained within an outer select statement. The simple subquery is executed one time and its result is available within the where clause of the outer select.

To learn how to use simple subqueries, follow these steps:

  1. You'll need to download and install Oracle 12c. The instructions for the setup can be found in How to use sample schemas. Follow steps 1 through 4 in this topic before proceeding to the next step.
  2. We begin by selecting the employees that earn a salary that is greater than the average salary of the Employees table. Execute the following SQL select statement:
    Display employees with salary greater than average salary
    Note that I have displayed the first part of the output that contains 51 employees overall. I have highlighted the actual subquery. The subquery is within parentheses and returns the result of the avg aggregate function.
  3. Now let's display the employees that earn the maximum salary:
    Display employees with salary equal to maximum salary
    The subquery is enclosed within parentheses and returns the result of the max aggregate function.
Author: Stephen Withrow

Stephen has over 30 years of experience in training, development, and consulting in a variety of technology areas including Python, Java, C, C++, XML, JavaScript, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen has a degree in Computer Science and Physics from Florida State University.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.