Oracle 12c : Learn SQL & PL/SQL

Brief History of SQL

Relational Databases

        - Tables

        - Rows

        - Columns

        - Relationships

        - Datatypes

        - Primary Keys

        - Foreign Keys

        - Relational Database Management System

Popular Databases

        - Commercial Databases

        - Popular Open Source Databases

        - Valid Object References

SQL Statements

        - Database Manipulation Language (DML)

        - Database Definition Language (DDL)

        - Database Control Language (DCL)

Introduction to the Northwind Database

Some Basics

        - Comments

        - Whitespace and Semi-colons

        - Case Sensitivity

SELECTing All Columns in All Rows

Exploring the Tables

SELECTing Specific Columns

Sorting Records

        - Sorting By a Single Column

        - Sorting By Multiple Columns

        - Sorting By Column Position

        - Ascending and Descending Sorts

The WHERE Clause and Operator Symbols

        - Checking for Equality

        - Checking for Inequality

        - Checking for Greater or Less Than

        - Checking for NULL

        - WHERE and ORDER BY

Using the WHERE clause to check for equality or inequality

Using the WHERE clause to check for greater or less than

Checking for NULL

Using WHERE and ORDER BY Together

The WHERE Clause and Operator Words

        - The BETWEEN Operator

        - The IN Operator

        - The LIKE Operator

        - The NOT Operator

More SELECTs with WHERE

Checking Multiple Conditions

        - AND

        - OR

        - Order of Evaluation

Writing SELECTs with Multiple Conditions

Calculated Fields

        - Concatenation

        - Mathematical Calculations

        - Aliases

Calculating Fields

Aggregate Functions and Grouping

        - Aggregate Functions

        - Grouping Data

        - Selecting Distinct Records

Working with Aggregate Functions

Built-in Data Manipulation Functions

        - Common Math Functions

        - Common String Functions

        - Common Date Functions

Data Manipulation Functions

Subqueries

Joins

        - Table Aliases

        - Multi-table Joins

Using Joins

Outer Joins

Unions

        - UNION ALL

        - UNION Rules

Working with Unions

Conditional Processing with CASE

INSERT, UPDATE, DELETE

Student Challenges - Design your own reports

Introduction to Users and Schemas

        - Definitions and Client Tools

        - SQL Developer Login

About the HR Schema

        - Sample Schemas

        - HR Schema Entities

Creating An Application Schema

Exercise: Create a User

• Using The Dual Table
• Functions
• Exercise: Use the SYSCONTEXT function
• Pseudo-Columns
• Using Rowid
• Using Rownum
• Exercise: Use ROWNUM

Simple Subqueries

        - Definition

        - Nested Subquery

Inline Views

        - Subquery in the HAVING clause

Correlated Subqueries

Scalar Subquery

Exercise: Subqueries

• Review of Joins
• Equijoins

        - Inner Joins

        - Outer Joins

• Cross Joins
• Reflexive Join
• Non-Key Join
• Natural Joins
• Semijoins and Antijoins
• Using Named Subqueries
• Exercise: Join Exercises

• About Group Processing
• EMP_DETAILS_VIEW
• Simple GROUP BY
• ROLLUP Function
• GROUPING Function
• Using Cube
• Exercise: Practice GROUP BY, ROLLUP and CUBE

• Set Operators Defined
• Relationship to Mathematical Set Theory
• Restrictions on Set Operators
• Exercise: Set Operators Exercise

The DECODE Function

The Case Expression

Exercise: Conditional Processing

What Are SQL Functions?

Character Functions

        - CONCAT

        - LENGTH

        - INSTR

        - REPLACE

        - UPPER

        - LOWER

        - INITICAP

        - LPAD

        - RPAD

        - TRIM

        - TO_CHAR

        - SOUNDEX

Regular Expressions

Exercise: Character Functions

• Numeric Functions

        - The TO_CHAR Function with numbers

• Date/Time Functions
• Date Format Functions
• Date Arithmetic Functions
• Null Value Functions
• Exercise: SQL Non-Character Functions

• The INSERT Statement

        - INSERT Statement Categories

• The UPDATE Statement
• The DELETE Statement
• Transactions
• Complex Table References
• The MERGE statement
• Exercise: Data Manipulation Language (DDL)

• Prerequisites

        - File Naming Conventions

• Connecting to Oracle

        - SQL*Developer

        - SQL*Plus

• Executing PL/SQL Code
• SQL Developer Configuration
• The PL/SQL Development Cycle

• PL/SQL Block
• Anonymous Block Structure
• Named Block Structure
• Executing Blocks
• Calling PL/SQL Functions
• Executing PL/SQL Blocks and Functions

• Variable Usage
• Variable Data Types
• Variable Naming
• Variable Assignment

        - Variable Display

• Complex Variable Types
• More Information
• Variable Declaration, Initialization and Display

Conditional Processing

        - The IF Statement

        - The CASE Statement

Iterative Processing

        - The LOOP Statement

        - The WHILE Statement

        - The FOR Statement

Salary Classification using Conditional Statements

Salary Increases using Loops

• Overview of Exceptions
• Causing System Generated Exceptions
• Handling System Generated Exceptions
• Identifying System Generated Exceptions
• OTHERS Exception Handler
• User Defined Exceptions
• Exception Handling for Invalid Salary

• Implicit Cursors
• %TYPE and %ROWTYPE Attributes
• EXECUTE IMMEDIATE statement
• Cursors Attributes
• Implicit Cursor
• Explicit Cursors
• Explicit Cursor
• Cursor FOR loop

• Nesting Blocks
• Scope of Variables
• Scope of Exceptions
• Nested Subprograms
• Exception Propagation in Nested Blocks
• Block Variable Visibility

• Anonymous blocks
• Types of Subprograms
• Finding Subprograms through SQLDeveloper
• Finding Subprograms in the Oracle Data Dictionary
• Object Dependencies
• Subprogram information in the Oracle Data Dictionary

• Creating Subprograms
• Modifying Subprograms
• Removing Subprograms
• Application Maintenance
• Exercise Title
• Parameters
• Cursors as Parameters
• Subprogram Development Techniques
• Addressing Compilation Errors
• Directives for Debugging
• Issues with Booleans
• Integrated Development Environments
• PL/SQL Subprograms with Parameters

• Structure of Packages
• Purpose of Packages
• Wrap Utility
• Positional vs Named Parameter Notation
• Subprograms Omitted from Specifications
• Using PL/SQL Packages

• Purpose of Triggers
• Invocation of Triggers
• Coding Triggers
• Validation Trigger
• Modifying Triggers
• Viewing Triggers
• Enabling/Disabling Triggers
• Trigger Errors
• System and User Event Triggers
• Logon Counter Trigger

• Cursor Review
• Cursor Parameters
• Cursor Variables
• Dynamic SQL
• Employee Report by State/Province