About Partners Contact Management Site Map   
 
 
 
 
   
  Oracle 11i Financials    
  Oracle 10g DBA    
  
   
   
   
   
   
   
   
   
 
 
 
 
 
 
 
         
   

Other Modules

    DBA Fundamental I
    DBA Fundamental II
    Performance Tuning

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

Required Prerequisites:
Familiarity with data processing concepts and techniques

Suggested Prerequisites:
Ability to use a graphical user interface (GUI)

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

Suggested Next Courses:

Oracle9i Database Administration Fundamentals I
Oracle9i Program with PL/SQL

GO TO TOP            
 
Comments, Suggestions, Requests: info@baseltd.com Cell:+8801713144220.
Copyright; 2006 @ BASE Limited.