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.

TABLE OF CONTENTS:
  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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

SELECT
    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
  (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:

SELECT
  empno,
  ename,
  sal AS salary,
  ROUND((sal -(SELECT AVG(sal) FROM emp)),2) AS avgcompare,
  CASE 
    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:

EMPNO   ENAME      SALARY   AVGCOMPARE   PAYING

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

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 ARTICLES

- 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: