Inserting Sub-Queries in SELECT Statements in Oracle - Sub-Queries as (or part of) columns in Oracle SELECT statements
(Page 6 of 6 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 36 
