Use sub query as a virtual table : Subquery Basics « Subquery « Oracle PL / SQL






Use sub query as a virtual table

    


SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /

Table created.

SQL>
SQL> -- prepare data
SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2334.78, 'Vancouver','Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 2334.78,'New York',  'Tester')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 2334.78,'New York',  'Manager')
  3  /

1 row created.

SQL> insert into Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                values('08','James',    'Cat',     to_date('19960917','YYYYMMDD'), to_date('20020415','YYYYMMDD'), 2334.78,'Vancouver', 'Tester')
  3  /

1 row created.

SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
  2  /

ID   FIRST_NAME LAST_NAME  START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01   Jason      Martin     25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison     Mathews    21-MAR-76 21-FEB-86    2334.78 Vancouver  Tester
03   James      Smith      12-DEC-78 15-MAR-90    2334.78 Vancouver  Tester
04   Celia      Rice       24-OCT-82 21-APR-99    2334.78 Vancouver  Manager
05   Robert     Black      15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda      Green      30-JUL-87 04-JAN-96    2334.78 New York   Tester
07   David      Larry      31-DEC-90 12-FEB-98    2334.78 New York   Manager
08   James      Cat        17-SEP-96 15-APR-02    2334.78 Vancouver  Tester

8 rows selected.

SQL>
SQL>
SQL>
SQL> -- set into a virtual table. Such a solution could look like this:
SQL>
SQL> SELECT id "Emp #", First_name "Name", salary "Salary", ROWNUM rank
  2  FROM
  3    (SELECT id, first_name, salary FROM employee ORDER BY salary);

Emp  Name           Salary       RANK
---- ---------- ---------- ----------
01   Jason         1234.56          1
03   James         2334.78          2
08   James         2334.78          3
05   Robert        2334.78          4
06   Linda         2334.78          5
07   David         2334.78          6
02   Alison        2334.78          7
04   Celia         2334.78          8

8 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee;

Table dropped.

SQL>
           
         
    
    
    
  








Related examples in the same category

1.Compare with data from subquery
2.An example of a nested three-level subquery
3.If an inner query returns a NULL, the outer query also returns NULL
4.Working with multi-column subqueries
5.Use aggregate function in sub query
6.Sub query: 'SELECT 1 FROM dept d'
7.Using the EXISTS and NOT EXISTS operators
8.Writing Single Row Subqueries
9.Compare with the sub query result
10.Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query
11.Subqueries in a FROM Clause (Inline Views)
12.Single Row Subqueries May Return a Maximum of One Row
13.Subqueries May Not Contain an ORDER BY Clause
14.Sub query with table join
15.Writing Multiple Column Subqueries with table join
16.Subquery just returns a literal value: improve performance of your query
17.EXISTS typically offers better performance than IN with subqueries
18.NVL() is used to convert null in correlated query
19.Writing Nested Subqueries
20.subqueries in the SELECT column list (New Way)
21.subqueries in the SELECT column list (Old way)
22.Not equals and subquery
23.Greater than average salary
24.Greater than max(salary)
25.Larger than value from subquery
26.Subquery in select statement
27.Subquery in where clause
28.Subqueries That Return Multiple Results
29.Subqueries in the WHERE Clause: equals
30.Subqueries in the WHERE Clause: less than
31.Simple Subqueries in select statement
32.Single-row subqueries return only one row of result.
33.The parent query of a single-row subquery can return more than one row.
34.Born after employee 4 was born
35.inline view: What percentage of these items exist in each bin selected