Interview Ques

SQL 

User Exceptional Handling Categories Oraca.h sqlca.h

   

Joins – Natural + Minus, intersect, outer, left, right, union, cross. Outer join-subjoin. How to select the driving tables in the join? Deletion of duplicate rows. Delete from client_master where rowid not in (select min(rowid) from client_master group by client_no, name, bal_due);

    

Views. Error handling. Merge command. Fourth largest salary from a table (if duplicate). Top-N queries and In-line views. List of three lowest paid employees Select ename, job, sal, rownum from (select ename, job, sal from emp order by sal) where rownum <=3;

                   

How to retrieve nth max or min in a table column (5min). Snapshot error (ora-01555) Normalization (1st to 5th normal form) TRANSACTION in oracle. Savepoint, commit and rollback. Differences between Truncate or drop table. Where and having clause. Sub-queries. Difference between co-related sub-query and a query. Indexes (Btree/bitmap) (2 min). When to select B*Tree and bitmap indexes. Autonomous transactions. In 3rd normal form, is a non key column can depend on other non-key column. Different types of locks. When the lock gets released. What is an instance, session and schemas. What are objects? How to export data into flat files with syntax. What are external tables, how to use data of those tables. How to imp data from other schemas.

                 

How to identify the primary key. What is composite primary key. Rownum, rowid differences. How rownum will change, if its on a sorting field. What is the significance of rownum and row-id. What are loosely coupled tables. Syntax for creation of the schema. To select count of rows of all table in the database. What is data-warehousing and data-mining. How to optimise the sub-query and what are the designing guidelines. How many tables are there in the current module and what is the size of each entity. What are the difference between RDBMS and DBMS (I said normalization, Data Redundancy, Data inconsistency) What is Data Redundancy, Data inconsistency, and explain the Normalizations. Materialized Views / Snap Shots - Huge explanation is required DDL / DML / DCL Statements ...I forgot one more question.( What is a Transaction -----What is commit and rollback, Save point) - be prepare on this Oracle Inbuild functions...an overview....Sub Queries / Co-related sub queries differences Also prepare about Oracle Tables - Objective Types (Create Type as Table.....Like these) What are 9i Features CASE statement - Instead of Decode

PL/SQL         

  

What is PL/SQL table, how to implement these. How to pass arrays to functions and procedures. Referential cursor. Cursors. Differences bet ref and normal cursor. Cursor types. Different processes for opening a cursor. Procedures, functions, Independence statement (In/Out parameters). Procedure and Function Differences ( Function is used in the expression and procedure as a statement - Functions returns a value....If there is only one return value we will use function if they are number of return values we will use procedures using out parameter) What is function overloading and inbuild function overloading ( i said sum(ints) sum(floats)) Uses of packages and its details. What is the Packages and what are the uses of it ( Package usages 1. Function overloading, Modularization, Reusuablity of code, )

    

When to create a package and what is its significance. Triggers (Types and syntax). How many cascading triggers can be placed in oracle?--61 Before and after trigger differences. How many type of triggers - DML Triggers ( Insert, Update, Delete Before/ After , Statement/Row Level - Total 8 combinations) prepare it well. Instead of Triggers - Used for modification of Views (Defined on multiple tables) System Level Triggers/ DDL Triggers - At start up of instance or creation of table we can create a trigger

  

Where a global variable declared in order to use by all procedures and functions. Same question for packages. How to write case statements in PL/SQL and in query.

Optimisation  Some performance tuning of SQL.  What is optimizer.  EXPLAIN_PLAN table...and questions like that.  Explain PLAN significance.  Is explain PLAN cost based.  How query statistics can be retrieved?  Optimization techniques for queries.

PRO*C             

Prototype of Pro*C program. Makefile funda. Error handling in Pro*C.( WHENEVER, SQLCA). Precompiler options. Dynamic SQL. Details of project Role. Host/Indicator variables. Precompiler. Varchar in Pro*C(2-byte length field and a n-byte character array). SQLCA.H (What does it stand for) Pro*c - What is Indicator Variable...Host variable.....Result in indicator variable (0,-1, >1,-2) significance of these - I told wrongly) How to assign NULL to a column even if a host variable has some value? What is datatype equivalencing?

 

How to build a .exe in proc -- I said using makefile Makefile Contents.