Oracle SQL and SQL*Plus: Advanced SELECT Statements

This course is the first course in a two-part series which covers advanced SQL and SQL*Plus. This course introduces the learner to the advanced SELECT statements and the use of subqueries in Oracle Statements. In addition this course explores the functions used in Oracle and covers the use of set operators to combine two or more queries.

This is primarily online training This is an online eLearning or CBT training program Contact Serebra Learning Corporation for more information

Duration: 8 hours Training Presented in: English

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

• Oracle SQL and SQL*Plus: SQL*Plus and Reporting