blackdogcode/OraclePLSQL

The Complete PL/SQL Bootcamp : "Beginner to Advanced PL/SQL"

0. Articles

• What is SQL and How is it Used?
• NTC Hosting: Structured Query Language (it's worth exploring this site, not just this singular posting)
• The Star Schema
• The Snowflake Schema
• Star Schema vs. Snowflake Schema
• What is data modeling – An Introduction for Business Analysts
• Data Modeling 101
• Dataconomy: SQL vs. NoSQL - What You Need to Know
• TechRepublic: NoSQL keeps rising, but relational databases still dominate big data
• SiliconRepublic: Data Science Skills: Is NoSQL Better than SQL?
• What is difference between SQL, SQL*PLUS, PL/SQL

1. References

• Entity–relationship model
  • Crow’s Foot Notation
• Oracle 11g PL/SQL DataTypes
• Setting NLS(National Language Support)

2. SQL

• Difference Between Union and Union All
• Subqueries versus Joins

SQL for R

• SQLDF Package
• Documentation
• Examples

SQL for Spark

• Overview
• Documentation

SQL with Hadoop

• Hive Overview
• Documentation

SQL for Python

• Python-SQL Package Documentation
• Thinking in SQL vs Thinking in Python

2-1. Key Constraints

• Primary Keys
• Foreign Key Topics
• Unique Constraints
• Check Constraints

3. Oracle PL/SQL(Procedural Language extensions to SQL) Reference

• Oracle - Database Administrator's Guide
• Oracle - Database Performance Tuning Guide
• Oracle / PLSQL: Oracle Error Messages
• Oracle PL/SQL Technet

4. PL/SQL Basics

• Oracle Dual Table
• System Tables
• Anonymous Block
• Oracle DB Naming Conventions
• DBMS_OUTPUT and SET SERVEROUTPUT ON
• Oracle / PLSQL: Comments within SQL
• Pseudocolumns
• DESCRIBE Command
• Subprograms
• Union vs Union All
• BETWEEN

5. PL/SQL Variables

• Oracle Data Types
• Declaring variables
• %TYPE Attribute
• PL SQL Delimiters
• Scope Rules
• Bind Variables
• PGA(Program Global Area)

6. PL/SQL Control Structures

• IF and CASE Statements
• LOOP and EXIT Statements
  • FOR LOOP
  • While Loop
• GOTO and NULL Statements

7. SQL in PL/SQL

7-1. DDL(Data Definition Language)

• CREATE TABLE Statement
• CREATE TABLE AS Statement
• ALTER TABLE Statement
• DROP TABLE Statement
• TRUNCATE TABLE Statement
• VIEW
• Indexes

7-2. DML(Data Manipulation Language)

• SELECT Statement
• INSERT Statement
• DELETE Statement
• DISTINCT Statement
• WHERE Clause
• AND, OR and NOT Operators
• Operators
• IN Operator
• NULL Values
• Operator Precedence
• LIKE Operator
• ORDER BY Keyword
• Aliases
• || Concatenation Operator
• BETWEEN Operator
• EXISTS Condition
• Joins
• MINUS Operator

7-3 DCL(Data Control Language)

Grant/Revoke Privileges Roles Change a user's password in Oracle

7-4 Advanced SQL

• SELECT INTO

8. PL/SQL Composite Data Types

• Record Variables
• Collection Types(key-value paris)
  • Nested Tables
  • VARRAYs(Bounded Array)
  • Associatvie Arrays

9. PL/SQL Cursors(Pointers to the data)

• Implicit Cursor
  • syntax
  • semantics
  • Examples
  • Related Topics

• Explicit Cursor
  • syntax
  • semantics
  • examples
  • Related Topics

Cursor Attributes

Cursor FOR LOOP

CURSOR FOR UPDATE

LOCK TABLE Statement

WHERE CURRENT OF Statement

SELECT FOR UPDATE Statement

ROWID Pseudocolumn

What is difference between Weak reference type and Strong reference type - PL/SQL cursor

10. PL/SQL Exceptions

• Oracle / PLSQL: Oracle Error Messages
• Overview of PL/SQL Run-Time Error Handling
• Exception types
• Reraising a PL/SQL Exception
• Handling Raised PL/SQL Exceptions
• Overview of PL/SQL Compile-Time Warnings
• SQLCODE
• SQLERRM
• RAISE_APPLICATION_ERROR

11. PL/SQL Functions

• Using PL/SQL Subprograms
• Developing and Using Stored Procedures
• Using Single-Row Functions
• SQL Server Functions

11.0 Pre-built Functions

• nvl()

11.1 Numeric and Math Functions

• count()
• mod(m, n)
• MIN Function

11.2 String and Char Functions

• INITCAP()

11.3 Analytic Functions

• SQL Window Functions Introduction
• listagg()
• RANK Function
• DENSE_RANK Function

12. PL/SQL Procedures

• Programming PL/SQL Procedure Tutorial
  • PL/SQL Anonymous Block

13. PL/SQL Packages

• What is a Package?
• Reasons to Use Packages
• Package Specification
• Package Body
• Package Instantiation and Initialization
• Package State
• SERIALLY_REUSABLE Packages
• Package Writing Guidelines
• Package Example
• How STANDARD Package Defines the PL/SQL Environment

14. PL/SQL Triggers

• Introduction to Triggers
• Components of a Trigger
• Types of Triggers
• Trigger Execution

15. Advanced PL/SQL

• Sequences (Autonumber)
• PL/SQL Semantics for LOBs

16. PL/SQL Optimization and Tuning

• PL/SQL Optimizer
• Candidates for Tuning
• Minimizing CPU Overhead
• Bulk SQL and Bulk Binding
• Chaining Pipelined Table Functions for Multiple Transformations
• Updating Large Tables in Parallel
• Collecting Data About User-Defined Identifiers
• Profiling and Tracing PL/SQL Programs
• Compiling PL/SQL Units for Native Execution