|
What you will
learn:
This
class is applicable to Oracle9i and Oracle8i users. This course offers
students an extensive introduction to data server technology. The class
covers the concepts of both relational and object relational databases and
the powerful SQL programming language. Students are taught to create and
maintain database objects and to store, retrieve, and manipulate data.
Students learn to retrieve data by using advanced techniques such as
ROLLUP, CUBE, set operators, and hierarchical retrieval. They also learn
to write SQL and SQL*Plus script files using the iSQL*Plus tool to
generate report-like output. Demonstrations and hands-on practice
reinforce the fundamental concepts. |
|
Course
Objectives:
Describe relational and object relational database concepts
Retrieve, insert, update, and delete data
Use the Single row and Group functions
Retrieve data using additional advanced techniques
like using ROLLUP, CUBE, set operators, correlated subqueries and
hierarchical queries
Solve problems by using multicolumn subqueries,
subqueries in the from clause of a SELECT statement, correlated subqueries,
and scalar subqueries
Use the iSQL*Plus environment
Write scripts to generate script files
Generate reports using iSQL*Plus
Create and maintain database objects
Control transactions
Control data/user access
Use the Oracle9i Single row functions
Use GROUPING SETS and the WITH clause
Use the Oracle9i extensions to DML and DDL
statements
Apply techniques in real life simulation
|
|
Course Topics:
Introduction
Describing the Life Cycle Development Phases
Defining a Relational Database
Discussing the Theoretical, Conceptual, and
Physical Aspects of a Relational Database
Describing How a Relational Database Management
System (RDBMS) Is Used to Manage a Relational Database
Describing the Oracle Implementation of Both the
RDBMS and the Object Relational Database Management System (ORDBMS)
Describing How SQL Is Used in the Oracle Product
Set
Writing a
Basic SQL Statement
Describing the SQL Select Capabilities
Executing a Basic Select Statement with the
Mandatory Clauses
Differentiating Between SQL and iSQL*Plus Commands
Restricting and Sorting Data
Limiting the Rows Retrieved by a Query
Sorting the Rows Retrieved by a Query
Single Row
Functions
Describing Various Types of Functions Available in SQL
Using a Variety of Character, Number, and Date
Functions in SELECT Statements
Explaining What the Conversion Functions Are and
How They Are Used
Using Control Statements
Displaying
Data from Multiple Tables
Writing
SELECT Statements to Access Data from More Than One Table
Describing the Cartesian Product
Describing and Using the Different Types of Joins
Writing Joins Using the Tips Provided
Aggregating Data by Using Group Functions
Identifying the Different Group Functions Available
Explaining the Use of Group Functions
Grouping Data by Using the GROUP BY Clause
Writing
Subqueries
Describing the Types of Problems That Subqueries Can Solve
Describing Subqueries
Listing the Types of Subqueries
Writing Single-Row and Multi-Row Subqueries
Describing and Explaining the Behavior of
Subqueries When NULL Values Are Retrieved
Producing
Readable Output with iSQL*Plus
Producing Queries That Require an Input Variable
Customizing the iSQL*Plus Environment
Producing More Readable Output
Creating and Executing Script Files
Manipulating Data
Describing Each Data Manipulation Language (DML) Command
Inserting Rows into a Table
Updating Rows in a Table
Deleting Rows from a Table
Merging Rows into a Table
Controlling Transactions
Describing Transaction Processing
Describing Read Consistency and Implicit and
Explicit Locking
Creating
and Managing Tables
Describing the Main Database Objects
Creating Tables
Describing the Oracle Data Types
Altering Table Definitions
Dropping, Renaming, and Truncating Tables
Including
Constraints
Describing Constraints
Creating and Maintaining Constraints
Creating
Views
Describing Views and Their Uses
Creating a View
Retrieving Data by Means of a View
Inserting, Updating, and Deleting Data Through
Views
Dropping Views
Altering the Definition of a View
Inline Views
Top 'N' Analysis
Other
Database Objects
Creating, Maintaining, and Using Sequences
Creating and Maintaining Indexes
Creating Private and Public Synonyms
Controlling User Access
Understanding the Concepts of Users, Roles, and Privileges
Granting and Revoking Object Privileges
Creating Roles and Granting Privileges to Roles
Creating Synonyms for Ease of Table Access
SQL
Workshop
Applying Techniques Learned in This Course
Preparing for Future Oracle Courses
Using Set
Operators
Describing the Set Operators
Obeying the Set Operators Rules and Guidelines
Using a Set Operator to Combine Multiple Queries
into a Single Subquery
Controlling the Order of Rows Returned
Oracle 9i
Datetime Functions
Using
DATETIME Functions
Using the NVL2 Function to Handle NULL Values
Enhancements to the GROUP BY Clause
Using
ROLLUP as an Extension to the GROUP BY Clause to Produce Subtotal Values
Using CUBE as an Extension to the GROUP BY Clause
to Produce Cross-Tabulation Values
Using the GROUPING Function to Identify the Row
Values Created by ROLLUP or CUBE Operators
Using GROUPING SETS to Produce a Single Result Set
That Is Equivalent to a UNION ALL Approach
Using the WITH Clause
Advanced
Subqueries
Multiple-Column Subqueries
Writing a Subquery in the FROM Clause
Writing and describing Correlated Subquery
Using EXISTS and NOT EXISTS Operators
Updating and Deleting Rows Using Correlated
Subqueries
Using Scalar Subqueries in SQL
Hierarchical Retrieval
Discussing the Benefits of the Hierarchical Query
Ordering the Rows Retrieved by a Query in a
Hierarchical Manner
Formatting Hierarchical Data so That It Is Easy to
Read
Excluding Branches from the Tree Structure
Oracle9i
Extensions to DML and DDL Statements
Discussing Multitable Inserts
Creating and Using External Tables
Naming the Index and Using the CREATE INDEX
Command at the Time of Creating Primary Key Constraint
|