What is difference between SUBSTR and INSTR ?
SUBSTR returns a specified portion of a string eg SUBSTR('BCDEF',4) output BCDE
INSTR provides character position in which a pattern is found in a string.
eg INSTR('ABC-DC-F','-',2) output 7 (2
nd
occurence of '-')
There is a string '120000 12 0 .125' ,how you will find the position of the decimal place ?
INSTR('120000 12 0 .125',1,'.')
output 13
There is a '%' sign in one field of a column. What will be the query to find it ?
'\' Should be used before '%'.
When you use WHERE clause and when you use HAVING clause ?
HAVING
clause is used when you want to specify a condition for a group function and it is
written after GROUP BY clause
The WHERE clause is used when you want to specify a condition for columns, single row
functions except group functions and it is written before GROUP BY clause if it is used.
Which is more faster - IN or EXISTS ?
EXISTS is more faster than IN because EXISTS returns a Boolean value whereas IN
returns a value.
What is a OUTER JOIN ?
Outer Join--Its a join condition used where you can query all the rows of one of the tables
in the join condition even though they don't satisfy the join condition.
What is a pseudo column. Give some examples ?
It is a column that is not an actual column in the table.
eg USER, UID, SYSDATE, ROWNUM, ROWID, NULL, AND LEVEL.
Suppose customer table is there having different columns like customer no, payments.What
will be the query to select top three max payments ?
SELECT
customer_no, payments from customer C1 WHERE 3<=(SELECT COUNT(*) from
customer C2 WHERE C1.payment <= C2.payment)
What is the purpose of a cluster ?
Oracle does not allow a user to specifically locate tables, since that is a part of the function
of the RDBMS. However, for the purpose of increasing performance, oracle allows a developer
to create a CLUSTER. A CLUSTER provides a means for storing data from different tables
together for faster retrieval than if the table placement were left to the RDBMS.
What is a cursor ?
Oracle uses work area to execute SQL statements and store processing information PL/SQL
construct called a cursor lets you name a work area and access its stored information A cursor is a
mechanism used to fetch more than one row in a PL/SQL block.
Difference between an implicit & an explicit cursor ?
PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including
quries that return only one row. However,queries that return more than one row you must declare
an explicit cursor or use a cursor FOR loop.
|
|
