Skip Headers
Oracle® Database 2 Day Developer's Guide
11g Release 2 (11.2)

Part Number E10766-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

4 Selecting Table Data

This chapter contains the following topics:

Note:

To do the tutorials in this document, you must be connected to Oracle Database as the user HR from SQL Developer. For instructions, see "Connecting to Oracle Database as User HR from SQL Developer".

About Queries

A query, or SQL SELECT statement, selects data from one or more tables or views.

The simplest form of query has this syntax:

SELECT select_list FROM source_list

The select_list specifies the columns from which the data is to be selected, and the source_list specifies the tables or views that have these columns.

A query nested within another SQL statement is called a subquery.

In the SQL*Plus environment, you can enter a query after the SQL> prompt.

In the SQL Developer environment, you can enter a query in the SQL Worksheet. For instructions, see "Running Queries in SQL Developer".

Note:

When the result of a query is displayed, records can be in any order, unless you specify their order with the ORDER BY clause. For more information, see "Sorting Selected Data".

See Also:

Running Queries in SQL Developer

This topic explains how to run queries or other SQL statements in SQL Developer, if you are connected to Oracle Database as user HR from SQL Developer (for instructions, see "Connecting to Oracle Database as User HR from SQL Developer").

To run queries in SQL Developer:

  1. Click the icon SQL Worksheet.

    Either the Connection Information window opens or the SQL Worksheet pane appears.

  2. If the Select Connection window opens:

    1. If the Connection field does not have the value hr_conn, select that value from the drop-down list.

    2. Click OK.

    The SQL Worksheet pane appears. Under "Enter SQL Statement:" is a field where you can enter a SQL statement.

  3. In the SQL Worksheet pane, type a query (a SELECT statement).

  4. Click the icon Execute Statement.

    The query runs.

  5. Click the tab Results.

    The Results pane appears, showing the result of the query.

  6. Click the icon Clear.

    The query and its results disappear, and you can enter another SQL statement in the SQL Worksheet.

See Also:

Oracle Database SQL Developer User's Guide for information about using the SQL Worksheet in SQL Developer

Tutorial: Selecting All Columns of a Table

This tutorial shows how to select all columns of the EMPLOYEES table.

To select all columns of the EMPLOYEES Table:

  1. Click the icon SQL Worksheet.

    The SQL Worksheet pane appears.

  2. In the field under "Enter SQL Statement:", enter this query:

    SELECT * FROM EMPLOYEES;
    
  3. Click the Execute Statement.

    The query runs.

  4. Click the tab Results.

    The Results pane appears, showing the result of the query.

See Also:

"Tutorial: Viewing EMPLOYEES Table Properties and Data" for information about another way to view table data with SQL Developer

Tutorial: Selecting Specific Columns of a Table

This tutorial shows how to select only the columns FIRST_NAME, LAST_NAME, and DEPARTMENT_ID of the EMPLOYEES table.

To select only FIRST_NAME, LAST_NAME, and DEPARTMENT_ID:

  1. Click the icon SQL Worksheet.

    The SQL Worksheet pane appears.

  2. In the field under "Enter SQL Statement:", enter this query:

    SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
    FROM EMPLOYEES;
    
  3. Click the icon Execute Statement.

    The query runs.

  4. Click the tab Results.

    The Results pane appears, showing the results of the query, which are similar to:

    FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
    -------------------- ------------------------- -------------
    Donald               OConnell                             50
    Douglas              Grant                                50
    Jennifer             Whalen                               10
    Michael              Hartstein                            20
    Pat                  Fay                                  20
    Susan                Mavris                               40
    Hermann              Baer                                 70
    Shelley              Higgins                             110
    William              Gietz                               110
    Steven               King                                 90
    Neena                Kochhar                              90
     
    FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
    -------------------- ------------------------- -------------
    Lex                  De Haan                              90
    ...
    Kevin                Feeney                               50
     
    107 rows selected.
    

Displaying Selected Columns Under New Headings

When query results are displayed, the default column heading is the column name. To display a column under a new heading, specify the new heading (alias) immediately after the name of the column. The alias renames the column for the duration of the query, but does not change its name in the database.

The query in Example 4-1 selects the same columns as the query in Tutorial: Selecting Specific Columns of a Table, but it also specifies aliases for them. Because the aliases are not enclosed in double quotation marks, they are displayed in uppercase letters.

Example 4-1 Displaying Selected Columns Under New Headings

SELECT FIRST_NAME First, LAST_NAME last, DEPARTMENT_ID DepT
FROM EMPLOYEES;

Result is similar to:

FIRST                LAST                            DEPT
-------------------- ------------------------- ----------
Donald               OConnell                          50
Douglas              Grant                             50
Jennifer             Whalen                            10
Michael              Hartstein                         20
Pat                  Fay                               20
Susan                Mavris                            40
Hermann              Baer                              70
Shelley              Higgins                          110
William              Gietz                            110
Steven               King                              90
Neena                Kochhar                           90
 
FIRST                LAST                            DEPT
-------------------- ------------------------- ----------
Lex                  De Haan                           90
...
Kevin                Feeney                            50
 
107 rows selected.

If you enclose column aliases in double quotation marks, case is preserved, and the aliases can include spaces, as in Example 4-2.

Example 4-2 Preserving Case and Including Spaces in Column Aliases

SELECT FIRST_NAME "Given Name", LAST_NAME "Family Name"
FROM EMPLOYEES;

Result is similar to:

Given Name           Family Name
-------------------- -------------------------
Donald               OConnell
Douglas              Grant
Jennifer             Whalen
Michael              Hartstein
Pat                  Fay
Susan                Mavris
Hermann              Baer
Shelley              Higgins
William              Gietz
Steven               King
Neena                Kochhar
 
Given Name           Family Name
-------------------- -------------------------
Lex                  De Haan
...
Kevin                Feeney
 
107 rows selected.

See Also:

Oracle Database SQL Language Reference for more information about the SELECT statement, including the column alias (c_alias)

Selecting Data that Satisfies Specified Conditions

To select only data that matches a specified condition, include the WHERE clause in the SELECT statement. The condition in the WHERE clause can be any SQL condition (for information about SQL conditions, see Oracle Database SQL Language Reference).

