What you will learn:

This class is applicable to Oracle8i, Oracle9i and Oracle Database 10g users.

This course introduces Oracle Database 10g technology and the relational database concepts and the powerful SQL programming language. This course provides the learners with the essential SQL skills of querying the database, the meta data and creating database objects.

In addition, the course also delves into the advanced querying and reporting techniques, data warehousing concepts and manipulating large data sets in different time zones.

Course Objectives:
Search data using advanced sub queries
Retrieve row and column data from tables with the SELECT statement
Employ SQL functions to generate and retrieve customized data
Run data manipulation statements (DML) to update data in the Oracle Database 10g
Control user access and manage schema objects

Course Topics:
Introduction
List the Oracle Database 10g main features
Provide an overview of: components, internet platform, apps server and developer suite
Describe relational and object relational database designs
Review the system development life cycle
Describe different means of storing data
Review the relational database concept
Define the term data models
Show how multiple tables can be related

Retrieving Data Using the SQL SELECT Statement
Define projection, selection, and join terminology
Review the syntaxes for the basic SQL SELECT statements
Use Arithmetic and Concatenation operators in SQL statements
List the differences between SQL and iSQL*Plus
Log into the database using iSQL*Plus
Explain the iSQL*Plus interface
Categorize the different types of iSQL*Plus commands
Save SQL statements to script files

Restricting and Sorting Data
Limit rows using a selection
Using the WHERE clause to retrieve specific rows
Using the comparison conditions in the WHERE clause
Use the LIKE condition to compare literal values
List the logical conditions AND, OR, NOT
Describe the rules of precedence for the conditions shown in this lesson
Sort rows with the ORDER BY clause
Use ampersand substitution in iSQL*Plus to restrict and sort output at run time

Using Single Row Functions to Customize Reports
Show the differences between single row and multiple row SQL functions
Categorize the character functions into case manipulation and character manipulation types
Use the character manipulation functions in the SELECT and WHERE clauses
Explain and use the DATE and numeric functions
Use the SYSDATE function to retrieve the current date in the default format
Introduce the DUAL table as a means to view function results
List the rules for applying the arithmetic operators on dates
Use the arithmetic operators with dates in the SELECT clause

Reporting Aggregated Data Using the Group Functions
Describe and categorize the group functions
Use the group functions
Utilize the DISTINCT keyword with the group functions
Describe how nulls are handled with the group functions
Create groups of data with the GROUP BY clause
Group data by more than one column
Avoid illegal queries with the group functions
Exclude groups of data with the HAVING clause

Displaying Data From Multiple Tables
Show the join tables syntax using SQL 99 syntax
Use table aliases to write shorter code and explicitly identify columns from multiple tables
Issue a SQL CROSS JOIN statement to produce a cartesian product
Use the NATURAL JOIN clause to retrieve data from tables with the same named columns
Create a join with the USING clause to identify specific columns between tables
Create a three way join with the ON clause to retrieve information from 3 tables
List the types of outer joins LEFT, RIGHT, and FULL
Add additional conditions when joining tables with the AND clause

Using Sub queries to Solve Queries
List the syntax for sub queries in a SELECT statements WHERE clause
List the guidelines for using sub queries
Describe the types of sub queries
Execute single row sub queries and use the group functions in a sub query
Identify illegal statements with sub queries
Execute multiple row sub queries
Analyze how the ANY and ALL operators work in multiple row sub queries
Explain how null values are handled in sub queries

Using the SET Operators
Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
Use the UNION ALL operator to return all rows from multiple tables
Describe the INTERSECT operator
Use the INTERSECT operator
Explain the MINUS operator
Use the MINUS operator
List the SET operator guidelines
Order results when using the UNION operator

Manipulating Data
Write INSERT statements to add rows to a table
Copy rows from another table
Create UPDATE statements to change data in a table
Generate DELETE statements to remove rows from a table
Use a script to manipulate data
Save and discard changes to a table through transaction processing
Show how read consistency works
Describe the TRUNCATE statement

Using DDL Statements to Create and Manage Tables
List the main database objects and describe the naming rules for database objects
Introduce the schema concept
Display the basic syntax for creating a table and show the DEFAULT option
Explain the different types of constraints
Show resulting exceptions when constraints are violated with DML statements
Create a table with a sub query
Describe the ALTER TABLE functionality
Remove a table with the DROP statement and Rename a table

Creating Other Schema Objects
List the main database objects and describe the naming rules for database objects
Introduce the schema concept
Display the basic syntax for creating a table and show the DEFAULT option
Explain the different types of constraints
Show resulting exceptions when constraints are violated with DML statements
Create a table with a sub query and remove a table with the DROP statement
Describe the ALTERTABLE functionality
Rename a table

Managing Objects with Data Dictionary Views
Describe the structure of each of the dictionary views
List the purpose of each of the dictionary views
Write queries that retrieve information from the dictionary views on the schema objects

Controlling User Access
Controlling user access
System versus objects privileges
Creating user sessions and granting system privileges
Using roles to define user groups
Creating and granting privileges to a role
Granting and revoking object privileges
Changing your password
Using Database Links

Manage Schema Objects
Creating directories
Creating and querying external tables
Creating Index Organized Tables
Creating Function based indexes
Dropping Columns
Altering the structure of tables and adding constraints
Performing FLASHBACK Statement
Materialized Views overview

Manipulating Large Data Sets
Using the MERGE Statement
Performing DML with Subqueries
Performing DML with a RETURNING Clause
Overview of Multitable INSERT Statements
Tracking Changes in DML

Generating Reports by Grouping Related Data
Overview of GROUP BY and Having Clause
Aggregating data with ROLLUP and CUBE Operators
Determine subtotal groups using GROUPING Functions
Compute multiple groupings with GROUPING SETS
Define levels of aggregation with Composite Columns
Create combinations with Concatenated Groupings

Managing Data in Different Time Zones
TIME ZONES
Oracle9i Date time Support
Conversion operations

Searching Data Using Advanced Sub queries
Subquery Overview
Using a Sub query
Comparing several columns using Multiple-Column Sub queries
Defining a Data source Using a Sub query in the FROM Clause
Returning one Value using Scalar Sub query Expressions
Performing ROW by-row processing with Correlated Sub queries
Reusing query blocks using the WITH Clause

Hierarchical Data Retrieval
Sample Data from the EMPLOYEES Table
The Tree Structure of Employee data
Hierarchical Queries
Ranking Rows with LEVEL
Formatting Hierarchical Reports Using LEVEL and LPAD
Pruning Branches with the WHERE and CONNECT BY clauses

Performing Regular Expression Support and Case Insensitive
Regular Expression Support Overview
Describing simple and complex patterns for searching and manipulating data