Basics of Oracle/SQL
- Features of ORACLE 11G
- Difference between ROWNUM and ROWID?
- How to identify Columns having all NULL values?
- What are Sub-Queries and Co-Related Sub-Queries in ORACLE?
- What is the Difference betwenn CASE and DECODE?
- Order of execution of where,having,order by,select etc.
- SQL Functions
- Difference between Analytical and Aggregate Function
- ANALYTICAL Vs. AGGREGATE Function Examples
- Remove Duplicate Records From Table
- Find Highest/Mimimun Salary of Employee
- Differences between DELETE, TRUNCATE and DROP?
- Count() Function
- Difference between IN and EXISTS operator
- Difference between NOT IN and NOT EXISTS operator
- Separate Numeric/Non-Numeric/Date Values from a Column
- Difference between UNION and UNION ALL
- Differences between CHAR,NCHAR,VARCHAR,VARCHAR2 & NVARCHAR
- Why Insert is Faster than DELETE?
- When-rowid-changes?
- What is the Difference betwenn ROWNUM and ROW_NUMBER()?
- How WITH Clause helps in Simplifying Complex SQLs?
- What is the difference between %TYPE and %ROWTYPE?
- Difference between PRIMARY and UNIQUE key
- WHERE/HAVING Alternatives
- Pass COMMA Separated Values to IN Operator
- Change Column Values to COMMA Separated Values
- RETURNING TABLE From a FUNCTION
- Comparing Two Table Structure(Columns/Data Types)
- OLAP Vs OLTP
- Handle Exception While Processing MERGE Statement
Normalization Techniques
Indexes in ORACLE
- What are Oracle indexes and its types?
- What are the Advantages/Disadvantages of Indexes?
- What are clustered and non clustered indexes?
- What are Global and Local indexes?
- How indexes stored in database and improve query performance?
Joins in ORACLE
- Joins in oracle and their types
- Cross Join
- When CROSS JOIN Will Be Useful?
- Inner Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
Synonyms in ORACLE
Views in Oracle
- What are views and its types in Oracle?
- What is difference between Oracle Table and Materialized view?
Functions, Stored Procedure & Packages
- What is difference between Functions and Stored procedure?
- Passing parameters in stored procedures in Oracle(IN,OUT,IN-OUT).
- What are oracle Packages?
- Advantages of Packages
- Disadvantages of Packages
- Overloading Packages, Functions & Procedures
Oracle Cursors
- What are cursors and its types?
- What are parameterized cursors?
- What is ref cursors?
- What are different ways of opening parameterized cursor?
- How cursor works internally?
- HANDLING Exceptions Inside CURSOR LOOP
- Returning REF CURSOR From a PROCEDURE
- SYS_REFCURSOR Vs. REF CURSOR
REGULAR Expressions
ORACLE PIVOT/UNPIVOT
Exception Handling in Oracle
- What is Exception Handling?
- Types of exceptions
- What are various exception trapping functions?
- How can we define custom error messages in oracle?
- HANDLING Exceptions Inside CURSOR LOOP
- DML Error Logging(INSERT/UPDATE/DELETE/MERGE)
Oracle Triggers
- What are triggers?
- Advantages of triggers
- Types of triggers
- Triggering events
- Trigger execution hierarchy
- Instead of Trigger
- COMMIT inside Trigger and PRAGMA AUTONOMOUS_TRANSACTION
- RESTRICT DROP/TRUNCATE on TABLE
Global Temporary Tables
Oracle Collections
- What is Oracle Collections?
- What are different types of collections?
- What are different types of collection methods?
- What are different types of collection exceptions?
- Few examples of collections and its methods.
- How do you decide which collection type to use?
- How to handle COLLECTION having NULL Values
- RETURNING Record Set From a FUNCTION
- Equality Test of Two COLLECTION Types
- Compare and Merge Collections
- Prevent a VALUE_ERROR Exception
NESTED TABLE Functions
Bulk operations in Oracle
- What is Bulk collect and bulk bind, How it helps in improving performance?
- How to handle bulk exceptions?
- Bulk collect with native dynamic SQL.
- How Bulk collect is used with collections of records?
- Rollback behavior with/without exception handler.
Locking in Oracle
- What is table locking and its types?
- What are possible causes of lock conflicts?
- What are different locking modes in Oracle?
- Pessimistic and Optimistic Oracle Locking
- Handle CONCURRENT Updates
Mutating Table error in Oracle
Kill Oracle Session
Dynamic where clause
Continue & Continue when
Data Migration
ORACLE SQL* Loader
- How SQL Loader helps in loading data from External Files to DB?
- How to upload file using SQL Loader?
- How to insert(APPEND) additional records through SQL Loader?
- Data inside the CONTROL file itself using BEGINDATA
- Handle Date format and different Delimiters
- Fixed Length Data Upload using SQL Loader
- Change the Data during upload
- Load Data from Multiple Files
- Load Data into Multiple Tables
- Handling rejected(bad) records in SQL Loader
- Load specific rows from a Datafile
- Skip Column when using SQL Loader
- Skip header records while Loading
- Load Images/Sound Clips and Documents using SQL* Loader
- Optimize ORACLE SQL Loader Performance
ORACLE UTL_FILE PACKAGE
Import/Export
EXTERNAL Table
DBMS_PROFILER
ORACLE SQL Execution Plan
ORACLE TABLE PARTITIONING
SQL Tuning
- Explain basic tips for SQL Tuning?
- ORACLE Driving Tables
- How OPTIMIZER works in ORACLE?
- What are Different OPTIMIZER Modes available in ORACLE?
- CURSOR_SHARING in ORACLE
- HASH Join and NESTED Loop Join
- INDEX Usage with LIKE Operator and DOMAIN INDEX
- DYNAMIC_SAMPLING and its Impact on OPTIMIZER
- NOT NULL and INDEXED Column
- DETERMINISTIC Functions To Improve Performance
