Importent SQL Interview Question

  =========
    SQL:-
  =========

  To Find the Salary using SQL
  =================================
  select M,[Column_name],[Column_name] from(select rownum M,[Column_name],[Column_name] from(select rownum,[Column_name],[Column_name] from <Table Name> order by salary desc))where M=&k;

  select M,ENAME,SAL from(select rownum M,ENAME,SAL from(select rownum,ENAME,SAL from emp order by SAL desc))where M=&k;
 
  Select and Delete the duplicate rows
  ========================================
  delete from <Table Name> where rowid not in (select max(rowid) from <Table Name> group by [Column_name]);
 
  delete from emp where rowid not in(select max(rowid) from emp group by EMPNO)
 
  select * from emp where rowid not in(select max(rowid) from emp group by EMPNO)

  select the alternate row number in sql
  =========================================
 
  select * from emp where (rowid,0) in (select rowid,mod(rownum,2)from emp)
 
  select *from <Table Name> where (rowid,0) in (select rowid,mod(rownum,2) from <Table Name>);
 
 
    LAG
    ========
    The LAG function is used to access data from a previous row.

    LEAD
    ==========
    The LEAD function is used to return data from the next row.
  
  
    how to identify the Multi-Org
    ===============================
    select multi_org_flag from fnd_product_groups;
  
    select * from fnd_product_groups
  
    NUM ROWS avalabile in table
    ==============================
  
    select * from ALL_ALL_TABLES where upper(table_name) like '%XXX%';
  
    select * from ALL_ALL_TABLES where upper(table_name) like 'PO%';
  
    to find the version
    =======================
     select * from v$version;
  
    TO find the URL
    ======================
  
     SELECT home_url
   FROM icx_parameters
 
          Substr of Instr command
          ========================
        
                     select substr('Elangovan.R',1,9) from dual
                 
                     select instr('Elangovan.R','.',1) from dual
                 
                     select substr('Elangovan.R', 1, instr('Elangovan.R','.', 1)) from dual
                                 
                     select substr('Elangovan.R', 1, instr('Elangovan.R','.', 1)-1) from dual
                   
                   
        ============
          PL-SQL:-
        ============
      
        How to got profile value:-
      ===============================
    
        fnd_profile.VALUE ('USER_ID')
        fnd_profile.VALUE ('LOGIN_ID')
        fnd_global.conc_request_id
        fnd_profile.VALUE ('ORG_ID')
        fnd_profile.VALUE ('RESP_ID')
        fnd_profile.VALUE ('RESP_APPL_ID')
      
   
       EXCEPTION
      ==============
    
        NO_DATA_FOUND
        TOO_MANY_ROWS
        INVALID_CURSOR
        ZERO_DIVIDE
        DUP_VAL_ON_INDEX


     There are three types of exceptions
   =========================================
 
   1)    Pre-Defined
   2)    Non Pre-Defined
           Exception_name Exception;
           Pragma Exception_init (Exception_name,error_number)
   3)    User - defined
            e_invalid_department Exception
            Raise_application_error
 
            To write the log
         =======================
         FND_FILE.PUT_LINE
      
         DBMS_OUTPUT.PUT_LINE
       
         FND_REQUEST.SUBMIT_REQUEST
       
         FND_CONCURRENT.WAIT_FOR_REQUEST
       
         APEX_APPLICATION UTIL
         =======================
             APEX_UTIL.CLEAR
             APEX_MAIL
             APEX_ITEM
             APEX_CUSTOM
           
          To set org 11i and R12
        ===========================
      
          mo_global.set_policy_context ('S', pv_org_id);
        
          mo_global.init
        
        Cursors:-
      ============
      IMPLICIT CURSORS
      ===================
      These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed.\
    
      EXPLICIT CURSORS
      ===================
      They must be created when you are executing a SELECT statement that returns more than one row
   
     %FOUND, %NOTFOUND, %ROWCOUNT,%ISOPEN