Introduction to Oracle Database 10g for Experienced SQL Users

What you will learn
 In this course, students who have experience with other ANSI SQL based databases, are introduced to Oracle Database 10g. They learn about the various Oracle Database extensions to the ANSI SQL standard. The course introduces and familiarizes the students with the Oracle Database environment and iSQL *Plus. The students are provided an overview of the basic elements of SQL and taught how to create, combine and manage tables and other database objects. An introduction to optimization of SQL is provided towards the end of the course. Demonstrations and hands-on practice reinforce all that has been taught.

Audience
 Database Designers
PL/SQL Developer
Technical Consultant

Course Objectives
 Identify the major structural components of the Oracle Database 10g.
Log in to iSQL* Plus and use its various features.
Describe and work with the basic elements of Oracle SQL.
Combine tables using joins and subqueries.
Create and manage tables, synonyms, sequences, indexes, and views.
Control user access by granting privileges and creating roles.
Optimize SQL Statements.

Course Topics
Understanding the Oracle Database Environment
 Identify Oracle Database components
Define an instance and how to connect to it
Describe parsing of a SQL statement
Describe the processing of a SQL statement
Declare, reference, and display bind variables
Describe read consistency

Working with iSQL*Plus
 Differentiate between SQL statements and iSQL* Plus commands
Log in to iSQL* Plus
Identify the various user interface elements of the iSQL*Plus window
Load, Save, and execute script files
Configure the iSQL*Plus settings

Getting Started with Oracle SQL
 Execute a SELECT statement
Implement pseudocolumns in SQL
Utilize the different types operators, conditions, expressions, and functions in SQL
Convert between data types
Describe how SQL treats NULL values

Combining Tables using Advanced Queries
 Write subqueries
Update and delete rows using correlated subqueries
Explain the concept of hierarchical query
Perform joins in SQL
Create a tree-structured report
Exclude branches from the tree structure

Creating and Managing Tables
 Create and maintain tables by using the CREATE, ALTER, DROP, RENAME, and TRUNCATE statements
Use the data dictionary to view and maintain information about tables
Create and maintain integrity constraints
Discuss constraint states
Use the data dictionary to view and maintain information about tables

Creating and Managing Other Oracle Database Objects
 Create and maintain a view
Retrieve, insert, update, and delete data through a view
Create, maintain, and use sequences
Create and maintain indexes
Create private and public synonyms

Controlling User Access
 Discuss the concepts of users, roles, and privileges
Create users
Create roles
Grant and revoke object privileges
Create and access database links

Oracle Database Extensions to DML and DDL Statements
 Describe the features of multitable INSERT statements
Use multitable INSERT statements
Merge rows using the MERGE statement
Create and use external tables
Describe transaction processing

Designing for Optimum SQL Performance
 Describe scalability
Employ proactive tuning methodologies
Use a performance checklist

Appendix A: Practice Solutions
Appendix B: Table Descriptions
Appendix C: Extensible Features of SQL
Appendix D: ANSI SQL Joins
Additional Practices and Solutions