//
you're reading...
SQL

A Short Guide to Oracle SQL [IV] – More on SELECT

In the previous part we have talk about generally used DML statements and transaction control statements. Let’s begin this part with two clauses of SELECT statement WHERE clause and ORDER BY clause. But first let’s start with DUAL table and Pseudo-columns.

DUAL table

DUAL is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one row.

SQL> DESC DUAL 
Name                    Null?    Type 
----------------------- -------- ---------------- 
DUMMY                            VARCHAR2(1) 
  
SQL> SELECT * FROM DUAL; 
D 
- 
X 

The owner of dual is SYS but dual can be accessed by every user.
As dual contains exactly one row (unless someone fiddled with it), it is guaranteed to return exactly one row in select statements if a constant expression is selected against dual, such as in:

SQL> SELECT SYSDATE FROM DUAL; 

DUAL table is useful for function testing, doing some calculation, incrementing sequences or executing queries that do not need table’s data.

SQL> SELECT (319/212)+10 FROM DUAL; 
(319/212)+10
------------
   11.504717

SQL> SELECT emp_seq.NEXTVAL FROM DUAL; 
   NEXTVAL
----------
      1006

Pseudo-columns

A pseudo-column is much like a table column that yields a value when selected, but is not actually stored in a table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. ROWNUM, ROWID, SYSDATE, USER etc. are frequently used pseudo-columns.

ROWNUM

When a query is processed, each row is assigned a number (ROWNUM). Numbers in the ROWNUM pseudocolumn indicate the order in which a row is selected from a table or set of joined rows.

hr@O11R2>SELECT rownum, f_staff_id "Faculty ID", f_last_name "Last Name" 
         FROM faculty;

    ROWNUM Faculty ID Last Name
---------- ---------- ---------------
         1      15234 Ramakrishnan
         2      15241 Sasidharan
         3      15201 Molinaro
         4      15223 Srivastava
         5      15287 Ambler
         6      15229 Sawhney
         7      15291 Wilfred
         8      15239 Quigley
         9      15263 Abbas
        10      15267 Evans
        11      15233 Wilson
        12      15213 Kansal
        13      15203 Coffing
        14      15285 Visser

14 rows selected.

hr@O11R2>SELECT rownum, f_staff_id "Faculty ID", f_last_name "Last Name" 
         FROM faculty
         ORDER BY f_last_name;  

    ROWNUM Faculty ID Last Name
---------- ---------- ---------------
         9      15263 Abbas
         5      15287 Ambler
        13      15203 Coffing
        10      15267 Evans
        12      15213 Kansal
         3      15201 Molinaro
         8      15239 Quigley
         1      15234 Ramakrishnan
         2      15241 Sasidharan
         6      15229 Sawhney
         4      15223 Srivastava
        14      15285 Visser
         7      15291 Wilfred
        11      15233 Wilson

14 rows selected.

ROWNUM can be used for TOP-n Reporting queries. The following query returns the name of lowest paid employees.

scott@O11R2> SELECT empno, ename, sal
            FROM (SELECT * FROM emp ORDER BY sal)
            WHERE ROWNUM <= 3;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7900 JAMES             950
      7876 ADAMS            1100

ROWID

ROWID pseudocolumn returns the exact physical location of a row in the database. It is an internally generated and maintained binary value that identifies a row of data in a table.

scott@O11R2>SELECT rowid, empno, ename, sal 
            FROM emp;

