Oracle SQL

Oracle PL/SQL

Oracle Forms

Oracle Reports

Residential Courses

Prices

Location

Oracle SQL: Data Retrieval Techniques
Computer based training course

Course ID: 61111

Course Overview

This course is the second in a three-part Oracle series that is based on 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.

Learn To:

• Retrieve rows from multiple tables by using an equijoin, a non-equijoin, 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 single-row, multiple row, multiple-column 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.

Content Emphasis

Skills-Based

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: Certified Database Administrator and Oracle Certified Application Developer, Release 2.

Total Learning Time

6 - 8 Hour(s)

Course Contents

Unit 1: Displaying Data From MultipleTables

Duration: 1 - 2 Hour(s)

• 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

Duration: 1 - 2 Hour(s)

• 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

Duration: 2 Hour(s)

• 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

Duration: 2 Hour(s)

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

Anglesey has over 125 miles of coastline designated "areas of Natural Outstanding Beauty".

Tyn Y Felin is a mere five minutes away from the A55 North Wales Coast Expressway and is a perfectly situated coastal retreat on the serene and beautiful Isle of Anglesey.