Oracle 19c SQL

  1. Home
  2. Docs
  3. Oracle 19c SQL
  4. 5 Subqueries In Oracle
  5. Inline Subquery

Inline Subquery

A subquery in the FROM clause is equivalent to a VIEW as it defines a data source for the main SQL statement. Hence a subquery in the FROM clause is called as Inline View.

The data source name is not compulsory in every situation. If you provide an alias for a FROM clause subquery, the name is called correlation name or virtual table name or data source name.

You can select all/any column(s) from Inline subquery.

--Selecting all columns from a Inline Subquery

SELECT *
  FROM (
        SELECT *
          FROM Dept
       );

    DEPTNO DNAME          LOC          
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
--Selecting limited columns from a Inline Subquery

SELECT Ename, Job, Deptno, Sal, HireDate
  FROM (
         SELECT * FROM Emp
       );

ENAME      JOB           DEPTNO        SAL HIREDATE  
---------- --------- ---------- ---------- --------- 
SMITH      CLERK             20        800 17-DEC-80 
ALLEN      SALESMAN          30       1600 20-FEB-81 
WARD       SALESMAN          30       1250 22-FEB-81 
JONES      MANAGER           20       2975 02-APR-81 
MARTIN     SALESMAN          30       1250 28-SEP-81 
BLAKE      MANAGER           30       2850 01-MAY-81 
CLARK      MANAGER           10       2450 09-JUN-81 
SCOTT      ANALYST           20       3000 19-APR-87 
KING       PRESIDENT         10       5000 17-NOV-81 
TURNER     SALESMAN          30       1500 08-SEP-81 
ADAMS      CLERK             20       1100 23-MAY-87 
JAMES      CLERK             30        950 03-DEC-81 
FORD       ANALYST           20       3000 03-DEC-81 
MILLER     CLERK             10       1300 23-JAN-82

14 rows selected.
--You can nest subqueries to any level.

SELECT *
  FROM (
        SELECT *
          FROM (
                SELECT *
                  FROM (
                        SELECT *
                          FROM Dept
                       )
               )
       );

    DEPTNO DNAME          LOC          
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
--Giving alias for a Inline Subquery

SELECT iv.*
  FROM (
        SELECT *
          FROM Dept
       ) iv;

    DEPTNO DNAME          LOC          
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

Q : Get the details of the employees with their department number, salary and their department’s average salary?

Ans:

SELECT Ename, ve.Deptno, Sal, DeptAvgSalary
  FROM Emp pe, (
                SELECT Deptno, AVG(Sal) DeptAvgSalary
                  FROM Emp
                 GROUP BY Deptno
               ) ve
 WHERE pe.Deptno = ve.Deptno;

ENAME          DEPTNO        SAL DEPTAVGSALARY
---------- ---------- ---------- -------------
SMITH              20        800          2175 
ALLEN              30       1600    1566.66667 
WARD               30       1250    1566.66667 
JONES              20       2975          2175 
MARTIN             30       1250    1566.66667 
BLAKE              30       2850    1566.66667 
CLARK              10       2450    2916.66667 
SCOTT              20       3000          2175 
KING               10       5000    2916.66667 
TURNER             30       1500    1566.66667 
ADAMS              20       1100          2175 
JAMES              30        950    1566.66667 
FORD               20       3000          2175 
MILLER             10       1300    2916.66667 

14 rows selected.

When Inline queries are implemented you should always make sure that any data in aggregation should be aliased compulsory. Else that particular data can’t be used for the purpose of operational criteria.

Q : Get the details of the employees with their department number, salary and their department’s average salary and also the difference between their salary and average salary of their department?

Ans:

SELECT Ename, ve.Deptno, Sal, DeptAvgSalary, (DeptAvgSalary - Sal) DIFFSALARY
  FROM Emp pe, (
                SELECT Deptno, ROUND(AVG(Sal), 2) DeptAvgSalary
                  FROM Emp
                 GROUP BY Deptno
               ) ve
 WHERE pe.Deptno = ve.Deptno;

ENAME          DEPTNO        SAL DEPTAVGSAL DIFFSALARY
---------- ---------- ---------- ---------- ----------    
SMITH              20        800       2175       1375   
ALLEN              30       1600    1566.67     -33.33   
WARD               30       1250    1566.67     316.67   
JONES              20       2975       2175       -800   
MARTIN             30       1250    1566.67     316.67   
BLAKE              30       2850    1566.67   -1283.33   
CLARK              10       2450    2916.67     466.67   
SCOTT              20       3000       2175       -825   
KING               10       5000    2916.67   -2083.33   
TURNER             30       1500    1566.67      66.67   
ADAMS              20       1100       2175       1075  
JAMES              30        950    1566.67     616.67   
FORD               20       3000       2175       -825   
MILLER             10       1300    2916.67    1616.67   

14 rows selected.

Q : Get the department number, department name and total number of employees for only those departments in which number of employees recruited in the department is greater than 4?

Ans:

