Oracle SQL and PLSQL

Module 1: Writing Basic SQL SELECT Statements

• Basic SELECT Statement
• Selecting All Columns
• Selecting Specific Columns
• Writing SQL Statements
• Column Heading Defaults
• Arithmetic Expressions
• Using Arithmetic Operators
• Operator Precedence
• Using Parentheses
• Defining a Null Value
• Null Values in Arithmetic Expressions
• Defining a Column Alias
• Using Column Aliases
• Concatenation Operator
• Using the Concatenation Operator
• Literal Character Strings
• Using Literal Character Strings
• Duplicate Rows
• Eliminating Duplicate Rows

Module 2: Restricting and Sorting Data

• Limiting Rows Using a Selection
• Limiting the Rows Selected
• Using the WHERE Clause
• Character Strings and Dates
• Comparison Conditions
• Using Comparison Conditions
• Other Comparison Conditions
• Using the BETWEEN Condition
• Using the IN Condition
• Using the LIKE Condition
• Using the NULL Conditions
• Logical Conditions
• Using the AND Operator
• Using the OR Operator
• Using the NOT Operator
• Rules of Precedence
• ORDER BY Clause
• Sorting in Descending Order
• Sorting by Column Alias
• Sorting by Multiple Columns

Module 3: Single-Row Functions

• SQL Function
• Two Types of SQL Functions
• Single-Row Functions
• Single-Row Functions
• Character Functions
• Character Functions
• Case Manipulation Functions
• Using Case Manipulation Functions
• Character-Manipulation Functions
• Using the Character-Manipulation Functions
• Number Functions
• Using the ROUND Function
• Using the TRUNC Function
• Using the MOD Function
• Working with Dates
• Arithmetic with Dates
• Using Arithmetic Operators with Dates
• Date Functions
• Using Date Functions
• Practice 3, Part One:
• Conversion Functions
• Implicit Data Type Conversion
• Explicit Data Type Conversion
• Using the TO_CHAR Function with Dates
• Elements of the Date Format Model
• Using the TO_CHAR Function with Dates
• Using the TO_CHAR Function with Numbers
• Using the TO_NUMBER and TO_DATE Functions
• RR Date Format
• Example of RR Date Format
• Nesting Functions General Functions
• NVL Function
• Using the NVL Function
• Using the NVL2 Function
• Using the NULLIF Function
• Using the COALESCE Function
• Conditional Expressions
• The CASE Expression
• Using the CASE Expression
• The DECODE Function
• Using the DECODE Function

Module 4: Displaying Data from Multiple Tables

• Obtaining Data from Multiple Tables
• Cartesian Products
• Generating a Cartesian Product
• Types of Joins
• Joining Tables Using Oracle Syntax
• What is an Equijoin?
• Retrieving Records with Equijoins
• Additional Search Conditions Using the AND Operator
• Qualifying Ambiguous Column Names
• Using Table Aliases
• Joining More than Two Tables
• Non-Equijoins
• Retrieving Records with Non-Equijoins
• Outer Joins Outer Joins Syntax
• Using Outer Joins
• Self Joins
• Joining a Table to Itself
• Joining Tables Using SQL: 1999 Syntax
• Creating Cross Joins
• Creating Natural Joins
• Retrieving Records with Natural Joins
• Creating Joins with the USING Clause
• Retrieving Records with the USING Clause
• Creating Joins with the ON Clause
• Retrieving Records with the ON Clause
• Creating Three-Way Joins with the ON Clause
• INNER Versus OUTER Joins
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN
• Additional Conditions

Module 5: Aggregating Data Using Group Functions

