Oracle SQL Full Course Step by Step. by Deepak Bishoyi YT

Oracle SQL


SQL

INDEX

Introduction to SQL

Data Definition Language-DDL

  • Create 
  • Alter
  • Drop
  • Truncate
  • Rename

Data Manipulation Language-DML

  • Insert
  • Update
  • Delete
  • Marge

Data Retrieval Language-DRL  or  Data Query Language -DQL

  • Select

Transactional Control Language- TCL

  • Commit
  • Rollback
  • Save Point 
  • Sava Point to 

Data Control Languege -DCL

  • Grant 
  • Revoke

DATA TYPES

Number 


Character

  • Char
  • Varchar
  • Varchar2
  • Long

Date


OPERATORS

  1. Arithmetic Operator
  2. Relational Operator
  3. Logical Operator

Special Operator

In              &   Not in
Between   &   Not Between
Is Null      &   Is Not null
Like         &   Not Like


Functions

Predefined Functions

Number Functions

  • Abs()
  • Mod(m,n)
  • Round
  • Trunc(m,n)
  • Gretest
  • Ceil & Floor()
  • Rollup,Cube
  • Upper()
  • Lower()
  • Initcap()
  • Length()
  • Substr()
  • Instr()
  • Lpad()
  • Rpad()
  • Ltrim()
  • Rtrim()
  • Trim()
  • Translate()
  • Replace()
  • Concat()
  • To_Char()
  • To_Date()

Character Functions
Date Functions

Group or Aggregate Functions

  • Max()
  • Min()
  • Avg()
  • Sum()
  • Count(*)
  • Count(column name)

User Defined Functions

Joins

8i Joins

  1. Equi Join or Inner Join
  2. Non Equi Join
  3. Self Join
  4. Outer Join
  5. Cross Join

9i Joins & ANSI Joins

  1. Inner Join
  2. Left Outer Join
  3. Right Outer Join
  4. Full Outer Join
  5. Natural Join

Constraints

  1. Not Null
  2. Unique
  3. Primary Key
  4. Foreign Key
  5. Check
  6. Default Clause 

  • Table Level 
  • Column Level

  • Super Key
  • Candidate Key

SubQuery

Non- Correlated Subquery

  • Single Row Subquery
  • Multiple row subquery
  • Multiple column subquery
  • inline views or subquery are used in "from "clause

Correlated Subquery

  • RowID
  • RowNum

Analytical Functions

  • Row_number()
  • Rank()
  • Dense_rank()
  • Lag()
  • Lead()


View

  • Simple View
  • Complex View (or) Join View

 Materialized View

  1. Complete refresh materialized View
  2. Fast refresh materialized view

Synonyms 

  1. Private Synonyms
  2. Public Synonyms

Sequence 

Index

  • B-tree Index
  • BIT Map Index
  • Function Based Index

Set Operators

  1. Union
  2. Union all
  3. Intersect
  4. Minus

Conversion

  • Implicit Conversions
  • Explicit conversions
  • Decode
  • Pivoting

Data type conversion function 

  • to_number()
  • to_char()
  • to_date()

Null Value Functions

  • NVL()
  • NVL2()
  • nullif()
  • coalesce()

Regular Expressions

  • Regexp_like()
  • Regexp_instr()
  • Regexp_substr()
  • Regexp_replace()
  • Regexp_count()

  • Level
  • Start with
  • Connect by

Normalization

  • First Normal Form
  • Second Normal Form
  • Third Normal Form
  • Boyce-Codd Normal Form(BCNF)
  • Fourth Normal Form
  • Fifth Normal Form


Clusters



Locking

Row Level Locks

Table Level Locks

  • Share Lock
  • Exclusive Lock

  • Dead Locks

Flashback Query

Partitions

  • Range Partition
  • List Partition
  • Hash Partition



PL/SQL

Index


Plsql Introduction

Cursors

  1. Implicit Cursor
  2. Explicit Cursor

Exceptions

  1. Predefined Exceptions
  2. User Defined Exceptions
  3. Unnamed Exceptions

Store Procedure 

Store Functions

Packages

Collections Types

  1. Index By Table
  2. Nested Table
  3. Varray
  4. Refcursor

Bulk Bind & Bulk Collect

UTI_FILE Package

SQL Loader

Triggers

  1. Row Level
  2. Statement Level
  3. Instant of trigger

Post a Comment

0 Comments