ROWID                   EMPNO ENAME             SAL
------------------ ---------- ---------- ----------
AAAR3sAAEAAAACXAAA       7369 SMITH             800
AAAR3sAAEAAAACXAAB       7499 ALLEN            1600
AAAR3sAAEAAAACXAAC       7521 WARD             1250
AAAR3sAAEAAAACXAAD       7566 JONES            2975
AAAR3sAAEAAAACXAAE       7654 MARTIN           1250
AAAR3sAAEAAAACXAAF       7698 BLAKE            2850
AAAR3sAAEAAAACXAAG       7782 CLARK            2450
AAAR3sAAEAAAACXAAH       7788 SCOTT            3000
AAAR3sAAEAAAACXAAI       7839 KING             5000
AAAR3sAAEAAAACXAAJ       7844 TURNER           1500
AAAR3sAAEAAAACXAAK       7876 ADAMS            1100
AAAR3sAAEAAAACXAAL       7900 JAMES             950
AAAR3sAAEAAAACXAAM       7902 FORD             3000
AAAR3sAAEAAAACXAAN       7934 MILLER           1300

14 rows selected.

A ROWID format can be interpreted as:

ROWID format

ROWID format

ROWID values uniquely identify each row in the database but in a cluster two different tables stored together can have same rowid.

SYSDATE

Returns the current date and time of the OS where database resides.

SQL> SELECT SYSDATE FROM DUAL; 
 
SYSDATE 
--------- 
31-DEC-12 

USER

Returns the name of the current user with the datatype of VARCHAR2.

SQL> SELECT USER FROM DUAL; 
 
USER 
--------- 
SCOTT 

WHERE Clause

WHERE clause is used to specify a condition, on which a data set is filtered to be included in the result.

In the previous part we use equal operator with WHERE clause to retrieve rows with the salary value of 2500. Now lets look at some other operators.

Comparison Operators

Comparison operator compares two expression. The result can be evaluated to TRUE or FALSE (and could be NULL if presence of NULL value in expression).

    SQL>SELECT empno, ename, job FROM emp WHERE sal=3000;
    
    EMPNO ENAME      JOB     
    ----- ---------- ---------
     7788 SCOTT      ANALYST   
     7902 FORD       ANALYST   
    

    In the above statement, we use equal operator to retrieve only those rows whose salary column have a value of 3000. For each rows this condition can produce one of the three results:

  • If condition results in TRUE, rows is included in the results.
  • If FALSE, excluded from the query results.
  • If any column has NULL [unknown] value, those also excluded from the result set.
  • Row Selection in WHERE clause

    Row Selection in WHERE clause

    -- Employee details with SALARY less than 3000
    SQL>SELECT empno, ename, job FROM emp WHERE sal>3000;
    
    EMPNO ENAME      JOB     
    ----- ---------- ---------
     7839 KING       PRESIDENT 
    
    -- Employee details with SALARY gretaer than 3000
    SQL>SELECT empno, ename, job FROM emp WHERE sal<3000;
    
    EMPNO ENAME      JOB     
    ----- ---------- ---------
     7499 ALLEN      SALESMAN  
     7521 WARD       SALESMAN  
     7654 MARTIN     SALESMAN  
     7698 BLAKE      MANAGER   
     7782 CLARK      MANAGER   
     7839 KING       PRESIDENT 
     7844 TURNER     SALESMAN  
     7900 JAMES      CLERK     
     7934 MILLER     CLERK     
    
     9 rows selected. 
    

    Comparison operators can be used with TEXT and DATE data types.

    -- Following query shows employee's details who hired after 01-April-1981. 
    
    SQL>SELECT empno, ename, job, hiredate 
        FROM emp
        WHERE hiredate > '01-APR-81';
    
    EMPNO ENAME      JOB       HIREDATE
    ----- ---------- --------- ---------
     7566 JONES      MANAGER   02-APR-81 
     7654 MARTIN     SALESMAN  28-SEP-81 
     7698 BLAKE      MANAGER   01-MAY-81 
     7782 CLARK      MANAGER   09-JUN-81 
     7788 SCOTT      ANALYST   19-APR-87 
     7839 KING       PRESIDENT 17-NOV-81 
     7844 TURNER     SALESMAN  08-SEP-81 
     7876 ADAMS      CLERK     23-MAY-87 
     7900 JAMES      CLERK     03-DEC-81 
     7902 FORD       ANALYST   03-DEC-81 
     7934 MILLER     CLERK     23-JAN-82 
    
     11 rows selected.
    
    SQL>SELECT empno, ename, job 
        FROM emp
        WHERE job != 'MANAGER';
    
    EMPNO ENAME      JOB     
    ----- ---------- ---------
     7369 SMITH      CLERK     
     7499 ALLEN      SALESMAN  
     7521 WARD       SALESMAN  
     7654 MARTIN     SALESMAN  
     7788 SCOTT      ANALYST   
     7839 KING       PRESIDENT 
     7844 TURNER     SALESMAN  
     7876 ADAMS      CLERK     
     7900 JAMES      CLERK     
     7902 FORD       ANALYST   
     7934 MILLER     CLERK     
    
     11 rows selected.
    
    -- <> or ^= can be used for 'NOT EQUAL TO' operator.
    
    -- In the following query Greater than or equal to (>=) operator is used with the country_id column,
    -- which contains a string value. So query will return all the rows with country_id that begins with IN 
    -- and go through Z.
    
    SQL>SELECT city, country_id 
        FROM hr.locations
        WHERE country_id >= 'IN';
    
    CITY                           COUNTRY_ID
    ------------------------------ ----------
    Bombay                         IN         
    Roma                           IT         
    Venice                         IT         
    Tokyo                          JP         
    Hiroshima                      JP         
    Mexico City                    MX         
    Utrecht                        NL         
    Singapore                      SG         
    London                         UK         
    Oxford                         UK         
    Stretford                      UK         
    Southlake                      US         
    South San Francisco            US         
    South Brunswick                US         
    Seattle                        US         
    
     15 rows selected.
    

