i2 ORACLE Interview

Database Fundamentals
----------------------------------
# Types of Data Models
# ER and ERR [ Entity relationship & Enhanced Entity relationship concepts]
# Describe - RDBMS, Structure of RDBMS
# Structure of Oracle instance

Advanced Database concept
------------------------------------------
# Describe - Normalization,Functional dependency
# First form , Second form & Third form Normalization [ Fundamental only]

SQL
-------
# Queries on Usage of Functions- Character function , Date function
# Queries on Group function, NVL function,Having clause
# Some typical queries like - * Extract 3rd highest salary, * Delete Duplicate records, *Fetch nth Row
# Queries on Row num and Row id concept
# Implementation of IF-ELSE logic in SQL
# Querries using ' Case ' and ' Decode' claused
# Describe- Outer join , Inner Join , Query on Self Join
# Types of Constraints , describe- Foreign Key
# Describe - View, Materialised View, Syntax of Materialised View
# Usage of Index

PL/SQL
------------
# Describe %Type and %Rowtype declaration
# Cursors, Syntax, Ref cursors
# Describe For loop Cursors
# Error reporting Functions
# Describe - Exception Handling
# Give Examples of System generated Exception
# Usage of Stored Procedure . Give an example with INOUT parameter
# Difference between Stored procedure and Function
# Describe Package specification and Package body
# Describe private and public constructs
# Errors while compiling Packages
# Trigger Syntax

Oracle Architecture
----------------------------
# Table space
# Concept of Commit and Rollback interms of Oracle Process
# How Oracle handles multiple processes acting on a same object and a single instant of time



PL/SQL [ GENERAL ]
==================
# Syntax Fundamentals
# Advanced data type declarations [ %Type and %Rowtype ]
# Conditional statements and loops
# Exception handling [ System & user defined Exceptions ]

CURSORS
=======
# General cursors
# Attributes
# Explicit,Implicit & For loop cursors
# Ref / Dynamic cursors

PROCEDURES
==========
# Creation,Alter and drop procedures
# Invokation
# Parameters and default specifications

FUNCTIONS
=========
# Creation,Alter and drop procedures
# Invokation
# Passing Parameters

PACKAGES
=========
# Creation,Alter and drop packages
# Invokation of package constructs

TRIGGERS
========
# Creation,Alter and drop ptRIGGERS

ADVANCED DATABASE CONCEPT
==========================
[ DBMS- Korth]
# Normalization
# First Normal Form
# Second Normal Form
# Third Normal form

Advanced SQL
=============
# Materized Views
# Heirarchial Queries


SQL Questions
1. List the Top 5 salaries in the Employee Table. (Without using Rank function).
2. What is NVL()?
3. Display zero in place of NULL in a column in a table. The column is having integer datatype.
4. Display "N/A" in place of NULL in a column in a table. The column is having integer datatype.
5. Is it corect: select NVL(Commision,sysdate) from table; ?
6. What is NVL2()?
7. In the Emp table, select the dept which is having maximum number of employees?
8. Distinguish between TRUNCATE, DELETE, DROP?