7
SQL Statements (continued)

SELECT and Subqueries

Syntax

subquery::=

table_expression_clause::=

sample_clause::=

with_clause::=

table_collection_expression::=

where_clause::=

outer_join::=

hierarchical_query_clause::=

group_by_clause::=

order_by_clause::=

for_update_clause::=

Purpose

To retrieve data from one or more tables, object tables, views, object views, or materialized views. For general information on queries and subqueries, see "Queries and Subqueries".

Note:
If the result (or part of the result) of a SELECT statement is equivalent to an existing materialized view, Oracle may use the materialized view in place of one or more tables specified in the SELECT statement. This substitution is called query rewrite, and takes place only if cost optimization is enabled and the QUERY_REWRITE_ENABLED parameter is set to TRUE. To determine whether query write has occurred, use the EXPLAIN PLAN statement (see "EXPLAIN PLAN"). For more information on materialized views and query rewrite, see Oracle8i Tuning.

Prerequisites

For you to select data from a table or materialized view, the table or materialized view must be in your own schema or you must have the SELECT privilege on the table or materialized view.

For you to select rows from the base tables of a view,

You must have the SELECT privilege on the view, and
Whoever owns the schema containing the view must have the SELECT privilege on the base tables.

The SELECT ANY TABLE system privilege also allows you to select data from any table or any materialized view or any view's base table.

Keywords and Parameters

Examples

Simple Query Examples

The following statement selects rows from the EMP table with the department number of 30:

SELECT * 
    FROM emp 
    WHERE deptno = 30;

The following statement selects the name, job, salary and department number of all employees except sales people from department number 30:

SELECT ename, job, sal, deptno 
    FROM emp 
    WHERE NOT (job = 'SALESMAN' AND deptno = 30);

The following statement selects from subqueries in the FROM clause and gives departments' total employees and salaries as a decimal value of all the departments:

SELECT a.deptno "Department", 
       a.num_emp/b.total_count "%Employees", 
       a.sal_sum/b.total_sal "%Salary"
  FROM
 (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum
    FROM scott.emp
    GROUP BY deptno) a,
 (SELECT COUNT(*) total_count, SUM(sal) total_sal
    FROM scott.emp) b ;

PARTITION Example

You can select rows from a single partition of a partitioned table by specifying the keyword PARTITION in the FROM clause. This SQL statement assigns an alias for and retrieves rows from the NOV98 partition of the SALES table:

SELECT * FROM sales PARTITION (nov98) s
   WHERE s.amount_of_sale > 1000;

The following example selects rows from the SALES table for sales earlier than a specified date:

SELECT * FROM sales
   WHERE sale_date < TO_DATE('1998-06-15', 'YYYY-MM-DD');

SAMPLE example:

The following query estimates the number of employees in the EMP table:

SELECT COUNT(*) * 100 FROM emp SAMPLE BLOCK (1);

GROUP BY Examples

To return the minimum and maximum salaries for each department in the employee table, issue the following statement:

SELECT deptno, MIN(sal), MAX (sal)
     FROM emp
     GROUP BY deptno;

DEPTNO     MIN(SAL)   MAX(SAL)  
---------- ---------- ----------
        10       1300       5000
        20        800       3000
        30        950       2850

To return the minimum and maximum salaries for the clerks in each department, issue the following statement:

SELECT deptno, MIN(sal), MAX (sal)
     FROM emp
     WHERE job = 'CLERK'
     GROUP BY deptno;

DEPTNO     MIN(SAL)   MAX(SAL)  
---------- ---------- ----------
        10       1300      1300
        20        800      1100
        30        950       950

CUBE Example

To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query:

SELECT DECODE(GROUPING(dname), 1, 'All Departments',
      dname) AS dname,
   DECODE(GROUPING(job), 1, 'All Jobs', job) AS job,
   COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal"
   FROM emp, dept 
   WHERE dept.deptno = emp.deptno
   GROUP BY CUBE (dname, job);

DNAME           JOB       Total Empl Average Sa 
--------------- --------- ---------- ---------- 
ACCOUNTING      CLERK              1      15600 
ACCOUNTING      MANAGER            1      29400 
ACCOUNTING      PRESIDENT          1      60000 
ACCOUNTING      All Jobs           3      35000 
RESEARCH        ANALYST            2      36000 
RESEARCH        CLERK              2      11400 
RESEARCH        MANAGER            1      35700 
RESEARCH        All Jobs           5      26100 
SALES           CLERK              1      11400 
SALES           MANAGER            1      34200 
SALES           SALESMAN           4      16800 
SALES           All Jobs           6      18800 
All Departments ANALYST            2      36000 
All Departments CLERK              4      12450 
All Departments MANAGER            3      33100 
All Departments PRESIDENT          1      60000 
All Departments SALESMAN           4      16800 
All Departments All Jobs          14 24878.5714

Hierarchical Query Examples

The following CONNECT BY clause defines a hierarchical relationship in which the EMPNO value of the parent row is equal to the MGR value of the child row:

CONNECT BY PRIOR empno = mgr;

In the following CONNECT BY clause, the PRIOR operator applies only to the EMPNO value. To evaluate this condition, Oracle evaluates EMPNO values for the parent row and MGR, SAL, and COMM values for the child row:

CONNECT BY PRIOR empno = mgr AND sal > comm;