Logical Operator (AND, OR and NOT)

Logical operators are used to combine the results of two search conditions to produce a single result or to revert the result of a single comparison.
Using AND operator in the WHERE clause means that the expressions on both sides must be true to retrieve the rows. If either expression is false, AND returns FALSE.

SQL> SELECT employee_id, last_name||', '||first_name "Employee Name", hire_date, salary, job_id
     FROM employees
     WHERE job_id='SA_REP' AND salary > 9000;

EMPLOYEE_ID Employee Name                                   HIRE_DATE     SALARY  JOB_ID
----------- ----------------------------------------------- --------- ----------  ----------
        150 Tucker, Peter                                   30-JAN-05      10000  SA_REP
        151 Bernstein, David                                24-MAR-05       9500  SA_REP 
        156 King, Janette                                   30-JAN-04      10000  SA_REP 
        157 Sully, Patrick                                  04-MAR-04       9500  SA_REP 
        162 Vishney, Clara                                  11-NOV-05      10500  SA_REP 
        163 Greene, Danielle                                19-MAR-07       9500  SA_REP 
        168 Ozer, Lisa                                      11-MAR-05      11500  SA_REP 
        169 Bloom, Harrison                                 23-MAR-06      10000  SA_REP 
        170 Fox, Tayler                                     24-JAN-06       9600  SA_REP 
        174 Abel, Ellen                                     11-MAY-04      11000  SA_REP 

10 rows selected.

OR condition returns TRUE, if any of the comparisons is true. The OR operator in following example retrieve rows if any either condition is evaluates to TRUE.

SQL> SELECT employee_id, last_name||', '||first_name "Employee Name", hire_date, salary, job_id
     FROM employees
     WHERE job_id='SA_REP' OR SALARY >9000;

