Oracle SQL and SQL*Plus: Advanced SELECT Statements

Audience

Application developers database administrators system designer/developers and technical support professionals. Learners should be familiar with the basic principles of SQL and PL/SQL or have completed courses 61110 61111 61112 and 60113.

Objective

• Write singlerow subqueries.
• Write multiplerow and multiplecolumn subqueries.
• Write correlated subqueries.
• Write queries using number DECODE and character functions.
• Write hierarchical queries and format hierarchical reports.
• Write compound queries by using set operators and control the order of the output of the compound queries.

Topics Include

Unit 1: Single-Row Subqueries

• Identify the properties of the different types of subqueries.
• Sequence the steps in the execution of a single-row subquery.
• Complete a SELECT statement by using a single-row subquery.
• Complete a SELECT single-row subquery by incorporating comparison operators.
• Complete a query by using group functions in a single-row subquery.
• Complete a SELECT statement by using multiple single-row subqueries.
• Complete the statement to create a table based on the definition of an existing table by using a subquery.
• Complete the statement to insert values into a table from another table by using a subquery.
• Complete the UPDATE statement by using subqueries.
• Complete a DELETE statement by using subqueries.

Unit 2: Multiple-Value Subqueries

• Identify the multiple-row comparison operator used for a given situation.
• Complete the query by using the appropriate operator that checks for the presence or absence of a value in a given set of discrete values.
• Complete a query by using the ANY operator.
• Complete a query by using the ALL operator.
• Complete a query by using a multiple-column subquery.
• Identify the difference between pairwise multiple-column subqueries and non-pairwise subqueries.
• Complete an UPDATE statement by suing a multiple-column subquery.

Unit 3: Correlated Subqueries

• Complete a SELECT statement by using a correlated subquery.
• Complete a DELETE statement by using a correlated subquery.
• Complete an UPDATE statement by using a correlated subquery.
• Complete a query by using the EXISTS operator.
• Complete a query by using the NOT EXISTS operator.

Unit 4: Functions in Queries

• Identify the features of a dual table.
• Format numbers by using numeric functions.
• Write the DECODE function to mimic the IF-THEN-ELSE logic within a query.
• Write a function to determine the position of a substring within a string.
• Write a SUBSTR function to extract a substring from within a string.
• Write a function to substitute a substring in a string.
• Write a function to pad a given character string.
• Write the function to trim a given character string.

Unit 5: Hierarchical Queries

• Identify the features of a hierarchical query.
• Complete a SELECT statement to display rows from a table that has a hierarchy.
• Complete the statement to format a hierarchical report by using the PRIOR operator and LEVEL pseudocolumn.
• Complete a statement to prune a hierarchical tree.

Unit 6: Set Operators

• Write the compound queries by using the different set operators.
• Write a compound query that displays a set of rows returned by any set of queries within it.
• Write a compound query that displays the common rows returned by the queries within it.
• Write a compound query that displays all the rows returned by the first query and not by the second query.
• Identify the rules that must be followed when using set operators in compound queries.
• Control the order of the output of a compound query by using the ORDER BY clause.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media