Course Overview
This Oracle Database 11g R2 SQL Complete course is designed to prepare students for the Oracle certification exams (OCP).
To prepare for the ‘Exam 1Z0-047: Oracle Database: SQL Certified Expert’ certification, one must complete this course as well as the course DBOR-921 ORACLE DATABASE 11G: NEW & ADVANCED FEATURES FOR DEVELOPERS.
What you’ll learn
The objective of this course is to provide an introduction to the SQL database language within the context of an Oracle database, based upon the latest features available. The primary objective of this course is to consider advanced subjects and techniques pertaining to the SQL database language, based upon the latest features available with the Oracle release. Even professionals experienced in other implementations of the industry-standard SQL language will benefit from the advanced and Oracle-specific features of SQL discussed in this course.
Requirements
- No mandatory prerequisites exist for this course. However, one or more of the following courses are suggested prerequisites: ENTERPRISE ARCHITECTURES FOR EXECUTIVES DATA MODELING: LOGICAL DATABASE DESIGN
Target audiences
- The target audience for this training course is all Oracle professionals, both business and systems professionals, who wish to pass the Oracle certification exams and need a 'how to' guide to acquire the knowledge of SQL. Further, this course is well suited for non-Oracle professionals already experienced in the SQL but who wish to become acquainted with the unique and advanced features of Oracle SQL. Among the specific groups for whom this course will be helpful are application developers and database administrators.
Curriculum
- 1 Section
- 28 Lessons
- 5 Days
Expand all sectionsCollapse all sections
- Topics28
- 1.1RELATIONAL DATABASES & SQL ABOUT relational databases ELEMENTS of SQL
- 1.2CHOOSING A SQL& PL/SQL INTERFACE ABOUT database connections ABOUT bind variables USING SQUL developer USING SQL*plus USING application express
- 1.3BUILDING A SELECT STATEMENT ABOUT the select statement USING alias names
- 1.4RESTRICTING DATA WITH THE WHERE CLAUSE ABOUT logical operations EQUALITY operator BOOLEAN operators NULL & between operators IS (not) null operator (NOT) between operator FINDING text strings (NOT) like operator REGEXP_like() IN operator ABOUT the order by clause MULTIPLE column sorts SPECIFYING the sort sequence ABOUT null values within sorts USING column aliases PSEUDO columns & functions USING rowed USING rownum USING the functions SYSDATE USER & IUD USING the dual table SESSIONTIMEZONE function JOINING tables ABOUT joins INNER join REFLEXIVE join NON-KEY join OUTER join
- 1.5USING THE SET UP OPERATORS ABOUT the set operators SET operator examples
- 1.6SUMMARY FUNCTIONS ABOUT summary functions SUMMARY operator examples
- 1.7USING SUB-QUERIES ABOUT sub-queries STANDARD sub-queries CORRELATED sub-queries
- 1.8AGGGREGATING DATA WITHIN GROUPS ABOUT summary groups FINDING groups within the base tables SELECTIONS data from the base tables SELECTIONG groups from the result tables
- 1.9BUILD SIMPLE SQL*PLUS RESORTS FORMAT column output with column DEFINING report breaks with break PRODUCE subtotals with compute
- 1.10USE DATA DEFINITION LANGUAGE TO CREATE & MANAGE TABLES CREATE table statement NOT null DEFAULT ALTER table statement DROP table statement SUPPORTIVE statements DESCRIBE RENAME
- 1.11USE DATA MANIPULATION LANGUAGE TO MANIPULATE DATA ABOUT the insert statement ABOUT the delete statement ABOUT the update statement ABOUT transactions ROLLBACK COMMIT SAVEPOINT SET transaction TRUNCATE table COMPLEX table references
- 1.12APPENDIX A REFERENCE information SQL DDL statement reference SQL DML statement reference SQL transaction control statement reference SQL operators reference
- 1.13SQL99 JOIN TECHNIQUES CROSS joins NATURAL joins INNER joins OUTER joins ANTI joins USING named subqueries
- 1.14ENHANCING GROUPS WITH ROLLUP & CUBE USING rollup USING cube
- 1.15USING THE CASE EXPRESSION SQL FUNCTIONS: CHARACTER STRING formatting functions ASCII codes functions PAD &trim functions STRING manipulation functions STRING comparison functions PHONETIC search functions
- 1.16SQL FUNCTIONS: NUMERIC: ABOUT the numeric functions NULL value functions
- 1.17SQL FUNCTIONS: DATE DATE format functions DATE arithmetic functions
- 1.18DATABASE OBJECTS: RELATIONAL VIEWS ABOUT database objects ABOUT relational views UPDATING view data MAINTAINING view definitions ALTER view DROP view
- 1.19DATABASE OBJECTS: DATA DICTIONARY STORAGE ABOUT the data dictionary OBJECT-specific dictionary views USER_updatable_columns UNDERSTAND the data dictionary structure
- 1.20DATABASE OBJECTS: INDEXES ABOUT indexes USE B-tree indexes
- 1.21DATABASE OBJECTS: OTHER OBJECTS MORE about creating tables ABOUT sequences ALTER sequence & drop sequence ALTER sequence DROP sequence ABOUT synonyms CREATE schema authorization
- 1.22DATA INTEGRITY USING CONSTRAINTS ABOUT constraints NOT null constraint CHECK constraint UNIQUE constraint PRIMARY key constraint REFERENCES constraint DEFINING constraints on existing tables
- 1.23DATABASE OBJECTS: SECURITY USER passwords OBJECT security
- 1.24MANAGING CONSTRAINT DEFINITIONS RENAMING & dropping constraints ENABLING & disabling constraints DEFERRED enforcement SET constraints HANDLING constraint exceptions CONSTRAINTS with views DATA dictionary storage
- 1.25OPTIONAL-ADVANCED SQL*PLUS REPORTS REPORT-1 REPORT-2 SOLUTIONS REPORT-1 REPORT-2
- 1.26ADVANCED SQL*PLUS REPORTS BREAK COMPUTE COLUMN TTITLE & BTITLE
- 1.27OPTIONAL-BUILDING SQL*PLUS SCRIPTS USING SQL*plus variables DEFINE and undefined ACCEPT PROMPT PAUSE
- 1.28OPTIONAL -SQL*PLUS SESSION CONTROL CONTROLLING system variables SET SHOW
Instructor