• What Are Group Functions?
• Types of Group Functions
• Group Functions Syntax
• Using the AVG and SUM Functions
• Using the MIN and MAX Functions
• Using the COUNT Function
• Using the DISTINCT Keyword
• Group Functions and Null Values
• Using the NVL Function with Group Functions
• Creating Groups of Data
• Creating Groups of Data: The GROUP BY Clause Syntax
• Using the GROUP BY Clause
• Grouping by More Than One Column
• Using the GROUP BY Clause on Multiple Columns
• Illegal Queries Using Group Functions
• Excluding Group Results
• Excluding Group Results: The HAVING Clause
• Using the HAVING Clause
• Nesting Group Functions

Module 6: Subqueries

• Objectives
• Using a Subquery to Solve a Problem
• Subquery Syntax
• Using a Subquery
• Guidelines for Using Subqueries
• Types of Subqueries
• Single-Row Subqueries
• Executing Single-Row Subqueries
• Using Group Functions in a Subquery
• The HAVING Clause with Subqueries
• What is Wrong with this Statement?
• Will this Statement Return Rows?
• Multiple-Row Subqueries
• Using the ANY Operator in Multiple-Row Subqueries
• Using the ALL Operator in Multiple-Row Subqueries
• Null Values in a Subquery

Module 7: . Manipulating Data

• Data Manipulation Language
• Adding a New Row to a Table
• The INSERT Statement Syntax 8-5
• Inserting New Rows
• Inserting Rows with Null Values
• Inserting Special Values
• Inserting Specific Date Values
• Creating a Script
• Copying Rows from Another Table
• Changing Data in a Table
• The UPDATE Statement Syntax
• Updating Rows in a Table
• Updating Two Columns with a Subquery
• Updating Rows Based on Another Table
• Updating Rows: Integrity Constraint Error
• Removing a Row from a Table
• The DELETE Statement
• Deleting Rows from a Table
• Deleting Rows Based on Another Table
• Deleting Rows: Integrity Constraint Error
• Using a Subquery in an INSERT Statement
• Using the WITH CHECK OPTION Keyword on DML Statements
• Overview of the Explicit Default Feature
• Using Explicit Default Values
• The MERGE Statement
• The MERGE Statement Syntax
• Merging Rows
• Database Transactions
• Advantages of COMMIT and ROLLBACK Statements
• Controlling Transactions
• Rolling Back Changes to a Marker
• Implicit Transaction Processing
• State of the Data Before COMMIT or ROLLBACK
• State of the Data after COMMIT
• Committing Data
• State of the Data After ROLLBACK
• Statement-Level Rollback
• Read Consistency
• Implementation of Read Consistency
• Locking
• Implicit Locking
• Read Consistency Example

Module 8: Creating and Managing Tables

• Database Objects
• Naming Rules
• The CREATE TABLE Statement
• Referencing Another User?s Tables
• The DEFAULT Option
• Creating Tables
• Tables in the Oracle Database
• Querying the Data Dictionary 9-10
• Data Types
• DateTime Data Types
• TIMESTAMP WITH TIME ZONE Data Type
• TIMESTAMP WITH LOCAL TIME Data Type
• INTERVAL YEAR TO MONTH Data Type
• INTERVAL DAY TO SECOND Data Type
• Creating a Table by Using a Subquery Syntax
• Creating a Table by Using a Subquery
• The ALTER TABLE Statement
• Adding a Column
• Modifying a Column
• Dropping a Column
• The SET UNUSED Option
• Dropping a Table
• Changing the Name of an Object
• Truncating a Table
• Adding Comments to a Table

Module 9: Including Constraints

• What are Constraints?
• Constraint Guidelines
• Defining Constraints
• The NOT NULL Constraint
• The UNIQUE Constraint
• The PRIMARY KEY Constraint
• The FOREIGN KEY Constraint
• FOREIGN KEY Constraint Keywords
• The CHECK Constraint
• Adding a Constraint Syntax
• Adding a Constraint
• Dropping a Constraint
• Disabling Constraints
• Enabling Constraints
• Cascading Constraints
• Viewing Constraints
• Viewing the Columns Associated with Constraints

Module 10: Creating Views

