Oracle SQL: Data Retrieval Techniques

This course is the second in a three-part Oracle series that is based on the ILT course Introduction to Oracle: SQL and PL/SQL. This course describes how to access data from more than one table using joins and aggregating data using group functions. This course also describes how to embed a SQL statement in a clause of another SQL statement to enhance the power of SQL queries. In addition how to use SQL*Plus commands to build on basic SQL statements and create customized queries that retrieve the information and display it in a format specified by the user will be covered.

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 designer/developers and technical support professionals. Prior to taking this course participants should be able to use a graphical user interface (GUI) and should be familiar with data processing concepts and techniques. Some prior knowledge of programming languages would be an advantage. In addition participants should have taken the first part of this series Oracle SQL: Basic SELECT Statements (61110). This course will help candidates prepare for test 1Z0-001 which applies towards the following Oracle Certified Professional (OCP) Program certification tracks: Oracle7.3 Certified Database Administrator Oracle8 Certified Database Administrator and Oracle Certified Application Developer Release 2.

Objective

• Retrieve rows from multiple tables by using an equijoin a nonequijoin an outer join and a self join.
• Write SQL statements that contain common group functions.
• Restrict groups of rows retrieved by using the HAVING clause and to write SQL statements that contain common group functions.
• Group rows retrieved by using the GROUP BY clause.
• Write singlerow multiple row multiplecolumn subqueries.
• Obtain values at run time by creating and maintaining substitution variables.
• Use SQL*Plus format commands to format report output.
• Run a formatted report using a SQL*Plus script file.

Topics Include

Unit 1: Displaying Data From MultipleTables

• Identify the four join types used to display data from more than one table.
• Identify a Cartesian product.
• Retrieve rows from two tables by using an equijoin.
• Specify additional search conditions in an equijoin by using the AND operator in the WHERE clause.
• Simplify a query by using a table alias in an equijoin.
• Retrieve rows from more than two tables by using an equijoin.
• Retrieve rows from two tables by using a non-equijoin.
• Retrieve rows from two tables by using an outer join.
• Retrieve rows within the same table by using a self join.

Unit 2: Group Functions

• Identify the features of a group function.
• Write SQL statements that contain common group functions.
• Manage null values when using group functions in a SQL statement.
• Group rows retrieved by using the GROUP BY clause.
• Retrieve a subgroup of rows from within a larger group retrieved by using the GROUP BY clause.
• Restrict groups of rows retrieved by using the HAVING clause.
• Identify illegal queries involving group functions.
• Write a SQL statement that contains nested group functions.

Unit 3: Subqueries

• Identify the requirements for using subqueries.
• Identify the characteristics of three subquery types.
• Write a single-row subquery to return one row in the WHERE clause of a SELECT statement.
• Retrieve data that is dependent upon the value of a group function in a single-row subquery.
• Write a single-row subquery in the HAVING clause of a SELECT statement.
• Identify two common errors that occur when using single-row subqueries.
• Identify the guidelines for using multiple-row subqueries.
• Write a multiple-row subquery.
• Identify how a query handles null values returned from a multiple-row subquery.
• Write a multiple-column subquery.
• Identify the different output that results from pairwise and nonpairwise subqueries.
• Write a multiple-column subquery in the FROM clause of a SELECT statement.

Unit 4: Reporting with SQL*Plus

• Identify the characteristics of substitution variables.
• Prompt the user for a number value using a variable prefixed with a single ampersand.
• Prompt the user for character and date values using a variable prefixed with a single ampersand and surrounded by single quotation marks.
• Specify column names and expressions at run time using substitution variables.
• Prompt the user for a value using a variable prefixed with a double ampersand.
• Accept user input at run time using the ACCEPT command.
• Maintain a variable at run time using the DEFINE and UNDEFINE commands.
• Match the SET command variables with their functions.
• Identify the guidelines for using the SQL*Plus format commands.
• Control the display of a column using the COLUMN command options.
• Group related rows using the BREAK command.
• Format page headers and footers using the TTITLE and BTITLE format commands.
• Run a formatted report using a SQL*Plus script file.

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

CDROM

• Oracle SQL: Basic SELECT Statements
• Oracle SQL: DML and DDL