Oracle Training : Introduction to Oracle9i: Advanced SQL

Introduction to Oracle9i: Advanced SQL

Oracle Training Overview

Essential advanced features within the Oracle SQL language are considered within this course. Building indexes, specifying declarative constraints, managing security privileges for tables and using SQL to satisfy some of the most complex query requests are all among the topics considered within this course. This course largely focuses on SQL features that are unique to the Oracle database and as such is applicable even for SQL professionals experienced in other databases.

This course was formerly called �Introduction to Oracle9i: Advanced SQL & SQL*Plus�.

Oracle Training Prerequisites

The course Introduction to Oracle9i: SQL

Suggested Next Courses

The course Introduction to Oracle 9i: PL/SQL Language

Oracle Training Audience

All Oracle professionals including developers, database administrators, designers and analysts.

Oracle Training Objectives

Complete a schema definition with indexes, synonyms, sequences and tablespace placement

Protect data integrity using declarative constraints.
Protect table access using database object security
Complete complex SQL requests using ROLLUP, CUBE and hierarchy processing

Oracle Training Course duration

3 Days

Oracle Training Course outline

DEFINING DATABASE OBJECTS

CREATING AN APPLICATION SCHEMA
TRUNCATE
Difference From DELETE and DROP TABLE
CREATE INDEX / DROP INDEX
USER_IND_COLUMNS
ALTER VIEW
USER_UPDATABLE_COLUMNS
DEFINING SEQUENCES
CREATE SEQUEN
NEXTVAL, CURRVAL
ALTER, DROP SEQUENCE
CREATE SYNONYM / DROP SYNONYM

DATABASE SECURITY

USER SCHEMA PASS_A
ABOUT OBJECT SECURITY
GRANT, REVOKE

SQL FUNCTIONS (CHARACTER)

CHARACTER FUNCTIONS
UPPER(), LOWER(), INITCAP()
CHR(), ASCII()
RPAD(), RTRIM()
DECODE(), SUBSTR()
INSTR(), TRANSLATE()
REPLACE()
GREATEST(), LEAST()
SOUNDEX()

SQL FUNCTIONS (NON-CHARACTER)

GREATEST(), ABS()
ROUND(), TRUNC(), SIGN()
TO_CHAR()
Format Elements: YYYY, DDTH, FM, RR
TO_DATE() Exam
MONTHS_BETWEEN()
ADD_MONTHS(), LAST_DAY()
LAST_DAY(), NEXT_DAY()
TRUNC(), ROUND() Dates
NULL VALUE FUNCTI_

ADVANCED SQL LANGUAGE CONSTRUCTS

CASE EXPRESSION
COALESCE() FUNC_SCE

PROTECTING TABLES WITH DECLARATIVE CONSTRAINTS

NOT NULL CONSTRAINT
CHECK CONSTRAINT
UNIQUE CONSTRAINT
PRIMARY KEY CONSTRAINT
REFERENCES CONSTRAINT
ON DELETE CASCADE
ON DELETE SET NULL

MAINTAINING CONSTRAINT DEFINITIONS

DROP TABLE CASCADE
ENABLING & DISABLING CONSTRAINTS
VALIDATE NEW TRANSACTIONS ONLY
DEFERRED ENFORC_D
SET CONSTRAINTS DEFERRED
MANAGING CONSTRAINT EXCEPTIONS
CONSTRAINTS
DATA DICTIONARY STORAGE
USER_CONSTRAINTS
CONSTRAINT_TYPE Code Explanation

ENHANCING GROUPS WITH ROLLUP & CUBE

USING ROLLUP
GROUPING()
CUBE

ADVANCED SQL*PLUS REPORTS

BREAK, COMPUTE
COLUMN, TTITLE / BTITLE

BUILDING SQL*PLUS SCRIPTS

XSQL File
DEFINE / UND
ACCEPT
START OR @
PROMPT, PAUSE

PROCESSING HIERARCHIES

:N HIERARCHY (SUPERVISION)
M:N HIERARCHY (BILL-OF-MATERIALS)

ADVANCED SQL & SQL*PLUS FEATURES