Course Details
In this course, you will learn how to extract data from Oracle using SQL, SQL*Plus, SQL Developer, and PL/SQL. This training covers topics that are necessary to query data for analysis from an Oracle12c database.
Course Prerequisites
A general understanding of relational database design concepts
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