To qualify as a child row, a row must have a MGR value equal to the EMPNO value of the parent row and it must have a SAL value greater than its COMM value.

HAVING Example

To return the minimum and maximum salaries for the clerks in each department whose lowest salary is below $1,000, issue the next statement:

SELECT deptno, MIN(sal), MAX (sal)
     FROM emp
     WHERE job = 'CLERK'
     GROUP BY deptno
     HAVING MIN(sal) < 1000;

DEPTNO     MIN(SAL)   MAX(SAL)  
---------- ---------- ----------
        20        800       1100
        30        950        950

ORDER BY Examples

To select all salesmen's records from EMP, and order the results by commission in descending order, issue the following statement:

SELECT * 
    FROM emp 
    WHERE job = 'SALESMAN' 
    ORDER BY comm DESC;

To select the employees from EMP ordered first by ascending department number and then by descending salary, issue the following statement:

SELECT ename, deptno, sal 
    FROM emp 
    ORDER BY deptno ASC, sal DESC;

To select the same information as the previous SELECT and use the positional ORDER BY notation, issue the following statement:

SELECT ename, deptno, sal 
    FROM emp 
    ORDER BY 2 ASC, 3 DESC;

FOR UPDATE Examples

The following statement locks rows in the EMP table with clerks located in New York and locks rows in the DEPT table with departments in New York that have clerks:

SELECT empno, sal, comm 
    FROM emp, dept 
    WHERE job = 'CLERK' 
        AND emp.deptno = dept.deptno 
        AND loc = 'NEW YORK' 
    FOR UPDATE;

The following statement locks only those rows in the EMP table with clerks located in New York. No rows are locked in the DEPT table:

SELECT empno, sal, comm 
    FROM emp, dept 
    WHERE job = 'CLERK' 
        AND emp.deptno = dept.deptno 
        AND loc = 'NEW YORK' 
    FOR UPDATE OF emp.sal;

LOB Locking Example

The following example uses a SELECT ... FOR UPDATE statement to lock a row containing a LOB prior to updating the LOB value.

INSERT INTO t_table VALUES (1, 'abcd'); 

COMMIT; 
   DECLARE 
     num_var      NUMBER; 
     clob_var     CLOB;
     clob_locked  CLOB;
     write_amount NUMBER; 
     write_offset NUMBER;
     buffer       VARCHAR2(20) := 'efg';

   BEGIN 
     SELECT clob_col INTO clob_locked FROM t_table 
     WHERE num_col = 1 FOR UPDATE; 

  write_amount := 3;
  dbms_lob.write(clob_locked, write_amount, write_offset, buffer);
END;

Table Collection Examples

You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the table_expression_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression, the collection expression must be a subquery that selects the table's nested table column. The examples that follow are based on this scenario:

CREATE TYPE ProjectType AS OBJECT( 
    pno   NUMBER, 
    pname CHAR(31), 
    budget NUMBER); 
CREATE TYPE ProjectSet AS TABLE OF ProjectType; 
 
CREATE TABLE Dept (dno NUMBER, dname CHAR(31), projs ProjectSet) 
    NESTED TABLE projs STORE AS  
        ProjectSetTable ((Primary Key(Nested_Table_Id, pno)) 
ORGANIZATION 
INDEX COMPRESS 1); 
 
INSERT INTO Dept VALUES (1, 'Engineering', ProjectSet());

This example inserts into the 'Engineering' department's 'projs' nested table:

INSERT INTO TABLE(SELECT d.projs  
                  FROM   Dept d 
                  WHERE  d.dno = 1) 
  VALUES (1, 'Collection Enhancements', 10000);

This example updates the 'Engineering' department's 'projs' nested table:

UPDATE TABLE(SELECT d.projs 
             FROM   Dept d 
             WHERE  d.dno = 1) p 
  SET  p.budget = p.budget + 1000;

This example deletes from the 'Engineering' department's 'projs' nested table

DELETE TABLE(SELECT d.projs 
             FROM   Dept d 
             WHERE  d.dno = 1) p 
 WHERE p.budget > 100000;

Subquery Examples

To determine who works in Taylor's department, issue the following statement:

SELECT ename, deptno 
    FROM emp 
    WHERE deptno = 
        (SELECT deptno 
            FROM emp 
            WHERE ename = 'TAYLOR');

To give all employees in the EMP table a 10% raise if they have not already been issued a bonus (if they do not appear in the BONUS table), issue the following statement:

UPDATE emp 
    SET sal = sal * 1.1
    WHERE empno NOT IN (SELECT empno FROM bonus);

To create a duplicate of the DEPT table named NEWDEPT, issue the following statement:

CREATE TABLE newdept (deptno, dname, loc) 
    AS SELECT deptno, dname, loc FROM dept;

WITH CHECK OPTION Example

The following statement is legal even though the second value violates the condition of the subquery where_clause:

INSERT INTO 
   (SELECT ename, deptno FROM emp WHERE deptno < 10)
    VALUES ('Taylor', 20);

However, the following statement is illegal because of the WITH CHECK OPTION clause:

INSERT INTO 
   (SELECT ename, deptno FROM emp
       WHERE deptno < 10
       WITH CHECK OPTION)
    VALUES ('Taylor', 20);

Equijoin Examples