EMPLOYEE_ID Employee Name                                 HIRE_DATE     SALARY JOB_ID
----------- --------------------------------------------- --------- ---------- ----------
        201 HartsteinMichael                              17-FEB-04      13000 MK_MAN
        204 BaerHermann                                   07-JUN-02      10000 PR_REP
        205 HigginsShelley                                07-JUN-02      12008 AC_MGR
        100 KingSteven                                    17-JUN-03      24000 AD_PRES
        101 KochharNeena                                  21-SEP-05      17000 AD_VP
        102 De HaanLex                                    13-JAN-01      17000 AD_VP
        108 GreenbergNancy                                17-AUG-02      12008 FI_MGR
        114 RaphaelyDen                                   07-DEC-02      11000 PU_MAN
        145 RussellJohn                                   01-OCT-04      14000 SA_MAN
        146 PartnersKaren                                 05-JAN-05      13500 SA_MAN
        147 ErrazurizAlberto                              10-MAR-05      12000 SA_MAN
        148 CambraultGerald                               15-OCT-07      11000 SA_MAN
        149 ZlotkeyEleni                                  29-JAN-08      10500 SA_MAN
        150 TuckerPeter                                   30-JAN-05      10000 SA_REP
        151 BernsteinDavid                                24-MAR-05       9500 SA_REP
        152 HallPeter                                     20-AUG-05       9000 SA_REP
        153 OlsenChristopher                              30-MAR-06       8000 SA_REP
        156 KingJanette                                   30-JAN-04      10000 SA_REP
        157 SullyPatrick                                  04-MAR-04       9500 SA_REP
        158 McEwenAllan                                   01-AUG-04       9000 SA_REP
        159 SmithLindsey                                  10-MAR-05       8000 SA_REP
        162 VishneyClara                                  11-NOV-05      10500 SA_REP
        163 GreeneDanielle                                19-MAR-07       9500 SA_REP
        168 OzerLisa                                      11-MAR-05      11500 SA_REP
        169 BloomHarrison                                 23-MAR-06      10000 SA_REP
        170 FoxTayler                                     24-JAN-06       9600 SA_REP
        174 AbelEllen                                     11-MAY-04      11000 SA_REP
        175 HuttonAlyssa                                  19-MAR-05       8800 SA_REP
        176 TaylorJonathon                                24-MAR-06       8600 SA_REP
        177 LivingstonJack                                23-APR-06       8400 SA_REP

30 rows selected.

NOT operator negate the search condition. It returns TRUE if condition is FALSE and returns FALSE if condition is TRUE.

SQL> SELECT employee_id, last_name||', '||first_name "Employee Name", hire_date, salary, job_id
     FROM employees
     WHERE not (salary < 9000 or job_id = 'SA_REP');

EMPLOYEE_ID Employee Name                                   HIRE_DATE     SALARY JOB_ID
----------- ----------------------------------------------- --------- ---------- ----------
        201 Hartstein, Michael                              17-FEB-04      13000 MK_MAN
        204 Baer, Hermann                                   07-JUN-02      10000 PR_REP
        205 Higgins, Shelley                                07-JUN-02      12008 AC_MGR
        100 King, Steven                                    17-JUN-03      24000 AD_PRES
        101 Kochhar, Neena                                  21-SEP-05      17000 AD_VP
        102 De Haan, Lex                                    13-JAN-01      17000 AD_VP
        103 Hunold, Alexander                               03-JAN-06       9000 IT_PROG
        108 Greenberg, Nancy                                17-AUG-02      12008 FI_MGR
        109 Faviet, Daniel                                  16-AUG-02       9000 FI_ACCOUNT
        114 Raphaely, Den                                   07-DEC-02      11000 PU_MAN
        145 Russell, John                                   01-OCT-04      14000 SA_MAN
        146 Partners, Karen                                 05-JAN-05      13500 SA_MAN
        147 Errazuriz, Alberto                              10-MAR-05      12000 SA_MAN
        148 Cambrault, Gerald                               15-OCT-07      11000 SA_MAN
        149 Zlotkey, Eleni                                  29-JAN-08      10500 SA_MAN

15 rows selected.

BETWEEN Operator

BETWEEN operator evaluates whether the test expression falls within the specified range. It evaluates to TRUE if the value falls within the range that means test_expr is greater than or equal to expr1 and less than or equal to expr2.

test_expr [NOT] BETWEEN expr1 AND expr2

Keep in mind that the range in BETWEEN operator is inclusive that means it includes both the end values specified.
First example shows the employees with the salaries between 1500 and 3000 and second example shows employees details who hired before 01-JAN-2001 and after 31-DEC-2005.

SQL>SELECT empno, ename, job, sal
    FROM emp
    WHERE sal BETWEEN 1500 AND 3000;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7499 ALLEN      SALESMAN        1600
      7566 JONES      MANAGER         2975
      7698 BLAKE      MANAGER         2850
      7782 CLARK      MANAGER         2450
      7788 SCOTT      ANALYST         3000
      7844 TURNER     SALESMAN        1500
      7902 FORD       ANALYST         3000

7 rows selected.

SQL> SELECT * FROM job_history
     WHERE start_date NOT BETWEEN '01-Jan-01' AND '31-DEC-05';

EMPLOYEE_ID START_DAT END_DATE  JOB_ID     DEPARTMENT_ID
----------- --------- --------- ---------- -------------
        101 21-SEP-97 27-OCT-01 AC_ACCOUNT           110
        114 24-MAR-06 31-DEC-07 ST_CLERK              50
        122 01-JAN-07 31-DEC-07 ST_CLERK              50
        200 17-SEP-95 17-JUN-01 AD_ASST               90
        176 24-MAR-06 31-DEC-06 SA_REP                80
        176 01-JAN-07 31-DEC-07 SA_MAN                80

6 rows selected.

IN operator

The IN operator is used in the WHERE clause to check if a value belongs to a set of values in the provided list.

hr@O11R2> SELECT location_id, city, state_province
       FROM locations
       WHERE country_id IN ('JP', 'IN', 'CA');
LOCATION_ID CITY                           STATE_PROVINCE
----------- ------------------------------ -------------------------
       1800 Toronto                        Ontario
       1900 Whitehorse                     Yukon
       2100 Bombay                         Maharashtra
       1200 Tokyo                          Tokyo Prefecture
       1300 Hiroshima

-- WHERE condition can be written as      
-- WHERE country_id='JP' OR country_id='IN' OR country_id='CA'

-- Using NOT with IN will reverse the operation.

hr@O11R2>SELECT location_id, city, state_province
       FROM locations
       WHERE country_id NOT IN ('JP', 'IN', 'CA');

LOCATION_ID CITY                           STATE_PROVINCE
----------- ------------------------------ -------------------------
       1000 Roma
       1100 Venice
       1400 Southlake                      Texas
       1500 South San Francisco            California
       1600 South Brunswick                New Jersey
       1700 Seattle                        Washington
       2000 Beijing
       2200 Sydney                         New South Wales
       2300 Singapore
       2400 London
       2500 Oxford                         Oxford
       2600 Stretford                      Manchester
       2700 Munich                         Bavaria
       2800 Sao Paulo                      Sao Paulo
       2900 Geneva                         Geneve
       3000 Bern                           BE
       3100 Utrecht                        Utrecht
       3200 Mexico City                    Distrito Federal,

18 rows selected.

-- WHERE condition can be written as      
-- WHERE country_id<>'JP' AND country_id<>'IN' AND country_id<>'CA'

NOTE:
If any member of the list in NOT IN condition is set to NULL, NOT IN evaluates to FALSE. Look at the following code:

hr@O11R2>SELECT * FROM departments
         WHERE location_id NOT IN (1700,1800);

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500

hr@O11R2>SELECT * FROM departments
         WHERE location_id NOT IN (1700, 1800, null);

no rows selected.

-- The second example evaluates to 
-- WHERE location_id<>1700 AND location_id<>1800 AND location_id<>NULL

LIKE operator

The LIKE condition is used pattern matching to compare data value. LIKE uses wildcard characters [percent sign (%) and the underscore symbol ( _ )] to search for patterns. % sign matches any number of characters whereas the underscore ( _ ) matches a single character. There can be more than one wildcard in a LIKE condition.