• Database Objects
• What is a View?
• Why use Views?
• Simple Views and Complex Views
• Creating a View
• Retrieving Data from a View
• Querying a View
• Modifying a View
• Creating a Complex View
• Rules for Performing DML Operations on a View
• Using the WITH CHECK OPTION Clause
• Denying DML Operations

Module 11: Other Database Objects

• Database Objects
• What is a Sequence?
• The CREATE SEQUENCE Statement Syntax
• Creating a Sequence
• Confirming Sequences
• NEXTVAL and CURRVAL Pseudocolumns
• Using a Sequence
• Modifying a Sequence
• Guidelines for Modifying a Sequence
• Removing a Sequence
• What is an Index?
• How Are Indexes Created?
• Creating an Index
• When to Create an Index
• When Not to Create an Index
• Confirming Indexes
• Function-Based Indexes
• Removing an Index
• Creating and Removing Synonyms

Module 12: Controlling User Access

• Objectives
• Controlling User Access
• Privileges
• System Privileges
• Creating Users
• User System Privileges
• Granting System Privileges
• What is a Role?
• Creating and Granting Privileges to a Role
• Changing Your Password
• Object Privileges
• Granting Object Privileges
• Using the WITH GRANT OPTION and PUBLIC Keywords
• Confirming Privileges Granted
• How to Revoke Object Privileges
• Revoking Object Privileges
• Database Links

Module 13: SQL Workshop

• Workshop Overview

Module 14: Using SET Operators

• The SET Operators
• Tables Used in This Lesson
• The UNION Operator
• Using the UNION Operator
• The UNION ALL Operator
• Using the UNION ALL Operator
• The INTERSECT Operator
• Using the INTERSECT Operator
• The MINUS Operator
• SET Operator Guidelines
• The Oracle Server and SET Operators
• Matching the SELECT Statements
• Controlling the Order of Rows

Module 15: Enhancements to the GROUP BY Clause

• Review of Group Functions
• Review of the GROUP BY Clause
• Review of the HAVING Clause
• GROUP BY with ROLLUP and CUBE Operators
• ROLLUP Operator
• ROLLUP Operator Example
• CUBE Operator
• CUBE Operator: Example
• GROUPING Function
• GROUPING Function: Example
• GROUPING SETS
• GROUPING SETS: Example
• Composite Columns
• Composite Columns: Example
• Concatenated Groupings
• Concatenated Groupings Example

Module 16: Advanced Subqueries

• What Is a Subquery?
• Subqueries
• Using a Subquery
• Multiple-Column Subqueries
• Column Comparisons
• Pairwise Comparison Subquery
• Nonpairwise Comparison Subquery
• Using a Subquery in the FROM Clause
• Scalar Subquery Expressions
• Scalar Subqueries: Examples
• Correlated Subqueries
• Using Correlated Subqueries
• Using the EXISTS Operator
• Using the NOT EXISTS Operator
• Correlated UPDATE
• Correlated DELETE
• The WITH Clause
• WITH Clause: Example
• Hierarchical Retrieval
• Sample Data from the EMPLOYEES Table
• Natural Tree Structure

Module 17: Hierarchical Queries

• Walking the Tree
• Walking the Tree: From the Bottom Up
• Walking the Tree: From the Top Down
• Ranking Rows with the LEVEL Pseudocolumn
• Formatting Hierarchical Reports Using LEVEL and LPAD
• Pruning Branches

Module 18: Extensions to DML and DDL Statements

• Review of the INSERT Statement
• Review of the UPDATE Statement
• Overview of Multitable INSERT Statements
• Overview of Multitable INSERT Statements
• Types of Multitable INSERT Statements
• Multitable INSERT Statements
• Unconditional INSERT ALL
• Conditional INSERT ALL
• Conditional FIRST INSERT
• Pivoting INSERT
• External Tables
• Creating an External Table
• Example of Creating an External Table
• Querying External Tables
• CREATE INDEX with CREATE TABLE Statement