This equijoin returns the name and job of each employee and the number and name of the department in which the employee works:

SELECT ename, job, dept.deptno, dname
     FROM emp, dept
     WHERE emp.deptno = dept.deptno;

ENAME      JOB       DEPTNO     DNAME         
---------- --------- ---------- --------------
CLARK      MANAGER           10 ACCOUNTING    
KING       PRESIDENT         10 ACCOUNTING    
MILLER     CLERK             10 ACCOUNTING    
SMITH      CLERK             20 RESEARCH      
ADAMS      CLERK             20 RESEARCH      
FORD       ANALYST           20 RESEARCH      
SCOTT      ANALYST           20 RESEARCH      
JONES      MANAGER           20 RESEARCH      
ALLEN      SALESMAN          30 SALES         
BLAKE      MANAGER           30 SALES         
MARTIN     SALESMAN          30 SALES         
JAMES      CLERK             30 SALES         
TURNER     SALESMAN          30 SALES         
WARD       SALESMAN          30 SALES

You must use a join to return this data because employee names and jobs are stored in a different table than department names. Oracle combines rows of the two tables according to this join condition:

emp.deptno = dept.deptno

The following equijoin returns the name, job, department number, and department name of all clerks:

SELECT ename, job, dept.deptno, dname
     FROM emp, dept
     WHERE emp.deptno = dept.deptno
     AND job = 'CLERK';

ENAME      JOB       DEPTNO     DNAME         
---------- --------- ---------- --------------
MILLER     CLERK             10 ACCOUNTING    
SMITH      CLERK             20 RESEARCH      
ADAMS      CLERK             20 RESEARCH      
JAMES      CLERK             30 SALES

This query is identical to the preceding example, except that it uses an additional where_clause condition to return only rows with a JOB value of 'CLERK'.

Self Join Example

The following query uses a self join to return the name of each employee along with the name of the employee's manager:

SELECT e1.ename||' works for '||e2.ename 
"Employees and their Managers" 
    FROM emp e1, emp e2   WHERE e1.mgr = e2.empno; 

Employees and their Managers   
-------------------------------
BLAKE works for KING           
CLARK works for KING           
JONES works for KING           
FORD works for JONES           
SMITH works for FORD           
ALLEN works for BLAKE          
WARD works for BLAKE           
MARTIN works for BLAKE         
SCOTT works for JONES          
TURNER works for BLAKE         
ADAMS works for SCOTT          
JAMES works for BLAKE          
MILLER works for CLARK

The join condition for this query uses the aliases E1 and E2 for the EMP table:

e1.mgr = e2.empno

Outer Join Examples

This query uses an outer join to extend the results of the Equijoin example above:

SELECT ename, job, dept.deptno, dname 
    FROM emp, dept 
    WHERE emp.deptno (+) = dept.deptno; 

ENAME      JOB       DEPTNO     DNAME
---------- --------- ---------- --------------
CLARK      MANAGER           10 ACCOUNTING
KING       PRESIDENT         10 ACCOUNTING
MILLER     CLERK             10 ACCOUNTING
SMITH      CLERK             20 RESEARCH
ADAMS      CLERK             20 RESEARCH
FORD       ANALYST           20 RESEARCH
SCOTT      ANALYST           20 RESEARCH
JONES      MANAGER           20 RESEARCH
ALLEN      SALESMAN          30 SALES
BLAKE      MANAGER           30 SALES
MARTIN     SALESMAN          30 SALES
JAMES      CLERK             30 SALES
TURNER     SALESMAN          30 SALES
WARD       SALESMAN          30 SALES
                             40 OPERATIONS

In this outer join, Oracle returns a row containing the OPERATIONS department even though no employees work in this department. Oracle returns NULL in the ENAME and JOB columns for this row. The join query in this example selects only departments that have employees.

The following query uses an outer join to extend the results of the preceding example:

SELECT ename, job, dept.deptno, dname 
    FROM emp, dept 
    WHERE emp.deptno (+) = dept.deptno 
        AND job (+) = 'CLERK'; 

ENAME      JOB       DEPTNO     DNAME
---------- --------- ---------- --------------
MILLER     CLERK             10 ACCOUNTING
SMITH      CLERK             20 RESEARCH
ADAMS      CLERK             20 RESEARCH
JAMES      CLERK             30 SALES
                             40 OPERATIONS

In this outer join, Oracle returns a row containing the OPERATIONS department even though no clerks work in this department. The (+) operator on the JOB column ensures that rows for which the JOB column is NULL are also returned. If this (+) were omitted, the row containing the OPERATIONS department would not be returned because its JOB value is not 'CLERK'.

This example shows four outer join queries on the CUSTOMERS, ORDERS, LINEITEMS, and PARTS tables. These tables are shown here:

SELECT custno, custname 
    FROM customers; 

CUSTNO     CUSTNAME 
---------- -------------------- 
1          Angelic Co. 
2          Believable Co.         
3          Cabels R Us 

SELECT orderno, custno, 
    TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE" 
    FROM orders; 

ORDERNO    CUSTNO     ORDERDATE 
---------- ---------- ----------- 
      9001          1 OCT-13-1998 
      9002          2 OCT-13-1998 
      9003          1 OCT-20-1998 
      9004          1 OCT-27-1998 
      9005          2 OCT-31-1998 

