Advanced SQL : The Ultimate Guide

Udemy

Learn about the intermediate to advanced concepts and approaches used in SQL to get ready for the SQL certification exams.

Online

₹ 599 4099

particular

details

                                    Medium of instructions
                                    English

                                    Mode of learning
                                    Self study

                                    Mode of Delivery
                                    Video and Text Based

Course Introduction

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

Software Installation

About the Database Installation
The Ways to Get a Database
Option 1: Installing VMware and VirtualBox on Windows
Option 1: How to Install the Virtual Box on Mac OS X?
Option 1: Downloading Virtual Machine Image
Option 1: Configuring the VMware Virtualization Software
Option 1: Configuring the Oracle VirtualBox Virtualization Software
Option 1: Configuring SQL Developer inside of the Virtual Machine
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

Appendix + Conclusion

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
Bonus Lecture

Popular Courses

Popular Platforms

Popular Searches

Advanced SQL : The Ultimate Guide

Online

₹ 599 4099

Quick Facts

Course overview

The highlights

Program offerings

Course and certificate fees

Fees information

certificate availability

certificate providing authority

Who it is for

What you will learn

The syllabus