hr@O11R2> SELECT * FROM countries
         WHERE country_name LIKE '%ia';

CO COUNTRY_NAME                              REGION_ID
-- ---------------------------------------- ----------
AU Australia                                         3
IN India                                             3
ML Malaysia                                          3
NG Nigeria                                           4
ZM Zambia                                            4

hr@O11R2> SELECT street_address, city, postal_code FROM locations
         WHERE postal_code LIKE '_0%';

STREET_ADDRESS                           CITY                           POSTAL_CODE
---------------------------------------- ------------------------------ ------------
1297 Via Cola di Rie                     Roma                           00989
93091 Calle della Testa                  Venice                         10934
2007 Zagora St                           South Brunswick                50090
Schwanthalerstr. 7031                    Munich                         80925
Murtenstrasse 921                        Bern                           3095
Pieter Breughelstraat 837                Utrecht                        3029SK

6 rows selected.

SQL> SELECT empno, ename, job, sal 
     FROM scott.emp 
     WHERE ename NOT LIKE '%E%';

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7369 SMITH      CLERK            800
      7521 WARD       SALESMAN        1250
      7654 MARTIN     SALESMAN        1250
      7782 CLARK      MANAGER         2450
      7788 SCOTT      ANALYST         3000
      7839 KING       PRESIDENT       5000
      7876 ADAMS      CLERK           1100
      7902 FORD       ANALYST         3000

8 rows selected.

The keyword ESCAPE is used to distinguish wildcard characters to literal. If escape character placed immediately before any wildcard symbols then that wildcard character treated as literal. The escape character (in this case \) must be enclosed in single quotation marks (‘\’).

SQL> SELECT * FROM all_users
     WHERE username LIKE '%\_%' ESCAPE '\';

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
ORACLE_OCM                             21 02-APR-10
SI_INFORMTN_SCHEMA                     56 02-APR-10
SPATIAL_WFS_ADMIN_USR                  67 02-APR-10
SPATIAL_CSW_ADMIN_USR                  70 02-APR-10
MGMT_VIEW                              74 02-APR-10
FLOWS_FILES                            75 02-APR-10
APEX_PUBLIC_USER                       76 02-APR-10
APEX_030200                            78 02-APR-10
OWBSYS_AUDIT                           83 02-APR-10
DB_SPOTLIGHT                           96 13-AUG-12

10 rows selected.

NULL Values

NULL shows the absence of data in a field/column. It does not mean a column has a zero or a blank in it. A zero or a blank is a value. NULL means nothing or unknown.
Since equal to ( = ) and not equal to (!= ) operator will not work with NULL values, Oracle provide two operator IS NULL and IS NOT NULL to work with NULL values.

Following example shows the details of employee who earn commission and who do not earn any commission, respectively.

scott@O11R2> SELECT * FROM emp
          WHERE comm IS NOT NULL;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

scott@O11R2> SELECT * FROM emp
          WHERE comm IS NULL;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

10 rows selected.

Substitution Variable and Ampersand

Ampersand (&) is used to supply values at run-time. Ampersand prompts user for enter a value for the variable.

hr@O11R2> SELECT employee_id, first_name||', '||last_name "Employee Name", job_id, salary
         FROM employees
         WHERE department_id = &deptID;
Enter value for deptid: 20
old   3: WHERE department_id = &deptID
new   3: WHERE department_id = 20

EMPLOYEE_ID Employee Name                                   JOB_ID         SALARY
----------- ----------------------------------------------- ---------- ----------
        201 Michael, Hartstein                              MK_MAN          13000
        202 Pat, Fay                                        MK_REP           6000

hr@O11R2> SELECT department_name, location_id
        FROM departments
        WHERE department_id = &deptID;
Enter value for deptid: 20
old   3: WHERE department_id = &deptID
new   3: WHERE department_id = 20

DEPARTMENT_NAME                LOCATION_ID
------------------------------ -----------
Marketing                             1800