SELECT orderno, lineno, partno, quantity 
    FROM lineitems; 

ORDERNO    LINENO     PARTNO     QUANTITY 
---------- ---------- ---------- ---------- 
      9001          1        101         15 
      9001          2        102         10 
      9002          1        101         25 
      9002          2        103         50 
      9003          1        101         15 
      9004          1        102         10 
      9004          2        103         20 

SELECT partno, partname 
    FROM parts; 

PARTNO PARTNAME 
------ -------- 
   101 X-Ray Screen 
   102 Yellow Bag        
   103 Zoot Suit

The customer Cables R Us has placed no orders, and order number 9005 has no line items.

The following outer join returns all customers and the dates they placed orders. The (+) operator ensures that customers who placed no orders are also returned:

SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE" 
    FROM customers, orders 
    WHERE customers.custno = orders.custno (+); 

CUSTNAME             ORDERDATE
-------------------- --------------
Angelic Co.          OCT-13-1993
Angelic Co.          OCT-20-1993
Angelic Co.          OCT-27-1993
Believable Co.       OCT-13-1993 
Believable Co.       OCT-31-1993 
Cables R Us

The following outer join builds on the result of the previous one by adding the LINEITEMS table to the FROM clause, columns from this table to the select list, and a join condition joining this table to the ORDERS table to the where_clause. This query joins the results of the previous query to the LINEITEMS table and returns all customers, the dates they placed orders, and the part number and quantity of each part they ordered. The first (+) operator serves the same purpose as in the previous query. The second (+) operator ensures that orders with no line items are also returned:

SELECT custname, 
   TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE", 
   partno, 
   quantity 
      FROM customers, orders, lineitems 
      WHERE customers.custno = orders.custno (+) 
      AND orders.orderno = lineitems.orderno (+); 

CUSTNAME             ORDERDATE          PARTNO   QUANTITY 
-------------------- -------------- ---------- ---------- 
Angelic Co.          OCT-13-1993           101         15 
Angelic Co.          OCT-13-1993           102         10 
Angelic Co.          OCT-20-1993           101         15 
Angelic Co.          OCT-27-1993           102         10 
Angelic Co.          OCT-27-1993           103         20 
Believable Co.       OCT-13-1993           101         25 
Believable Co.       OCT-13-1993           103         50 
Believable Co.       OCT-31-1993 
Cables R Us

The following outer join builds on the result of the previous one by adding the PARTS table to the FROM clause, the PARTNAME column from this table to the select list, and a join condition joining this table to the LINEITEMS table to the where_clause. This query joins the results of the previous query to the PARTS table to return all customers, the dates they placed orders, and the quantity and name of each part they ordered. The first two (+) operators serve the same purposes as in the previous query. The third (+) operator ensures that rows with NULL part numbers are also returned:

SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE", 
    quantity, partname 
    FROM customers, orders, lineitems, parts 
    WHERE customers.custno = orders.custno (+) 
    AND orders.orderno = lineitems.orderno (+) 
    AND lineitems.partno = parts.partno (+); 

CUSTNAME             ORDERDATE        QUANTITY PARTNAME 
-------------------- -------------- ---------- ------------ 
Angelic Co.          OCT-13-1993            15 X-Ray Screen 
Angelic Co.          OCT-13-1993            10 Yellow Bag 
Angelic Co.          OCT-20-1993            15 X-Ray Screen 
Angelic Co.          OCT-27-1993            10 Yellow Bag 
Angelic Co.          OCT-27-1993            20 Zoot Suit 
Believable Co.       OCT-13-1993            25 X-Ray Screen 
Believable Co.       OCT-13-1993            50 Zoot Suit 
Believable Co.       OCT-31-1993 
Cables R Us

Collection Unnesting Examples

Suppose the database contains a table HR_INFO with columns DEPT, LOCATION, and MGR, and a column of nested table type PEOPLE which has NAME, DEPT, and SAL columns. You could get all the rows from HR_INFO and all the rows from PEOPLE using the following statement:

SELECT t1.dept, t2.* FROM hr_info t1, TABLE(t1.people) t2
   WHERE t2.dept = t1.dept;

Now suppose that PEOPLE is not a nested table column of HR_INFO, but is instead a separate table with columns NAME, DEPT, ADDRESS, HIREDATE, and SAL. You can extract the same rows as in the preceding example with this statement:

SELECT t1.department, t2.* 
   FROM hr_info t1, TABLE(CAST(MULTISET(
      SELECT t3.name, t3.dept, t3.sal FROM people t3
      WHERE t3.dept = t1.dept)
      AS NESTED_PEOPLE)) t2;

Finally suppose that PEOPLE is neither a nested table column of table HR_INFO nor a table itself. Instead, you have created a function PEOPLE_FUNC that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:

SELECT t1.dept, t2.* FROM HY_INFO t1, TABLE(CAST
   (people_func( ... ) AS NESTED_PEOPLE)) t2;

For more examples of collection unnesting, see Oracle8i Application Developer's Guide - Fundamentals.

LEVEL Examples

The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is 'PRESIDENT'. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.

SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, 
        empno, mgr, job
    FROM emp 
    START WITH job = 'PRESIDENT' 
    CONNECT BY PRIOR empno = mgr; 