The query in Example 4-3 selects data only for employees in department 90.

Example 4-3 Selecting Data from One Department

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;

Result is similar to:

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
-------------------- ------------------------- -------------
Steven               King                                 90
Neena                Kochhar                              90
Lex                  De Haan                              90

The query in Example 4-4 selects data only for employees in departments 100, 110, and 120.

Example 4-4 Selecting Data from Specified Departments

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (100, 110, 120);

Result is similar to:

FIRST_NAME           LAST_NAME                 DEPARTMENT_ID
-------------------- ------------------------- -------------
John                 Chen                                100
Daniel               Faviet                              100
William              Gietz                               110
Nancy                Greenberg                           100
Shelley              Higgins                             110
Luis                 Popp                                100
Ismael               Sciarra                             100
Jose Manuel          Urman                               100
 
8 rows selected.

There are no employees in department 120.

The query in Example 4-5 selects data only for employees whose last names start with "Ma".

Example 4-5 Selecting Data for Last Names that Start with the Same Substring

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'Ma%';

Result is similar to:

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Jason                Mallin
Steven               Markle
James                Marlow
Mattea               Marvins
Randall              Matos
Susan                Mavris
 
6 rows selected.

The query in Example 4-6 selects data only for employees whose last names include "ma".

Example 4-6 Selecting Data for Last Names that Include the Same Substring

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE LAST_NAME LIKE '%ma%';

Result is similar to:

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Sundita              Kumar
Jose Manuel          Urman
Shanta               Vollman

The query in Example 4-7 tests for two conditions—whether the salary is at least 11000, and whether the commission percentage is not null.

Example 4-7 Selecting Data that Satisfies Two Conditions

SELECT FIRST_NAME, LAST_NAME, SALARY, COMMISSION_PCT "%"
FROM EMPLOYEES
WHERE (SALARY >= 11000) AND (COMMISSION_PCT IS NOT NULL);

Result is similar to:

FIRST_NAME           LAST_NAME                     SALARY          %
-------------------- ------------------------- ---------- ----------
John                 Russell                        14000         .4
Karen                Partners                       13500         .3
Alberto              Errazuriz                      12000         .3
Gerald               Cambrault                      11000         .3
Lisa                 Ozer                           11500        .25
Ellen                Abel                           11000         .3
 
6 rows selected.

See Also:

Specifying Conditions with Regular Expressions

As stated in "Selecting Data that Satisfies Specified Conditions", the condition in the WHERE clause can be any SQL condition. This topic shows how to specify conditions with SQL functions that accept regular expressions. A regular expression defines a search pattern, using metacharacters to specify search algorithms and literals to specify characters.

Suppose that you want to select all managers in the EMPLOYEES table. The JOB_ID of a manager ends with either '_MGR' or '_MAN', depending on the department. Therefore, the search pattern must be a regular expression, and you must use the REGEXP_LIKE function, as in Example 4-8.

In the regular expression (_m[an|gr]), the metacharacter | indicates the OR condition. The third function parameter, 'i', specifies that the match is case-insensitive.

Example 4-8 Selecting All Managers in the EMPLOYEES Table

SELECT FIRST_NAME, LAST_NAME, JOB_ID
FROM EMPLOYEES
WHERE REGEXP_LIKE(JOB_ID, '(_m[an|gr])', 'i');

Result is similar to:

FIRST_NAME           LAST_NAME                 JOB_ID
-------------------- ------------------------- ----------
Michael              Hartstein                 MK_MAN
Shelley              Higgins                   AC_MGR
Nancy                Greenberg                 FI_MGR
Den                  Raphaely                  PU_MAN
Matthew              Weiss                     ST_MAN
Adam                 Fripp                     ST_MAN
Payam                Kaufling                  ST_MAN
Shanta               Vollman                   ST_MAN
Kevin                Mourgos                   ST_MAN
John                 Russell                   SA_MAN
Karen                Partners                  SA_MAN
 
FIRST_NAME           LAST_NAME                 JOB_ID
-------------------- ------------------------- ----------
Alberto              Errazuriz                 SA_MAN
Gerald               Cambrault                 SA_MAN
Eleni                Zlotkey                   SA_MAN
 
14 rows selected.

Suppose that you want to select every employee whose last name has a double vowel (two adjacent occurrences of the same vowel). Example 4-9 shows how you can do this.

The regular expression ([AEIOU]) represents any vowel. The metacharacter \1 represents the first (and in this case, only) regular expression. The third function parameter, 'i', specifies that the match is case-insensitive.

Example 4-9 Selecting All Employees Whose Last Names Have Double Vowels

SELECT FIRST_NAME, LAST_NAME
FROM EMPLOYEES
WHERE REGEXP_LIKE(LAST_NAME, '([AEIOU])\1', 'i');

Result is similar to:

FIRST_NAME           LAST_NAME
-------------------- -------------------------
Harrison             Bloom
Lex                  De Haan
Kevin                Feeney
Ki                   Gee
Nancy                Greenberg
Danielle             Greene
Alexander            Khoo
David                Lee
 
8 rows selected.

Suppose that, in the displayed query results, you want to replace phone numbers that are stored in the format nnn.nnn.nnnn with their equivalents in the format (nnn) nnn-nnnn. You can use the REGEXP_REPLACE function, with regular expressions in the search pattern (the stored format) and references to those regular expressions in the replace string (the display format), as in Example 4-10.

The search pattern has three regular expressions, each of which is enclosed in parentheses. The metacharacter [[:digit:]] represents a digit, the metacharacter {n} specifies n occurrences, and the metacharacter \ is an escape character. The character immediately after an escape character is interpreted as a literal. Without the escape character, the metacharacter . represents any character.

The replace string uses \1, \2, and \3 to represent the first, second, and third regular expressions in the search pattern, respectively. (In the replace string, \ is not an escape character.)

Example 4-10 Displaying Phone Numbers in a Different Format

