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