Which query would give the following result? last_name first_name department_id king steven 90

This chapter contains the following topics:

• About Queries

• Running Queries in SQL Developer

• Tutorial: Selecting All Columns of a Table

• Tutorial: Selecting Specific Columns of a Table

• Displaying Selected Columns Under New Headings

• Selecting Data that Satisfies Specified Conditions

• Specifying Conditions with Regular Expressions

• Sorting Selected Data

• Selecting Data from Multiple Tables

• Using Operators and Functions in Queries

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:

• Oracle Database SQL Language Reference for more information about queries and subqueries

• Oracle Database SQL Language Reference for more information about the SELECT statement

• SQL*Plus User's Guide and Reference for more information about the SQL*Plus command line interface

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

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.

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.

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.

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:

• Oracle Database SQL Language Reference for more information about the SELECT statement, including the WHERE clause

• Oracle Database SQL Language Reference for more information about SQL conditions

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:

• Oracle Database Advanced Application Developer's Guide for more information about using regular expressions in database applications

• Oracle Database SQL Language Reference for syntax of regular expressions

• Oracle Database SQL Language Reference for more information about the REGEXP_LIKE expression

• Oracle Database SQL Language Reference for more information about the REGEXP_REPLACE expression

• Oracle Database SQL Language Reference for more information about the REGEXP_SUBSTR expression

• Oracle Database SQL Language Reference for more information about the REGEXP_COUNT expression

• Oracle Database SQL Language Reference for more information about the REGEXP_INSTR expression

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.

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;

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:

• Using Arithmetic Operators in Queries

• Using Numeric Functions in Queries

• Using the Concatenation Operator in Queries

• Using Character Functions in Queries

• Using Datetime Functions in Queries

• Using Conversion Functions in Queries

• Using Aggregate Functions in Queries

• Using NULL-Related Functions in Queries

• Using CASE Expressions in Queries

• Using the DECODE Function in Queries

See Also:

• Oracle Database SQL Language Reference for more information about SQL operators

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

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.

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.

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

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:

• Oracle Database SQL Language Reference for more information about SQL conversion functions

• Oracle Database SQL Language Reference for more information about the TO_CHAR function

• Oracle Database SQL Language Reference for more information about the TO_NUMBER function

• Oracle Database SQL Language Reference for more information about the TO_DATE function

• Oracle Database SQL Language Reference for more information about the TO_TIMESTAMP function

• "About the NLS_DATE_FORMAT Parameter"

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

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:

• Oracle Database SQL Language Reference for more information about the NVL function

• Oracle Database SQL Language Reference for more information about the NVL2 function

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 CASE Statement"

• Oracle Database SQL Language Reference for more information about CASE expressions

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.

Which operator is used to combine columns of character strings to other columns?

Which clause would you include in a select statement to sort the rows returned by the lastname column?

How to write query in Oracle?

What function would you use to return the highest date in month?