In this course, you gain a solid foundation of database concepts, SQL and PL/SQL to take advantage of Oracle Database 12c features for developing robust databases.

After completing this course, students will be able to:

·         Build robust and scalable Oracle Database 12c applications

·         Write efficient data manipulation statements using SQL Developer

·         Create database and external tables, sequences and synonyms

·         Ensure data integrity, establish security and enhance performance

·         Improve cursor processing with FOR LOOPs and parameters

·         Develop stored procedures, triggers and packages using PL/SQL

This is an introductory course that benefits a wide range of professionals. Familiarity with relational databases and experience with the SQL programming language is helpful, but not required.

1 -    Introduction to Oracle Database 12c Technology

·         Relational database concepts

·         Applying data modeling techniques

·         Defining entities, attributes and relationships

·         Oracle database tools: SQL Developer and SQL*Plus

2 -    Configuring the Database Environment

·         Analyzing the Oracle database architecture

·          Assessing memory structures, processes and files

·          Establishing a storage hierarchy

·         Maintaining Oracle database components

·          Reviewing the Oracle Database 12c installation

·          Generating DBA scripts with the data dictionary

·          Working with Oracle SQL Developer

3 -    Retrieving and Controlling Data

·         Implementing queries with SQL Developer

·          Selecting, filtering and ordering results

·          Avoiding pitfalls in null values

·          Deploying built-in SQL functions

·          Comparing CASE and DECODE functions

·         Leveraging powerful SQL techniques

·          Joining table data using inner and outer ANSI joins

·          Grouping and aggregating data

·          Combining result sets with set operators

·          Performing simple and correlated subqueries

·         Manipulating data with SQL statements

·          Inserting, updating, deleting and merging data

·          Locking data and managing transactions

4 -    Designing and Formulating the Database

·         Translating logical models to physical design

·          Mapping entities and relationships to tables and keys

·          Creating users and schemas

·         Constructing and managing tables

·          Creating, altering and dropping tables and columns

·          Restoring data with Flashback and the recycle bin

·         Developing views and sequences

·          Creating views to implement security

·          Comparing identity columns with sequences

5 -    Enhancing Security, Integrity and Performance

·         Maintaining integrity

·          Determining referential integrity with primary, unique and foreign keys

·          Implementing deferred and enforced constraints

·         Enforcing security

·          Authenticating users with password controls

·          Controlling access with system and object privileges

·          Simplifying object usage with synonyms

·          Improving privilege management with roles

·         Optimizing performance

·          Guidelines for defining appropriate indexes

·          Indexing the data for efficient retrieval

·          Managing unique, nonunique and composite indexes

6 -    Server Programming with PL/SQL

·         Programming with fundamental PL/SQL constructs

·          Declaring variables, constants and records

·          Establishing conditional control with IF and CASE

·          Controlling iterations with WHILE and FOR LOOPs

·          Creating exception handlers for predefined exceptions

·         Developing with cursors

·          Controlling implicit and explicit cursors

·          Increasing flexibility with cursor parameters

·          Simplifying cursors with FOR LOOPs

·          Improving update and delete performance with CURRENT OF or ROWID

7 -    Developing Server-Side Logic

·         Designing modular code

·          Developing procedures and functions

·          Debugging programs with DBMS_OUTPUT

·         Building packages and triggers

·          Advantages of bundling subprograms in packages

·          Creating compound-, statement- and row-level triggers

·          Controlling triggers with conditional predicates

·         Supporting web development with PL/SQL procedures

·          Returning data to web applications with REF cursors

·          Saving application data to the database