Oracle Database 10g Introduction to SQL - Part II Study Guide

1-4188-4726-7 © 2006 Publish date: April 6, 2005 416 pages

Buy Now: $119.99

Course ID: oracle-db-10g-intro-sql-ii-sg

The second part of our two-part Oracle computer training books series on SQL is the Oracle Database 10g Introduction to SQL - Part II study guide. This course picks up where part one left off providing you with advance techniques and topics related to the SQL database language. Because the course focuses on Oracle-specific features of SQL, even professionals who are well-versed in other SQL implementations will benefit from following the content in this study guide.

The Oracle Database 10g Introduction to SQL - Part II courseware is appropriate for all Oracle professionals as well as for non-Oracle professionals that currently have SQL knowledge wish to learn the intricacies and advanced features of Oracle SQL. It is specifically tailored for database administrators and application developers.

The self-directed format of this class allows you to learn these techniques and concepts according to your own needs and schedule. A typical student can complete the course in about three days. You can use the guide on your own as a standalone teaching method or you can use it to supplement your existing Oracle courses. No matter how you use it, you'll want to hang on to the Oracle Database 10g Introduction to SQL - Part II courseware afterwards as a handy reference book.

Course Objectives

By the end of the course you will have moved beyond basic table definitions and understand the full range of database objects including advanced table definitions, sequences, indexes, views and more. You will understand basic database security, object privileges, built-in SQL functions, and declarative constraints as well as be able to perform advanced SQL queries and generate complex SQL*Plus reports and scripts.

Course Contents

SQL99 join techniques

• An introduction to ANSI/ISO SQL99
• Working with cross joins
• Using natural joins
• Working with inner joins
• Exploring outer joins
• A look at anti-joins
• Named subqueries

Enhancing groups with ROLLUP and CUBE

• An introduction to enhanced group processing
• ROLLUP
• CUBE

Case expression

SQL functions 

• An overview of SQL functions
• About character functions
• About numeric functions
• Working with date format functions
• Using date arithmetic functions
• About null value functions

Database objects - relational views

• An introduction to database objects
• An introduction to relational views
• Updating view data
• View definitions maintanence

Database objects - data dictionary storage

• An introduction to the data dictionary
• Object-specific dictionary views
• Exploring the data dictionary structure

Database objects – other objects

• Exploring indexes
• Application schema
• Building tables
• Sequences
• An introduction to synonyms
• Schema authorizations

Database security

• An introduction to database security
• User IDs/passwords
• About object security

Data integrity when using constraints

• An introduction to constraints
• The NOT NULL constraint
• About Check constraint
• Exploring Unique constraint
• The Primary key constraint
• Working with the references constraint
• How to define constraints on existing tables

Maintaining constraint definitions

• Maintaining constraints
• How to rename and drop constraints
• About enabling and disabling constraints
• Validating new transactions only
• Deferred enforcement techniques
• Managing exceptions
• About constraints and views
• Working with data dictionary storage

Advanced SQL*plus reports

• An introduction to the report commands
• Break command
• Compute command
• Column command
• TTITLE command and BTITLE command

Creating SQL*plus scripts

• An introduction to script files
• SQL*plus variables
• Accept
• Prompt
• Pause

Additional advanced SQL features and SQL*plus features