ViTech

• Which statement is fast? Commit or Rollback
• RETURN statement is mandatory in a function?
• Difference b/w NVL and NVL2
• How to add a new parameter to a procedure without affecting existing calls
• Can we call a function which has DML in SELECT 
• Write a query to display how many days are there for his/her next birthday 
• For vs For All statements ?
• A select statement retrives 2 million records and took 10 minutes time. Now I executed delete and Commit. Again I executed select * form table, result is no rows selected. what will be the time taken for the second select satement?
• Emp table has 1 million records, Dept table has 4 rows. Now I wrote normal join query. How many hits will go ?
• What is the best practice to get Nth highest salary 

Query to get days for Next B'day :
SELECT HIREDATE,
, TRUNC(
case 
    when   
          TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate))) -sysdate > 0 
      then    TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate))) -sysdate  
    when
         TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate))) -sysdate  < 0 
      then    
         TO_DATE (SUBSTR(HIREDATE,1,7)|| TO_CHAR(extract(year from sysdate)+1)) -sysdate 
  END ) AS   DAYSREMAINFOR_ANNIVERSARY
FROM EMP;