Course Overview

Learn To:

• Write single-row subqueries.

 
• Write multiple-row and multiple-column 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.

Skills-Based

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 using Procedure Builder or have completed courses 60111, 60112 and 60113.

Total Learning Time

7 - 8 Hour(s)

Course Contents

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

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

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

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

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

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

Click here for Secure Yahoo Order: Oracle SQL and SQL*Plus: Advanced SELECT Statements

Get Train in Oracle
Oracle Training CD

Best Regards,
Oracle Database, SQL, Application, Programming Tips