Toad For Oracle 12.1 Download

  

You can connect to Oracle Database only through a client program, such as SQL*Plus or SQL Developer.

Note:

Then, get your free, 30-day trial of Toad® to save hours, increase accuracy and enjoy easier workdays. If you are a licensed customer and are looking to upgrade to our latest version of Toad, please visit the support portal. For more details on what’s available for each Toad for Oracle Edition, please refer to this data sheet.

After connecting to Oracle Database through a client program, you enter and run commands in that client program. For details, see the documentation for your client program.

This chapter contains:

Connecting to Oracle Database from SQL*Plus

Jan 24, 2013 Toad for Oracle is a comprehensive database tool for development and administration that enables you to perform daily tasks efficiently and accurately. Only Toad combines the deepest functionality. Toad For Oracle Freeware 12.1 Download Toad® for Oracle is a comprehensive database tool for development and administration that enables you to perform daily tasks efficiently and accurately. It has grown over the past 10 years into the most widely-used tool of its kind. Toad ® for Oracle is the only developer tool that helps you simplify your workflow, reduce code defects and improve code quality and performance while supporting team collaboration. Automate administration tasks and proactively manage your databases while embracing performance optimization and risk mitigation.

SQL*Plus is a client program with which you can access Oracle Database. This section shows how to start SQL*Plus and connect to Oracle Database.

Note:

For steps 3 and 4 of the following procedure, you need a user name and password.

To connect to Oracle Database from SQL*Plus:

  1. If you are on a Windows system, display a Windows command prompt.

  2. At the command prompt, type sqlplus and then press the key Enter.

  3. At the user name prompt, type your user name and then press the key Enter.

  4. At the password prompt, type your password and then press the key Enter.

    Note:

    For security, your password is not visible on your screen.

    The system connects you to an Oracle Database instance.

    You are in the SQL*Plus environment. At the SQL> prompt, you can enter and run SQL*Plus commands, SQL statements, PL/SQL statements, and operating system commands.

    To exit SQL*Plus, type exit and press the key Enter.

    Note:

    Exiting SQL*Plus ends the SQL*Plus session, but does not shut down the Oracle Database instance.

    Example 2-1 starts SQL*Plus, connects to Oracle Database, runs a SQL SELECT statement, and exits SQL*Plus. User input is bold.

Example 2-1 Connecting to Oracle Database from SQL*Plus

See Also:

  • 'About SQL*Plus' for a brief description of SQL*Plus

  • SQL*Plus User's Guide and Reference for more information about starting SQL*Plus and connecting to Oracle Database

Connecting to Oracle Database from SQL Developer

SQL Developer is a client program with which you can access Oracle Database. With Oracle Database 11g Release 2 (11.2), Oracle recommends using SQL Developer version 4.0 or later, which you can download from:

This section assumes that SQL Developer is installed on your system, and shows how to start it and connect to Oracle Database. If SQL Developer is not installed on your system, then see Oracle Database SQL Developer User's Guide for installation instructions.

Note:

For the following procedure:

  • If you're using a SQL Developer kit that does not include the JDK, then the first time you start SQL Developer on your system, you must provide the full path to java.exe in step 1.

  • For step 3, you need a user name and password.

To connect to Oracle Database from SQL Developer:

  1. Start SQL Developer.

    For instructions, see Oracle Database SQL Developer User's Guide.

    If this is the first time you have started SQL Developer on your system, you are prompted to enter the full path to java.exe (for example, C:Program FilesJavajdk1.6.0_021binjava.exe). Either type the full path after the prompt or browse to it, and then press the key Enter.

  2. In the Connections frame, click the icon New Connection.

  3. In the New/Select Database Connection window:

    1. Type the appropriate values in the fields Connection Name, Username, and Password.

      For security, the password characters that you type appear as asterisks.

      Near the Password field is the check box Save Password. By default, it is deselected. Oracle recommends accepting the default.

    2. If the Oracle pane is not showing, click the tab Oracle.

    3. In the Oracle pane, accept the default values.

      (The default values are: Connection Type, Basic; Role, default, Hostname, localhost; Port, 1521; SID option, selected; SID field, xe.)

    4. Click the button Test.

      The connection is tested. If the connection succeeds, the Status indicator changes from blank to Success.


      Description of the illustration success.gif

    5. If the test succeeded, click the button Connect.

      The New/Select Database Connection window closes. The Connections frame shows the connection whose name you entered in the Connection Name field in step 3.

