Udemy Advanced SQL : SQL Expert Certification Preparation Course 2020

Video Introducing this tutorial


Course Introduction :
Introduction
UDEMY 101: How to Use Udemy? +Some Useful Tips

Software Installation :
About the Database Installation
Which Option to Choose to Have a Database?
Option 1: Having the Database with the Oracle Virtual Box
Option 1: How to Install the Virtual Box on Mac OS X?
What is Pluggable Database (Multitenant Architecture)?
Option 2: Downloading And Installing the Oracle Database
Option 2: Unlocking the HR Schema
Option 2: Configuring and Using the SQL Developer
Option 3: Using Oracle Live SQL
Option 3: About Oracle Live SQL Restrictions
The Sample (HR) Schema Used in This Course

Introducing Database Objects :
Introducing Database Objects and Reaching Another User's Objects
Reaching Another User's Objects
The Working Document
Don't Forget to Leave a Rating!
Code Samples
Quiz 1

Data Dictionary Views :
What Are Data Dictionary Views?
What Is There In Data Dictionary Views?
Dictionary Table
Differences Between USER, ALL, DBA, V$ Prefixes
USER_OBJECTS, ALL_OBJECTS, DBA_OBJECTS Views
USER_TABLES View
USER_TAB_COLUMNS View
User Constraints View
USER_CONS_COLUMNS View
USER_TAB_COMMENTS & USER_COL_COMMENTS Views
Quiz 2

Creating, Using and Managing Sequences :
What is a Sequence and Why is It Used For?
Creating Sequences
Modifying Sequences
Dropping Sequences
Using Sequences
Using Sequences As a Default Value
Sequence Caching
User Sequences
Quiz 3

Creating, Using and Analyzing Synonyms :
What Is a Synonym and Why Is It Used For?
Creating & Dropping Synonyms
Using Synonyms
Analyzing USER_SYNONYMS
Quiz 4

Understanding Indexes :
What is an Index and Why is It Used For?
Types of Indexes
Creating Indexes
Creating Index on Table Creation
Function Based Indexes
Multiple Indexes With Same Columns
Dropping Indexes
USER_INDEXES and USER_IND_COLUMNS Views
Quiz 5

Managing Views :
What is a View and Why Is It Used For?
Types Of Views
Creating Simple Views
Creating Complex Views
Modifying Views
Analyzing User Views
Performing DML Operations with Views
Using WITH CHECK OPTION Clause
Preventing DML Operations on A View
Dropping Views
Quiz 6

Materialized Views :
What is a Materialized View and Why is it Used For?
Creating Materialized Views Part 1
Creating Materialized Views Part 2
Complex Materialized Views
Types of Refreshing Materialized Views
Refreshing Materialized Views Manually
Scheduling Periodic Refresh of Materialized Views
Quiz 7

Using Advanced Subqueries :
Using Subqueries as a Source
Scalar Subqueries
Multiple Column Subqueries
Correlated Subqueries
EXISTS Operator
NOT EXISTS Operator
Using WITH Clause
Using Recursive WITH Clause
Inserting Data By Using a Subquery as a Target
Manipulating Data by Using "WITH CHECK OPTION" Keyword
Correlated Update
Correlated Delete
Quiz 8

Advanced Data Manipulations :
Using DEFAULT Keyword on Insert And Update Statements
Multitable Insert Statements
Unconditional Insert Statements
Conditional Insert Statements
Conditional Insert First Statements
Pivoting Insert
Merging Data
Flashback Operations
Purge Operations
Tracking Changes In Data In a Particular Time
FLASHBACK Query
FLASHBACK VERSIONS Query
Quiz 9

Constraints (Controlling Schema Objects) :
Adding Constraints
Removing Constraints
On Delete CASCADE & On Delete SET NULL
Cascading Constraints
Renaming Constraints
Disabling Constraints
Enabling Constraints
Status Of Constraints
Deferring Constraints
Creating Temporary Tables
Quiz 10

Administering User Privileges and Roles :
What Are Privileges And Why Are They Used For?
Creating A User
System Privileges
Granting System Privileges
What is A Role and Why is It Used For?
Changing The User Password
Object Privileges
Granting Object Privileges
Revoking Object Privileges
Searching For Privileges in Data Dictionary Views
Quiz 11

Hierarchical Queries (Tree Structure) :
Understanding Hierarchical Retrieval
Generating Hierarchical Queries
Walking On The Tree (Bottom to Up - Up to Down)
Ranking Rows (Using LEVEL Pseudocolumn)
Formatting Tree Structure (LEVEL & LPAD)
Pruning Branches Of a Tree
Quiz 12

Generating Dynamic SQL Scripts :
Generating Scripts By Using Data Dictionary Views

Regular Expressions :
What is Regular Expressions and Why and Where Do We Use Them?
What Are Metacharacters?
Regular Expression Functions
Using REGEXP_LIKE() function
Using REGEXP_REPLACE() Function
Using REGEXP_SUBSTR() Function
Using REGEXP_INSTR() Function
Using REGEXP_COUNT() Function
Using REGEX in Constraints

Analytical SQL Functions :
What are the Analytic Functions?
ROW_NUMBER Analytic Function
RANK and DENSE_RANK Analytic Functions
LEAD & LAG Analytic Functions (Next & Previous Values)
Windowing Clause in Analytic Functions - Part 1
Windowing Clause in Analytic Functions - Part 2
INTERVAL Keyword In Analytic Functions
FIRST_VALUE and LAST_VALUE Analytic Functions
Selecting Top-N Rows with Analytic Functions

Bonus(+) Section :
Where to next?

Appendix :
Appendix 1: Oracle Database 12c Installation into Your Computer
Appendix 2: How to Unlock the HR User (Schema) in the Oracle Database 12c?
Appendix 3: Configuring and Using Oracle SQL Developer for Oracle Database 12c

©2021 | All rights reserved.