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).
The network 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 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
Serebra Learning Corporation 119 - 7565 132nd Street Surrey BC V3W 1K5 Canada