ORG_CHART    EMPNO      MGR        JOB
------------ ---------- ---------- ---------
KING               7839            PRESIDENT
  JONES            7566       7839 MANAGER
    SCOTT          7788       7566 ANALYST
      ADAMS        7876       7788 CLERK
    FORD           7902       7566 ANALYST
      SMITH        7369       7902 CLERK
  BLAKE            7698       7839 MANAGER
    ALLEN          7499       7698 SALESMAN
    WARD           7521       7698 SALESMAN
    MARTIN         7654       7698 SALESMAN
    TURNER         7844       7698 SALESMAN
      JAMES        7900       7698 CLERK
 CLARK             7782       7839 MANAGER
   MILLER          7934       7782 CLERK

The following statement is similar to the previous one, except that it does not select employees with the job 'ANALYST'.

SELECT LPAD(' ', 2*(LEVEL-1)) || ename org_chart,
     empno, mgr, job
     FROM emp
     WHERE job != 'ANALYST'
     START WITH job = 'PRESIDENT'
     CONNECT BY PRIOR empno = mgr;

ORG_CHART            EMPNO      MGR        JOB      
-------------------- ---------- ---------- ---------
KING                       7839            PRESIDENT
  JONES                    7566       7839 MANAGER  
      ADAMS                7876       7788 CLERK    
      SMITH                7369       7902 CLERK    
  BLAKE                    7698       7839 MANAGER  
    ALLEN                  7499       7698 SALESMAN 
    WARD                   7521       7698 SALESMAN 
    MARTIN                 7654       7698 SALESMAN 
    TURNER                 7844       7698 SALESMAN 
    JAMES                  7900       7698 CLERK    
  CLARK                    7782       7839 MANAGER  
    MILLER                 7934       7782 CLERK

Oracle does not return the analysts SCOTT and FORD, although it does return employees who are managed by SCOTT and FORD.

The following statement is similar to the first one, except that it uses the LEVEL pseudocolumn to select only the first two levels of the management hierarchy:

SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, 
empno, mgr, job 
    FROM emp 
    START WITH job = 'PRESIDENT' 
    CONNECT BY PRIOR empno = mgr AND LEVEL <= 2; 

ORG_CHART    EMPNO      MGR        JOB
------------ ---------- ---------- ---------
KING               7839            PRESIDENT
  JONES            7566       7839 MANAGER
  BLAKE            7698       7839 MANAGER
  CLARK            7782       7839 MANAGER

Distributed Query Example

This example shows a query that joins the DEPT table on the local database with the EMP table on the HOUSTON database:

SELECT ename, dname 
    FROM emp@houston, dept 
    WHERE emp.deptno = dept.deptno;

Correlated Subquery Examples

The following examples show the general syntax of a correlated subquery:

SELECT select_list 
    FROM table1 t_alias1 
    WHERE expr operator 
        (SELECT column_list 
            FROM table2 t_alias2 
            WHERE t_alias1.column 
               operator t_alias2.column); 
UPDATE table1 t_alias1 
    SET column = 
        (SELECT expr 
            FROM table2 t_alias2 
            WHERE t_alias1.column = t_alias2.column); 
DELETE FROM table1 t_alias1 
    WHERE column operator 
        (SELECT expr 
            FROM table2 t_alias2 
            WHERE t_alias1.column = t_alias2.column);

The following statement returns data about employees whose salaries exceed their department average. The following statement assigns an alias to EMP, the table containing the salary information, and then uses the alias in a correlated subquery:

SELECT deptno, ename, sal 
    FROM emp x 
    WHERE sal > (SELECT AVG(sal) 
          FROM emp 
          WHERE x.deptno = deptno) 
    ORDER BY deptno;

For each row of the EMP table, the parent query uses the correlated subquery to compute the average salary for members of the same department. The correlated subquery performs the following steps for each row of the EMP table:

The DEPTNO of the row is determined.
The DEPTNO is then used to evaluate the parent query.
If that row's salary is greater than the average salary for that row's department, then the row is returned.

The subquery is evaluated once for each row of the EMP table.

DUAL Table Example

The following statement returns the current date:

SELECT SYSDATE FROM DUAL;

You could select SYSDATE from the EMP table, but Oracle would return 14 rows of the same SYSDATE, one for every row of the EMP table. Selecting from DUAL is more convenient.

Sequence Examples

The following statement increments the ZSEQ sequence and returns the new value:

SELECT zseq.nextval 
    FROM dual;

The following statement selects the current value of ZSEQ:

SELECT zseq.currval 
    FROM dual;

SET CONSTRAINT(S)

Syntax

Purpose

To specify, for a particular transaction, whether a deferrable constraint is checked following each DML statement or when the transaction is committed.

Prerequisites

To specify when a deferrable constraint is checked, you must have SELECT privilege on the table to which the constraint is applied unless the table is in your schema.

Keywords and Parameters

constraint

is the name of one or more integrity constraints.

ALL

sets all deferrable constraints for this transaction.

IMMEDIATE

indicates that the conditions specified by the deferrable constraint are checked immediately after each DML statement.

DEFERRED

indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed.

You can verify the success of deferrable constraints prior to committing them by issuing a SET CONSTRAINTS ALL IMMEDIATE statement.

Examples

The following statement sets all deferrable constraints in this transaction to be checked immediately following each DML statement:

SET CONSTRAINTS ALL IMMEDIATE;

