back to Course Categories | back | print
Oracle9iR2: Introduction to SQL and SQL*Plus          

Duration: 3 days

Description 
This class is designed for analysts, developers and report writers who need to understand SQL Programming in Oracle. Attendees will learn fundamentals of the Oracle architecture, simple and complex query skills, transaction management, security, schema objects, ANSI SQL features and report writing skills in SQL*Plus. The pace is designed to give students plenty of practice working with SQL in an Oracle environment.

Topics

  • What is an Oracle Server?
  • Oracle9i Architecture
  • Oracle Versioning
  • What is SQL?
  • SQL Statements
  • SQL Standards
  • More on Standards
  • Built-in Data Types
  • What is SQL*PLUS?
  • Starting an Oracle Session
  • Windows and Browser Version of SQL*Plus
  • Logging into Oracle Through SQL*Plus
  • Using the SELECT Command
  • Constants and Data Conversion
  • More on Constants
  • Pseudo Columns
  • Expressions and Aliases
  • DISTINCT
  • WHERE Clause
  • Arithmetic Operators
  • Logical Connectives
  • More on Logical Connectives
  • Predicates
  • ORDER BY Clause
  • GROUP BY Clause
  • HAVING Clause
  • Metadata and User Data
  • What is the Oracle Data Dictionary?
  • Base Tables and Static Views in the Data Dictionary
  • Dynamic Views in the Data Dictionary
  • More on Data Dictionary Database Objects
  • ALL_Views
  • DBA_Views
  • USER Views
  • Additional Views
  • V$ Views
  • SQL*PLUS Commands
  • The DESCRIBE Command
  • Using the SHOW Command
  • Modifying Session Variables with SET
  • More with the Set Command
  • The SQL Buffer
  • Editing System Files with SQL*PLUS
  • Executing SQL*PLUS Commands
  • Tracing Statements in SQL*Plus
  • Setting Up a Site or User Profile
  • SQL*PLUS Reports
  • Modifying Column Formats
  • Using Compute Functions
  • Working with Report Titles
  • HELP Command
  • Creating Web Reports Using SQL*Plus
  • The Data Manipulation Language
  • Modifying Data
  • How to Insert Data
  • Multi-row Insert
  • The Insert Command
  • Modifying Data
  • Update Command
  • Deleting Data
  • Transaction Processing
  • The COMMIT Command
  • The ROLLBACK Command
  • Savepoints
  • Locks
  • Using Oracle’s SQL Loader
  • Functions
  • Using SQL Functions
  • Types of SQL Functions
  • NUMERIC Functions
  • Char Functions
  • Data Conversion Functions
  • DATE Functions
  • Miscellaneous Functions
  • DECODE Function
  • Group Functions
  • Additional Functions
  • New ROLLUP Operators
  • The GROUPING Function
  • Analytical Functions
  • Sample Clause
  • Full List of SQL Functions
  • Working with Joins
  • Types of Joins
  • Basic Join
  • Equi-Joins
  • Outer Joins
  • Left and Right Outer Joins
  • Subqueries
  • Subquery Rules
  • Comparison Tests for Subqueries
  • Using IN and EXISTS Operators in Subqueries
  • Using the ANY and ALL Operators in Subqueries
  • DML Commands Using Single-Row Subqueries
  • Top-N Analysis
  • Using Set Operators
  • NULLS and Logic
  • Schemas
  • The Data Definition Language
  • Creating Tables
  • Defining DEFAULT Values
  • Using Explicit Defaults
  • The ALTER TABLE Command
  • Using Indexes
  • Query Optimization
  • Creating Indexes
  • Creating Views
  • Creating Sequences
  • Accessing Sequences
  • Creating Synonyms
  • Dropping Database Objects
  • Truncating tables
  • Constraints
  • Different Types of Constraints
  • Building Constraints
  • Primary Key Constraints
  • Foreign Key Constraints
  • Naming Constraints
  • Unique Constraints
  • Not Null Constraints
  • Check Constraints
  • Constraints can be Deferred
  • Enabling and Disabling Constraints
  • Index-Organized Table
  • Restrictions on Index-Organized Tables
  • More on Index-Organized Tables
  • Temporary Tables
  • Index Types
  • B*Tree Indexes
  • Composite Indexes
  • Creating a Composite Index
  • Reverse Key Indexes
  • Bitmap Indexes
  • Cluster (B*Tree) Indexes
  • Index Organized Tables
  • Function-Based Indexes
  • Domain Indexes
  • Index Options
  • Index Maintenance
  • Analyzing the Index
  • Rebuilding the Index
  • Coalescing the Index
  • Indexes on Primary Keys
  • Export and Import of Indexes
  • SQL*Loader and Indexes
  • ANSI Join Syntax
  • The JOIN Statement
  • More on the JOIN Statement
  • Cross Join
  • Natural Join
  • Natural Join Restrictions
  • The USING Clause
  • Outer Joins
  • Full Outer Join
  • CASE Expression
  • Simple CASE Statement
  • Searched CASE Statement
  • NULLIF CASE Statement
  • COALESCE CASE Statement
  • Scalar Subqueries
  • Restrictions of Scalar Subqueries
  • The MERGE Statement
  • New Analytical Functions
  • WIDTH_BUCKET
  • GROUPING SETS
  • Grouping with Composite Columns
  • Using Concatenated Groupings
  • WITH Clause
  • FIRST and LAST Aggregate Sets
  • Globalization: Date and Time Features
  • Daylight Savings Time Boundaries
  • Formats
  • Named Regions
  • New Datatypes Related to Globalization
  • Globalization Parameters
  • TIMESTAMP Literals
  • Using Interval and Timestamp Datatypes
  • Date, Time, and Interval Arithmetic
  • New Date and Time Types
  • TIMESTAMP Formats
  • New Date and Time Types
  • Datetime Functions
  • Datetime Conversion Functions
  • System Privileges
  • Roles
  • Granting Privileges
  • Viewing System Privileges
  • Granting Object Privileges
  • Privileges and the User Group PUBLIC
  • Revoking System Privileges and Roles
  • Object Privileges
  • Database Authentication
  • Predefined Roles
  • Setting up a Role
  • Parent versus Child Roles
  • Working with Default Roles
  • Enabling and Disabling Roles
  • Guidelines and Standards for Roles

Prerequisites

  • A fundamental understanding of relational database concepts is required.


 
 
 

Trubix Inc. P.O. Box 2235 Littleton, CO 80161 | 1.866.4-TRUBIX • 303.730.6800
email: info@trubix.com
....................................................................................................................................................................

Training Service | Consulting / Mentoring | Public Schedule | Course Listings | Tru iLearning | About Us | Contact Us
Oracle DBA Classes | Oracle Dev Classes | Oracle App Server Classes | Linux / Unix Classes
SQL Server / .NET / Tools / DB2 Classes / Public Classes

 

 
all content property © 2006, 2007Trubix Inc.| site design by Art Z design