SELECT PHONE_NUMBER "Old Format",
REGEXP_REPLACE(PHONE_NUMBER,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "New Format"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;

Result is similar to:

Old Format          New Format
-------------------- ---------------------------------------------------------
515.123.4567         (515) 123-4567
515.123.4568         (515) 123-4568
515.123.4569         (515) 123-4569

Suppose that you want to extract the street number from each STREET_ADDRESS in the LOCATIONS table. Some street numbers include hyphens, so the search pattern must use a regular expression, and you must use the REGEXP_SUBSTR function, as in Example 4-11.

Example 4-11 Extracting the Street Number from Each STREET_ADDRESS

COLUMN Number FORMAT A8
SELECT STREET_ADDRESS "Address",
REGEXP_SUBSTR(STREET_ADDRESS, '[[:digit:]-]+') "Number"
FROM LOCATIONS;
COLUMN Number CLEAR

Result is similar to:

Address                                  Number
---------------------------------------- --------
1297 Via Cola di Rie                     1297
93091 Calle della Testa                  93091
2017 Shinjuku-ku                         2017
9450 Kamiya-cho                          9450
2014 Jabberwocky Rd                      2014
2011 Interiors Blvd                      2011
2007 Zagora St                           2007
2004 Charade Rd                          2004
147 Spadina Ave                          147
6092 Boxwood St                          6092
40-5-12 Laogianggen                      40-5-12
 
Address                                  Number
---------------------------------------- --------
1298 Vileparle (E)                       1298
12-98 Victoria Street                    12-98
198 Clementi North                       198
8204 Arthur St                           8204
Magdalen Centre, The Oxford Science Park
9702 Chester Road                        9702
Schwanthalerstr. 7031                    7031
Rua Frei Caneca 1360                     1360
20 Rue des Corps-Saints                  20
Murtenstrasse 921                        921
Pieter Breughelstraat 837                837
 
Address                                  Number
---------------------------------------- --------
Mariano Escobedo 9991                    9991
 
23 rows selected.

To count the number of spaces in each STREET_ADDRESS, you can use the REGEXP_COUNT function, as in Example 4-12.

Example 4-12 Counting the Number of Spaces in Each STREET_ADDRESS

SELECT STREET_ADDRESS,
REGEXP_COUNT(STREET_ADDRESS, ' ') "Number of Spaces"
FROM LOCATIONS;

Result is similar to:

STREET_ADDRESS                           Number of Spaces
---------------------------------------- ----------------
1297 Via Cola di Rie                                    4
93091 Calle della Testa                                 3
2017 Shinjuku-ku                                        1
9450 Kamiya-cho                                         1
2014 Jabberwocky Rd                                     2
2011 Interiors Blvd                                     2
2007 Zagora St                                          2
2004 Charade Rd                                         2
147 Spadina Ave                                         2
6092 Boxwood St                                         2
40-5-12 Laogianggen                                     1
 
STREET_ADDRESS                           Number of Spaces
---------------------------------------- ----------------
1298 Vileparle (E)                                      2
12-98 Victoria Street                                   2
198 Clementi North                                      2
8204 Arthur St                                          2
Magdalen Centre, The Oxford Science Park                5
9702 Chester Road                                       2
Schwanthalerstr. 7031                                   1
Rua Frei Caneca 1360                                    4
20 Rue des Corps-Saints                                 3
Murtenstrasse 921                                       1
Pieter Breughelstraat 837                               2
 
STREET_ADDRESS                           Number of Spaces
---------------------------------------- ----------------
Mariano Escobedo 9991                                   2
 
23 rows selected.

To report the position of the first space in each STREET_ADDRESS, you can use the REGEXP_INSTR function, as in Example 4-13.

Example 4-13 Reporting the Position of the First Space in Each STREET_ADDRESS

SELECT STREET_ADDRESS,
REGEXP_INSTR(STREET_ADDRESS, ' ') "First Space"
FROM LOCATIONS;

Result is similar to:

STREET_ADDRESS                           First Space
---------------------------------------- -----------
1297 Via Cola di Rie                               5
93091 Calle della Testa                            6
2017 Shinjuku-ku                                   5
9450 Kamiya-cho                                    5
2014 Jabberwocky Rd                                5
2011 Interiors Blvd                                5
2007 Zagora St                                     5
2004 Charade Rd                                    5
147 Spadina Ave                                    4
6092 Boxwood St                                    5
40-5-12 Laogianggen                                8
 
STREET_ADDRESS                           First Space
---------------------------------------- -----------
1298 Vileparle (E)                                 5
12-98 Victoria Street                              6
198 Clementi North                                 4
8204 Arthur St                                     5
Magdalen Centre, The Oxford Science Park           9
9702 Chester Road                                  5
Schwanthalerstr. 7031                             17
Rua Frei Caneca 1360                               4
20 Rue des Corps-Saints                            3
Murtenstrasse 921                                 14
Pieter Breughelstraat 837                          7
 
STREET_ADDRESS                           First Space
---------------------------------------- -----------
Mariano Escobedo 9991                              8
 
23 rows selected.

See Also:

Sorting Selected Data

When the results of a query are displayed, records can be in any order, unless you specify their order with the ORDER BY clause.

The results of the query in Example 4-14 are sorted by LAST_NAME, in ascending order (the default).

Alternatively, in SQL Developer, you can omit the ORDER BY clause and double-click the name of the column to sort.

Example 4-14 Sorting Selected Data by LAST_NAME

SELECT FIRST_NAME, LAST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY LAST_NAME;

Result:

FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Ellen                Abel                      11-MAY-96
Sundar               Ande                      24-MAR-00
Mozhe                Atkinson                  30-OCT-97
David                Austin                    25-JUN-97
Hermann              Baer                      07-JUN-94
Shelli               Baida                     24-DEC-97
Amit                 Banda                     21-APR-00
Elizabeth            Bates                     24-MAR-99
...
FIRST_NAME           LAST_NAME                 HIRE_DATE
-------------------- ------------------------- ---------
Jose Manuel          Urman                     07-MAR-98
Peter                Vargas                    09-JUL-98
Clara                Vishney                   11-NOV-97
Shanta               Vollman                   10-OCT-97
Alana                Walsh                     24-APR-98
Matthew              Weiss                     18-JUL-96
Jennifer             Whalen                    17-SEP-87
Eleni                Zlotkey                   29-JAN-00

107 rows selected

The sort criterion need not be included in the select list, as Example 4-15 shows.

Example 4-15 Sorting Selected Data by an Unselected Column

SELECT FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
ORDER BY LAST_NAME;
 

Result:

FIRST_NAME           HIRE_DATE
-------------------- ---------
Ellen                11-MAY-96
Sundar               24-MAR-00
Mozhe                30-OCT-97
David                25-JUN-97
Hermann              07-JUN-94
Shelli               24-DEC-97
Amit                 21-APR-00
Elizabeth            24-MAR-99
...
FIRST_NAME           HIRE_DATE
-------------------- ---------
Jose Manuel          07-MAR-98
Peter                09-JUL-98
Clara                11-NOV-97
Shanta               10-OCT-97
Alana                24-APR-98
Matthew              18-JUL-96
Jennifer             17-SEP-87
Eleni                29-JAN-00
 
107 rows selected.

See Also:

Oracle Database SQL Language Reference for more information about the SELECT statement, including the ORDER BY clause

Selecting Data from Multiple Tables

Suppose that you want to select the FIRST_NAME, LAST_NAME, and DEPARTMENT_NAME of every employee. FIRST_NAME and LAST_NAME are in the EMPLOYEES table, and DEPARTMENT_NAME is in the DEPARTMENTS table. Both tables have DEPARTMENT_ID. You can use the query in Example 4-16. Such a query is called a join.

Example 4-16 Selecting Data from Two Tables (Joining Two Tables)

SELECT EMPLOYEES.FIRST_NAME "First",
EMPLOYEES.LAST_NAME "Last",
DEPARTMENTS.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;
 

Result:

First                Last                      Dept. Name
-------------------- ------------------------- ------------------------------
Jennifer             Whalen                    Administration
Michael              Hartstein                 Marketing
Pat                  Fay                       Marketing
Den                  Raphaely                  Purchasing
Karen                Colmenares                Purchasing
Alexander            Khoo                      Purchasing
Shelli               Baida                     Purchasing
Sigal                Tobias                    Purchasing
Guy                  Himuro                    Purchasing
Susan                Mavris                    Human Resources
Donald               OConnell                  Shipping
 
First                Last                      Dept. Name
-------------------- ------------------------- ------------------------------
Douglas              Grant                     Shipping
...
Shelley              Higgins                   Accounting
 
106 rows selected.

Table-name qualifiers are optional for column names that appear in only one table of a join, but are required for column names that appear in both tables. The following query is equivalent to the query in Example 4-16:

SELECT FIRST_NAME "First",
LAST_NAME "Last",
DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES, DEPARTMENTS
WHERE EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID;

To make queries that use qualified column names more readable, use table aliases, as in the following example:

SELECT FIRST_NAME "First",
LAST_NAME "Last",
DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

Although you create the aliases in the FROM clause, you can use them earlier in the query, as in the following example:

SELECT e.FIRST_NAME "First",
e.LAST_NAME "Last",
d.DEPARTMENT_NAME "Dept. Name"
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;

See Also:

Oracle Database SQL Language Reference for more information about joins

Using Operators and Functions in Queries

The select_list of a query can include SQL expressions, which can include SQL operators and SQL functions. These operators and functions can have table data as operands and arguments. The SQL expressions are evaluated, and their values appear in the results of the query.

Topics:

See Also:

Using Arithmetic Operators in Queries

SQL supports the basic arithmetic operators: + (addition), - (subtraction), * (multiplication), and / (division).

The query in Example 4-17 displays LAST_NAME, SALARY (monthly pay), and annual pay for each employee in department 90, in descending order of SALARY.

Example 4-17 Using an Arithmetic Expression in a Query

SELECT LAST_NAME,
SALARY "Monthly Pay",
SALARY * 12 "Annual Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
ORDER BY SALARY DESC;

Result:

LAST_NAME                 Monthly Pay Annual Pay
------------------------- ----------- ----------
King                            24000     288000
De Haan                         17000     204000
Kochhar                         17000     204000

Using Numeric Functions in Queries

Numeric functions accept numeric input and return numeric values. Each numeric function returns a single value for each row that is evaluated. The numeric functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

The query in Example 4-18 uses the numeric function ROUND to display the daily pay of each employee in department 100, rounded to the nearest cent.

Example 4-18 Rounding Numeric Data

SELECT LAST_NAME,
ROUND (((SALARY * 12)/365), 2) "Daily Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

LAST_NAME                  Daily Pay
------------------------- ----------
Chen                          269.59
Faviet                        295.89
Greenberg                     394.52
Popp                          226.85
Sciarra                       253.15
Urman                         256.44
 
6 rows selected.

The query in Example 4-19 uses the numeric function TRUNC to display the daily pay of each employee in department 100, truncated to the nearest dollar.

Example 4-19 Truncating Numeric Data

SELECT LAST_NAME,
TRUNC (((SALARY * 12)/365), 2) "Daily Pay"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

LAST_NAME                  Daily Pay
------------------------- ----------
Chen                             269
Faviet                           295
Greenberg                        394
Popp                             226
Sciarra                          253
Urman                            256
 
6 rows selected.

See Also:

Oracle Database SQL Language Reference for more information about SQL numeric functions

Using the Concatenation Operator in Queries

The concatenation operator (||) combines two strings into one string, by appending the second string to the first. For example, 'a'||'b'='ab'. You can use this operator to combine information from two columns or expressions in the same column of the report, as in the query in Example 4-20.

Example 4-20 Concatenating Character Data

SELECT FIRST_NAME || ' ' || LAST_NAME "Name"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

Name
----------------------------------------------
John Chen
Daniel Faviet
Nancy Greenberg
Luis Popp
Ismael Sciarra
Jose Manuel Urman
 
6 rows selected.

Using Character Functions in Queries

Character functions accept character input. Most return character values, but some return numeric values. Each character function returns a single value for each row that is evaluated. The character functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

The functions UPPER, INITCAP, and LOWER display their character arguments in uppercase, initial capital, and lowercase, respectively.

The query in Example 4-21 displays LAST_NAME in uppercase, FIRST_NAME with the first character in uppercase and all others in lowercase, and EMAIL in lowercase.

Example 4-21 Changing the Case of Character Data

SELECT UPPER(LAST_NAME) "Last",
INITCAP(FIRST_NAME) "First",
LOWER(EMAIL) "E-Mail"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY EMAIL;

Result:

Last                      First                E-Mail
------------------------- -------------------- -------------------------
FAVIET                    Daniel               dfaviet
SCIARRA                   Ismael               isciarra
CHEN                      John                 jchen
URMAN                     Jose Manuel          jmurman
POPP                      Luis                 lpopp
GREENBERG                 Nancy                ngreenbe
 
6 rows selected.

The functions LTRIM and RTRIM trim characters from the left and right ends of their character arguments, respectively. The function TRIM trims leading zeros, trailing zeros, or both.

The query in Example 4-22 finds every clerk in the EMPLOYEES table and trims '_CLERK' from the JOB_ID, displaying only the characters that identify the type of clerk.

Example 4-22 Trimming Character Data

SELECT LAST_NAME,
RTRIM(JOB_ID, '_CLERK') "Clerk Type"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%_CLERK'
ORDER BY LAST_NAME;

Result:

LAST_NAME                 Clerk Type
------------------------- ----------
Atkinson                  ST
Baida                     PU
Bell                      SH
Bissot                    ST
Bull                      SH
Cabrio                    SH
Chung                     SH
Colmenares                PU
Davies                    ST
Dellinger                 SH
Dilly                     SH
 
LAST_NAME                 Clerk Type
------------------------- ----------
Everett                   SH
Feeney                    SH
...
LAST_NAME                 Clerk Type
------------------------- ----------
Walsh                     SH
 
45 rows selected.

The functions LPAD and RPAD pad their character arguments on the left and right, respectively, with a specified character (the default character is a space).

The query in Example 4-23 displays FIRST_NAME and LAST_NAME in 15-character columns, blank-padding FIRST_NAME on the left and LAST_NAME on the right.

Example 4-23 Padding Character Data

SELECT LPAD(FIRST_NAME,15) "First",
RPAD(LAST_NAME,15) "Last"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY FIRST_NAME;

Result:

First           Last
--------------- ---------------
         Daniel Faviet
         Ismael Sciarra
           John Chen
    Jose Manuel Urman
           Luis Popp
          Nancy Greenberg
 
6 rows selected.

The SUBSTR function accepts as arguments a string, a character position, and a length, and returns the substring that starts at the specified position in the string and has the specified length.

The query in Example 4-24 uses SUBSTR to abbreviate FIRST_NAME to first initial and to strip the area code from PHONE_NUMBER.

Example 4-24 Extracting Substrings from Character Data

SELECT SUBSTR(FIRST_NAME, 1, 1) || '. ' || LAST_NAME "Name",
SUBSTR(PHONE_NUMBER, 5, 8) "Phone"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

Name                         Phone
---------------------------- --------
J. Chen                      124.4269
D. Faviet                    124.4169
N. Greenberg                 124.4569
L. Popp                      124.4567
I. Sciarra                   124.4369
J. Urman                     124.4469
 
6 rows selected.

The REPLACE function replaces one substring with another.

The query in Example 4-25 uses the SUBSTR function in the WHERE clause to select the employees whose JOB_ID starts with 'SH', and uses the REPLACE function to replace 'SH' with 'SHIPPING' in each such JOB_ID.

Example 4-25 Replacing Substrings in Character Data

COLUMN "Job" FORMAT A15;
SELECT LAST_NAME,
REPLACE(JOB_ID, 'SH', 'SHIPPING') "Job"
FROM EMPLOYEES
WHERE SUBSTR(JOB_ID, 1, 2) = 'SH'
ORDER BY LAST_NAME;

Result:

LAST_NAME                 Job
------------------------- ---------------
Bell                      SHIPPING_CLERK
Bull                      SHIPPING_CLERK
Cabrio                    SHIPPING_CLERK
Chung                     SHIPPING_CLERK
Dellinger                 SHIPPING_CLERK
Dilly                     SHIPPING_CLERK
Everett                   SHIPPING_CLERK
Feeney                    SHIPPING_CLERK
Fleaur                    SHIPPING_CLERK
Gates                     SHIPPING_CLERK
Geoni                     SHIPPING_CLERK
 
LAST_NAME                 Job
------------------------- ---------------
Grant                     SHIPPING_CLERK
Jones                     SHIPPING_CLERK
McCain                    SHIPPING_CLERK
OConnell                  SHIPPING_CLERK
Perkins                   SHIPPING_CLERK
Sarchand                  SHIPPING_CLERK
Sullivan                  SHIPPING_CLERK
Taylor                    SHIPPING_CLERK
Walsh                     SHIPPING_CLERK
 
20 rows selected.

See Also:

Oracle Database SQL Language Reference for more information about SQL character functions

Using Datetime Functions in Queries

Datetime functions operate on date, timestamp, and interval values. Each datetime function returns a single value for each row that is evaluated. The datetime functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

To understand Example 4-26, you must understand the JOB_HISTORY table.

When an employee changes jobs, the START_DATE and END_DATE of his or her previous job are recorded in the JOB_HISTORY table. Employees who have changed jobs more than once have multiple rows in the JOB_HISTORY table, as the following query and its results show:

SELECT * FROM JOB_HISTORY
ORDER BY EMPLOYEE_ID;

Result:

EMPLOYEE_ID START_DAT END_DATE  JOB_ID     DEPARTMENT_ID
----------- --------- --------- ---------- -------------
        101 21-SEP-89 27-OCT-93 AC_ACCOUNT           110
        101 28-OCT-93 15-MAR-97 AC_MGR               110
        102 13-JAN-93 24-JUL-98 IT_PROG               60
        114 24-MAR-98 31-DEC-99 ST_CLERK              50
        122 01-JAN-99 31-DEC-99 ST_CLERK              50
        176 24-MAR-98 31-DEC-98 SA_REP                80
        176 01-JAN-99 31-DEC-99 SA_MAN                80
        200 17-SEP-87 17-JUN-93 AD_ASST               90
        200 01-JUL-94 31-DEC-98 AC_ACCOUNT            90
        201 17-FEB-96 19-DEC-99 MK_REP                20
 
10 rows selected.

The query in Example 4-26 uses the MONTHS_BETWEEN function to show how many months each employee held each of his or her previous jobs. For information about the MONTHS_BETWEEN function, see Oracle Database SQL Language Reference.

Example 4-26 Displaying the Number of Months Between Dates

SELECT e.EMPLOYEE_ID,
e.LAST_NAME,
TRUNC(MONTHS_BETWEEN(j.END_DATE, j.START_DATE)) "Months Worked"
FROM EMPLOYEES e, JOB_HISTORY j
WHERE e.EMPLOYEE_ID = j.EMPLOYEE_ID
ORDER BY "Months Worked";

Result:

EMPLOYEE_ID LAST_NAME                 Months Worked
----------- ------------------------- -------------
        176 Taylor                                9
        122 Kaufling                             11
        176 Taylor                               11
        114 Raphaely                             21
        101 Kochhar                              40
        201 Hartstein                            46
        101 Kochhar                              49
        200 Whalen                               53
        102 De Haan                              66
        200 Whalen                               69
 
10 rows selected.

The query in Example 4-27 uses the EXTRACT and SYSDATE functions to show how many years each employee in department 100 has been employed. The SYSDATE function returns the current date of the system clock. For more information about the SYSDATE function, see Oracle Database SQL Language Reference. For information about the EXTRACT function, see Oracle Database SQL Language Reference.

Example 4-27 Displaying the Number of Years Between Dates

SELECT LAST_NAME,
(EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM HIRE_DATE)) "Years Employed"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY "Years Employed";