The following statement checks three deferred constraints when the transaction is committed:

SET CONSTRAINTS unq_name, scott.nn_sal,
  adams.pk_dept@dblink DEFERRED;

SET ROLE

Syntax

Purpose

To enable and disable roles for your current session. For information on creating roles, see "CREATE ROLE".

When a user logs on, Oracle enables all privileges granted explicitly to the user and all privileges in the user's default roles. During the session, the user or an application can use the SET ROLE statement any number of times to change the roles currently enabled for the session. The number of roles that can be concurrently enabled is limited by the initialization parameter MAX_ENABLED_ROLES. For information on changing a user's default roles, see "ALTER USER"

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

Prerequisites

You must already have been granted the roles that you name in the SET ROLE statement.

Keywords and Parameters

role

is a role to be enabled for the current session. Any roles not listed are disabled for the current session.
Restriction: You cannot specify a role unless it was granted to you either directly or through other roles.

IDENTIFIED BY password

is the password for a role. If the role has a password, you must specify the password to enable the role.

ALL

enables all roles granted to you for the current session except those optionally listed in the EXCEPT clause.
Restriction: You cannot use this clause to enable roles with passwords that have been granted directly to you.

EXCEPT

Roles listed in the EXCEPT clause must be roles granted directly to you. They cannot be roles granted to you through other roles.

If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, the role remains enabled by virtue of the role to which it has been granted.

NONE

disables all roles for the current session, including the DEFAULT role.

Examples

To enable the role GARDENER identified by the password MARIGOLDS for your current session, issue the following statement:

SET ROLE gardener IDENTIFIED BY marigolds;

To enable all roles granted to you for the current session, issue the following statement:

SET ROLE ALL;

To enable all roles granted to you except BANKER, issue the following statement:

SET ROLE ALL EXCEPT banker;

To disable all roles granted to you for the current session, issue the following statement:

SET ROLE NONE;


Click the "Next" button to see the next statement in this chapter.

hint	is a comment that passes instructions to the optimizer on choosing an execution plan for the statement. For the syntax and description of hints, see "Hints" and Oracle8i Tuning.
DISTINCT \| UNIQUE	returns only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list. Restrictions: When you specify DISTINCT or UNIQUE, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE. You cannot specify DISTINCT if the FROM clause contains LOB columns.
ALL	returns all rows selected, including all copies of duplicates. The default is ALL.
*	selects all columns from all tables, views, or snapshots listed in the FROM clause.
	Note: If you are selecting from a table (that is, you specify table in the FROM clause rather than an view or a snapshot), columns that have been marked as UNUSED by the ALTER TABLE SET UNUSED statement are not selected. See "ALTER TABLE".
schema	is the schema containing the selected table, view, or snapshot. If you omit schema, Oracle assumes the table, view, or snapshot is in your own schema.
table.* \|view.* \| snapshot.*	selects all columns from the specified table, view, or snapshot. You can use the schema qualifier to select from a table, view, or snapshot in a schema other than your own. A query that selects rows from two or more tables, views, or snapshots is a join. For more information, see "Joins".
expr	selects an expression. See the syntax description of expr in "Expressions". A column name in this list can be qualified with schema only if the table, view, or snapshot containing the column is qualified with schema in the FROM clause.
	Restrictions: If you also specify a group_by_clause in this statement, this select list can contain only the following types of expressions: - constants - aggregate functions and the functions USER, UID, and SYSDATE - expressions identical to those in the group_by_clause - expressions involving the above expressions that evaluate to the same value for all rows in a group
	You can select a rowid from a join view only if the join has one and only one key-preserved table. The rowid of that table becomes the rowid of the view. For information on key-preserved tables, see Oracle8i Administrator's Guide. If two or more tables have some column names in common, you must qualify column names with names of tables.
