Email Us   Phone : 503-259-0312   
  Home    |    Instructor-led Training    |    Self-Paced Learning    |    Online Training     


Contact Us   -   Why Choose Wintrac   -   Clients    

Courses
ADA
Adobe
Agile
AJAX
Android
Apache
AutoCAD
Big Data
Business Analysis
Business Intelligence
Business Objects
Business Skills
C++ programming
Cisco
Citrix
Cloud Computing
COBOL
Cognos
ColdFusion
COM/COM+
CompTIA
CORBA
CRM
Crystal Reports
Datawarehousing
DB2
Desktop Application Software
DNS
Embedded Systems
Google Web Toolkit (GWT)
IPhone
ITIL
Java
JBoss
LDAP
Leadership Development
Lotus
Machine learning/AI
Macintosh
Mainframe programming
Microsoft technologies
Mobile
MultiMedia and design
.NET
NetApp
Networking
New Manager Development
Object oriented analysis and design
OpenVMS
Oracle
Oracle VM
Perl
PHP
PowerBuilder
Professional Soft Skills Workshops
Project Management
Rational
Ruby
Sales Performance
SAP
SAS
Security
SharePoint
SOA
Software quality and tools
SQL Server
Sybase
Symantec
Telecommunications
Teradata
Tivoli
Tomcat
Unix/Linux/Solaris/AIX/
HP-UX
Visual Basic
Visual Foxpro
VMware
Web Development
WebLogic
WebSphere
Websphere MQ (MQSeries)
Windows programming
XML
XML Web Services
Other
Oracle12c Release 1 SQL And PL/SQL
Oracle Training Overview

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

 
About us
Contact us
Careers at Wintrac
Our Clients
Why Wintrac


Register for a free training CD-ROM drawing
Refer a client or instructor and earn $$$


Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007
 
Wintrac, Inc. All rights reserved.                                                                               Site Map   |   Terms of Use   |   Privacy Policy