Course Agenda

1. SQL Basics and SQL*Plus

• Understanding the Difference Between SQL, SQL*Plus and PL/SQL
• Invoking and Leaving SQL*Plus
• Entering and Executing Commands
• The SQL Buffer, SQL*Plus Default Output
• LOGIN.SQL / GLOGIN.SQL
• The SELECT Command and Mandatory Clauses

2. Using SQL Developer

• Understanding SQL Developer
• SQL Developer Setup
• Advanced Functionality

3. The WHERE and ORDER BY Clause

• WHERE, ORDER BY Clause
• Using the Data Dictionary
• Hierarchical Queries
• Pattern Matching

4. Single Row Functions

• Arithmetic Operators
• Operator Precedence
• Single Row vs. Group Functions
• Date and Numeric Format Models
• Data Conversion / Date Manipulation / Time Zones
• Control Statements
• IF THEN ELSE Logic
• Regular Expressions

5. Joining Data from Multiple Tables

• ANSI SQL 92 and SQL99 Joins
• The JOIN Condition / The Cartesian Product
• Outer Joins / SET Operators / Row Ordering

6. Group Functions and the GROUP BY Clause

• Group Functions
• Distinct Operator in Group Functions
• GROUP BY, WITH Clause, ROLLUP, CUBE
• GROUPING SETS

7. Using Subqueries

• Single-Row, Multi-Row, Multi-Column Subqueries
• Scalar Subqueries
• Correlated Subqueries
• Hierarchical Queries
• EXISTS/NOT EXISTS/WITH/Recursive WITH

8. Advanced SQL

• Single Row Analytic Functions
• Aggregating Analytic Functions
• PIVOT / UNPIVOT
• The MODEL clause

9. SQL*Plus Reporting

• SQL*Plus Report Writing Commands
• COLUMN Command / Titles, System Variables
• Master-Detail Report with TITLE and COLUMN Commands
• Control Breaks, Computing Aggregate Amounts
• Using SET Variables in SQL*Plus, SQL to Generate SQL

10. Data Manipulation Language (DML) Commands

• INSERT, UPDATE, DELETE, MERGE
• Use of Subqueries
• Transaction Control Commands / Read Consistency / Locking
• The MERGE Command
• Flashback Queries

11. Data Definition Language (DDL) Commands

• DDL Commands, Object Naming, Dropping Objects
• TRUNCATE, COMMENT, RENAME
• Creating Tables / Integrity Constraints
• CREATE TABLE, ALTER TABLE Command
• Datatypes, Types of Declarative Constraints
• Default Values
• Creating, Deferrable, Dropping, Disabling / Enabling Constraints
• Displaying Constraint Information
• Handling Exceptions
• Views, Synonyms, Sequences, Indexes, Virtual Columns
• Temporary Tables, External Tables
• Changed Data Tracking

12. Getting Data In And Out Of Oracle

• SQL*Loader Basics
• Importing Oracle Data Into Other Programs
• Direct Database Queries Using ODBC
• Using SQL Developer for Import and Export

13. PL/SQL Basics

• SQL and PL/SQL
• PL/SQL anonymous block structure and Datatypes
• Variable declarations and naming conventions
• Object naming rules
• Executable Statements
• Operators in PL/SQL
• Conditional control / Repetition control
• LOOP statements – Basic LOOP, WHILE, FOR
• CASE Expressions and Statements
• Nested Blocks
• Labels and block identification
• Basic Coding Standards
• Using SQL Developer
• Bind variables / Substitution variables
• Datatypes: Scalar, Collection, Record, and Reference
• Commenting code

14. Working with Database Data

• SELECT statements in PL/SQL
• Referencing other variables or a database column’s datatypes with %TYPE
• Using %ROWTYPE
• Using sequences in PL/SQL
• Selecting data into PL/SQL variables
• Explicit cursors
• Cursor attributes
• Transaction control commands
• Using the cursor FOR LOOP
• FOR UPDATE / WHERE CURRENT OF

15. Exception Handling

• Handling predefined and user-defined exceptions
• Controlling exception processing
• Preventing unhandled exceptions
• RAISE_APPLICATION_ERROR
• Using SQLCODE / SQLERRM