Introduction to Oracle 11g and SQL

3 Days

Description

This course is designed for beginners: developers who are not experienced with Oracle Database 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 and the common query tools, SQL*Plus and SQL Developer.  You will learn:

 

  • SELECT Statement
  • Oracle Datatypes, including DATE and TIMESTAMP
  • Oracle Scalar Functions
  • Join Operation
  • Subquery
  • GROUP BY
  • Transaction Control
  • Views and Synonyms
  • SQL Performance Tips
  • and Much More...

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Introduction to Oracle 11g and SQL class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Course Overview

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

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Introduction to Oracle 11g and SQL class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Prerequisites

Relational database experience is helpful but not required.  Previous experience with Oracle Database or SQL is not required

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Introduction to Oracle 11g and SQL class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date

Course Comments

Private only course

No Upcoming Public Classes

There are currently no public events available for this course. However, you can submit a request for a new date and we will try our best to get you into a Introduction to Oracle 11g and SQL class.

Private Training Available
No date scheduled, don’t see a date that works for you or looking for a private training event, please call 651-905-3729 or submit a request for further information here.
request a private session or new date