Course Search:
Request more details:


Print Page

Introduction to Oracle9i: Advanced SQL

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.

View other Oracle Courses available
  • 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.
Prerequisites:
Introduction To Oracle9i: SQL
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


Dublin: 01 6627700 - Cork: 021 4319010 - Limerick: 061 311982

© Professional Training Solutions Limited 1996-2010
CRO No. 252390 - Unit 22, Westland Square, Pearse Street, Dublin 2, Ireland.