--Approach 1

SELECT T1.Deptno, T1.Dname, Staff_Cnt
  FROM Dept T1, (
                SELECT Deptno, COUNT(*) AS Staff
                  FROM Emp
                 GROUP BY Deptno
               ) T2
 WHERE T1.Deptno = T2.Deptno AND Staff_Cnt > 4;

    DEPTNO DNAME               STAFF
---------- -------------- ----------
        30 SALES                   6
        20 RESEARCH                5

Rows can be filtered within a subquery as well using WHERE for individual rows and HAVING for aggregated rows.

--Approach 2: Filtering aggregated rows using HAVING

SELECT T1.Deptno, T1.Dname, Staff_Cnt
  FROM Dept T1, (
                SELECT Deptno, COUNT(*) AS Staff
                  FROM Emp
                 GROUP BY Deptno
                HAVING COUNT(*) > 4
               ) T2
 WHERE T1.Deptno = T2.Deptno;

    DEPTNO DNAME               STAFF
---------- -------------- ----------
        30 SALES                   6
        20 RESEARCH                5

The same problem can be attempted without a subquery.

--Approach 3

SELECT D.Deptno, D.Dname, COUNT(*) Staff
  FROM Dept D, Emp E
 WHERE E.Deptno = D.Deptno
 GROUP BY D.Deptno, D.Dname
HAVING COUNT(*) > 4;

Q : Write a query to get the number of rows in Emp table, Dept table and Salgrade table? Also display the total rows in all these table, maximum and minimum rows among all these tables?

Ans:

SELECT 
  e.emp_count, 
  d.dept_count, 
  s.salgrade_count,
  (e.emp_count + d.dept_count + s.salgrade_count)       Total_Rows,
  GREATEST(e.emp_count, d.dept_count, s.salgrade_count) Max_Rows
  LEAST(e.emp_count, d.dept_count, s.salgrade_count)    Min_Rows
  FROM ( SELECT count(*) emp_count FROM Emp) e,
       ( SELECT count(*) dept_count FROM Dept) d,
       ( SELECT count(*) salgrade_count FROM Salgrade) s;

 EMP_COUNT DEPT_COUNT SALGRADE_COUNT TOTAL_ROWS
---------- ---------- -------------- ----------
    14        4           5                  23

Q : Get the job wise investments along with the percentage of investment in that job?

Ans:

--Approach 1

SELECT 
    Job, 
    SUM(Sal) "Job Wise Investment",
    ROUND(SUM(Sal)/TotalSal*100, 2) "Investment %"
  FROM Emp, (
           SELECT SUM(Sal) TotalSal FROM Emp
          )
 GROUP BY Job, TotalSal;

JOB       Job Wise Investment Investment %
--------- ------------------- ------------
PRESIDENT                5000        17.23
ANALYST                  6000        20.67
SALESMAN                 5600        19.29
MANAGER                  8275        28.51
CLERK                    4150         14.3
--Approach 2

SELECT 
    IE.Job, 
    IE.JobWiseSum "Job Wise Investment",
    ROUND(IE.JobWiseSum/SUM(Sal)*100, 2) "Investment %"
  FROM Emp E, (
             SELECT Job, SUM(Sal) JobWiseSum
               FROM Emp
              GROUP BY Job
            ) IE
 GROUP BY IE.Job, IE.SalSum;

JOB       Job Wise Investment Investment %
--------- ------------------- ------------
PRESIDENT                5000        17.23
ANALYST                  6000        20.67
SALESMAN                 5600        19.29
MANAGER                  8275        28.51
CLERK                    4150         14.3

Q : Get the total number of employees in each designation along with the employee percentage in that designation?

Ans:

SELECT 
    Job, 
    COUNT(*) Staff, 
    (COUNT(*)/TotalStaff)*100 "STAFF %" 
  FROM Emp, 
       ( SELECT COUNT(*) TotalStaff 
           FROM Emp
       )
 GROUP BY Job, TotalStaff;

JOB            STAFF    STAFF %
--------- ---------- ----------
CLERK              4 28.5714286
PRESIDENT          1 7.14285714
MANAGER            3 21.4285714
SALESMAN           4 28.5714286
ANALYST            2 14.2857143

Q : Get the details of investments in each year along with the percentage of investment in that particular year?

Ans:

SELECT 
    TO_CHAR(HireDate, 'YYYY') Year, 
    SUM(Sal) YearWiseInvestment,
    ROUND(SUM(Sal)*100/IE.TotalSal, 2) "Investment %"
  FROM Emp E, (
                SELECT SUM(Sal) TotalSal FROM Emp
              ) IE
 GROUP BY TO_CHAR(HireDate, 'YYYY'), IE.TotalSal;

YEAR     YEARWISEINVESTMENT Investment %
---- ---------------------- ------------
1987                   4100        14.13
1982                   1300         4.48
1980                    800         2.76
1981                  22825        78.64
Was this article helpful to you? Yes No

How can we help?