Tuesday, February 26, 2013

SQL Queries



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

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

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

/* nth row selection */
4. SELECT * FROM EMP WHERE ROWNUM < &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 WHERE ROWNUM <= &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 null in 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.

     * old and new is not null for update
     * old is not null and new is null for delete
     * new is not null and old is null for insert


--7) if we change store procedure in store procedure
   a) compiling particular program in the stored procedure.
   b) compiling stroe procedure in database is enough.
   c) compiling all procedures in form is enough.
    * both compiled and source stored in database
   
--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 a role 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 row will be retreive

    a) 2 b) 4 c) 3
      * 4

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

--12) can we give tcl statement in a trigger
      * no
     
--13) what is the purpose of optimizer hint.
      * to choose the effiecient way to execute 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
      * to get out put in different 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 in database
    b) p-code will come into ram
    c) source code will come into memory.
      * p-code,source code will be in database

--19) select count(*) into :a from emp;
    if there is no record what will be the value 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 in forms.
    b) a two-dimensional array
    c) a three dimensional array
      * two-dimensional array

--25) why enter-query mode in forms
    a) for default where clase
    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

    the syntax will be in

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

     * pl/sql table type

--31) what is ref cursor
     * cursor variable

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

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

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


--35) to display dbms_output.put_line what you will set
       * set serveroutput 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
    * subtype is a user-defined pl/sql type

--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
    * no of days

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

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

--46) select * from global_name.

    * it will displays database name.

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

--48) select e.*,rowid from emp e;
    * it will display all rows , rowid in emp

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

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect