Introduction to Oracle9i: Advanced SQL
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.
View other Oracle Courses available
View other Oracle Courses available
3 days
Contact us for pricing
Prerequisites
Introduction To Oracle9i: SQLDEFINING DATABASE OBJECTS
creating an application schematruncate
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_aabout object security
grant, revoke
SQL FUNCTIONS (CHARACTER)
character functionsupper(), 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 expressioncoalesce() func_sce
PROTECTING TABLES WITH DECLARATIVE CONSTRAINTS
not null constraintcheck constraint
unique constraint
primary key constraint
references constraint
on delete cascade
on delete set null
MAINTAINING CONSTRAINT DEFINITIONS
drop table cascadeenabling & 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 rollupgrouping()
cube
ADVANCED SQL*PLUS REPORTS
break, computecolumn, ttitle / btitle
BUILDING SQL*PLUS SCRIPTS
xsql filedefine / und
accept
start or @
prompt, pause
PROCESSING HIERARCHIES
:n hierarchy (supervision)m:n hierarchy (bill-of-materials)
advanced sql & sql*plus features
OracleOracle 9iOracle Database 9i