Introduction to Oracle 10g and SQL for Experienced Developers and DBAs
- 21 Hour Online Class or 3 Day Classroom
Introduction to Oracle 10g for developers, DBAs, analysts or IT or management professionals who are already familiar with other relational databases. Assumes a working knowledge of basic relational database concepts and SQL, and focuses on applying your knowledge in Oracle 10g. Introduces Oracle 10g, SQL*Plus and iSQL*Plus; covers Oracle 10g-specific querying including differences from other databases, Joins, Set Operators, grouping data and Flashback Queries; Oracle Datatypes, Functions, data manipulation, concurrency and transaction control; an introduction to Oracle Tables, Indexes, Views, Synonyms and security; SQL*Loader, external tables, Data Pump and an introduction to SQL Tuning.
This course is designed for developers and analysts who have experience with a relational database (e.g. Sybase, DB2 or SQL Server) but are new to Oracle.
This course leverages your existing knowledge of relational concepts and SQL to quickly get you started working with the Oracle database. The course will not waste your time covering basics such as simple SELECT, UPDATE, DEL ETE and INSERT statements.
You will receive a comprehensive foundation of knowledge in Oracle SQL extensions (subquery factoring, regular expressions, flashback query, etc), Oracle datatypes, Oracle functions, Oracle schema objects, Oracle utilities and the common developer / administration tools, SQL*Plus and iSQL*Plus. Developers will be prepared to begin work in an Oracle environment. Administrators will be prepared for out Oracle10g Database Administration course.
As with all of our courses, this class is highly customizable to your specific training requirements. Hands-on workshops constitute approximately 50% of the class.
Audience: Developers, DBAs, Analysts. Quality Assurance personnel and I.T. Management with solid and recent hands-on experience with another relational database will also benefit from this course.
Prerequisites:Relational database experience is required. Previous experience with Oracle is not required.
Next Courses: Advanced Queries for Oracle10g Databases, Oracle10g SQL Tuning for Developers and DBAs, Introduction to Oracle10g PL /SQL Programming, Oracle10g Database Administration.
Objectives: After successfully completing this course, you will be able to:
Query data using Oracle features such as flashback query, CASE, CUBE, ROLLUP and subquery factoring
Query the Oracle data dictionary to answer questions about your Oracle database
Create Oracle objects such as, but not limited to, simple tables, constraints, sequences, indexes and synonyms
Use Oracle functions
Use Oracle datatypes
Use the common developer / administration tools SQL*Plus and iSQL*Plus
Understand and make effective use of Oracle concurrency controls (locking techniques)
Understand and make effective use of transaction controls
Grant (and revoke) privileges
Change your user password
Determine the quota assigned to your user
Use SQL*Plus AUTOTRACE for very basic query tuning (attend our Oracle10g SQL Tuning for Developers and DBAs for an in-depth course on tuning)
Describe the purpose (at a high-level) of many of the principal and notable features and tools in the Oracle database (e.g. PL/SQL, RMAN, cost-based optimization, HTMLDB, XMLDB, Data Pump, etc.)
Books that may help you learn
Any books written by Tom Kyte are recommended
Topic Summary
Introduction to Oracle10g
What is the Oracle Database?
Principal Features and Tools: Overview
Enterprise Edition
Standard & Personal Editions
9i Release Highlights
10g Release Highlights
Related Products
Summary
Workshop
Introduction to SQL*Plus
Introducing the Development Environment
Connect to SQL*Plus
SQL*Plus Describe Command
SQL*Plus Connect Command
Customizing Your Environment (LOGIN.SQL and Predefined Variables) (10g)
SQL*Plus Host Command
Executing Queries in SQL*Plus
Spooling the Output
Editing in SQL*Plus
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? (10g)
Workshop
Introducing iSQL*Plus
Starting the iSQL*Plus Application Server
Launching iSQL*Plus in Your Browser
Using the Workspace
Accessing History
Setting Preferences
Querying the Database, Part I (Things that are different from your database.)
Available Comparison Operators (=, BETWEEN, IN, LIKE, etc.)
Pattern Matching with REGEXP_LIKE
Oracle's Treatment of NULL
Quoting Text Strings (10g)
Column Aliases
Sorting by Column Alias
Sorting by NULL
Case (and Accent) Insensitive Sort and Search (10g)
Accessing Remote Tables
Dual: The Oracle Work Table
Using CASE in SQL Statements
Workshop
Oracle Datatypes
Oracle Datatypes
Oracle Date and Datetime Datatypes
Workshop
XML Data Type
Miscellaneous Data Types
Datatype Conversion
Workshop
Oracle Functions
Functions Overview
String Manipulation Functions
Regular Expression Functions
Case Conversion Functions
Numeric Functions
Conversion Functions
Time Zone Functions
Date Arithmetic & Functions
Adding & Subtracting Days
Pseudo Columns
Workshop
Querying the Database, Part II - Oracle Joins
Oracle Outer Joins (+)
ANSI Compliant Joins
ANSI Natural Join
ANSI Inner Join
ANSI Outer Join
ANSI Full Outer Join
ANSI Cross Join
Quick Mention: Partition Outer Join (10g) (attend our Advanced SQL Queries course for in-depth coverage of this feature functions)
Workshop
Querying the Database, Part III - Set Operators
UNION , INTERSECT and MINUS Operators
UNION Example
INTERSECT Example
MINUS Example
Workshop
Querying the Database, Part IV - Subqueries
Overview of Oracle Subquery Support
Extended Subquery Support Examples
Subquery Factoring (WITH)
Workshop
Querying the Database, Part V - Grouping Data
Oracle Group (Aggregate) Functions
Group Functions with Nulls
ROLLUP
CUBE
GROUPING Function
DECODE and GROUPING
Quick Mention: Analytic Functions (attend our Advanced SQL Queries course for in-depth coverage of analytic functions)
Workshop
Flashback Query
Session and Statement-Level Flashback
Flashback Version Query
Flashback_Transaction_Query
Flashback Table
Undo Application Changes
Recover from DROP TABLE
Flashback Table: Recover from DROP
Repeat Un-Drop
Purging Recycle Bin
Workshop
Data Manipulation
List of Oracle DML Statements
Returning Values from DML
Returning Aggregates (10g)
Inserting Multiple Rows
9i Multi-Table INSERT
TRUNCATE Command
MERGE statement
10g MERGE Enhancements (10g)
Using Sequences in DML
Creating Sequences
Referencing Sequences
Sequences & The Data Dictionary
Workshop
Concurrency Control
Introduction to Locks
Oracle 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
Introduction to Tables
Types of Tables Supported (Heap, Clustered, IOT, etc)