Result:

LAST_NAME                 Years Employed
------------------------- --------------
Popp                                   9
Urman                                 10
Chen                                  11
Sciarra                               11
Greenberg                             14
Faviet                                14
 
6 rows selected.

Suppose that an employee receives his or her first check on the last day of the month in which he or she was hired. The query in Example 4-28 uses the LAST_DAY function to show the first pay day for each employee in department 100. For information about the LAST_DAY function, see Oracle Database SQL Language Reference.

Example 4-28 Displaying the Last Day of a Selected Month

SELECT LAST_NAME,
HIRE_DATE "Hired",
LAST_DAY(HIRE_DATE) "Paid"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;

Result:

LAST_NAME                 Hired     Paid
------------------------- --------- ---------
Faviet                    16-AUG-94 31-AUG-94
Greenberg                 17-AUG-94 31-AUG-94
Chen                      28-SEP-97 30-SEP-97
Sciarra                   30-SEP-97 30-SEP-97
Urman                     07-MAR-98 31-MAR-98
Popp                      07-DEC-99 31-DEC-99
 
6 rows selected.

Suppose that an employee receives his or her first evaluation six months after being hired. The query in Example 4-29 uses the ADD_MONTHS function to show the first evaluation day for each employee in department 100. For information about the ADD_MONTHS function, see Oracle Database SQL Language Reference.