Module 19: Creating and Running PL/SQL Code

• SQL*Plus
• Performing Essential PL/SQL Tasks
• Calling PL/SQL from Other Languages
• Language Fundamentals
• PL/SQL Block Structure
• The PL/SQL Character Set
• Identifiers
• Literals
• The Semicolon Delimiter
• Comments
• The PRAGMA Keyword
• Labels

Module 20: Loop Basics

• The Simple Loop
• The WHILE Loop
• The Numeric FOR Loop
• The Cursor FOR Loop
• Loop Labels
• Tips for Iterative Processing

Module 21: Exception Handlers

• Exception-Handling Concepts and Terminology
• Defining Exceptions
• Raising Exceptions
• Handling Exceptions
• Building an Effective Error Management Architecture
• Making the Most of PL/SQL Error Management

Module 22 : Strings

• String Datatypes
• Working with Strings
• String Function Quick Reference

Module 23: Numbers

• Numeric Datatypes
• Number Conversions
• Numeric Functions

Module 24: Records

• Records in PL/SQL

Module 25: Collections

• Collections Overview
• Collection Methods (Built-Ins)
• Working with Collections
• Nested Table Multiset Operations

Module 26: Miscellaneous Datatypes

• The BOOLEAN Datatype
• The RAW Datatype
• The UROWID and ROWID Datatypes
• The LOB Datatypes
• Working with LOBs
• Predefined Object Types

Module 27: Data Retrieval

• Cursor Basics
• Working with Implicit Cursors
• Working with Explicit Cursors
• BULK COLLECT
• SELECT … FOR UPDATE
• Cursor Variables and REF CURSORs
• Cursor Expressions

Module 28: Procedures, Functions, and Parameters

• Procedures
• Functions
• Parameters
• Local Modules
• Module Overloading
• Forward Declarations
• Advanced Topics

Module 29: Packages

• Why Packages?
• Rules for Building Packages
• Rules for Calling Packaged Elements
• Working with Package Data
• When to Use Packages
• Packages and Object Types

Module 30: Triggers

• DML Triggers
• DDL Triggers
• Database Event Triggers
• INSTEAD OF Triggers
• AFTER SUSPEND Triggers
• Maintaining Triggers

Module 31: Managing PL/SQL Code

• Managing Code in the Database
• Using Native Compilation
• Using the Optimizing Compiler and Compile-Time Warnings
• Conditional Compilation
• Testing PL/SQL Programs
• Debugging PL/SQL Programs
• Tuning PL/SQL Programs
• Protecting Stored Code

Module 32: I/O and PL/SQL

• Displaying Information
• Reading and Writing Files
• Sending Email
• Working with Web-Based Data (HTTP)
• Other Types of I/O Available in PL/SQL

Meet Ananth Tirumanur. Hi there 👋

I work on projects in data science, big data, data engineering, data modeling, software engineering, and system design.

• 👨‍💻 All of my projects are available at https://github.com/akrish1982
• 💬 Ask me about Data engineering, SQL, Databases, Data pipelines, Data infrastructure
• 📄 My work history: https://www.linkedin.com/in/ananthtirumanur/
• ⚡ Fun fact: Marathoner & Casual Birding enthusiast

Connect with me:

• Twitter: @akrish82
• LinkedIn: https://linkedin.com/in/ananthtirumanur/

My Resources:

• LinkedIn Newsletter: Data Engineering with AWS
• Udemy Course: AWS Certified Data Engineer Associate Practice Test
• Python Crash Course: Python Crash Course on Gumroad

Languages and Tools:

AWS, Bash, Docker, Elasticsearch, Git, Grafana, Hadoop, Hive, EMR, Glue, Athena, Lambda, Step Functions, Airflow/MWAA, DynamoDB, Kafka, Kubernetes, Linux, MariaDB, MySQL, Pandas, PostgreSQL, Python, Redis, Scala, SQLite