Use double ampersand to avoid unnecessary prompts for values.

hr@O11R2> SELECT employee_id, first_name||', '||last_name "Employee Name", job_id, salary
         FROM employees
         WHERE department_id = &&deptID;
Enter value for deptid: 20
old   3:          WHERE department_id = &&deptID
new   3:          WHERE department_id = 20

EMPLOYEE_ID Employee Name                                   JOB_ID         SALARY
----------- ----------------------------------------------- ---------- ----------
        201 Michael, Hartstein                              MK_MAN          13000
        202 Pat, Fay                                        MK_REP           6000

hr@O11R2> SELECT department_name, location_id
         FROM departments
         WHERE department_id = &deptID;
old   3: WHERE department_id = &deptID
new   3: WHERE department_id = 20

DEPARTMENT_NAME                LOCATION_ID
------------------------------ -----------
Marketing                             1800

ORDER BY

ORDER BY clause is used sort the result set in the specific order.

ORDER BY expr|position {ASC|DESC}  {NULLS FIRST|NULLS LAST}

By default the ORDER BY clause arranges the rows of the query result in ascending order [ASC]. Use DESC Keyword to sort the rows in descending order.

hr@O11R2> SELECT location_id, city, state_province, country_id FROM locations
        ORDER BY state_province;

LOCATION_ID CITY                           STATE_PROVINCE            CO
----------- ------------------------------ ------------------------- --
       3000 Bern                           BE                        CH
       2700 Munich                         Bavaria                   DE
       1500 South San Francisco            California                US
       3200 Mexico City                    Distrito Federal,         MX
       2900 Geneva                         Geneve                    CH
       2100 Bombay                         Maharashtra               IN
       2600 Stretford                      Manchester                UK
       1600 South Brunswick                New Jersey                US
       2200 Sydney                         New South Wales           AU
       1800 Toronto                        Ontario                   CA
       2500 Oxford                         Oxford                    UK
       2800 Sao Paulo                      Sao Paulo                 BR
       1400 Southlake                      Texas                     US
       1200 Tokyo                          Tokyo Prefecture          JP
       3100 Utrecht                        Utrecht                   NL
       1700 Seattle                        Washington                US
       1900 Whitehorse                     Yukon                     CA
       2300 Singapore                                                SG
       2000 Beijing                                                  CN
       1300 Hiroshima                                                JP
       1100 Venice                                                   IT
       1000 Roma                                                     IT
       2400 London                                                   UK

23 rows selected.

hr@O11R2> SELECT location_id, city, state_province, country_id FROM locations
        ORDER BY state_province DESC;
LOCATION_ID CITY                           STATE_PROVINCE            CO
----------- ------------------------------ ------------------------- --
       1000 Roma                                                     IT
       1100 Venice                                                   IT
       2400 London                                                   UK
       1300 Hiroshima                                                JP
       2300 Singapore                                                SG
       2000 Beijing                                                  CN
       1900 Whitehorse                     Yukon                     CA
       1700 Seattle                        Washington                US
       3100 Utrecht                        Utrecht                   NL
       1200 Tokyo                          Tokyo Prefecture          JP
       1400 Southlake                      Texas                     US
       2800 Sao Paulo                      Sao Paulo                 BR
       2500 Oxford                         Oxford                    UK
       1800 Toronto                        Ontario                   CA
       2200 Sydney                         New South Wales           AU
       1600 South Brunswick                New Jersey                US
       2600 Stretford                      Manchester                UK
       2100 Bombay                         Maharashtra               IN
       2900 Geneva                         Geneve                    CH
       3200 Mexico City                    Distrito Federal,         MX
       1500 South San Francisco            California                US
       2700 Munich                         Bavaria                   DE
       3000 Bern                           BE                        CH

23 rows selected.

As we see, NULL placed last in the default ascending order and first in the descending order. To override this default order use NULLS FIRST and NULLS LAST keywords.