Example 4-29 Displaying a Date Six Months from a Selected Date

SELECT LAST_NAME,
HIRE_DATE "Hired",
ADD_MONTHS(HIRE_DATE, 6) "Evaluated"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;

Result:

LAST_NAME                 Hired     Evaluated
------------------------- --------- ---------
Faviet                    16-AUG-94 16-FEB-95
Greenberg                 17-AUG-94 17-FEB-95
Chen                      28-SEP-97 28-MAR-98
Sciarra                   30-SEP-97 31-MAR-98
Urman                     07-MAR-98 07-SEP-98
Popp                      07-DEC-99 07-JUN-00
 
6 rows selected.

The query in Example 4-30 uses the SYSTIMESTAMP function to display the current system time and date. SYSTIMESTAMP is similar to SYSDATE, but it returns more information. For information about the SYSTIMESTAMP function, see Oracle Database SQL Language Reference.

The table in the FROM clause of the query, DUAL, is a one-row table that Oracle Database creates automatically along with the data dictionary. Select from DUAL when you want to compute a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. For more information about selecting from DUAL, see Oracle Database SQL Language Reference.

Example 4-30 Displaying System Date and Time

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' ||
ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP) || '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date"
FROM DUAL;

Results depend on current SYSTIMESTAMP value, but have this format:

