Oracle

Syllabus

Environment

  • Oracle 11 g Express

 

Introduction To SQL

  • Introduction Database
  • Understanding DBMS vs RDBMS
  • Difference between 10g vs 11g vs 12c
  • Installation of 12c
  • About SQL*Plus and use of developer tool
  • Datatypes in Oracle
  • Operators in Oracle
  • Understanding Schema design and objects

 Data Retrieval Techniques

  • How to use select statement in different ways to retrieve records?
  • Working with Column alias
  • Working with Table alias
  • Data filtering and sorting with in single table
  • Clauses and its types in oracle
  • Where clause
  • Having clause
  • From clause
  • Group by clause
  • Order by clause
  • Using clause
  • Constraint clause
  • For update clause

 Working With DDL Commands

  • Table creation using CREATE statement
  • Creating table from another table
  • Dropping a table using DROP command
  • Altering the column of a table
  • Modifying the column datatype in a table
  • Renaming the column of a table
  • Renaming an entire table
  • Using truncate command
  • Difference between Delete and Truncate command

 Working With DML Commands

  • How to copy data from one table to another table?
  • How to copy the structure alone from a table?
  • Different types of inserting row to an existing table
  • Updating any value of with in a record using UPDATE command
  • Deleting a particular record from a table
  • Using merge & insert all command [Implementation of Project 1 ]

 Integrity Constraints

  • How to declare column level constraints?
  • How to declare row level constraints ?
  • How to add constraints to an existing table?
  • Types of integrity constraints
    • Not null
    • Unique key
    • Primary key
    • Referential integrity
    • Check integrity
  • How to enable and disable constraints?
  • How to get information about constraints?

Built In Functions

  • Understanding Single row functions
  • How to use single row functions using dummy table?
  • Types of single row functions
    • String functions
    • Date functions
    • Mathematical functions
    • Conversion functions
    • Special functions
    • Analytical functions
  • Working with multi row functions [Implementation of Project 2]

 Data Aggregation

  • Working with aggregate function
    • Count()
    • Sum()
    • Max()
    • Min()
    • Avg()
  • Working with group by clause
  • Working with having clause
  • Difference between WHERE and HAVING clause

 Importance Of JOIN

  • Understanding joins and its uses
  • Types of joins
    • Equi join
    • Non – equi join
    • Self join
    • Outer join
    • Left & Right outer join
    • Full outer join
    • Cross join [Implementation of Project 3]

Set Operators And Pseudo Columns:

  • How to use set operators in a single table content?
  • Working with set operator types
    • UNION
    • UNION ALL
    • INTERSECT
    • MINUS
  • Working with pseudo columns using the following
    • ROWID
    • ROWNUM

Sub Queries

  • Importance of sub queries
  • Using different types of sub queries
    • Single row sub queries
    • Multi row sub queries
    • Nested queries
    • Multi column sub queries
    • Correlated sub queries
  • Using inline views and scalar queries [ Implementation of Project 4]

Database Transaction And Security

  • Working with data query language using TCL
  • Working with data control language commands
  • Use of commit and rollback
  • Use of savepoint and set transaction
  • How to give system privileges to an user?
  • How to invoke and revoke object privileges?
  • How to create users and roles?

Design Of Schema Objects

  • Creating and working with Views
  • Working with Synonyms
  • Creating Index and clusters
  • Working with in materialized view
  • Understanding sequences and its types [Implementation of Project 5]

 

 

PL/SQL (Procedural Language Extension to SQL)

 Introduction To PL/SQL

  • Informal introduction to PL/SQL
  • Advantages of PL/SQL
  • Datatypes in PL/SQL
  • Program structure of PL/SQL
  • Embedding SQL statements
  • Using conditional statements and loops

Creating And Using Cursors

  • What is cursor?
  • How to create cursor?
  • Using cursors in PL/SQL
  • How to create explicit cursor?
  • Creation of for loop cursor
  • What are cursor parameters?
  • How to use for update clause?
  • What is ref cursors?
  • How to use implicit cursors?

 Understanding Exception Handling

  • What is an Exception?
  • Describing Exception types
  • Handling system defined exceptions
  • Handling user defined exceptions?
  • Sql code vs Sql errm
  • Pragma exception_init

Creation Of Stored Procedures

  • Creating procedures in Pl/SQL
  • Working with procedure parameters
    • IN parameter
    • OUT parameter
    • INOUT parameter
  • How to create procedures with cursors
  • How procedures return records?
  • What is Pragma autonomous transaction?

Triggers In PL/SQL

  • How to create triggers?
  • Benefits of trigger
  • How to trigger a trigger?
  • Using DML trigger & DDL trigger
  • How to audit database using triggers?
  • What are database level trigger?

 Collections In PL/SQL

  • What is collection?
  • How to use arrays?
  • Using nested tables
  • How to use index by value?
  • Listing types of collection methods.
  • General overview and discussion about DBA Concepts