Oracle 12c – SQL for Business and Data Analysts

Overview
Course Outline

Overview

Course Description

Thank you for reading this post, don't forget to subscribe!

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.

Audience Profile

Analysts, end users, executives, programmers, and others who need to extract data from Oracle

Prerequisites

A general understanding of relational database design concepts

Learning Objectives

Converse in relational database terminology.
Understand Structured Query Language (SQL) basics.
Format reports using SQL*Plus commands.
Extract and organize information from the database.
Insert, update, and delete information in database tables.
Create and drop tables, views, synonyms, and indexes.
Work with implicit and explicit cursors

Course Outline

Course Outline

Module 1: SQL Basics and SQL*Plus
Lessons
• 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
Lab: SQL*Plus Basics

Module 2: Using SQL Developer
Lessons
• Understanding SQL Developer
• SQL Developer Setup
• Advanced Functionality
Lab: Using SQL Developer

Module 3: The WHERE and ORDER BY Clause
Lessons
• WHERE, ORDER BY Clause
• Using the Data Dictionary
• Hierarchical Queries
• Pattern Matching
Lab: SQL Queries

Module 4: Single Row Functions
Lessons
• 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
Lab: Filtering And Sorting Data

Module 5: Joining Data from Multiple Tables
Lessons
• ANSI SQL 92 and SQL99 Joins
• The JOIN Condition / The Cartesian Product
• Outer Joins / SET Operators / Row Ordering
Lab: Single Row Functions

Module 6: Group Functions and the GROUP BY Clause
Lessons
• Group Functions
• Distinct Operator in Group Functions
• GROUP BY, WITH Clause, ROLLUP, CUBE
• GROUPING SETS
Lab: Joining Multiple Tables

Module 7: Using Subqueries
Lessons
• Single-Row, Multi-Row, Multi-Column Subqueries
• Scalar Subqueries
• Correlated Subqueries
• Hierarchical Queries
• EXISTS/NOT EXISTS/WITH/Recursive WITH

Module 8: Advanced SQL
Lessons
• Single Row Analytic Functions
• Aggregating Analytic Functions
• PIVOT / UNPIVOT
• The MODEL clause
Lab: ANSI SQL99 Joins

Module 9: SQL*Plus Reporting
Lessons
• 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
Lab: Group Clauses and Functions

Module 10: Data Manipulation Language (DML) Commands
Lessons
• INSERT, UPDATE, DELETE, MERGE
• Use of Subqueries
• Transaction Control Commands / Read Consistency / Locking
• The MERGE Command
• Flashback Queries
Lab: Subqueries

Module 11: Data Definition Language (DDL) Commands
Lessons
• 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
Lab: SQL*Plus Reporting

Module 12: Getting Data In and Out of Oracle
Lessons
• SQL*Loader Basics
• Importing Oracle Data Into Other Programs
• Direct Database Queries Using ODBC
• Using SQL Developer for Import and Export
Lab: PL/SQL Basics

Module 13: PL/SQL Basics
Lessons
• 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
Lab: Selecting and Updating Database Data

Module 14: Working with Database Data
Lessons
• 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
Lab: Using Explicit Cursors

Module 15: Exception Handling
Lessons
• Handling predefined and user-defined exceptions
• Controlling exception processing
• Preventing unhandled exceptions
• RAISE_APPLICATION_ERROR
• Using SQLCODE / SQLERRM