Print
Oracle 10g SQL Training

S.C. UniORA Grup SRL introduces the first course from the series of the Oracle coursesĀ  - Introduction to Oracle 10g and SQL

The course has been prepared by Oracle Partner company Skillbuilders (U.S.) (www.skillbuilders.com)
Duration of the course: 5 days
Dates of the first training: February 23, 2009 - February 27,2009
Trainer: Igor Duca, OCP (http://duca.at)
Price: 1375 US Dollars w/o VAT per student, including books and lunch
Method of Payment - prepayment in MDL - wire transfer - exchange rate to be fixed by BNM for the day of transfer
Location of the course : Training room of UniORA: Alba Iulia 190/1 of 34, 35 (New KVINT building: across the street - Orange Moldova)
Language - Russian, terminology - English

Class closed.

Introduction to Oracle10g and SQL

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.

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. Management

Prerequisites: 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

Topic Summary

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