• Decrease font size
  • Default font size
  • Increase font size

Intro to Oracle 9i Advanced SQL

 Print E-mail
Course Length - 3 Days
 
Course Description

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.

 

You Will Learn How To:

  • 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
 

Course Prerequisites

  • Introduction To Oracle9i: SQL
 

Who Should Attend?

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

 

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 (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