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
Cost: Price on application
Duration: 3 days
This course is not available as part of our public schedule but can be provided on a customised client specific basis.
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
USING NAMED SUBQUERIES
ENHANCING GROUPS WITH ROLLUP & CUBE:
ABOUT ENHANCED GROUP PROCESSING
USING THE CASE EXPRESSION :
SQL FUNCTIONS (CHARACTER) :
WHAT ARE THE SQL FUNCTIONS?
SQL FUNCTIONS (NON-CHARACTER) :
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
CREATE SCHEMA AUTHORIZATION
ABOUT DATABASE SECURITY
USER ID PASSWORDS
DATA INTEGRITY USING CONSTRAINTS:
NOT NULL CONSTRAINT
PRIMARY KEY CONSTRAINT
DEFINING CONSTRAINTS ON EXISTING TABLES
MAINTAINING CONSTRAINT DEFINITIONS:
RENAMING & DROPPING CONSTRAINTS
ENABLING & DISABLING CONSTRAINTS
VALIDATE NEW TRANSACTIONS ONLY
MANAGING CONSTRAINT EXCEPTIONS
CONSTRAINTS AND VIEWS
DATA DICTIONARY STORAGE
ADVANCED SQL*PLUS REPORTS :
ABOUT THE REPORT COMMANDS
TTITLE & BTITLE
BUILDING SQL*PLUS SCRIPTS :
ADVANCED SQL & SQL*PLUS FEATURES:
ABOUT SCRIPT FILES
USING SQL*PLUS VARIABLES