c_alias	provides a different name for the column expression and causes the alias to be used in the column heading. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause, but not other clauses in the query.
*FROM* *table_expression_clause*	specifies the table, view, snapshot, or partition from which data is selected, or a subquery that specifies the objects from which data is selected.
	*sample_clause*	causes Oracle to select from a random sample of rows from the table, rather than from the entire table.
		BLOCK instructs Oracle to perform random block sampling instead of random row sampling. For a discussion of the difference, refer to Oracle8i Concepts.
		sample_percent is a number specifying the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to 99.
		Restrictions: You can specify SAMPLE only in a query that selects from a single table. Joins are not supported. When you specify SAMPLE, Oracle automatically uses the cost-based optimizer. The rule-based optimizer is not supported with this clause.
		WARNING: The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results. Refer to *Oracle8i Concepts* for more information on using the *sample_clause*.
	PARTITION (partition) SUBPARTITION (subpartition)	specifies partition-level data retrieval. The partition parameter may be the name of the partition within table from which to retrieve data or a more complicated predicate restricting retrieval to just one partition of the table.
	dblink	is the complete or partial name for a database link to a remote database where the table, view, or snapshot is located. This database need not be an Oracle database. For more information on referring to database links, see "Referring to Objects in Remote Databases". For more information about distributed queries, see "Distributed Queries".
		If you omit dblink, Oracle assumes that the table, view, or snapshot is on the local database.
	table, view, snapshot	is the name of a table, view, or snapshot from which data is selected.
	*with_clause*	restricts the subquery in one of the following ways:
		WITH READ ONLY specifies that the subquery cannot be updated.
		WITH CHECK OPTION specifies that, if the subquery is used in place of a table in an INSERT, UPDATE, or DELETE statement, Oracle prohibits any changes to that table that would produce rows that are not included in the subquery. See the WITH CHECK OPTION Example .
	*table_collection_expression*	informs Oracle that the collection value expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a CAST or DECODE expression, a function, or a collection constructor. Regardless of its form, it must return a collection value (that is, a value whose type is nested table or varray). This process of extracting the elements of a collection is called collection unnesting. See "Collection Unnesting Examples".
		The collection_expression can reference columns of tables defined to its left in the FROM clause. This is called left correlation. Left correlation can occur only in table_collection_expression. Other subqueries cannot contains references to columns defined outside the subquery.
		The optional "(+)" lets you specify that table_collection_expression should return a row with all fields set to NULL if the collection is null or empty. The "(+)" is valid only if collection_expression uses left correlation. The result is similar to that of an outer join. For more information see "Outer Joins". Restriction: Queries and subqueries referencing nested tables cannot be parallelized.
	Note: In earlier releases of Oracle, when collection_expression was a subquery, table_collection_expr was expressed as "THE subquery". That usage is now deprecated.
	t_alias	provides a correlation name for the table, view, snapshot, or subquery for evaluating the query and is most often used in a correlated query. Other references to the table, view, or snapshot throughout the query must refer to the alias.
		Note: This alias is required if the table_expression_clause references any object type attributes or object type methods.

where_clause	restricts the rows selected to those that satisfy one or more conditions. condition can be any valid SQL condition. See the syntax description of condition in "Conditions". outer_join applies only if the table_expression_clause specifies more than one table. This special form of condition requires Oracle to return all the rows that satisfy the condition, as well as all the rows from one of the tables for which no rows of the other table satisfy the condition. For more information, including rules and restrictions that apply to outer joins, see "Outer Joins". If one of the elements in the table_expression_clause is actually a nested table or some other form of collection, you specify the outer-join syntax in the table_collection_expression rather than in the where_clause. If you omit this clause, Oracle returns all rows from the tables, views, or snapshots in the FROM clause.
	Note: If this clause refers to a DATE column of a partitioned table or index, you must specify the year completely using the TO_DATE function with a 4-character format mask. Otherwise Oracle will not perform partition pruning. "PARTITION Example".
*hierarchical_query_clause*	lets you select rows in a hierarchical order. For a discussion of hierarchical queries, see "Hierarchical Queries". The preceding where_clause, if specified, restricts the rows returned by the query without affecting other rows of the hierarchy. SELECT statements that contain hierarchical queries can contain the LEVEL pseudocolumn. LEVEL returns the value 1 for a root node, 2 for a child node of a root node, 3 for a grandchild, etc. The number of levels returned by a hierarchical query may be limited by available user memory. For more information on LEVEL, see the section "Pseudocolumns".
	Restrictions: If you specify a hierarchical query: The same statement cannot also perform a join. The same statement cannot also select data from a view whose query performs a join. If you also specify the order_by_clause, it takes precedence over any ordering specified by the hierarchical query.
	START WITH	identifies the row(s) to be used as the root(s) of a hierarchical query. This clause specifies a condition that the roots must satisfy. If you omit this clause, Oracle uses all rows in the table as root rows. The START WITH condition can contain a subquery.
	CONNECT BY	specifies the relationship between parent rows and child rows of the hierarchy. condition can be any condition as described in "Conditions". However, some part of the condition must use the PRIOR operator to refer to the parent row. The part of the condition containing the PRIOR operator must have one of the following forms: PRIOR expr comparison_operator expr expr comparison_operator PRIOR expr
		Restriction: The CONNECT BY condition cannot contain a subquery.
*group_by_clause*	groups the selected rows based on the value of expr(s) for each row, and returns a single row of summary information for each group. If this clause contains CUBE or ROLLUP extensions, then superaggregate groupings are produced in addition to the regular groupings.
	Expressions in the group_by_clause can contain any columns in the tables, views, and snapshots in the FROM clause, regardless of whether the columns appear in the select list.
	Restrictions: The group_by_clause can contain no more than 255 expressions. You cannot specify LOB columns, nested tables, or varrays as part of expr. The total number of bytes in all expressions in the group_by_clause is limited to the size of a data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead. If the group_by_clause references any object columns, the query will not be parallelized.
	ROLLUP	is an extension to the group_by_clause that groups the selected rows based on the values of the first n, n-1, n-2, ... 0 expressions for each row, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values. For example, given three expressions in the ROLLUP clause of the group_by_clause, the operation results in n+1 = 3+1 = 4 groupings. Rows based on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows. An example appears with the description of the GROUPING function. See "GROUPING". See also Oracle8i Application Developer's Guide - Fundamentals.
	CUBE	is an extension to the group_by_clause that groups the selected rows based on the values of all possible combinations of expressions for each row, and returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values.
		For example, given three expressions in the CUBE clause of the group_by_clause, the operation results in 2n = 23 = 8 groupings. Rows based on the values of 'n' expressions are called regular rows, and the rest are called superaggregate rows. See the "CUBE Example" and "GROUPING". See also Oracle8i Application Developer's Guide - Fundamentals.
	HAVING	restricts the groups of rows returned to those groups for which the specified condition is TRUE. If you omit this clause, Oracle returns summary rows for all groups. Specify GROUP BY and HAVING after the where_clause and CONNECT BY clause. If you specify both GROUP BY and HAVING, they can appear in either order.
	See also the syntax description of expr in "Expressions" and the syntax description of condition in "Conditions".
