Oracle Training : Oracle12c Release 1 SQL And PL/SQL

This course provides a complete, hands-on introduction to both SQL and PL/SQL including the use of both SQL Developer and SQL*Plus. This coverage is appropriate for both users of Oracle12c and Oracle11g. A full presentation of the basics of relational databases and their use are also covered.

Oracle Training Audience

This course is appropriate for anyone needing to interface with an Oracle database or those needing a general understanding ofOracle database functionality. That would include end users, business analysts, application developers and database administrators.

Oracle Training Prerequisites

Basic computer skills are needed. A basic knowledge of databases is desired but not required.

Course duration

5 Days

Oracle Training Course outline

SQL

CHAPTER 1 - BASIC RDBMS PRINCIPLES

CHAPTER OVERVIEW
RELATIONAL DESIGN PRINCIPLES
ACCESSING DATA THROUGH A STRUCTURED QUERY LANGUAGE
ENTITY RELATIONSHIP DIAGRAMS
DATA DOMAINS
NULL VALUES
INDEXES
VIEWS
DENORMALIZATION
DATA MODEL REVIEW
LAB 1: BASIC RDBMS PRINCIPLES
LAB 1 SOLUTIONS: BASIC RDBMS PRINCIPLES
CHAPTER SUMMARY

CHAPTER 2 - THE SQL LANGUAGE AND TOOLS

CHAPTER OVERVIEW
USING SQL*PLUS
- Why Use SQL*Plus When Other Tools Are Available?
- Starting SQL*Plus
- EZConnect
- SQL Commands
- PL/SQL Commands
- SQL*Plus Commands
The COLUMN Command
- The HEADING Clause
- The FORMAT Clause
- The NOPRINT Clause
- The NULL Clause
- The CLEAR Clause
PREDEFINED DEFINE VARIABLES
LOGIN.SQL
COMMAND HISTORY
COPY AND PASTE IN SQL*PLUS
ENTERING SQL COMMANDS
ENTERING PL/SQL COMMANDS
ENTERING SQL*PLUS COMMANDS
DEFAULT OUTPUT FROM SQL*PLUS
ENTERING QUERIES
WHAT ABOUT PL/SQL?
LAB 2: SQL LANGUAGE AND TOOLS
LAB 2 SOLUTIONS: SQL LANGUAGE AND TOOLS
CHAPTER SUMMARY

CHAPTER 3 - USING SQL DEVELOPER

CHAPTER OVERVIEW
CHOOSING A SQL DEVELOPER VERSION
CONFIGURING CONNECTIONS
- Creating A Basic Connection
- Creating A TNS Connection
- Connecting
CONFIGURING PREFERENCES
USING SQL DEVELOPER
- The Columns Tab
- The Data Tab
- The Constraints Tab
- The Grants Tab
- The Statistics Tab
- Other Tabs
- Queries In SQL Developer
- Query Builder
- Accessing Objects Owned By Other Users
- The Actions Pulldown Menu
DIFFERENCES BETWEEN SQL DEVELOPER AND SQL*PLUS
79
- Reporting Commands Missing In SQL Developer
- General Commands Missing In SQL Developer
DATA DICTIONARY REPORTS
USER DEFINED REPORTS
USING SCRIPTS IN SQL DEVELOPER
LAB 3: USING SQL DEVELOPER
LAB 3 SOLUTIONS: USING SQL DEVELOPER
CHAPTER SUMMARY

CHAPTER 4 - SQL QUERY BASICS

CHAPTER OVERVIEW
UNDERSTANDING THE DATA DICTIONARY
- Exporting Key Data Dictionary Information
THE DICTIONARY VIEW
COMPONENTS OF A SELECT STATEMENT
- The SELECT Clause
- The FROM Clause
- The WHERE Clause
- The GROUP BY Clause
- The HAVING Clause
- The ORDER BY Clause
- The START WITH And CONNECT BY Clauses
- The FOR UPDATE Clause
- Set Operators
COLUMN ALIASES
FULLY QUALIFYING TABLES AND COLUMNS
TABLE ALIASES
USING DISTINCT AND ALL IN SELECT STATEMENTS
LAB 4: SQL QUERY BASICS
LAB 4 SOLUTIONS: SQL QUERY BASICS
CHAPTER SUMMARY

