750. Oracle 10/11g - Introduction to SQL
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 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.
LEARNING OBJECTIVES
· 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
Course Duration: 4
days
Prerequisites: No mandatory prerequisites exist for this course.
Audience: The target audience for this course is
all Oracle professionals, both business and systems professionals. Among the specific
groups for whom this course will be helpful are:
• Application designers and developers
• Database administrators
• Business users and non-technical end users
1. Introduction to Relational Databases
Relational Model of Data
Key Concepts
Data Structure: Two dimensional tables
What is a join?
Data Integrity
Primary Keys
SQL Concepts, Examples and Terminology
Relational Database Design
Entities, Attributes and Relationships Defined
Many-to-Many Relationship
Normal Forms
2. Introduction to Oracle10g
What is the Oracle Database?
Principal Features
DDL & DML Statements
Enterprise, Standard & Personal Editions
8i & 9i Releases
10g Release Overview
Oracle Architecture
Common Schema Objects
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
Listing and Editing the Buffer Contents
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
4. Introducing iSQL*Plus
Starting the iSQL*Plus Application Server
Launching iSQL*Plus in Your Browser
Using the Workspace
Accessing History
Setting Preferences
5. 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
6. Datatypes and Functions
Datatypes
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
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
Simple CASE Example
8. 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
9. Querying the Database, Part IV - Set Operators
UNION, INTERSECT and MINUS Operators
UNION Example
INTERSECT Example
MINUS Example
10. Querying the Database, Part V - Subqueries
Subquery Overview
Subquery IN Operator
Subquery EXISTS Operator
NOT EXISTS Example
Subquery in FROM Clause
Extended Subquery Support
Named Subqueries
Workshop
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 and GROUPING
Workshop
12. Regular Expressions (10g)
Introduction
Pattern Matching with REGEXP_LIKE
Using REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR
13. 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)
14. Concurrency Control
Introduction to Locks
Row Lock Architecture
Basic Locking Rules
DML & DDL Locks
Locking Issues: Lost Update.
Locking Issues: Blocking
Locking Issues: Deadlocks
Deadlocks: Cause and Fix
Workshop
15. 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
16. Tables and Indexes
Oracle Objects
Naming Rules
Listing Oracle Objects using the Data Dictionary
Creating Tables
Naming Constraints Example
Creating Tables from Other Tables (CTAS)
Managing Constraints
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
17. 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
Views & The Data Dictionary
What is a Synonym?
Private & Public Synonyms
CREATE SYNONYM Examples
Synonym Search Sequence
Synonyms & The Data Dictionary
Workshop
18. Other Database Objects
Privileges and Roles
Roles - Concepts
Determining Privileges
Sequences
Caching of Sequences
Sequences - Uses
Using a Sequence to Generate a Primary Key
Sequences & The Data Dictionary
Workshop
19. Introduction to SQL Tuning
SQL Tuning Basics
Tuning - The Process
The Plan Table
SQL*Plus AUTOTRACE
Workshop
20. Where do I go from here?
Certification
Getting Help
Other Topics
21. Appendices
Table Descriptions
Oracle Data Dictionary
Advanced SQL*Plus Scripting