System Time and Date
-------------------------------------------------------------------
18:47:33, 6/19/2008

See Also:

Oracle Database SQL Language Reference for more information about SQL datetime functions

Using Conversion Functions in Queries

Conversion functions convert one data type to another. The conversion functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

The query in Example 4-31 uses the TO_CHAR function to convert HIRE_DATE values (which are of type DATE) to character values that have the format FMMonth DD YYYY. FM removes leading and trailing blanks from the month name. FMMonth DD YYYY is an example of a datetime format model.

Example 4-31 Converting Dates to Characters Using a Format Template

SELECT LAST_NAME,
HIRE_DATE,
TO_CHAR(HIRE_DATE, 'FMMonth DD YYYY') "Date Started"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

LAST_NAME                 HIRE_DATE Date Started
------------------------- --------- -----------------
Chen                      28-SEP-97 September 28 1997
Faviet                    16-AUG-94 August 16 1994
Greenberg                 17-AUG-94 August 17 1994
Popp                      07-DEC-99 December 7 1999
Sciarra                   30-SEP-97 September 30 1997
Urman                     07-MAR-98 March 7 1998
 
6 rows selected.

The query in Example 4-32 uses the TO_CHAR function to convert HIRE_DATE values to character values that have the two standard formats DS (Date Short) and DL (Date Long).

Example 4-32 Converting Dates to Characters Using Standard Formats

SELECT LAST_NAME,
TO_CHAR(HIRE_DATE, 'DS') "Short Date",
TO_CHAR(HIRE_DATE, 'DL') "Long Date"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;

Result:

LAST_NAME                 Short Date Long Date
------------------------- ---------- -----------------------------
Chen                      9/28/1997  Sunday, September 28, 1997
Faviet                    8/16/1994  Tuesday, August 16, 1994
Greenberg                 8/17/1994  Wednesday, August 17, 1994
Popp                      12/7/1999  Tuesday, December 07, 1999
Sciarra                   9/30/1997  Tuesday, September 30, 1997
Urman                     3/7/1998   Saturday, March 07, 1998
 
6 rows selected.

The query in Example 4-33 uses the TO_CHAR function to convert SALARY values (which are of type NUMBER) to character values that have the format $99,999.99.

Example 4-33 Converting Numbers to Characters Using a Format Template

SELECT LAST_NAME,
TO_CHAR(SALARY, '$99,999.99') "Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY SALARY;

