HBFITES Data Center, Collaboration, Security, Storage, Wireless, Telepresence Training +91-9886770046 info@hbfites.com

OR106 - Oracle 12c: SQL for Business and Data Analysts

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