Introduction to Oracle for SQL

Length: 3 Day
Prerequisites:  Relational database experience is helpful but not required. Previous experience with
Oracle or SQL is not required.


Overview

This course is designed for developers who are new to relational databases and need to know how to query, update and create basic objects in Oracle databases.  Students receive a comprehensive introduction to Oracle's implementation of the SQL language.  The course includes a lesson on basic relational database concepts.

As with all our courses, this class is highly customizable to your specific training requirements.

Key Benefits:  After successfully completing this course, you will be able to:

• Describe the principal features of a relational database
• Describe the principal features, versions and editions of the Oracle database
• Query and manipulate an Oracle database using Structured Query Language (SQL), including the use of functions,
  regular expressions and much more
• Code sophisticated query operations such as join, grouping case and more
• Update data with insert, multi-table insert, update, delete and merge operations
• Utilize SQL *Pus or other tools to query, update and create database objects
• Create simple database tables with all the major datatypes such as NUMBER, VARCHAR,
  DATE, TIMESTAMP and LOBs
• Query Oracle data dictionary tables such as USER_TABLES and USER_OBJECTS
• Utilize transactions control statement such as Commit, Rollback and Savepoint
• Create database objects such as table, temporary, nested table, views, indexes, synonyms and sequences
• Grant and Revoke object privileges

Audience: Developers, Analysts, DBAs, Quality Assurances personnel, I.T. Management


Outline

Chapter 1. Introduction Relational Databases

• Relational Model Data
• Key Concepts
• Data Structure: Two dimensional tables
• What is a join?
• Data Integrity
• Entity Integrity
• SQL Concepts
• Partial List DDL Statements
• SQL Terminology
• Nulls
• Many to Many Relationship
• Normalizing Data
• Normal Forms

Chapter 2. Introduction to Oracle 10g

• What is the Oracle Database?
• Principal Features
• DDL Statements
• DML Statements
• Enterprise Edition
• Standard & Personal Editions
• 8i Releases
• 9i Releases
• 10g Release Overview
• Related Products
• Workshop
• Oracle Architecture
• Common Schema Objects 

Chapter 3. Introduction to SQL *Plus

• 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
• Buffer Contents
• Editor Commands
• Editing: A Better Way
• Running SQL *Scripts
• Exit From SQL * Plus
• What"s in my Recycle bin? (10g)

Chapter 4.  Introduction to iSQL *Plus

• Starting the iSQL *Plus Application Server
• Launching iSQL *Plus in Your Browser
• Using the Workspace
• Accessing History
• Setting Preferences

Chapter 5.  Querying the Database Simple SELECT Part 1

• Simplified SELECT Statement
• SELECT Column List
• SELECT DISTINCT
• Calculated Columns
• Sorting: Order By
• Sorting By Calculated Columns
• Case (and Accent) insensitive Sort (10g)
• Comparison Operators
• Available Comparison Operators
• Quoting Text Strings (10g)
• Quoting Text Strings (10g)
• Logical Operator AND
• Available Logical Operators
• Accessing Remote Tables

Chapter 6. Datatypes & Functions

• Datatypes
• Datetime Example
• Datetime Summary
• New XML datatype
• Miscellaneous Data Types
• Datatype Conversion
• Dual: The Oracle Work Table
• Psuedo Ciolumns user, sysdate, uid, null
• Psuedo Columns; rowid, and rownum
• Functions Overview
• String Manipulation Functions
• Case Conversion Functions
• Concatenation Function
• TRIM Function
• substr Function and instr Function
• DECODE Function
• Numeric, ROUND TRUNC, NVL Functions
• TO_CHAR Examples
• Time Zone Functions
• Adding and Subtracting Days
• ADD_MONTHS Function
• LAST_DAY Function

Chapter 7. 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 NULL Operator
• ANSI Compliant CASE

Chapter 8. Querying the Database Part II - 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
• Fall Outer Joins
• Partition Outer Join (10g)
• ANSI Cross Join

Chapter 9. Querying the Database Part IV - Set Operators

• UNION, INTERSECT and MINUS Operators

Chapter 10. Querying the Database Part V - Subqueries

• Subquery Concepts
• EXISTS
• NOT EXISTS
• Extended Subquery
• Subquery in FROM Clause
• Named Subquery

Chapter 11. 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
• ROLLUP
• CUBE
• GROUPING Function
• DECODE & GROUPING

Chapter 12.  Regular Expressions

• Introduction
• Pattern Matching with REGEXP_LIKE
• Using REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR

Chapter 13. Data Manipulation & Transaction Control

• Inserting Rows
• Rounding on Insert
• Returning Values from DML
• Returning Aggregates (10g)
• Inserting Multiple Rows
• 9i Multi-Table INSERT
• Deleting Rows
• TRUNCATE Command
• Updating a Single row
• Updating a Multiple Rows
• Merge Statement
• 10g Merge Enhancements

Chapter 13. Data Manipulation & Transaction Control

• Inserting Rows
• Rounding Insert
• Returning Values from DML
• Returning Aggregates (10g)
• Inserting Multiple Rows
• 9i Multi-Table INSERT
• Deleting Rows
• TRUNCATE Command
• Updating a Single Row
• Updating Multiple Rows
• MERGE statement
• 10g Merge Enhancements

Chapter 13.Concurrency Control

• Introduction to Locks
• Row Lock Architecture
• Basic Locking Rules
• DML & DDL Locks
• Locking Issues
• Deadlocks

Chapter 14 .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

Chapter 15. Tables & Indexes

• Oracle Objects
• Naming Rules
• Listing Oracle Objects using the Data Dictionary
• Creating Tables
• Constraints
• ALTER TABLE
• DROP COLUMN
• Introduction to Indexes
• B-Tree Indexes
• ROWID Structure

Chapter 16. Views & Synonyms

• What is a View
• Views Concept Diagram
• What Are Views Used For?
• Creating Views
• Updateable Views
• Read Only Views
• WITH CHECK OPTION
• Views & the Data Dictionary
• What is a Synonym?
• Private Synonyms
• Public Synonyms
• CREATE SYNONYM Examples
• Synonym Search Sequences
• Synonyms & The Data Dictionary

Chapter 17.  Other Database Objects

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

Chapter 18.  Introduction to SQL Tuning

• SQL Tuning Basics
• Tuning - The Process
• The Plan Table
• SQL *Plus AUTOTRACE

Chapter 19.  Where Do I Go From Here?

• Certification
• Getting Help
• Other Help
• Other Topics
• Congratulations

Chapter 20.  Appendices

• Table Descriptions
• Oracle Data Dictionary
• Advanced SQL * Plus Scripting

Back to Course Listing