Oracle10g SQL Introduction to SQL Part II

Course Description

The primary objective of this 3-day 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. Major subject areas to be explored are: 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. Performing advanced SQL queries such as grouping and cube operations and SQL99 join syntax. Developing complex SQL*Plus reports and using SQL*Plus scripts
3 days
Contact us for pricing
 

Prerequisites

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. The course will help students to prepare for Exam 1Z1-042: Oracle Database 10g: Administration I in conjunction with the Oracle 10g Introduction to SQL Part I course.

Students should have attended the Oracle 10g Introduction to SQL Part I course prior to attending this course

SQL99 JOIN TECHNIQUES

ABOUT ANSI/ISO SQL99
CROSS JOINS
NATURAL JOINS
INNER JOINS
OUTER JOINS
ANTIJOINS
USING NAMED SUBQUERIES

ENHANCING GROUPS WITH ROLLUP & CUBE

ABOUT ENHANCED GROUP PROCESSING
USING ROLLUP
USING CUBE

USING THE CASE EXPRESSION

SQL FUNCTIONS (CHARACTER)

WHAT ARE THE SQL FUNCTIONS?
CHARACTER FUNCTIONS

SQL FUNCTIONS (NON-CHARACTER)

NUMERIC FUNCTIONS
DATE FORMAT FUNCTIONS
DATE ARITHMETIC FUNCTIONS
NULL VALUE FUNCTIONS

DATABASE OBJECTS: RELATIONAL VIEWS

ABOUT DATABASE OBJECTS
ABOUT RELATIONAL VIEWS
UPDATING VIEW DATA
MAINTAINING VIEW DEFINITIONS

DATABASE OBJECTS: DATA DICTIONARY STORAGE

ABOUT THE DATA DICTIONARY
OBJECT-SPECIFIC DICTIONARY VIEWS
UNDERSTANDING THE DATA DICTIONARY STRUCTURE

DATABASE OBJECTS: INDEXES

DATABASE OBJECTS: OTHER OBJECTS

CREATING AN APPLICATION SCHEMA
MORE ABOUT CREATING TABLES
DEFINING SEQUENCES
ABOUT SYNONYMS
CREATE SCHEMA AUTHORIZATION

DATABASE SECURITY

ABOUT DATABASE SECURITY
USER ID PASSWORDS
OBJECT SECURITY

DATA INTEGRITY USING CONSTRAINTS

ABOUT CONSTRAINTS
NOT NULL CONSTRAINT
CHECK CONSTRAINT
UNIQUE CONSTRAINT
PRIMARY KEY CONSTRAINT
REFERENCES CONSTRAINT
DEFINING CONSTRAINTS ON EXISTING TABLES

MAINTAINING CONSTRAINT DEFINITIONS

MAINTAINING CONSTRAINTS
RENAMING & DROPPING CONSTRAINTS
ENABLING & DISABLING CONSTRAINTS
VALIDATE NEW TRANSACTIONS ONLY
DEFERRED ENFORCEMENT
MANAGING CONSTRAINT EXCEPTIONS
CONSTRAINTS AND VIEWS
DATA DICTIONARY STORAGE

ADVANCED SQL*PLUS REPORTS

ABOUT THE REPORT COMMANDS
BREAK
COMPUTE
COLUMN
TTITLE & BTITLE

BUILDING SQL*PLUS SCRIPTS

ADVANCED SQL & SQL*PLUS FEATURES

ABOUT SCRIPT FILES
USING SQL*PLUS VARIABLES
ACCEPT
PROMPT
PAUSE
Oracle 10g