SkillBuilders Welcome  |  Log in
All Oracle Classes

Introduction to Oracle 11g for Developers and DBA's
4 Day Onsite / 28 Hour Online Hands-On Training

This hands-on course will teach you what the Oracle database is, how to code SQL statements to query and update data stored within an Oracle database. This class is designed for application developers who are new to relational database.  Persons with experience with some other relational database, but new to Oracle database should attend our Introduction to Oracle for Experienced Developers course (3 days).


This class is designed for application developers who are new to relational database.  Persons with experience with some other relational database, but new to Oracle database should attend our Introduction to Oracle for Experienced Developers course (3 days).

 

This class will fully prepare you for Oracle Exam 1Z0-047 Oracle Database SQL Expert.

As with all SkillBuilders courses, this class is highly customizable to your specific training requirements (when delivered at your company facilities).  Hands-on workshops constitute approximately 50% of the class.

Compare to Oracle University course Oracle Database 11g: Introduction to SQL

SkillBuilders course is:

  • More complete in breadth and depth of subjects covered.
  • Less expensive.
  • Shorter: only 4 days (28 hours online) versus 5 days for the Oracle University course.
  • Taught by an experienced instructor you can talk to before enrolling in class.
Average Student Rating:

Introduction to Relational Databases

  • Relational Model of Data
  • Key Concepts
  • Data Structure: Two dimensional tables
  • What is a join?
  • Data Integrity
  • Entity Integrity
  • Primary Keys
  • Referential Integrity
  • Domain Integrity
  • SQL Concepts and Examples
  • SQL Terminology
  • Common SQL Statements
  • Why More than One Table? Relational Database Design
  • Entities Defined
  • Attributes Defined
  • Relationships Defined
  • Many-to-Many Relationship
  • Normalizing Data
  • Normal Forms
  • Workshop

Introduction to the Oracle 11g Database

  • What is the Oracle Database?
  • Principal Features
  • Enterprise Edition Overview
  • Oracle Express (XE), Standard (SE), Enterprise (EE) & Personal Edition Overview
  • Related Products
  • Summary
  • Workshop
  • Oracle Architecture
  • Common Schema Objects
  • Introduction to the Data Dictionary
  • Workshop

Introduction to SQL*Plus

  • Introduction (And Why SQL*Plus is Still Important)
  • Accessing the Windows and DOS Versions
  • Connect to SQL*Plus
  • SQL*Plus Describe Command
  • SQL*Plus Connect Command
  • Customizing Your Environment (LOGIN.SQL and Predefined Variables)
  • Replacing gLogin with Executable Defaults (11g Feature)
  • SQL*Plus Host Command
  • Executing Queries in SQL*Plus
  • Spooling the Output
  • Editing in SQL*Plus
  • Listing the Buffer Contents
  • Editing the Buffer Contents
  • Editor Commands
  • SQL*Plus Edit Command
  • Related SQL*Plus Commands
  • Editing: A Better Way
  • Running SQL*Plus Scripts
  • Exit from SQL*Plus
  • What's in my Recyclebin?
  • SQL*Plus Error Logging (11g Feature)
  • New BLOB Support (11g Feature)
  • Workshop

Introduction to SQL Developer

  • Downloading and Installing SQL Developer
  • Getting to Know the Interface
  • Making Database Connections
  • Using the SQL Worksheet
    • Creating a Table
    • Creating Some Data
    • Creating Reports
    • Exporting Data
    • Workshop

Querying the Database, Simple SELECT, Part I

  • Simplified SELECT Statement
  • SELECT Column List
  • SELECT DISTINCT
  • Calculated Columns
  • Column Aliases
  • Concatenated Columns
  • Sorting: Order By
  • Sorting by Calculated Columns
  • Sorting by Column Alias
  • Sorting by Multiple Columns
  • Case (and Accent) Insensitive Sort
  • Comparison Operators
  • Available Comparison Operators
  • Quoting Text Strings
  • Logical Operator AND
  • Available Logical Operators
  • Accessing Remote Tables
  • Querying the Data Dictionary to Answer Your Questions
  • Workshop

Datatypes and Functions

  • Datatypes Overview
  • Datetime
  • Workshop
  • New XML data type
  • Miscellaneous Data Types
  • Datatype Conversion
  • Dual: The Oracle Work Table
  • Pseudo Columns: user, sysdate, uid, null
  • Pseudo Columns: rowid and rownum
  • Functions Overview
  • String Manipulation Functions
  • Case Conversion Functions
  • Concatenation Function
  • TRIM Function
  • substr Function
  • instr Function
  • Nesting Functions
  • DECODE Function
  • TRANSLATE Function
  • Numeric Functions
  • ROUND and TRUNC Functions
  • NVL, NULLIF, and COALESCE functions
  • Character Conversion Function
  • TO_CHAR Example: number to character
  • New 9i built-in Functions
  • Date Conversion Functions
  • TO_CHAR Examples: character to date
  • TO_DATE Examples
  • Datetime Conversions
  • Time Zone Functions
  • Date Arithmetic & Functions
  • Adding & Subtracting Days
  • ADD_MONTHS Function
  • LAST_DAY Function
  • TRUNC Function
  • Workshop

