Oracle9i Advanced SQL Introduction

This 3-day course will provide students with a comprehensive overview of the advanced features within the Oracle SQL language. 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 covered by this course. This course largely focuses on SQL features that are nique to the Oracle database and as such is applicable for SQL professionals experienced in other databases.
On course completion, students will be able 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

Target audience

This course has been developed for all Oracle professionals including developers, database administrators, designers and analysts. Students should have attended the following course or have equivalent knowledge and experience:

• Introduction to Oracle 9i SQL

Course content

Defining database objects Creating an application schema • Truncate • Difference from Delete and Drop Table • Create Index/Drop Table • USER_IND_COLUMNS • Alter View • USER_UPDATABALE_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() • NEXT_DAY() • TRUNC() • ROUND() Dates • NULL VALUE FUNCTI_

Advanced SQL language constructs Case expression • COALESCE() FUNC_SCE

Protecting tables with declarative constraints Not null constraints • Check constraint • Unique constraint • Primary key constraint • References constraint • On delete cascade • On delete set null

Maintaining constraint definitions Drop table cascade • Enabling and disabling constraints • Validate new transactions only • Deferred ENFORC_D • Set constraints deferred • Managing constraints exceptions • Constraints • Data dictionary storage • USER_CONSTRAINTS • CONSTRAINT_TYPE code explanation

Enhancing groups with ROLLUP and CUBE Using ROLLUP • GROUPING() • CUBE

Advanced SQL*Plus reports BREAK • COMPUTE • COLUMN • TTILE • BTITLE

Building SQL*Plus scripts XSQL file • DEFINE • UND • ACCEPT • START or @ • PROMPT • PAUSE

Processing hierarchies :N hierarchy (supervision) • M:N hierarchy (bills of materials)

Advanced SQL and SQL*Plus features

About Courseware Company :: Instructor-led training courseware :: Self-study solutions :: Buy online today