1. What is the diff between Exist and In Operator?
· Exists return Boolean and in return values.
2. What is the Diff between Delete and Truncate and Drop?
· delete –dml explicit commit required,
· truncate- ddl implicit commit,
· drop –ddl drop db objects permanently from db.
· Delete-we can use in where clause
3. Can we drop the table if table is having the data?
· Yes
4. I have executed the Delete command after that I have created table wether deletions will be commit or not?if table is success fully created?
· Not commit
5. I have executed the Delete command after the I have created table weather deletions will be committed or not? if table is not created?
· Error table or view does not exists
6. What is RowID ? When it will be created? What is the format? What is the diff between RowID and Rownum?
· It is pseudocol associated to each row of table,18 charcters long hexadecimal,rowid is associated when each row is created while rownum is attached to each row when it is retrieved from db in a query.
7. Can we use RowID in the where clause ? if Yes can we use like follows
· Yes
8. Where rowid = 12;
· No. We have to use hexadecimal format.
9. What is Decode operator and what is the syntax when u have used in Oracle Apps?
· it is a fn used in sql for if-else-then as in procedural language ,compares expression value by value and if matches return corresponding result else return default and if default is omitted return null
· syntax: decode(gender,’m’,1,’f’,1,0);
10.What is NVL operator and what is the syntax when u have used in Oracle Apps?
· it is function used to deal with null values. it return value if found col value null else return col value
· syntax: nvl(commission,0);
11.What is Join and types of Joins and what is Outer Join?
· It is query that combines rows from 2 or more tables.
o Types: equi, nonequi, self, outer join
· Outer join is a join which extract all the rows from one table and matching rows from other table based on the outer join operator(+)
12.What is Set operator and what are the types what is the diff between Set operators and Joins?
· These operator are used to join the o/p of 2 queries.
o types: union, union all, intersect, minus.
· Difference - Set operator require same structure of both queries while join need not require this
13.What is View? What is the advantage? What are the types of views we have?
· It is also a db objects .it is virtual table that is not having data of its own.
· Easy retrieval of the data from database
· Types: updatable, not updateable (or) simple ,complex
14.When we can not update the view?
· When it contains joins, set operators, rownum, group by ,having
15.What is Materialized view and what is Snap shot?
· It is replicas of data from remote database, local copy of remote table, used for replication, distributing, computing. Used in large data like db warehousing.
· It is a view which is associated with a query having any no of joins and any no of group functions
· We can’t perform DML operations in Materialized views
· Snap shot is used for increase the performance by reducing network traffic by reducing i/o
· In older version of oracle is called as snapshot
· It is used for replication of data between distributed databases.
16.What is the diff between Materialized view and Snap shot?
· No difference
17. What are the Inline views?
· Sub query defined in from clause of select statement.
· In place of table name in from clause of select statement, we write sub query that is inline view.
· It is also a virtual table.
18.What is synonym and what are the types and what is the advantage?
· Synonym is an alternate name for a table, view.
· Two types – public, private
19.What is Index? Advantage of Index? Types of Indexes?
- Create indexes if u frequently want to retrieve less than 15% of the rows in a large table
- index columns used for joins to improve the performance on joins.
- Don’t use indexes on tables having less no rows.
- If the column has non-unique values u can use indexes on these columns.
- Don’t use indexes if the data type is lob, CLOB & BLOB.
- If the table is read only we can create more indexes on the table.
- We can't create indexes on views.
- Indexes are logically & physically independent of data
- If the index is developed all applications continue to function
· Index is an ordered list of contents of a column or group of columns in a table. Index created on a single table Simple Index and which is created on multiple tables is called Composite Index.
20.What is the Bit mapped Index advantage?
· It is used for low coordination number
21.What is sub-query and what is Co-Related Sub Query and diff between those two?
· Sub Query is select statement which is embedded with the other select statement and which executes once before the main query execution.
· Co Related sub query is for row by row processing, in this first outer query will be executed once based on that iner query will be executed for each row.
22.What is the syntax for To Date function?
· To_Date (DD-MON-RR)
23.What is diff between Replace and Translate?
· Replace is to replace character by character in a string
· Where as Translate is used for to replace string
24.How to execute DOS Commands from SQL Prompt?
· HOST
25.How can we find out no of Indexes we have create against a Table?
· USER_INDEXES
26.How to delete duplicate rows from the table?
DELETE FROM EMP A
WHERE ROWID>(SELECT MIN(ROWID)
FROM EMP B
WHERE A.EMPNO=B.EMPNO)
27.What is where clause?
· Conditional Clause
28.What is having clause and what is the diff between these two clauses?
· Having Clause is used with Group Functions
· Where Clause is used for simple conditions
29.How to handle the table level locks and column level locks?
· Shared/exclusive -When 2 transaction wants to read/write from db at the same time.
· Table level, row level
· Row Exclusive locks are obtained when updating, inserting or deleting rows
· Duration of locks
· All locks acquired during a transaction are released when the transaction is committed
· All locks are acquired during a transaction are released when the transaction is rolled back
· All locks are acquired after a savepoint are released when the transaction is rolled back to the savepoint
· Dead lock-It is will come in multi-user environment
· When the two users issues update statements then dead lock will come
· Dead- 1trans updates EMP and dep
· 2 trans update dep and EMP
· Dead locks are released when commit/rollback statements are issued or logging off
30.What are the constraints we have and how to declare?
· Which enforces the rules at table level
· Primary Key
· Foreign Key
31.What is the diff between primary key and Unique?
· Primary is a combination of unique
· Table can have only one Primary Key
· Table can have number of Unique Keys
32.Can we compare two Null values? Can we insert more than one null value in the unique column?
· No, Yes
33.What are CBO and RBO? What is the diff between these two?
· Cost Based optimization
· Role Based optimization
34.What are the performing tuning levels?
35.What is Explain plan and what is TKPROFF?
36.How can u tune the query?
- EXPLAN_PLAN
37. How to display odd rows
SELECT ROWNUM,ENAME FROM EMP
GROUP BY ROWNUM,ENAME
HAVING MOD(ROWNUM,2)=1
PL/SQL
1. What is Exception? Types of Exceptions? Exception Propagations?
· Exception is a warning or error condition in oracle
o User defined
o Pre-defined
o Non pre-defined
2. What is RAISE_APPLICATION_ERROR? And what is PRAGMA EXCEPTION_ INIT?
· It is user defined exception,
· it is used to write the our own error message with oracle error number
3. What are the Predefined exceptions we have?
Predefined exception is nothing but already defined by Oracle,
4. When we SELECT statement in the Execution block what are the Predefined exceptions we have to use in the Exception Block?
NO_DATA_FOUND
5. What is CURSOR? What are the Cursor types? What are cursor declaration steps?
· Cursor is nothing but a private SQL work area which is used to store process information.
· Types – Implicit – Explicit
6. What is the diff between Implicit and Explicit and Ref Cursor?
· Implicit – It is defined by the Oracle Server for queries that return only one row.
· Explicit – Which is defined by the Users, for queries that return more than one row
· Ref – With this we can change the select statement dynamically.
Without closing we can again open the cursor.
7. In which program u has used the Cursor? One example for implicit cursor?
8. What are the Cursor attributes we have?
· %OPEN
· %NOT FOUND
· %FOUND
· %ROWCOUNT
9. Can we issue the commit inside of the Cursor For Loop?
· Yes
10.What is For Update OF Clause in the Cursors?
· Is used in cursor to lock the rows first before u performs DML operations.
11.What is Procedure and what is Function?
· Procedure: Is used perform an action
· Function: Is used to compute a value
12.What is the RND and WND?
RND – Read No Database
WND – Write No Database
13.What is the diff between Procedure and Function?
· Function must return at least one value, where procedure may or may not return value.
14.If we drop the table which we have used in the procedure do we need to recompile the procedure then how?
Yes, we need to recompile the procedure by using below command
- ALTER PROCEDURE COMPILE
15.How to get the Procedure Source code from database?
SELECT * FROM USER_SOURCE - Text is the column name
16.What is the Advantage of Package?
· We can declare the global variables
· Other users can’t see the package body or logic what it contains.
17.What are the other objects we can group inside of Package?
Procedures
Functions
18.When we will go for creation of Package? Can we create Package body without creation of Package Specification?
· NO
19.Can we declare Procedure directly in the package body without declaring in the package specification?
· Yes
20.What is Trigger?
· Trigger is a PL/SQL block when ever an event occurs it will fire.
21.Can we commit inside of trigger? How to delete the Trigger? How many triggers we can use maximum?
· No - You can’t Commit Inside a Trigger
· Drop Trigger
· 12
22.What are The DML Triggers? What is diff between Row and Statement level trigger?
· Insert, Update, Delete – DML Trigger
· Row level - For every row, when row doesn’t exists it will not fire
· Statement level – only once
23.How to Debug PL/SQL Code?
· DBMS_OUTPUT Package
24.What are the Trigger Predicates we have?
· :OLD :NEW
25.What is diff between Trigger and Procedure?
· It is action based
· When ever we need we can call
26.What is Package Variable what is advantage?
· The variable which are declared in the Package Specifications
o We can use these variables any where
27.What is PL/SQL Table what is the advantage of that when we will use PL/SQL Table?
· PL/SQL Table is nothing but a INDEX by table
28.What is Global temporary table what diff between PL/SQL table and Temporary table?
·
29.What is %ROWTYPE?
· It is a record type to hold the different type of data types of the particular table.
30.What is mutating trigger? How can you handle this?
· If Procedure contains any DML operations on the table again we will using the same table for same operations, it will lock the table
· By using Pragma Autonomous Transaction
31.What are PL/SQL table attributes?
· %ROWTYPE
· %TYPE
Reports6i:
1. What is Frame, Repeating frame and Anchor Objects?
· Frames are used to surround other objects and protect them from being overwritten or pushed by other objects.
· Repeating frames surround all of the fields that are created for a group’s columns.
· Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent.
2. What are SYSTEM Parameters?
· Background, copies, currency, decimal, desformat, desname, destype, mode, orientation, printjob, thousands.
3. What are Bind Parameter and Lexical Parameter and what is the diff?
· Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date.
· Lexical references are placeholders for text that you embed in a SELECT statement.
4. When we will use Lexical Parameters?
· You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
5. Where we will handle the Lexical parameters?
· Data Model – SQL Window
6. Tell me where u has used Lexical parameters in u r exp?
7. What are the report triggers?
· Before parameter form
· After parameter form
· Before report
· Between pages
· After report .
8. What is the sequence of triggers firing?
· Before parameter form
· After parameter form
9. What is the diff between After Parameter Form and Before Report Trigger?
10. What are Format and validation and action triggers?
- A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes (font and color) of the object.
- Validation triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form.
- Action triggers are PL/SQL procedures executed when a button is selected in the Runtime Preview. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.
11. How to call another report from report?
· By using SRW.RUN_REPORT (command_line CHAR) built in
12. What if confine mode and Flex Mode?
· When confine mode is on u cannot move the child object from its parent,
· When flex mode is on u can resize the child object according to its parent.
13. What are SRW Packages we have?
SRW.DO_SQL (DDL &DML STMT),
SRW.RUN_REPORT,
14. What is User Exit?
· User exit is used to transfer the control from report builder to 3gl program and vice versa.
15. What are Formula, Summary, and Placeholder column? Difference?
· A formula column performs a user-defined computation on another column(s) data
· A summary column performs group functions like avg(),sum(),max() on the fields and returns a single value.
· A Place holder column is used to declare Global variables in a report
16. What is the diff between Data link and Group by?
· Data links relate the results of multiple queries.
17. What is the purpose of Between Pages trigger?
· The Between Pages trigger fires before each page of the report is formatted, except the very first page.
18. If out put is 10 pages how many times between pages trigger will be
Fired? If cursors go from last page to first page weather the trigger
will be created?
9 times - 0 time
No comments:
Post a Comment