Querying the Database, Part II – Advanced Filters

  • Conditional Operators
  • Search Lists: IN
  • Search Range: BETWEEN
  • Search Patterns: LIKE
  • Introduction to Nulls
  • Selecting Rows with Null Values
  • IS [NOT] NULL Operator
  • ANSI Compliant CASE
  • Simple CASE Example
  • Workshop

Regular Expressions

  • Introduction
  • Pattern Matching with REGEXP_LIKE
  • Using REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR
  • 11g Enhancements
  • Workshop

Querying the Database, Part III – Joins

  • Simple Joins
  • Unqualified Names in Joins
  • Table Aliases in Joins
  • Outer Joins
  • ANSI Compliant Joins
  • ANSI Natural Join
  • ANSI Inner Join
  • ANSI Outer Joins
  • Full outer joins
  • Partition Outer Join
  • ANSI cross join
  • Workshop

Querying the Database, Part IV – Set Operators

  • UNION, INTERSECT and MINUS Operators
  • UNION Example
  • INTERSECT Example
  • MINUS Example
  • Workshop

Querying the Database, Part V – Subqueries

  • Subquery Concepts
  • Subquery Example
  • Subquery Restrictions
  • Subquery Rules
  • Subquery IN Operator
  • Subquery EXISTS Operator
  • NOT EXISTS Example
  • Subquery in FROM Clause
  • Extended Subquery Support
  • Named Subqueries
  • Workshop

Querying the Database, Part VI – Grouping Data

  • Group Functions
  • MIN, MAX, SUM, COUNT, AVG
  • Group Functions with Nulls
  • Summary Grouping
  • GROUP BY Clause
  • Grouping Multiple Columns
  • Golden GROUP BY Rule
  • Where Clause Restrictions
  • HAVING Clause
  • HAVING Clause Example
  • ROLLUP
  • ROLLUP Example
  • CUBE
  • CUBE Example
  • GROUPING Function
  • GROUPING Example
  • DECODE and GROUPING
  • Workshop

Querying the Database, Part VII Hierarchical Retrieval

  • Understanding hierarchical data within a relational database
  • Coding hierarchical queries with the CONNECT BY clause
  • Identifying levels with the LEVEL clause
  • Formatting the result set with LEVEL and the LPAD function
  • Pruning branches with the WHERE and CONNECT BY clauses

Data Manipulation and Transaction Control

  • Inserting Rows
  • Rounding on Insert
  • Returning Values from DML
  • Returning Aggregates
  • Inserting Multiple Rows
  • Multi-Table INSERT
  • INSERT IGNORE_ROW_ON_DUPKEY_INDEX (11g Feature)
  • Deleting Rows
  • TRUNCATE Command
  • Updating a Single Row
  • Updating Multiple Rows
  • MERGE statement
  • Workshop

Concurrency Control

  • Introduction to Locks
  • Row Lock Architecture
  • Basic Locking Rules
  • DML Locks
  • DDL Locks
  • Locking Issues: Lost Update …
  • Locking Issues: Blocking
  • Locking Issues: Deadlocks
  • Deadlocks: Cause and Fix
  • Workshop

Transaction Control

  • Transaction Review
  • Supported Statements …
  • Statement Level Read Consistency …
  • Transaction Level Read Consistency
  • Oracle Isolation Levels
  • Phantom and Non-Repeatable Reads
  • Read Committed
  • Serializable
  • Read Only
  • Savepoints
  • Implicit Commits
  • Workshop

Tables and Indexes

  • Oracle Objects
  • Naming Rules
  • Listing Oracle Objects using the Data Dictionary
  • Creating Tables
  • Creating Tables Example
  • Naming Constraints Example
  • Creating Tables from Other Tables (CTAS)
  • Introducing Constraints
  • Disabling Constraints
  • Enabling Constraints
  • Listing Constraints using the Data Dictionary
  • Altering Table Structure
  • ALTER TABLE Adding Columns
  • ALTER TABLE Adding Constraints
  • DROP COLUMN
  • Dropping Tables
  • Introduction to Indexes …
  • B-Tree Indexes
  • Index Diagram
  • ROWID Structure
  • Index Creation
  • Index Use - Who Decides?
  • Workshop

Views and Synonyms

  • What is a View?
  • Views Concept Diagram
  • What Are Views Used For?
  • Creating Views: Hiding Sensitive Columns
  • Creating Views Simplify Table Access
  • Creating Views: Using Column Aliases
  • Updateable Views
  • Read Only Views
  • WITH CHECK OPTION
  • WITH CHECK OPTION Example
  • Views & The Data Dictionary
  • What is a Synonym?
  • Private Synonyms
  • Public Synonyms
  • CREATE SYNONYM Examples
  • Synonym Search Sequence
  • Synonyms & The Data Dictionary
  • Workshop

Other Database Objects

  • Privileges and Roles
  • Roles - Concepts
  • Using Roles
  • Determining Privileges
  • Sequences
  • Caching of Sequences
  • Sequences - Uses
  • Referencing Sequences
  • Using a Sequence to Generate a Primary Key
  • Sequences & The Data Dictionary
  • Workshop

Where do I go from here?

  • Certification
  • Getting Help
  • Other Topics
  • Congratulations!

Appendices

  • Table Descriptions
 
SkillBuilders Sample Video