CHAPTER 5 - DATA MANIPULATION

CHAPTER OVERVIEW
THE DATA MANIPULATION LANGUAGE
The INSERT Command
The UPDATE Command
The DELETE Command
Using The DEFAULT Keyword With Updates And Inserts
USING SQL DEVELOPER FOR DML
THE TRANSACTION CONTROL LANGUAGE (TCL)
IMPLICIT TCL
LAB 5: DATA MANIPULATION
LAB 5 SOLUTIONS: DATA MANIPULATION
CHAPTER SUMMARY

CHAPTER 6 -- WHERE AND ORDER BY

CHAPTER OVERVIEW
WHERE CLAUSE BASICS
COMPARISON OPERATORS
LITERALS AND CONSTANTS IN SQL
SIMPLE PATTERN MATCHING
LOGICAL OPERATORS
THE DUAL TABLE
ARITHMETIC OPERATORS
EXPRESSIONS IN SQL
CHARACTER OPERATORS
PSEUDO COLUMNS
ORDER BY CLAUSE BASICS
ORDERING NULLS
ACCENT AND CASE INSENSITIVE SORTS
SAMPLING DATA
WHERE AND ORDER BY IN SQL DEVELOPER
ALL, ANY, SOME
LAB 6: WHERE AND ORDER BY
LAB 6 SOLUTIONS: WHERE AND ORDER BY
CHAPTER SUMMARY

CHAPTER 7 - FUNCTIONS

CHAPTER OVERVIEW
THE BASICS OF ORACLE FUNCTIONS
NUMBER FUNCTIONS
CHARACTER FUNCTIONS
DATE FUNCTIONS
CONVERSION FUNCTIONS
OTHER FUNCTIONS
LARGE OBJECT FUNCTIONS
ERROR FUNCTIONS
THE RR FORMAT MODEL
LEVERAGING YOUR KNOWLEDGE
LAB 7: FUNCTIONS
LAB 7 SOLUTIONS: FUNCTIONS
CHAPTER SUMMARY

CHAPTER 8 - ANSI 92 JOINS

CHAPTER OVERVIEW
BASICS OF ANSI 92 JOINS
USING QUERY BUILDER WITH MULTIPLE TABLES
TABLE ALIASES
OUTER JOINS
- Outer Joins In Query Builder
SET OPERATORS
SELF-REFERENTIAL JOINS
NON-EQUIJOINS
LAB 8: ANSI 92 JOINS
LAB 8 SOLUTIONS: ANSI 92 JOINS
CHAPTER SUMMARY

CHAPTER 9 - ANSI 99 JOINS

CHAPTER OVERVIEW
CHANGES WITH ANSI99
CROSS JOIN
NATURAL JOIN
JOIN USING
JOIN ON
LEFT / RIGHT OUTER JOIN
FULL OUTER JOIN
LAB 9: ANSI 99 JOINS
LAB 9 SOLUTIONS: ANSI 99 JOINS
CHAPTER SUMMARY

CHAPTER 10 - GROUP BY AND HAVING

CHAPTER OVERVIEW
INTRODUCTION TO GROUP FUNCTIONS
- Limiting Rows
- Including NULL
- Using DISTINCT With Group Functions
GROUP FUNCTION REQUIREMENTS
THE HAVING CLAUSE
OTHER GROUP FUNCTION RULES
USING QUERY BUILDER WITH GROUP CLAUSES
ROLLUP AND CUBE
THE GROUPING FUNCTION
GROUPING SETS
LAB 10: GROUP BY AND HAVING
LAB 10 SOLUTIONS: GROUP BY AND HAVING
CHAPTER SUMMARY

