ORACLE12C RELEASE 1 SQL AND PL/SQL FASTTRACK – GTD20

Course Description

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.

^^

Duration

5 days

^^

Target Audience

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

^^

Course Prerequisites

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

^^

Suggested Follow on Courses

There are a number of options of suitable follow-on courses, depending on your business needs. Please Contact Us for further details.

^^

Course Content

Course Content – Oracle SQL

CHAPTER 1 – BASIC RDBMS PRINCIPLES

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 2 – THE SQL LANGUAGE AND TOOLS

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 3 – USING SQL DEVELOPER

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 4 – SQL QUERY BASICS

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 5 – DATA MANIPULATION

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 6 – WHERE AND ORDER BY

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 7 – FUNCTIONS

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 8 – ANSI 92 JOINS

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 9 – ANSI 99 JOINS

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 10 – GROUP BY AND HAVING

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 11 – SUBQUERIES

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

Course Content – Oracle PL/SQL

CHAPTER 1 – PL/SQL PROGRAM STRUCTURE

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

CHAPTER 2 – PL/SQL FLOW CONTROL

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

CHAPTER 3 – SQL DEVELOPER AND PL/SQL

SQL DEVELOPER AND PL/SQL

CREATING AND EXECUTING SCRIPTS

LAB 3: SQL DEVELOPER AND PL/SQL

LAB 3 SOLUTIONS: SQL DEVELOPER AND PL/SQL

CHAPTER 4 – SELECT INTO

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

CHAPTER 5 – THE PL/SQL CURSOR

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

CHAPTER 6 – OPTIMIZATION

TIMING PL/SQL

FOR UPDATE / WHERE CURRENT OF

LAB 6: OPTIMIZATION

LAB 6 SOLUTIONS: OPTIMIZATION

CHAPTER 7 – PL/SQL EXCEPTION HANDLING

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

CHAPTER 8 – STORED PROCEDURES

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

CHAPTER 9 – CREATING FUNCTIONS IN PL/SQL

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

CHAPTER 10 – PACKAGES

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

CHAPTER 11 — CREATING DML TRIGGERS

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

^^

See more Database Technologies courses