You are in the SQL Developer environment.

To exit SQL Developer, select Exit from the File menu.

Note:

Exiting SQL Developer ends the SQL Developer session, but does not shut down the Oracle Database instance. The next time you start SQL Developer, the connection you created using the preceding procedure still exists. SQL Developer prompts you for the password that you supplied in step 3 (unless you selected the check box Save Password).

See Also:

  • 'About SQL Developer' for a brief description of SQL Developer

  • Oracle Database SQL Developer User's Guide for more information about using SQL Developer to create connections to Oracle Database

Connecting to Oracle Database as User HR

This section shows how to unlock the HR account and connect to Oracle Database as the user HR, who owns the HR sample schema that the examples and tutorials in this document use.

To do the tutorials and examples in this document, you must connect to Oracle Database as the user HR.

This section contains:

Unlocking the HR Account

This section shows how to unlock the HR account and reset its password.

By default, when the HR schema is installed, the HR account is locked and its password is expired. You can connect to Oracle Database as the user HR only if the HR account is unlocked.

Note:

For the following procedure, you need the name and password of a user who has the ALTERUSER system privilege.

To unlock the HR account and reset its password:

Oracle

  1. Using SQL*Plus, connect to Oracle Database as a user with the ALTERUSER system privilege.

  2. At the SQL> prompt, unlock the HR account and reset its password:

    Caution:

    Choose a secure password. For guidelines for secure passwords, see Oracle Database Security Guide.

    The system responds:

    The HR account is unlocked and its password is password.

Now you can connect to Oracle Database as user HR with the password password.

See Also:

  • Oracle Database 2 Day + Security Guide for information about predefined user accounts provided by Oracle Database, including HR

  • Oracle Database SQL Developer User's Guide for information about accessing SQL*Plus within SQL Developer

Connecting to Oracle Database as User HR from SQL*Plus

This section shows how to connect to Oracle Database as the user HR from SQL*Plus.

Note:

If the HR account is locked, see 'Unlocking the HR Account' and then return to this section.

To connect to Oracle Database as user HR from SQL*Plus:

Note:

For this task, you need the password for the HR account.

  1. If you are connected to Oracle Database, close your current connection.

  2. Follow the directions in 'Connecting to Oracle Database from SQL*Plus', entering the user name HR at step 3 and the password for the HR account at step 4.

    You are now connected to Oracle Database as the user HR.

See Also:

SQL*Plus User's Guide and Reference for an example of using SQL*Plus to create an HR connection

Connecting to Oracle Database as User HR from SQL Developer

This section shows how to connect to Oracle Database as the user HR from SQL Developer.

Note:

If the HR account is locked, see 'Unlocking the HR Account' and then return to this section.

To connect to Oracle Database as user HR from SQL Developer:

Note:

For this task, you need the password for the HR account.

Follow the directions in 'Connecting to Oracle Database from SQL Developer', entering the following values at steps 3:

  • For Connection Name, enter hr_conn.

    (You can enter a different name, but the tutorials in this document assume that you named the connection hr_conn.)

  • For Username, enter HR.

  • For Password, enter the password for the HR account.

You are now connected to Oracle Database as the user HR.

Exploring Oracle Database with SQL*Plus

This section contains:

Note:

If you are not connected to Oracle Database as user HR from SQL*Plus, see 'Connecting to Oracle Database as User HR from SQL*Plus' and then return to this section.

Viewing HR Schema Objects with SQL*Plus

With SQL*Plus, you can view the objects that belong to the HR schema by querying the static data dictionary view USER_OBJECTS.

Example 2-2 shows how to view the names and data types of the objects that belong to the HR schema.