hr@O11R2> SELECT location_id, city, state_province, country_id FROM locations
        ORDER BY state_province DESC NULLS LAST;

LOCATION_ID CITY                           STATE_PROVINCE            CO
----------- ------------------------------ ------------------------- --
       1900 Whitehorse                     Yukon                     CA
       1700 Seattle                        Washington                US
       3100 Utrecht                        Utrecht                   NL
       1200 Tokyo                          Tokyo Prefecture          JP
       1400 Southlake                      Texas                     US
       2800 Sao Paulo                      Sao Paulo                 BR
       2500 Oxford                         Oxford                    UK
       1800 Toronto                        Ontario                   CA
       2200 Sydney                         New South Wales           AU
       1600 South Brunswick                New Jersey                US
       2600 Stretford                      Manchester                UK
       2100 Bombay                         Maharashtra               IN
       2900 Geneva                         Geneve                    CH
       3200 Mexico City                    Distrito Federal,         MX
       1500 South San Francisco            California                US
       2700 Munich                         Bavaria                   DE
       3000 Bern                           BE                        CH
       1300 Hiroshima                                                JP
       2300 Singapore                                                SG
       1100 Venice                                                   IT
       2000 Beijing                                                  CN
       1000 Roma                                                     IT
       2400 London                                                   UK

23 rows selected.
    • You can specify columns by their position (in the SELECT statement) rather than column names in the ORDER BY clause.
    • Column alias can be used in ORDER BY clause but it can’t be used in the WHERE clause.
    • You can specify more than one column in ORDER BY clause.
hr@O11R2> SELECT location_id "LOCATION ID", city, state_province "STATE PROVINCE", country_id "COUNTRY ID" 
    FROM locations
    WHERE state_province IS NOT NULL
    ORDER BY 4, "STATE PROVINCE";

LOCATION ID CITY                           STATE PROVINCE            CO
----------- ------------------------------ ------------------------- --
       2200 Sydney                         New South Wales           AU
       2800 Sao Paulo                      Sao Paulo                 BR
       1800 Toronto                        Ontario                   CA
       1900 Whitehorse                     Yukon                     CA
       3000 Bern                           BE                        CH
       2900 Geneva                         Geneve                    CH
       2700 Munich                         Bavaria                   DE
       2100 Bombay                         Maharashtra               IN
       1200 Tokyo                          Tokyo Prefecture          JP
       3200 Mexico City                    Distrito Federal,         MX
       3100 Utrecht                        Utrecht                   NL
       2600 Stretford                      Manchester                UK
       2500 Oxford                         Oxford                    UK
       1500 South San Francisco            California                US
       1600 South Brunswick                New Jersey                US
       1400 Southlake                      Texas                     US
       1700 Seattle                        Washington                US

17 rows selected.

NOTE:

    • An ORDER BY clause can’t contain more than 255 expressions.
    • You cannot order by a LOB, LONG, or LONG RAW column, nested table, or varray.
    • It is not necessary for a column to be in SELECT list in order to use in the ORDER BY clause unless DISTINCT keyword is used in the SELECT clause.
    • hr@O11R2> SELECT  salary, department_id
             FROM employees
             ORDER BY employee_id;
      
          SALARY DEPARTMENT_ID
      ---------- -------------
           24000            90
           17000            90
           17000            90
            9000            60
            6000            60
            4800            60
            4800            60
            4200            60
           12008           100
            9000           100
            8200           100
            7700           100
            7800           100
            6900           100
           11000            30
            3100            30
            2900            30
            2800            30
            2600            30
      
      19 rows selected.
      
      hr@O11R2> SELECT DISTINCT salary, department_id
             FROM employees
             ORDER BY employee_id;
      order by employee_id
               *
      ERROR at line 3:
      ORA-01791: not a SELECTed expression
      

REFERENCE:
Oracle Database SQL Language Reference 11g Release 2

Advertisements

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: