Introduction to Oracle Database 12c Training

Analysing 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

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

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

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

Optimising performance

• Guidelines for defining appropriate indexes
• Indexing the data for efficient retrieval
• Managing unique, nonunique and composite indexes

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

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