Example 2-2 Viewing HR Schema Objects with SQL*Plus

Result is similar to:

See Also:

  • Oracle Database Reference for information about USER_OBJECTS

  • 'Selecting Table Data' for information about using queries to view table data

  • 'About Sample Schema HR' for general information about the schema HR

Viewing EMPLOYEES Table Properties and Data with SQL*Plus

With SQL*Plus, you can use the SQL*Plus command DESCRIBE to view the properties of the columns of the EMPLOYEES table in the HR schema and the SQL statement SELECT to view the data. To view other properties of the table, use static data dictionary views (for example, USER_CONSTRAINTS, USER_INDEXES, and USER_TRIGGERS).

Example 2-3 shows how to view the properties of the EMPLOYEES table in the HR schema.

Example 2-3 Viewing EMPLOYEES Table Properties with SQL*Plus

Result:

Example 2-4 shows how to view some data in the EMPLOYEES table in the HR schema.

Example 2-4 Viewing EMPLOYEES Table Data with SQL*Plus

Result is similar to:

See Also:

  • SQL*Plus User's Guide and Reference for information about DESCRIBE

  • 'Selecting Table Data' for information about using queries to view table data

  • Oracle Database Reference for information about static data dictionary views

Exploring Oracle Database with SQL Developer

This section contains:

Toad for oracle 12.1 download for windows 10

Tutorial: Viewing HR Schema Objects with SQL Developer

This tutorial shows how to use SQL Developer to view the objects that belong to the HR schema—that is, how to browse the HR schema.

Note:

If you are not connected to Oracle Database as user HR from SQL Developer, see 'Connecting to Oracle Database as User HR from SQL Developer' and then return to this tutorial.

To browse the HR schema:

  1. In the Connections frame, to the left of the hr_conn icon, click the plus sign (+).

    If you are not connected to the database, the Connection Information window opens. If you are connected to the database, the hr_conn information expands (see the information that follows 'Click OK' in step 2).

  2. If the Connection Information window opens:

    1. In the User Name field, enter hr.

    2. In the Password field, enter the password for the user hr.

    3. Click OK.

    The hr_conn information expands: The plus sign becomes a minus sign (-), and under the hr_conn icon, a list of schema object types appears—Tables, Views, Indexes, and so on. (If you click the minus sign, the hr_conn information collapses: The minus sign becomes a plus sign, and the list disappears.)

Download Toad For Oracle Version

See Also:

  • Oracle Database SQL Developer User's Guide for more information about the SQL Developer user interface

  • 'About Sample Schema HR' for general information about schema HR

Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer

This tutorial shows how to use SQL Developer to view the properties and data of the EMPLOYEES table in the HR schema.

Note:

If you are not browsing the HR schema, see 'Tutorial: Viewing HR Schema Objects with SQL Developer' and then return to this tutorial.

To view the properties and data of the EMPLOYEES table:

  1. In the Connections frame, expand Tables.

    Under Tables, a list of the tables in the HR schema appears.

  2. Select the table EMPLOYEES.

    In the right frame of the Oracle SQL Developer window, in the Columns pane, a list of all columns of this table appears. To the right of each column are its properties—name, data type, and so on. (To see all column properties, move the horizontal scroll bar to the right.)

  3. In the right frame, click the tab Data.

    The Data pane appears, showing a numbered list of all records in this table. (To see more records, move the vertical scroll bar down. To see more columns of the records, move the horizontal scroll bar to the right.)

  4. In the right frame, click the tab Constraints.

    The Constraints pane appears, showing a list of all constraints on this table. To the right of each constraint are its properties—name, type, search condition, and so on. (To see all constraint properties, move the horizontal scroll bar to the right.)

  5. Explore the other properties by clicking on the appropriate tabs.

    To see the SQL statement for creating the EMPLOYEES table, click the SQL tab. The SQL statement appears in a pane named EMPLOYEES. To close this pane, click the x to the right of the name EMPLOYEES.

See Also:

Oracle Database SQL Developer User's Guide for more information about the SQL Developer user interface

Selecting Table Data

This section contains:

Note:

To do the tutorials and examples in this section, 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:

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 (or any other SQL statement) after the SQL> prompt.

In the SQL Developer environment, you can enter a query (or any other SQL statement) in the Worksheet.

Note:

When the result of a query is displayed, records can be in any order, unless you specify their order with the ORDERBY 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 Worksheet in SQL Developer

Running Queries in SQL Developer

This section explains how to run queries in SQL Developer, by using the Worksheet.

Note:

The Worksheet is not limited to queries; you can use it to run any SQL statement.Toad For Oracle 12.1 Download

To run queries in SQL Developer:

  1. If the right frame of SQL Developer shows the hr_conn pane:

    1. If the Worksheet subpane does not show, click the tab Worksheet.

    2. Go to step 3.

  2. Click the icon SQL Worksheet.

  3. If the Select Connection window opens:

    1. If the Connection field does not have the value hr_conn, select that value from the menu.

    2. Click OK.

    A pane appears with a tab labeled hr_conn and two subpanes, Worksheet and Query Builder. In the Worksheet, you can enter a SQL statement.

  4. In the Worksheet, type a query (a SELECT statement).

  5. Click the icon Run Statement.

    The query runs. Under the Worksheet, the Query Result pane appears, showing the query result.

  6. Under the hr_conn tab, click the icon Clear.

    The query disappears, and you can enter another SQL statement in the Worksheet. When you run another SQL statement, its result appears in the Query Result pane, replacing the result of the previously run SQL statement.

See Also:

Oracle Database SQL Developer User's Guide for information about using the 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. If a pane with the tab hr_conn is there, select it. Otherwise, click the icon SQL Worksheet, as in 'Running Queries in SQL Developer'.

  2. In the Worksheet, enter this query:

  3. Click the icon Run Statement.

    The query runs. Under the Worksheet, the Query Result pane appears, showing all columns of the EMPLOYEES table.

Caution:

Be very careful about using SELECT* on tables with columns that store sensitive data, such as passwords or credit card information.

See Also:

'Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer' 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. If a pane with the tab hr_conn is there, select it. Otherwise, click the icon SQL Worksheet, as in 'Running Queries in SQL Developer'.

  2. If the Worksheet pane contains a query, clear the query by clicking the icon Clear.

  3. In the Worksheet, enter this query:

  4. Click the icon Run Statement.

    The query runs. Under the Worksheet, the Query Result pane appears, showing the results of the query, which are similar to:

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 2-5 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 2-5 Displaying Selected Columns Under New Headings

Result is similar to:

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

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

Result is similar to:

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 2-7 selects data only for employees in department 90.

Example 2-7 Selecting Data from One Department

Result is similar to:

To select data only for employees in departments 100, 110, and 120, use this WHERE clause:

The query in Example 2-8 selects data only for employees whose last names start with 'Ma'.

Example 2-8 Selecting Data for Last Names that Start with the Same Substring

Result is similar to:

To select data only for employees whose last names include 'ma', use this WHERE clause:

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

Example 2-9 Selecting Data that Satisfies Two Conditions

Result is similar to:

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

Sorting Selected Data

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

The query results in Example 2-10 are sorted by LAST_NAME, in ascending order (the default).

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

Example 2-10 Sorting Selected Data by LAST_NAME

Result:

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

Example 2-11 Sorting Selected Data by an Unselected Column

Result:

See Also:

Oracle Database SQL Language Reference for more information about the SELECT statement, including the ORDERBY 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 2-12. Such a query is called a join.

Example 2-12 Selecting Data from Two Tables (Joining Two Tables)

Result:

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 2-12:

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

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

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.

This section contains:

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).

Oracle

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

Example 2-13 Using an Arithmetic Expression in a Query

Result:

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 2-14 uses the numeric function ROUND to display the daily pay of each employee in department 100, rounded to the nearest cent.

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

Example 2-15 Truncating Numeric Data

Result:

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 2-16.

See Also:

