ORA10g009 - Oracle Database 10g: Introduction to SQL II

Course ORA10g009

Table of Contents

Introduction
Audience
At Course Completion
Prerequisites
Certified Professional Exams
Student Materials
Course Outline

Introduction

The primary objective of this course is to consider advanced subjects and techniques pertaining to the SQL database language. Even professionals experienced in other implementations of the industry-standard SQL language will benefit from the advanced and Oracle-specific features of SQL discussed in this course.

Return to Top

Audience

The target audience for this course is all Oracle professionals. Further, this course is well suited for non-Oracle professionals already experienced in the SQL but who wish to become acquainted with the unique and advanced features of Oracle SQL. Among the specific groups for whom this course will be helpful are application developers and database administrators.

Return to Top

At Course Completion

Specific subject areas to be explored and objectives to be reached are:

• Learn to use the SQL99 join syntax. Also, performing advanced SQL queries using techniques such as grouping and cube operations.
• Moving beyond basic table definitions, consideration of defining the full range of database objects, such as advanced table definitions, indexes, sequences, views and others.
• Understanding basic database security and object privileges.
• Using the powerful set of built-in SQL functions.
• Protecting the database and extending the basic data model using declarative constraints.
• Developing complex SQL*Plus reports and using SQL*Plus scripts.

Return to Top

Prerequisites

Before attending this course, students must have:

• Completed ora10g008, Oracle Database 10g: Introduction to SQL I

Return to Top

Certified Professional Exams

This course, along with ora10g008, Oracle Database 10g: Introduction to SQL I, will help the student prepare for the following Certified Professional exams:

• Exam #1Z0-007 Introduction to Oracle9i: SQL

Following the training sessions, Oracle Corporation recommends six-months production experience with the technology prior to taking the certification examinations. While this is good practice, it may not be necessary for all individuals.

Return to Top

Student Materials

The student kit includes a comprehensive workbook and other necessary materials for this class.

Return to Top

Course Outline

SQL DATA DEFINITION LANGUAGE
• ABOUT SQL DDL
• CREATE TABLE STATEMENT
• ALTER TABLE STATEMENT
• DROP TABLE STATEMENT
• SUPPORTIVE STATEMENTS

DATABASE OBJECTS: RELATIONAL VIEWS
• ABOUT RELATIONAL VIEWS
• UPDATING VIEW DATA
• MAINTAINING VIEW DEFINITIONS
• ALTER VIEW
• DROP VIEW

DATABASE OBJECTS: DATA DICTIONARY STORAGE
• USER_UPDATABLE_COLUMNS
• UNDERSTANDING THE DATA DICTIONARY STRUCTURE

DATABASE OBJECTS: INDEXES
• ABOUT INDEXES
• USE B-TREE INDEXES

DATABASE OBJECTS: OTHER OBJECTS
• CREATING AN APPLICATION SCHEMA
• MORE ABOUT CREATING TABLES
• DEFINING SEQUENCES
• ALTER SEQUENCE & DROP SEQUENCE
• ALTER SEQUENCE
• DROP SEQUENCE
• ABOUT SYNONYMS
• DROP SYNONYM
• CREATE SCHEMA AUTHORIZATION

DATABASE OBJECTS: SECURITY
• USER ID PASSWORDS
• OBJECT SECURITY

DATA INTEGRITY USING CONSTRAINTS
• NOT NULL CONSTRAINT
• CHECK CONSTRAINT
• UNIQUE CONSTRAINT
• PRIMARY KEY CONSTRAINT
• REFERENCES CONSTRAINT

MANAGING CONSTRAINT DEFINITIONS
• MANAGING CONSTRAINTS
• RENAMING & DROPPING CONSTRAINTS
• ENABLING & DISABLING CONSTRAINTS
• DEFERRED ENFORCEMENT
• SET CONSTRAINTS
• HANDLING CONSTRAINT EXCEPTIONS
• DATA DICTIONARY STORAGE

OPTIONAL – ADVANCED SQL*PLUS REPORTS
• BREAK
• COMPUTE
• COLUMN
• TTITLE & BTITLE

OPTIONAL – BUILDING SQL*PLUS SCRIPTS
• USING SQL*PLUS VARIABLES
• DEFINE & UNDEFINE
• ACCEPT
• PROMPT
• PAUSE

Return to Top