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

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