Oracle Database SQL Language Reference for more information about the concatenation operator

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 2-17 displays LAST_NAME in uppercase, FIRST_NAME with the first character in uppercase and all others in lowercase, and EMAIL in lowercase.

Example 2-17 Changing the Case of Character Data

Result:

See Also:

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

Using Datetime Functions in Queries

Datetime functions operate on DATE, time stamp, 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.

For each DATE and time stamp value, Oracle Database stores this information:

  • Year

  • Month

  • Date

  • Hour

  • Minute

  • Second

    For each time stamp value, Oracle Database also stores the fractional part of the second, whose precision you can specify. To store the time zone also, use the data type TIMESTAMPWITHTIMEZONE or TIMESTAMPWITHLOCALTIMEZONE.

For more information about the DATE data type, see Oracle Database SQL Language Reference.

For more information about the TIMESTAMP data type, see Oracle Database SQL Language Reference.

For information about the other time stamp data types and the interval data types, see Oracle Database SQL Language Reference.

The query in Example 2-18 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 as a DATE value. 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 2-18 Displaying the Number of Years Between Dates

Result:

The query in Example 2-19 uses the SYSTIMESTAMP function to display the current system date and time. The SYSTIMESTAMP function returns a TIMESTAMP value. 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 2-19 Displaying System Date and Time

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

See Also:

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

Using Conversion Functions in Queries

Toad For Oracle 12.1 Download

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 2-20 uses the TO_CHAR function to convert HIRE_DATE values (which are of type DATE) to character values that have the format FMMonthDDYYYY. FM removes leading and trailing blanks from the month name. FMMonthDDYYYY is an example of a datetime format model. For information about datetime format models, see Oracle Database SQL Language Reference.

Example 2-20 Converting Dates to Characters Using a Format Template

Result:

The query in Example 2-21 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 2-21 Converting Characters to Numbers

Result:

See Also:

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

Using Aggregate Functions in Queries

An aggregate function takes a group of rows and returns a single result row. 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 GROUPBY clause, which groups query results by one or more columns, with a result for each group.

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

Example 2-22 Counting the Number of Rows in Each Group

Result:

Example 2-22 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:

Result:

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

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

Example 2-23 Limiting Aggregate Functions to Rows that Satisfy a Condition

Result:

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

Example 2-24 Using Aggregate Functions for Statistical Information

Result:

See Also:

Oracle Database SQL Language Reference for more information 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 2-25 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 2-25 Substituting a String for a NULL Value

Result:

The query in Example 2-26 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 2-26 Specifying Different Expressions for NULL and Not NULL Values

Result:

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 subprograms. There are two kinds of CASE expressions, simple and searched.

The query in Example 2-27 uses a simple CASE expression to show the country name for each country code.

Example 2-27 Using a Simple CASE Expression in a Query

Result:

Toad For Oracle 14.1 Download

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

Example 2-28 Using a Searched CASE Expression in a Query

Result:

See Also:

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

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

Using the DECODE Function in Queries

The DECODE function compares an expression to several search values. Whenever the value of the expression matches a search value, DECODE returns the result associated with that search value. If DECODE finds no match, then it returns the default value (if specified) or NULL (if no default value is specified).

The query in Example 2-29 uses the DECODE function to show proposed salary increases for three different jobs. The expression is JOB_ID; the search values are 'PU_CLERK', 'SH_CLERK', and 'ST_CLERK'; and the default is SALARY.

Example 2-29 Using the DECODE Function in a Query

Download Toad For Oracle Freeware 12.1 (x64 & X32) Free

Result:

Note:

The arguments of the DECODE function can be any of the SQL numeric or character types. Oracle automatically converts the expression and each search value to the data type of the first search value before comparing. Oracle automatically converts the return value to the same data type as the first result. If the first result has the data type CHAR or if the first result is NULL, then Oracle converts the return value to the data type VARCHAR2

Toad For Oracle Download

.

See Also:

Toad For Oracle 12 Youtube

  • Oracle Database SQL Language Reference for information about the DECODE function