UNION \| UNION ALL \| INTERSECT \| MINUS	are set operators that combine the rows returned by two SELECT statements into a single result. The number and datatypes of the columns selected by each component query must be the same, but the column lengths can be different. If you combine more than two queries with set operators, Oracle evaluates adjacent queries from left to right. You can use parentheses to specify a different order of evaluation. For information on these operators, see "Set Operators".
	Restrictions: These set operators are not valid on columns of type BLOB, CLOB, BFILE, varray, or nested table. To reference a column, you must use an alias to name the column. You cannot also specify the for_update_clause with these set operators. You cannot specify the order_by_clause in the subquery of these operators. You cannot use these operators in SELECT statements containing TABLE collection expressions. The total number of bytes in all select list expressions of a component query is limited to the size of a data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead.
	Note: To comply with emerging SQL standards, a future release of Oracle will give the INTERSECT operator greater precedence than the other set operators. Therefore, you should use parentheses to specify order of evaluation in queries that use the INTERSECT operator with other set operators.

*order_by_clause*	orders rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order. For a discussion of ordering query results, see "Sorting Query Results". expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or snapshots in the FROM clause. position orders rows based on their value for the expression in this position of the select list; position must be an integer. ASC and DESC specify either ascending or descending order. ASC is the default.
	You can specify multiple expressions in the order_by_clause. Oracle first sorts rows based on their values for the first expression. Rows with the same value for the first expression are then sorted based on their values for the second expression, and so on. Oracle sorts nulls following all others in ascending order and preceding all others in descending order.
	Restrictions: If you have specified the DISTINCT operator in this statement, this clause cannot refer to columns unless they appear in the select list. An order_by_clause can contain no more than 255 expressions. You cannot order by a LOB column, nested table, or varray. If you specify a group_by_clause in the same statement, this order_by_clause is restricted to the following expressions: Constants Aggregate functions The functions USER, UID, and SYSDATE Expressions identical to those in the group_by_clause Expressions involving the above expressions that evaluate to the same value for all rows in a group.
*for_update_clause*	locks the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement (not in subqueries).
	Prior to updating a LOB value, you must lock the row containing the LOB. One way to lock the row is with a SELECT... FOR UPDATE statement. See "LOB Locking Example". Nested table rows are not locked as a result of locking the parent table rows. If you want the nested table rows to be locked, you must lock them explicitly.
	OF	Locks the select rows only for a particular table in a join. The columns in the OF clause only specify which tables' rows are locked. The specific columns of the table that you specify are not significant. If you omit this clause, Oracle locks the selected rows from all the tables in the query.
	NOWAIT	returns control to you if the SELECT statement attempts to lock a row that is locked by another user. If you omit this clause, Oracle waits until the row is available and then returns the results of the SELECT statement.
	Restrictions: You cannot specify this clause with the following other constructs: DISTINCT operator, group_by_clause, set operators, aggregate functions, or the CURSOR operator. The tables locked by this clause must all be located on the same database, and on the same database as any LONG columns and sequences referenced in the same statement.

constraint	is the name of one or more integrity constraints.
ALL	sets all deferrable constraints for this transaction.
IMMEDIATE	indicates that the conditions specified by the deferrable constraint are checked immediately after each DML statement.
DEFERRED	indicates that the conditions specified by the deferrable constraint are checked when the transaction is committed.
You can verify the success of deferrable constraints prior to committing them by issuing a SET CONSTRAINTS ALL IMMEDIATE statement.

role	is a role to be enabled for the current session. Any roles not listed are disabled for the current session. Restriction: You cannot specify a role unless it was granted to you either directly or through other roles.
	IDENTIFIED BY password	is the password for a role. If the role has a password, you must specify the password to enable the role.
ALL	enables all roles granted to you for the current session except those optionally listed in the EXCEPT clause. Restriction: You cannot use this clause to enable roles with passwords that have been granted directly to you.
	EXCEPT	Roles listed in the EXCEPT clause must be roles granted directly to you. They cannot be roles granted to you through other roles.
	If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, the role remains enabled by virtue of the role to which it has been granted.
NONE	disables all roles for the current session, including the DEFAULT role.

7SQL Statements (continued)

SELECT and Subqueries

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

Simple Query Examples

PARTITION Example

SAMPLE example:

GROUP BY Examples

CUBE Example

Hierarchical Query Examples

HAVING Example

ORDER BY Examples

FOR UPDATE Examples

LOB Locking Example

Table Collection Examples

Subquery Examples

WITH CHECK OPTION Example

Equijoin Examples

Self Join Example

Outer Join Examples

Collection Unnesting Examples

LEVEL Examples

Distributed Query Example

Correlated Subquery Examples

DUAL Table Example

Sequence Examples

SET CONSTRAINT(S)

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

SET ROLE

Syntax

Purpose

Prerequisites

Keywords and Parameters

Examples

7
SQL Statements (continued)