/* 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 b
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) select 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
------------------------------------
1. How will u check the sysdate against one date field in the table.
(ie u can use the CHECK CONSTRAINT when u create table.)
But SYSDATE is not used for CHECK CONSTRAINT.
So we have to create a database trigger for checkig the values agaionst SYSDATE.
2. How will u select all the employee information those who are working in the
department in which the employee "XXXXX" is working.
select * from emp
where deptno IN (select deptno from emp where ename = 'XXXXX');
3. Whate are all the cursor attributes.
Found, Notfound, Isopen and Rowcount.
4. Howmany groubs and queries for creating MATRIX REPORTS.
ONE - query and FOUR groubs.
5. What is the Sequence of Report triggers.
---------------------------------------
1. explain INDEX How it works internally.
How to disable INDEX for particular query.
If u try to disabele the index as a whole by using the DIASABLE keyword It
would't work for any queries. But if u want to for a particular queries U can
use any function like upper or any functions.
2. explain about NORMALIZATION.
Normalization is the technique BY which we can reduce the redundancy of data
residing in the table.
They are
First normal form
Second '' ''
Third '' ''
3. explain TEMPORARY WORK AREA. how to create a TEMPORARY WORK AREA.
Two factors to be consider for creating TEMPORARY WORK AREA.
5. What is meant by DATAFILE.
datafile is the physical structure and it is used to store the original data in
the database.
6. What is meant by LOGFILE.
Logfile is the physical structure and it is used to store the Changes made to
the Database.
7. what is meant by Control file.
Control file will be storing the information about datafile and ligfile like
the time at which they have been created , size of the both , physical
location and name of the data and logfiles.
8. What is stored procedure.
Stored Procedure is the Database Procedure Which is stored In the datadbase
Which is reducing
the networking traffic.Because the procedure is allready stored in the
dsatabase. If we have the pl/sql procedure as a front end programme . The ity
has to send to database than it will be executing then pass the recrds to the
front end. That is the reason database procedure is better performance.
9. Oracle 8 features.
Objects
Nested tables.
Partitions.
Varray.
New Dtatypes like blob,clob,bfile.
10. explain about partitions.
Partitions is the tecnique by which we can split the contens of the tables in
to
named locations(partitions) in to different tablespaces. Due to this we can
avoid the data loss during the disc curreptions and while we queriying we
save the time.
11. When u are moving the application from forms30 to froms4.5 , the database
performance will be getting reduced. How this can be eliminated when you are
moving the application from forms30 to froms4.5
1.Mid row
****************
SELECT * FROM EMP WHERE ROWNUM <
(SELECT DECODE(MOD(MAX(ROWNUM),2),0,MAX(ROWNUM)/2+2,
,1,MAX(ROWNUM)/2+1) FROM EMP)
MINUS
(SELECT * FROM EMP WHERE ROWNUM <
(SELECT MAX(ROWNUM)/2 FROM EMP))
2. Top Nth row
***************
SELECT *
FROM emp1 a
WHERE 6 >= (SELECT COUNT(DISTINCT empno)
FROM emp1 b
WHERE b.empno >= a.empno)
ORDER BY empno desc
3. No to Word
**************
select to_char(to_date('&n','j'),'jsp') from dual
4. Del Dup Rows
****************
delete from emp x where rowid > (select min(rowid) from emp
where emp.empno = x.empno)
5. Alternate Rows
*****************
select * from emp where rowid in (select decode(mod(rownum,2),1,rowid) from
emp)
6. Nth Max
**********
select sal from emp x
where &n= (select count(distinct(sal)) from emp where x.sal <= emp.sal)
7. 3rd Max
**********
select sal from emp
where sal >= (select max(sal) from emp where sal < (select max(sal) from emp))
8.Cumulative addition
**********************
DECLARE
D1 DATE;
TSAL NUMBER(8,2);
BEGIN
D1 := '&DATE';
SELECT SUM(SAL) INTO TSAL FROM A WHERE HIREDATE >=
TRUNC(to_date(TO_CHAR(ADD_MONTHS(d1,-2),'DD-MON-YY')),'MM') AND HIREDATE <= D1 ;
DBMS_OUTPUT.PUT_LINE(TSAL);
END;
(OR)
DECLARE
d1 date;
tsal number(5,2);
BEGIN
D1 := '&DATE';
SELECT SUM(amt) INTO TSAL FROM damt WHERE dt >=
TRUNC(to_date(TO_CHAR(ADD_MONTHS(d1,-2),'DD-MON-YY')),'MM') AND dt <= D1;
DBMS_OUTPUT.PUT_LINE(TSAL);
END;
/
(or)
declare
dt1 date;
tamt number(5,2);
begin
dt1 := '&date';
SELECT SUM(AMT) into tamt FROM DAMT
WHERE
round((months_between(to_date('&dt1','dd-mon-yy'),to_date(dt,'dd-mon-yy'))))
between 0 and 2
and dt <= dt1;
dbms_output.put_line(tamt);
end;
/
(or)
select sum(amt) from damt where sign(trunc(months_between(dt,'&dt1'))) between -1
and 0
and dt <= '&dt1'
/
----
select * from (select * from (select * from dept where deptno = 10))
select decode(deptno,10,loc,
dname) from dept
9) Cumulative sal
*****************
select a.sal,sum(b.sal) from emp a,emp b
where b.rowid <= a.rowid group by a.rowid, a.sal
10) Index
************
create or replace PROCEDURE create_index
(index_in IN VARCHAR2,
tab_in IN VARCHAR2, col_in IN VARCHAR2)
IS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
DDL_statement VARCHAR2(200)
:= 'CREATE INDEX ' || index_in ||
' ON ' || tab_in ||
' ( ' || col_in || ')';
BEGIN
PLV.assert (INSTR (col_in, ':') = 0, 'No bind variables');
DBMS_SQL.PARSE
(cur, DDL_statement, DBMS_SQL.V7);
END;
/
11) Max sal without using MAX fn
********************************
select sal from emp where sal >= all(select sal from emp)
/
12)Triangle
***********
select rpad(lpad(lpad('*',2*rownum,' *'),(20-2*rownum)/2+2*rownum,' '),20,' ')
from tab where rownum <= 10
/
