add

About Me

My photo
Oracle Apps - Techno Functional consultant

Tuesday, February 26

SQL Queries



/* select the odd number of records */
1.select * from emp whererowid in ( select
   decode(mod(rownum,2),1,rowid)from emp);

/* select the even number of records */
2.select * from emp whererowid in ( select
   decode(mod(rownum,2),0,rowid)from emp);

/* delete a duplicate number of records */
3.A. delete fromemp where rowid notin(select max(rowid)from emp group by  ename)
   B.delete from emp ewhere rowid notin(select max(rowid)from emp where e.ename = ename)

/* nth row selection */
4.SELECT * FROM EMP WHEREROWNUM < &N+1
   MINUS
   SELECT* FROM EMP WHERE ROWNUM< &N

/* last nth row selection */
5.SELECT * FROM EMP
   MINUS
   SELECT* FROM EMP WHERE ROWNUM<= (SELECT COUNT(*)-&n FROM EMP)

/* first nth row selection */
6.SELECT * FROM EMP WHEREROWNUM <= &n

-------------------------------------

--1) The value of :system.record_status will be
     a)* insert b) update c)delete d) none


--2) Overlapping frames will be in
      a)tabular b) master-detail c)* matrix d) master-detail/matrix

--3) How to run a form without connecting to a database.
      * make nullin on-logon

--4) what will raise form trigger failure will do
      a)return ro calling form,menu b) * cease the trigger
      c)rollback all commits.

--5) can we give create_timer in on-validate record.
     *yes

--6) what will be the value of old and new in
   a)old is null and new is not null in insert ,update,delete trigger.
   b)old is not null and new is null in insert ,update,delete trigger.
   c)old is not null in insert,update.

     * oldand new is not null for update
     * oldis not null and new is null for delete
     * newis not null and old is null for insert


--7) if we change store procedure in store procedure
   a)compiling particular program inthe stored procedure.
   b)compiling stroe procedure in database isenough.
   c)compiling all procedures inform is enough.
    * bothcompiled and source stored indatabase
   
--8) how you will get check error code and message in pl/sql block
   a)sqlcode,sqlerrm
   b)errcode,errm   
     * sqlcode& sqlerrm

--9) how to disable/enable menu item based on user
     * creating arole and give persmisson

--10) select a from b group by a
     
        a         c

        1      z
        1         x
        2      z
        2         x
        3      z
        4         x

     how many rowwill be retreive

    a)2 b) 4 c)3
      * 4

--11) which trigger will get fire first before insert , before insert for each row.
      * statementlevel first
      * rowlevel statement

--12) can we give tcl statement in a trigger
      * no
     
--13) what is the purpose of optimizer hint.
      * tochoose the effiecient way toexecute a sql statement

--14) which will be necessary for a pl/sql block   

    a)begin/end b) declare/begin/end c)none
      * begin/end

--15) what is the purpose of multi layout
      * toget out put indifferent form

--16) what is the purpose of format trigger.
      *

--17) when you connect to non-oracle database how will you
    know how many records processed
    a)on-fetch b)on-query c)post-count
      * on-fetch

--18) while running a store procedure
    a)p-code,source code will be indatabase
    b)p-code will come intoram
    c)source code will come intomemory.
      * p-code,sourcecode will be in database

--19) select count(*) into :a from emp;
    ifthere is no record what will be thevalue of a.

   a)a=0  b)a=null  c)a=junk value
      * a=0

--20) when rows are found what the cursor will do.


--21) select to_date(''22-oct-99'') from dual;
    * it will work

--22) select replace(to_char(''10-22-99'',''dd/mon/yy''),''/'',''-'') from dual;
    a)22/oct/99
    b)22-oct-99
        * 22-oct-99
   
--23) seelct instr(date,1,1) from dual.
--    if date will be 01/01/99,10/01/99,21/01/99,20/03/10

    a)2-1-2-7
    b)2-1-2-1
    c)1-2-1-2
      *     2-1-2-7

--24)  record group will create
    a)a structure informs.
    b)a two-dimensional array
    c)a three dimensional array
      * two-dimensional array

--25) why enter-query mode in forms
    a)for default whereclase
    b)to delimit the user.

--26) when a row will be locked.

--27) when button pressed will be create in
    a)form level b)bloc c) record d)item
     * form,block,item

--28) @ is used to
    a)execute b) db-link
     * both

--29) result set is in sub query or co-related sub query
     * sub -query

--30) table em is table of

    thesyntax will be in

     a)pl/sql table b)pl/sql table type

     * pl/sqltable type

--31) what is ref cursor
     * cursorvariable

--32) what is the exact usage of in/out parameters.
     * infor read only
     * outfor write only   

--33) differnece betweenpre-defiend constraints and triggers
     * constarints validatefor exsiting data
     * triggersdoes not validate existiong data    

--34) display_item(:block.item)
     * changethe visual attribute.


--35) to display dbms_output.put_line what you will set
       * setserveroutput on

--36) to copy a value from library to block

   a)name_in(:block.itemname)
   b)copy(''block.itemname'',var)
   c)copy(var,''block.itemname'')

    *copy(var,''block.itemname'')
  
    
--37) what are background process are mandatory
    * dbwr ,lgwr, pmomn,smon

--38) what is subtype
    * subtypeis a user-defined pl/sqltype

--39) in which table audit_trial will strore   
    * user_audit_trail

--40) what are all the pseudo column
    * sysdate,currval,nxtval,rowid,rownum,level

--41) what is fastest way to execute a query
    * rowid

--42) select date-date from dual what will be the ouput
    1)no of days 2)date
    * noof days

--43) select to_char(sysdate,''W'') from dual
    * it will diaplay theweek in a month

--44) select * from emp,dept;
    emp=10dept=20
    
--45) in a function declartion returns number and in defintion if you return boolean
    * it will showerror

--46) select * from global_name.

    * it will displays databasename.

--47) create trigger trigger_name before insert on emp for each row
    whereempno=10
    begin
    end;
     a)will it ececute

--48) select e.*,rowid from emp e;
    * it will display allrows , rowid inemp

--49) what is size of char,number
    * 1,38

--50) what is the characteristic of modal window.
    * dialog window