Introduction to Oracle 10g and SQL Training Next Online Class Begins November 2nd!
Dates:
Nov. 2–Nov. 6
Times: 1:00 pm – 5:00 pm EDT
Cost: $1,185
21 Hour Online Class or 3 Day Classroom
Introductory Oracle SQL and SQL*Plus class designed for developers, DBAs, analysts, IT managers and other professionals who are new to relational databases. Hands-on class covers an introduction to relational databases including concepts, structure, terminology and statements; in-depth introduction to Oracle 10g, SQL*Plus and iSQL*Plus; Queries (simple SELECT, advanced filters, joins, set operators, subqueries and grouping data); Tables and Indexes; Views and Synonyms; Datatypes and Functions; data manipulation, transaction and concurrency control; other Database Objects and an intro to SQL Tuning.
Class Description:
This course is designed for developers who are new to relational database and need to know how to query, update and create basic objects in Oracle databases. Students with experience with some other relational database, but new to Oracle database should attend our Introduction to Oracle and SQL for Experienced Developers course.
Students receive a comprehensive introduction to Oracle's implementation of the SQL language and the common query tools, SQL*Plus and iSQL*Plus. The course includes a lesson on basic relational database concepts.
As with all SkillBuilders courses, this class is highly customizable to your specific training requirements.
Hands-on workshops constitute approximately 50% of the class.
Audience: Developers, Analysts, DBAs, Quality Assurance personnel, I.T. ManagementPrerequisites: Relational database experience is helpful but not required. Previous experience with Oracle or SQL is not required. Next Courses : Advanced Queries for Oracle Databases, SQL Tuning for Developers and DBAs, Introduction to Oracle PL/SQL Programming.
Objectives: 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*Plus and iSQL*Plus to query, update and create database objects
Create simple database tables with all the major datatypes such as NUMBER, VARCHAR2, DATE, TIMESTAMP and LOBs
Query Oracle data dictionary tables such as USER_TABLES and USER_OBJECTS
Utilize transaction control statements such as Commit, Rollback and Savepoint
Create database objects such as tables, temporary tables, nested tables, views, indexes, synonyms and sequences
Grant and Revoke object privileges
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
A Word About Nulls
SQL Concepts
SQL Examples
SQL Terminology
Partial List of DDL Statements
Partial List of DML Statements
Relational Database Design
Entities Defined
Attributes Defined
Relationships Defined
Many-to-Many Relationship
Normalizing Data
Normal Forms
Introduction to Oracle10g
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
Summary.
Workshop
Oracle Architecture
Common Schema Objects
Workshop
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
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? (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, 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 (10g)
Comparison Operators
Available Comparison Operators
Quoting Text Strings (10g)
Logical Operator AND
Available Logical Operators
Accessing Remote Tables
Workshop
Datatypes and Functions
Datatypes
Datetime Example
Datetime Summary
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
DECODE Function
TRANSLATE Function
Numeric Functions
ROUND Function
TRUNC Function
NVL Function
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 NULL Operator
ANSI Compliant CASE
Simple CASE Example
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 (10g)
ANSI cross join
Querying the Database, Part IV - Set Operators
UNION, INTERSECT and MINUS Operators
UNION Example
INTERSECT Example
MINUS Example
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
Regular Expressions (10g)
Introduction
Pattern Matching with REGEXP_LIKE
Using REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR
Data Manipulation and 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 Multiple Rows
MERGE statement.
10g MERGE Enhancements (10g)
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
Introduction to SQL Tuning
SQL Tuning Basics
Tuning - The Process
The Plan Table
SQL*Plus AUTOTRACE
Workshop
Where do I go from here?
Certification
Getting Help
Other Topics
Congratulations!
Appendices
Table Descriptions
Oracle Data Dictionary
Advanced SQL*Plus Scripting
