Home arrow Oracle arrow Page 6 - Inserting Sub-Queries in SELECT Statements in Oracle

Sub-Queries as (or part of) columns in Oracle SELECT statements - Oracle

This is the second article in a series concentrating on working with sub-queries in Oracle. My previous article (first in this series) explained the “dual” table in detail and gave a few practical examples of sub-queries. In this article, I shall introduce sub-queries at various interesting places within the same SELECT statement.

  1. Inserting Sub-Queries in SELECT Statements in Oracle
  2. Sub-Queries returning single and multiple values in Oracle
  3. Sub-Queries as part of the BETWEEN operator in Oracle
  4. Derived tables (or inline views) with Sub-Queries in Oracle
  5. Sub-Queries with CASE structure in Oracle SELECT statements
  6. Sub-Queries as (or part of) columns in Oracle SELECT statements
By: Jagadish Chatarji
Rating: starstarstarstarstar / 62
November 14, 2006

print this article



Before getting into sub-queries as part of columns, let us look at the following small query:

    MAX(sal) AS highest,
    MIN(sal) AS least,
    COUN(*) AS employees,
    SUM(sal) AS total
FROM emp

Everyone can easily understand that the above query returns only one row containing four values of aggregation. Let us rewrite the above query using sub-queries to get the same output.

  (SELECT MAX(sal) FROM emp) AS highest,
  (SELECT MIN(sal) FROM emp) AS least,
  (SELECT COUNT(*) FROM emp) AS employees,
  (SELECT SUM(sal) FROM emp) AS total
FROM dual

You can observe that I replaced all aggregate functions with sub-queries!  Another important issue to concentrate on in the above query is the “dual” table.  As the sub-queries in the above statement are working individually by themselves, I need not work with any table and thus I used the “dual” table.  If you want to learn more about the “dual” table, please go through my first article in this same series.

Now, let us look at an interesting query which deals with sub-queries at both the column level and the CASE level.  The following is the query:

  sal AS salary,
  ROUND((sal -(SELECT AVG(sal) FROM emp)),2) AS avgcompare,
    WHEN sal >= (SELECT AVG(sal) FROM emp) THEN 'HIGH'
    ELSE 'LOW'
  END AS paying
FROM emp

The following is the sample output of the above query:


------- ---------- -------- ------------ ------

7839    KING       5000     2926.79      HIGH  

7698    BLAKE      2850     776.79       HIGH  



7654    MARTIN     1250     -823.21      LOW   

7499    ALLEN      1600     -473.21      LOW   


Any bugs, doubts, suggestions, feedback etc. are highly appreciated at http://jagchat.spaces.live.com

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: