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 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 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 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 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 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 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 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 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 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 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
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
- 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
- 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
- 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
- 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
- OVERVIEW OF THIS CHAPTER
- TIMING PL/SQL
- FOR UPDATE / WHERE CURRENT OF
- LAB 6: OPTIMIZATION
- LAB 6 SOLUTIONS: OPTIMIZATION
- SUMMARY OF THIS CHAPTER
- 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
- 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
- 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
- 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
- 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
