Introduction

The Ways to Get a Database

About the Database Installation

Option 1 Downloading Virtual Machine Image(If the previous lecture didn't work)

Option 1 Installing VMware and VirtualBox on Windows

Option 1 Configuring the Oracle VirtualBox Virtualization Software

Option 1 Configuring the VMware Virtualization Software

Option 1 Configuring SQL Developer inside of the Virtual Machine

What is Pluggable Database (Multitenant Architecture)

Option 2 Unlocking the HR Schema

Option 3 Using Oracle Live SQL

Option 2 Configuring and Using the SQL Developer

Option 2 Installing Sample Schemas in Oracle Database

The Sample (HR) Schema Used in This Course

Option 2 Downloading And Installing the Oracle Database

Reaching Another User's Objects

Introducing Database Objects and Reaching Another User's Objects

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 TAB COLUMNS View

USER TABLES View

USER TAB COMMENTS & USER COL COMMENTS Views

USER CONS COLUMNS View

User Constraints View

What is a Sequence and Why is It Used For

Modifying Sequences

Dropping Sequences

Creating Sequences

Using Sequences

Sequence Caching

Using Sequences As a Default Value

User Sequences

What Is a Synonym and Why Is It Used For

Using Synonyms

Analyzing USER SYNONYMS

Creating & Dropping Synonyms

What is an Index and Why is It Used For

Types of Indexes

Creating Indexes

Creating Index on Table Creation

Function Based Indexes

Dropping Indexes

Multiple Indexes With Same Columns

USER INDEXES and USER IND COLUMNS Views

What is a View and Why Is It Used For

Types Of Views

Creating Complex Views

Modifying Views

Creating Simple Views

Analyzing User Views

Performing DML Operations with Views

Preventing DML Operations on A View

Dropping Views

Using WITH CHECK OPTION Clause

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

Scheduling Periodic Refresh of Materialized Views

Refreshing Materialized Views Manually

Using Subqueries as a Source

Scalar Subqueries

Correlated Subqueries

EXISTS Operator

NOT EXISTS Operator

Using WITH Clause

Using Recursive WITH Clause

Multiple Column Subqueries

Inserting Data By Using a Subquery as a Target

Correlated Delete

Correlated Update

Manipulating Data by Using WITH CHECK OPTION Keyword

Using DEFAULT Keyword on Insert And Update Statements

Multitable Insert Statements

Unconditional Insert Statements

Conditional Insert Statements

Conditional Insert First Statements

Pivoting Insert

Purge Operations

Tracking Changes In Data In a Particular Time

Merging Data

FLASHBACK Query

FLASHBACK VERSIONS Query

Flashback Operations

Adding Constraints

Removing Constraints

On Delete CASCADE & On Delete SET NULL

Cascading Constraints

Renaming Constraints

Enabling Constraints

Disabling Constraints

Status Of Constraints

Creating Temporary Tables

Deferring Constraints

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

Object Privileges

Changing The User Password

Revoking Object Privileges

Granting Object Privileges

Searching For Privileges in Data Dictionary Views

Understanding Hierarchical Retrieval

Ranking Rows (Using LEVEL Pseudocolumn)

Walking On The Tree (Bottom to Up - Up to Down)

Formatting Tree Structure (LEVEL & LPAD)

Generating Hierarchical Queries

Pruning Branches Of a Tree

Generating Scripts By Using Data Dictionary Views

What is Regular Expressions and Why and Where Do We Use Them

Using REGEXP LIKE() function

What Are Metacharacters

Using REGEXP REPLACE() Function

Using REGEXP INSTR() Function

Using REGEX in Constraints

Using REGEXP COUNT() Function

Using REGEXP SUBSTR() Function

Regular Expression Functions

What are the Analytic Functions

ROW NUMBER Analytic Function

RANK and DENSE RANK Analytic Functions

Windowing Clause in Analytic Functions - Part 1

LEAD & LAG Analytic Functions (Next & Previous Values)

Selecting Top-N Rows with Analytic Functions

FIRST VALUE and LAST VALUE Analytic Functions

Windowing Clause in Analytic Functions - Part 2

INTERVAL Keyword In Analytic Functions

Appendix 1 Oracle Database 12c Installation into Your Computer

Appendix 3 Configuring and Using Oracle SQL Developer for Oracle Database 12c