CHAPTER 11 - SUBQUERIES

CHAPTER OVERVIEW
WHY USE SUBQUERIES?
WHERE CLAUSE SUBQUERIES
FROM CLAUSE SUBQUERIES
HAVING CLAUSE SUBQUERIES
CORRELATED SUBQUERIES
SCALAR SUBQUERIES
DML AND SUBQUERIES
EXISTS SUBQUERIES
HIERARCHICAL QUERIES
TOP N AND BOTTOM N QUERIES
CREATING SUBQUERIES USING QUERY BUILDER
LAB 11: SUBQUERIES
LAB 11 SOLUTIONS: SUBQUERIES
CHAPTER SUMMARY

PL/SQL

CHAPTER 1 - PL/SQL PROGRAM STRUCTURE

OVERVIEW OF THIS CHAPTER
PL/SQL VS. SQL
PL/SQL ENGINES AVAILABLE
ANONYMOUS PL/SQL BLOCK STRUCTURE
OBJECT NAMING RULES
VARIABLE DECLARATIONS
AVAILABLE DATATYPES
- Scalar Datatypes
- Using Extended Datatypes
- Object Types
EXECUTABLE STATEMENTS
EXPRESSIONS
BLOCK LABELING
VARIABLE SCOPING RULES
COMMENTS IN PROGRAMS AND SCRIPTS
BASIC CODING STANDARDS
LAB 1: PL/SQL PROGRAM STRUCTURE
LAB 1 SOLUTIONS: PL/SQL PROGRAM STRUCTURE
SUMMARY OF THIS CHAPTER

CHAPTER 2 - PL/SQL FLOW CONTROL

OVERVIEW OF THIS CHAPTER
CONDITIONAL CONTROL
COMPARISON OPERATORS
LOGICAL OPERATORS
- Truth Tables
REPETITION CONTROL
- The Simple Loop
- WHILE Loop
- FOR Loop
- CONTINUE Statements In Loops
- Step Loops
THE GOTO STATEMENT
CASE EXPRESSIONS / STATEMENTS
BIND VARIABLES
SUBSTITUTION VARIABLES
LAB 2: PL/SQL FLOW CONTROL
LAB 2 SOLUTIONS: PL/SQL FLOW CONTROL
SUMMARY OF THIS CHAPTER

CHAPTER 3 - SQL DEVELOPER AND PL/SQL

OVERVIEW OF THIS CHAPTER
SQL DEVELOPER AND PL/SQL
CREATING AND EXECUTING SCRIPTS
LAB 3: SQL DEVELOPER AND PL/SQL
LAB 3 SOLUTIONS: SQL DEVELOPER AND PL/SQL
SUMMARY OF THIS CHAPTER

CHAPTER 4 - SELECT INTO

OVERVIEW OF THIS CHAPTER
SELECTING SINGLE ROWS OF DATA
ANCHORING VARIABLES TO DATATYPES
DML IN PL/SQL
RETURNING … INTO
SEQUENCES IN PL/SQL
TRANSACTION CONTROL IN PL/SQL
AUTONOMOUS TRANSACTIONS
LAB 4: SELECT INTO
LAB 4 SOLUTIONS: SELECT INTO
SUMMARY OF THIS CHAPTER

CHAPTER 5 - THE PL/SQL CURSOR

OVERVIEW OF THIS CHAPTER
DECLARING EXPLICIT CURSORS
OPENING AND CLOSING EXPLICIT CURSORS
USING EXPLICIT CURSORS TO RETRIEVE VALUES
EXPLICIT CURSOR ATTRIBUTES
USING A LOOP WITH AN EXPLICIT CURSOR
USING %ROWTYPE WITH CURSORS
THE CURSOR FOR LOOP
DBMS_OUTPUT
LAB 5: THE PL/SQL CURSOR
LAB 5 SOLUTIONS: THE PL/SQL CURSOR
SUMMARY OF THIS CHAPTER

