|
Provided by: 123-CBT Computer Based Training Oracle SQL and SQL Plus Advanced SELECT Statements |
 |
Training
Provided by 123-CBT Computer Based Training
Course Overview
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.
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.
Content Emphasis
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
Unit 1: Single-Row Subqueries
Duration: 2 - 3 Hour(s)- 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
Duration: 1 Hour(s)- 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
Duration: 1 Hour(s)- 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
Duration: 1 Hour(s)- 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
Duration: 1 Hour(s)- 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
Duration: 1 Hour(s)- 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.
|
|
||||||||||
Oracle SQL and SQL Plus Advanced SELECT Statements
--
About The Training Provider: 123-CBT Computer Based Training
123-CBT Computer Based Training - 123-CBT offers discount pricing on top quality eLearning
programs from leading computer based training providers. Many of the training courses are available both online or on CD so that you can study at home at your own pace:
E-Learning available for
- ABAP 6.10
- Access 2003
- Acrobat 6.0
- ASP
- ASP.NET
- C
- Captivate 1.0
- Crystal Reports 8.5
- Crystal Reports v10
- Dreamweaver MX
-...
