Oracle 12c - SQL for Business and Data Analysts

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

• 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

1. SQL*Plus Basics

2. Using SQL Developer

3. SQL Queries

4. Filtering And Sorting Data

5. Single Row Functions

6. Joining Multiple Tables

7. ANSI SQL99 Joins

8. Group Clauses and Functions

9. Subqueries

10. SQL*Plus Reporting

11. PL/SQL Basics

12. Selecting and Updating Database Data

13. Using Explicit Cursors

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