CHAPTER 6 - OPTIMIZATION

OVERVIEW OF THIS CHAPTER
TIMING PL/SQL
FOR UPDATE / WHERE CURRENT OF
LAB 6: OPTIMIZATION
LAB 6 SOLUTIONS: OPTIMIZATION
SUMMARY OF THIS CHAPTER

CHAPTER 7 - PL/SQL EXCEPTION HANDLING

OVERVIEW OF THIS CHAPTER
THE EXCEPTION SECTION
ORACLE NAMED EXCEPTIONS
PRAGMA EXCEPTION_INIT
USER DEFINED EXCEPTIONS
The Scope Of User-Defined Exceptions
RAISING NAMED EXCEPTIONS
EXCEPTION PROPAGATION
RAISING AN EXCEPTION AGAIN
LIFE AFTER AN EXCEPTION
WHEN OTHERS
TAKING YOUR BALL AND GOING HOME
DBMS_ERRLOG
LAB 7: PL/SQL EXCEPTION HANDLING
LAB 7 SOLUTIONS: PL/SQL EXCEPTION HANDLING
SUMMARY OF THIS CHAPTER

CHAPTER 8 - STORED PROCEDURES

OVERVIEW OF THIS CHAPTER
PROCEDURES
BENEFITS OF STORED PROCEDURES
- Database Security
- Performance
- Productivity
- Portability
PARAMETERS AND STORED PROCEDURES
- Parameter Notation
STORED OBJECT CREATION
- Syntax For Creating A Procedure
COMPILATION ERRORS
VIEWING COMPILED CODE
DROPPING A PROCEDURE
THE ALTER COMMAND AND STORED PROCEDURES
LAB 8: STORED PROCEDURES
LAB 8 SOLUTIONS: STORED PROCEDURES
SUMMARY OF THIS CHAPTER

CHAPTER 9 - CREATING FUNCTIONS IN PL/SQL

OVERVIEW OF THIS CHAPTER
FUNCTIONS
PURITY LEVELS
USING WHITE LISTS
OPTIMIZATIONS
PARALLEL_ENABLE
DETERMINISTIC FUNCTIONS
PL/SQL RESULT CACHE
NOCOPY
DBMS_OUTPUT IN FUNCTIONS
USING THE WITH CLAUSE FOR FUNCTIONS
PRAGMA UDF
PRAGMA INLINE
USING SQL DEVELOPER WITH STORED PROCEDURES
DEBUGGING
LAB 9: FUNCTIONS
LAB 9 SOLUTIONS: FUNCTIONS
SUMMARY OF THIS CHAPTER

CHAPTER 10 - PACKAGES

OVERVIEW OF THIS CHAPTER
CREATING PACKAGES
PACKAGE BENEFITS
- Security
- Persistent State
- I/O Efficiency
A SIMPLE PACKAGE
OVERLOADING
BODILESS PACKAGES
SOURCE CODE ENCRYPTION
CREATING PACKAGES FROM PROCEDURES AND FUNCTIONS
LAB 10: PACKAGES
LAB 10 SOLUTIONS: PACKAGES
SUMMARY OF THIS CHAPTER

CHAPTER 11 -- CREATING DML TRIGGERS

OVERVIEW OF THIS CHAPTER
DML TRIGGERS
DML TRIGGER STRUCTURE
CONDITIONAL TRIGGERING PREDICATES
TRIGGERS FOR BUSINESS RULES ENFORCEMENT
MUTATING AND CONSTRAINING TABLES
COMPOUND TRIGGERS
CONTROLLING FIRING ORDER
DDL FOR TRIGGERS
VIEWING TRIGGER SOURCE
INSTEAD OF TRIGGERS
LAB 11: DML TRIGGERS
LAB 11 SOLUTIONS: DML TRIGGERS
SUMMARY OF THIS CHAPTER

Please contact your training representative for more details on having this course delivered onsite or online

Training Outlines - the one stop shopping center for IT training.
© Training Outlines All rights reserved