subqueries in the SELECT column list (New Way) : Subquery Basics « Subquery « Oracle PL / SQL






subqueries in the SELECT column list (New Way)

     
SQL> --
SQL>
SQL> create table employee(
  2           emp_no                 integer         primary key
  3          ,lastname               varchar2(20)    not null
  4          ,firstname              varchar2(15)    not null
  5          ,midinit                varchar2(1)
  6          ,street                 varchar2(30)
  7          ,city                   varchar2(20)
  8          ,state                  varchar2(2)
  9          ,zip                    varchar2(5)
 10          ,zip_4                  varchar2(4)
 11          ,area_code              varchar2(3)
 12          ,phone                  varchar2(8)
 13          ,salary                 number(3)
 14          ,birthdate              date
 15          ,hiredate               date
 16          ,title                  varchar2(20)
 17          ,dept_no                integer
 18          ,mgr                    integer
 19          ,region                 number
 20          ,division               number
 21          ,total_sales            number
 22  );

Table created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (1,'Anderson','Nancy','N','33 Ave','London','NY','11111','1111','212','234-1111',3.75,'21-mar-1927','1-feb-1947','Sales Manager',2,null,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (2,'Last','First','F','12 Ave','Paris','CA','22222','2222','111','867-2222',7.75,'14-feb-1976','15-mar-1985','Sales Clerk',2,1,100,10,10000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (3,'Wash','Georgia','G','1 Street14','Barton','NJ','33333','3333','214','340-3333',11.50,'2-jul-1977','21-apr-2004','Designer',1,2,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (4,'Bush','Dave','D','56 Street','Island','RI','44444','4444','215','777-4444',21.65,'15-may-1945','2-aug-1975','Designer',1,2,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (5,'Will','Robin','W','56 Street','Island','MA','55555','5555','216','777-5555',24.65,'10-dec-1980','2-aug-2007','Designer',1,5,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (6,'Pete','Mona','M','13 Ave','York','MO','66666','6666','217','111-6666',9,'14-feb-1966','15-mar-1985','Sales Clerk',2,5,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (7,'Roke','John','J','67 Ave','New York','BC','77777','7777','218','122-7777',10.00,'14-jun-1955','15-mar-1975','Accountant',3,2,100,10,40000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2               values (8,'Horry','Tedi','T','1236 Lane','Newton','NY','88888','8888','219','222-8888',13.00,'10-jun-1955','15-aug-1985','Sales Representative',3,2,100,10,50000);

1 row created.

SQL> insert into employee(emp_no, lastname, firstname, midinit, street, city, state, zip,zip_4, area_code, phone, salary, birthdate, hiredate,title, dept_no, mgr, region, division, total_sales)
  2                values (9,'Bar','Candi','C','400 East Street','Yorken','NY','99999','9999','220','321-9999',12.00,'10-oct-1933','15-jan-1969','Sales Representative',3,5,100,10,35000);

1 row created.

SQL>
SQL> select * from employee;
    EMP_NO LASTNAME             FIRSTNAME       M
---------- -------------------- --------------- -
STREET                         CITY                 ST ZIP   ZIP_ ARE
------------------------------ -------------------- -- ----- ---- ---
PHONE        SALARY BIRTHDATE            HIREDATE
-------- ---------- -------------------- --------------------
TITLE                   DEPT_NO        MGR     REGION   DIVISION
-------------------- ---------- ---------- ---------- ----------
TOTAL_SALES
-----------
         1 Anderson             Nancy           N
33 Ave                         London               NY 11111 1111 212
234-1111          4 21-MAR-1927 00:00:00 01-FEB-1947 00:00:00
Sales Manager                 2                   100         10
      40000

         2 Last                 First           F
12 Ave                         Paris                CA 22222 2222 111
867-2222          8 14-FEB-1976 00:00:00 15-MAR-1985 00:00:00
Sales Clerk                   2          1        100         10
      10000

         3 Wash                 Georgia         G
1 Street14                     Barton               NJ 33333 3333 214
340-3333         12 02-JUL-1977 00:00:00 21-APR-2004 00:00:00
Designer                      1          2        100         10
      40000

         4 Bush                 Dave            D
56 Street                      Island               RI 44444 4444 215
777-4444         22 15-MAY-1945 00:00:00 02-AUG-1975 00:00:00
Designer                      1          2        100         10
      40000

         5 Will                 Robin           W
56 Street                      Island               MA 55555 5555 216
777-5555         25 10-DEC-1980 00:00:00 02-AUG-2007 00:00:00
Designer                      1          5        100         10
      40000

         6 Pete                 Mona            M
13 Ave                         York                 MO 66666 6666 217
111-6666          9 14-FEB-1966 00:00:00 15-MAR-1985 00:00:00
Sales Clerk                   2          5        100         10
      40000

         7 Roke                 John            J
67 Ave                         New York             BC 77777 7777 218
122-7777         10 14-JUN-1955 00:00:00 15-MAR-1975 00:00:00
Accountant                    3          2        100         10
      40000

         8 Horry                Tedi            T
1236 Lane                      Newton               NY 88888 8888 219
222-8888         13 10-JUN-1955 00:00:00 15-AUG-1985 00:00:00
Sales Representative          3          2        100         10
      50000

         9 Bar                  Candi           C
400 East Street                Yorken               NY 99999 9999 220
321-9999         12 10-OCT-1933 00:00:00 15-JAN-1969 00:00:00
Sales Representative          3          5        100         10
      35000


9 rows selected.

SQL>
SQL> SELECT e.lastname, e.salary,
  2     (SELECT avg(salary) FROM employee
  3      WHERE dept_no = e.dept_no) avg_sal_dept
  4  FROM employee e
  5  order by 1
  6  /
LASTNAME                 SALARY AVG_SAL_DEPT
-------------------- ---------- ------------
Anderson                      4            7
Bar                          12   11.6666667
Bush                         22   19.6666667
Horry                        13   11.6666667
Last                          8            7
Pete                          9            7
Roke                         10   11.6666667
Wash                         12   19.6666667
Will                         25   19.6666667

9 rows selected.

SQL>
SQL> drop table employee;

Table dropped.

SQL>
SQL> --

   
    
    
    
  








Related examples in the same category

1.Use sub query as a virtual table
2.Compare with data from subquery
3.An example of a nested three-level subquery
4.If an inner query returns a NULL, the outer query also returns NULL
5.Working with multi-column subqueries
6.Use aggregate function in sub query
7.Sub query: 'SELECT 1 FROM dept d'
8.Using the EXISTS and NOT EXISTS operators
9.Writing Single Row Subqueries
10.Compare with the sub query result
11.Subqueries in a HAVING Clause: Uses a subquery in the HAVING clause of the outer query
12.Subqueries in a FROM Clause (Inline Views)
13.Single Row Subqueries May Return a Maximum of One Row
14.Subqueries May Not Contain an ORDER BY Clause
15.Sub query with table join
16.Writing Multiple Column Subqueries with table join
17.Subquery just returns a literal value: improve performance of your query
18.EXISTS typically offers better performance than IN with subqueries
19.NVL() is used to convert null in correlated query
20.Writing Nested Subqueries
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