Oracle Course - DBA OCA OCP

Introduction

Introduction

Basic Database Concept And Sql

Restricting And Sorting Data

Single-Row Functions

Joins

Multi-Row Functions

Sub-Queries

Data Manipulation Language Dml And Transaction Control Language Tcl

Data Definition Language – Ddl

Constraints

Views

Other Database Objects

Dcl Commands

Set Operators

Advanced Group By Clause

Advanced Subqueries

INSTALLATION AND ADMINISTRATION – 1Z0-062

Introduction

Exploring The Oracle Database Architecture

Installing Oracle Database Software

Creating A New Database Using Dbca

Managing Database Storage Structures

Managing The Database Instance

Oracle Networking

Administering User Security

Managing Undo Data

Managing Data Concurrency

Backup And Recovery Concepts

Backup And Recovery Configuration

Performing Database Backups

Performing Database Recovery

Moving Data

Performing Database Maintenance

Managing Performance

BACKUP AND RECOVERY WORKSHOP – 1Z0-063

Introduction

Configuring Recoverability

Using The Rman Recovery Catalog

Backup Strategies And Terminology

Performing Backups

Diagnosing Failures

Restore And Recovery Concepts

Using Flashback Technologies

Flashback Database

Transporting Data Across Platforms

Duplicating A Database

ORACLE SQL Course

SECTION 1: INTRODUCTION

Describe the features of Oracle Database 12c

Describe the salient features of Oracle Cloud 12c

Explain the theoretical and physical aspects of a relational database

Describe Oracle server’s implementation of RDBMS and object relational database management system (ORDBMS)

SECTION 2: BASIC DATABASE CONCEPT AND SQL

Basic history of database concept: DBMS, RDBMS, ORDBMS

Advantage of ORACLE database and version information

SQL Language overview : DQL, DML, DDL, DCL, TCL

What is the usage of ANSI standard

SELECT Command – Column Alias Rules, String data

Concatenations with various data

Null Value handling with number and characters

Arithmetic Operator

Concatenation Operator

Eliminating Duplicate Rows

SECTION 3: RESTRICTING AND SORTING DATA

WHERE Clause – Character Strings and Dates, number

General Comparison Conditions = > >= < <= <>

Other Comparison BETWEEN , IN , LIKE , NULL

Logical Conditions AND OR NOT

ORDER BY Clause, Sorting by Column Alias , Column Position, Multiple Columns

SECTION 4: SINGLE-ROW FUNCTIONS

Character Functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, LPAD, RPAD, CONCAT, LTRIM, RTRIM, TRIM, REPLACE, TRANSLATE, REVERSE

Number Functions: ROUND, TRUNC, MOD, POWER, CEIL , FLOOR, ABS

Dates Functions: SYSDATE, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ADD_MONTHS, ROUND, TRUNC, Arithmetic on Date

Conversion Functions: Implicit Data-Type Conversion & Explicit Data-Type Conversion, TO_CHAR ,TO_NUMBER ,TO_DATE

General Functions: NVL , NVL2 , NULLIF, COALESCE

CASE Expression, DECODE

Nested function with real-time usage

SECTION 5: JOINS

EQUI JOIN / SIMPLE JOIN / NORMAL JOIN

ANSI JOIN, LEFT OUTER, RIGHT OUTER, FULL OUTER

NATURAL JOIN, NATURAL OUTER JOINS

INNER JOIN, JOIN … USING clause, JOIN … ON clause

CROSS JOIN, NON-EQUI JOIN, SELF JOIN

ORACLE STANDARD OUTER JOINS

SECTION 6: MULTI-ROW FUNCTIONS

Group Functions Rules, SUM, MIN, MAX, COUNT, AVG

Creating Groups of Data: GROUP BY Clause

Filtering Group Results: The HAVING Clause

SECTION 7: SUB-QUERIES

Single-Row Subqueries- Rules, Operators : = > >= < <= <>

Null Values in a Subquery

Multi-Row Subqueries- Rules, Operators : IN, ANY , ALL

SECTION 8: DATA MANIPULATION LANGUAGE DML AND TRANSACTION CONTROL LANGUAGE TCL

DML : INSERT, UPDATE, DELETE, MERGE

TCL : COMMIT, ROLLBACK, SAVEPOINT

SECTION 9: DATA DEFINITION LANGUAGE – DDL

DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE

DEFAULT OPTION

SECTION 10: CONSTRAINTS

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK

Column Level Constraint, Table Level Constraint €“ Naming constraints and usage

Adding a Constraint, Dropping a Constraint

Disabling Constraints, Enabling Constraints

Validating Constraints

SECTION 11: VIEWS

Simple Views and Complex Views €“ Create, Drop, Source Code

Rules for Performing DML Operations on a View

WITH CHECK OPTION , WITH READ ONLY

Inline Views

Materialized View €“ Create, Refresh, Drop – Usage

SECTION 12: OTHER DATABASE OBJECTS

Sequence- NEXTVAL and CURRVAL

Index – When to Create an Index, When Not to Create an Index.

Synonyms

SECTION 13: DCL COMMANDS

Granting / Revoking Privileges

SECTION 14: SET OPERATORS

UNION

UNION ALL

INTERSECT

MINUS

SECTION 15: ADVANCED DATE-TIME FUNCTIONS

TIME ZONES

SYSDATE, SYSTIMESTAMP

CURRENT_DATE , CURRENT_TIMESTAMP

Storing time zone data in Table

SECTION 16: ADVANCED GROUP BY CLAUSE

Group by with ROLLUP AND CUBE

GROUPING SETS

SECTION 17: ADVANCED SUBQUERIES

Pairwise Comparison Subquery

Nonpairwise Comparison Subquery

Correlated Subqueries

EXISTS , NOT EXISTS Operator

SECTION 18: HIERARCHICAL RETRIEVAL

Walking the Tree: From the Bottom Up , From the Top Down

LEVEL Pseudo column

Connect by prior

SECTION 19: MULTI-TABLE INSERT

Unconditional INSERT ALL

Conditional INSERT ALL

Conditional FIRST INSERT