Result:

LAST_NAME                 Salary
------------------------- -----------
Popp                        $6,900.00
Sciarra                     $7,700.00
Urman                       $7,800.00
Chen                        $8,200.00
Faviet                      $9,000.00
Greenberg                  $12,000.00
 
6 rows selected.

The query in Example 4-34 uses the TO_NUMBER function to convert POSTAL_CODE values (which are of type VARCHAR2) to values of type NUMBER, which it uses in calculations.

Example 4-34 Converting Characters to Numbers

SELECT CITY,
POSTAL_CODE "Old Code",
TO_NUMBER(POSTAL_CODE) + 1 "New Code"
FROM LOCATIONS
WHERE COUNTRY_ID = 'US'
ORDER BY POSTAL_CODE;

Result:

CITY                           Old Code       New Code
------------------------------ ------------ ----------
Southlake                      26192             26193
South Brunswick                50090             50091
Seattle                        98199             98200
South San Francisco            99236             99237
 
4 rows selected.

The query in Example 4-35 uses the TO_DATE function to convert a string of characters whose format is Month dd, YYYY, HH:MI A.M. to a DATE value.

Example 4-35 Converting a Character String to a Date

SELECT TO_DATE('January 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Date"
FROM DUAL;

Result:

Date
---------
05-JAN-07

The query in Example 4-36 uses the TO_TIMESTAMP function to convert a string of characters whose format is DD-Mon-RR HH24:MI:SS.FF to a TIMESTAMP value.

Example 4-36 Converting a Character String to a Time Stamp

SELECT TO_TIMESTAMP('May 5, 2007, 8:43 A.M.',
'Month dd, YYYY, HH:MI A.M.') "Timestamp"
FROM DUAL;

Result:

Timestamp
------------------------------------------------------------------------------
05-MAY-07 08.43.00.000000000 AM

See Also:

Using Aggregate Functions in Queries

An aggregate function returns a single result row, based on a group of rows. The group of rows can be an entire table or view. The aggregate functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

Aggregate functions are especially powerful when used with the GROUP BY clause, which groups query results by one or more columns, with a result for each group.

The query in Example 4-37 uses the COUNT function and the GROUP BY clause to show how many people report to each manager. The wildcard character, *, represents an entire record.

Example 4-37 Counting the Number of Rows in Each Group

SELECT MANAGER_ID "Manager",
COUNT(*) "Number of Reports"
FROM EMPLOYEES
GROUP BY MANAGER_ID;

Result:

Manager Number of Reports
---------- -----------------
       100                14
                           1
       123                 8
       120                 8
       121                 8
       147                 6
       205                 1
       108                 5
       148                 6
       149                 6
       201                 1
 
   Manager Number of Reports
---------- -----------------
       102                 1
       101                 5
       114                 5
       124                 8
       145                 6
       146                 6
       103                 4
       122                 8
 
19 rows selected.

Example 4-37 shows that one employee does not report to a manager. The following query selects the first name, last name, and job title of that employee:

COLUMN FIRST_NAME FORMAT A10;
COLUMN LAST_NAME FORMAT A10;
COLUMN JOB_TITLE FORMAT A10;
 
SELECT e.FIRST_NAME,
e.LAST_NAME,
j.JOB_TITLE
FROM EMPLOYEES e, JOBS j
WHERE e.JOB_ID = j.JOB_ID
AND MANAGER_ID IS NULL;

Result:

FIRST_NAME LAST_NAME  JOB_TITLE
---------- ---------- ----------
Steven     King       President

When used with the DISTINCT option, the COUNT function shows how many distinct values are in a data set.

The two queries in Example 4-38 show the total number of departments and the number of departments that have employees.

Example 4-38 Counting the Number of Distinct Values in a Set

SELECT COUNT(*) FROM DEPARTMENTS;
 

Result:

COUNT(*)
----------
        27

SELECT COUNT(DISTINCT DEPARTMENT_ID) "Number of Departments"
FROM EMPLOYEES;

Result:

Number of Departments
---------------------
                   11

The query in Example 4-39 uses several aggregate functions to show statistics for the salaries of each JOB_ID.

Example 4-39 Using Aggregate Functions for Statistical Information

SELECT JOB_ID,
COUNT(*) "#",
MIN(SALARY) "Minimum",
ROUND(AVG(SALARY), 0) "Average",
MEDIAN(SALARY) "Median",
MAX(SALARY) "Maximum",
ROUND(STDDEV(SALARY)) "Std Dev"
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;

Result:

JOB_ID              #    Minimum    Average     Median    Maximum    Std Dev
---------- ---------- ---------- ---------- ---------- ---------- ----------
AC_ACCOUNT          1       8300       8300       8300       8300          0
AC_MGR              1      12000      12000      12000      12000          0
AD_ASST             1       4400       4400       4400       4400          0
AD_PRES             1      24000      24000      24000      24000          0
AD_VP               2      17000      17000      17000      17000          0
FI_ACCOUNT          5       6900       7920       7800       9000        766
FI_MGR              1      12000      12000      12000      12000          0
HR_REP              1       6500       6500       6500       6500          0
IT_PROG             5       4200       5760       4800       9000       1926
MK_MAN              1      13000      13000      13000      13000          0
MK_REP              1       6000       6000       6000       6000          0
 
JOB_ID              #    Minimum    Average     Median    Maximum    Std Dev
---------- ---------- ---------- ---------- ---------- ---------- ----------
PR_REP              1      10000      10000      10000      10000          0
PU_CLERK            5       2500       2780       2800       3100        239
PU_MAN              1      11000      11000      11000      11000          0
SA_MAN              5      10500      12200      12000      14000       1525
SA_REP             30       6100       8350       8200      11500       1524
SH_CLERK           20       2500       3215       3100       4200        548
ST_CLERK           20       2100       2785       2700       3600        453
ST_MAN              5       5800       7280       7900       8200       1066
 
19 rows selected.

To have the query return only rows where aggregate values meet specified conditions, use the HAVING clause.

The query in Example 4-40 shows how much each department spends annually on salaries, but only for departments for which that amount exceeds $1,000,000.

Example 4-40 Limiting Aggregate Functions to Rows that Satisfy a Condition

SELECT DEPARTMENT_ID "Department",
SUM(SALARY*12) "All Salaries"
FROM EMPLOYEES
HAVING SUM(SALARY * 12) >= 1000000
GROUP BY DEPARTMENT_ID;

Result:

Department All Salaries
---------- ------------
        50      1876800
        80      3654000

The RANK function returns the relative ordered rank of a number, and the PERCENT_RANK function returns the percentile position of a number.

The query in Example 4-41 shows that a salary of $3,000 is the 20th highest, and is in the 42nd percentile, among all clerks.

Example 4-41 Showing the Rank and Percentile of a Number Within a Group

SELECT RANK(3000) WITHIN GROUP
  (ORDER BY SALARY DESC) "Rank",
ROUND(100 * (PERCENT_RANK(3000) WITHIN GROUP
  (ORDER BY SALARY DESC)), 0) "Percentile"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%CLERK';

Result:

Rank Percentile
---------- ----------
        20         42

The DENSE_RANK function is like the RANK function, except that the identical values have the same rank, and there are no gaps in the ranking. Using the DENSE_RANK function, $3,000 is the 12th highest salary for clerks, as Example 4-42 shows.

Example 4-42 Showing the Dense Rank of a Number Within a Group

SELECT DENSE_RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%CLERK';

Result:

Rank
----------
        12

See Also:

Oracle Database SQL Language Reference for more informaton about SQL aggregate functions

Using NULL-Related Functions in Queries

The NULL-related functions facilitate the handling of NULL values. The NULL-related functions that SQL supports are listed and described in Oracle Database SQL Language Reference.

The query in Example 4-43 returns the last name and commission of the employees whose last names begin with 'B'. If an employee receives no commission (that is, if COMMISSION_PCT is NULL), the NVL function substitutes "Not Applicable" for NULL.

Example 4-43 Substituting a String for a NULL Value

SELECT LAST_NAME,
NVL(TO_CHAR(COMMISSION_PCT), 'Not Applicable') "COMMISSION"
FROM EMPLOYEES
WHERE LAST_NAME LIKE 'B%'
ORDER BY LAST_NAME;
 

Result:

LAST_NAME                 COMMISSION
------------------------- ----------------------------------------
Baer                      Not Applicable
Baida                     Not Applicable
Banda                     .1
Bates                     .15
Bell                      Not Applicable
Bernstein                 .25
Bissot                    Not Applicable
Bloom                     .2
Bull                      Not Applicable
 
9 rows selected.

The query in Example 4-44 returns the last name, salary, and income of the employees whose last names begin with 'B', using the NVL2 function: If COMMISSION_PCT is not NULL, the income is the salary plus the commission; if COMMISSION_PCT is NULL, income is only the salary.

Example 4-44 Specifying Different Expressions for NULL and Not NULL Values

SELECT LAST_NAME, SALARY,
NVL2(COMMISSION_PCT, SALARY + (SALARY * COMMISSION_PCT), SALARY) INCOME
FROM EMPLOYEES WHERE LAST_NAME LIKE 'B%'
ORDER BY LAST_NAME;
 

Result:

LAST_NAME                     SALARY     INCOME
------------------------- ---------- ----------
Baer                           10000      10000
Baida                           2900       2900
Banda                           6200       6882
Bates                           7300       8468
Bell                            4000       4000
Bernstein                       9500      11970
Bissot                          3300       3300
Bloom                          10000      12100
Bull                            4100       4100
 
9 rows selected.

See Also:

Using CASE Expressions in Queries

A CASE expression lets you use IF ... THEN ... ELSE logic in SQL statements without invoking procedures.

The query in Example 4-45 uses a CASE expression to show proposed salary increases, based on length of service.

Example 4-45 Using a CASE Expression in a Query

SELECT LAST_NAME "Name",
HIRE_DATE "Started",
SALARY "Salary",
CASE
  WHEN HIRE_DATE < TO_DATE('01-Jan-90') THEN TRUNC(SALARY*1.15, 0)
  WHEN HIRE_DATE < TO_DATE('01-Jan-95') THEN TRUNC(SALARY*1.10, 0)
  WHEN HIRE_DATE < TO_DATE('01-Jan-00') THEN TRUNC(SALARY*1.05, 0)
  ELSE SALARY
END "Proposed Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;

Result:

Name                      Started       Salary Proposed Salary
------------------------- --------- ---------- ---------------
Faviet                    16-AUG-94       9000            9900
Greenberg                 17-AUG-94      12000           13200
Chen                      28-SEP-97       8200            8610
Sciarra                   30-SEP-97       7700            8085
Urman                     07-MAR-98       7800            8190
Popp                      07-DEC-99       6900            7245
 
6 rows selected.

See Also:

Using the DECODE Function in Queries

The DECODE function compares a value or expression to search values, and returns a result when it finds a match. If a match is not found, then DECODE returns the default value, or NULL (if a default value is not specified).

The query in Example 4-46 uses the DECODE function to show proposed salary increases for three different jobs.

Example 4-46 Using the DECODE Function in a Query

SELECT LAST_NAME, JOB_ID, SALARY,
DECODE(JOB_ID,
  'PU_CLERK', SALARY * 1.10,
  'SH_CLERK', SALARY * 1.15,
  'ST_CLERK', SALARY * 1.20,
  SALARY) "Proposed Salary"
FROM EMPLOYEES
WHERE JOB_ID LIKE '%_CLERK'
AND LAST_NAME < 'E'
ORDER BY LAST_NAME;

Result:

LAST_NAME                 JOB_ID         SALARY Proposed Salary
------------------------- ---------- ---------- ---------------
Atkinson                  ST_CLERK         2800            3360
Baida                     PU_CLERK         2900            3190
Bell                      SH_CLERK         4000            4600
Bissot                    ST_CLERK         3300            3960
Bull                      SH_CLERK         4100            4715
Cabrio                    SH_CLERK         3000            3450
Chung                     SH_CLERK         3800            4370
Colmenares                PU_CLERK         2500            2750
Davies                    ST_CLERK         3100            3720
Dellinger                 SH_CLERK         3400            3910
Dilly                     SH_CLERK         3600            4140
 
11 rows selected.

See Also:

Oracle Database SQL Language Reference for information about the DECODE function