select distinct sal from emp x where &n =(select count(distinct sal) from emp y
where x.sal <= y.sal)
2.Select n max salaries from emp table.
select distinct sal from emp x where &n >=(select count(distinct sal) from emp y
where x.sal <= y.sal)
3.To find the count of employees getting salaries > 1000 <1999 and so on...
select count(*),sal-mod(sal,1000) from emp group by sal-mod(sal,1000)
4.Display all the column along with rowid without specifying any col names
select rowid,emp.* from emp
5.To display the remarks as Minimum and Maximum for the respective
salaries
Select Sal, Decode(sal, (select max(Sal) From emp), 'Max',
(select min(Sal) From emp), 'Min' , '') Remarks
From emp
6.To print the character ****** at every 5th row reterived from the table
select empno,ename,decode(mod(rownum,5),0,'*******',' ') Remark from emp
7.To convert the salary into words.
select to_char(to_date(substr(to_char(sal),1),'j'),'jsp') from emp;
8.select the nth row from emp;
select * from emp where rowid = ( select max(rowid) from emp where rownum <= &n);
Select Rownum,empno,Ename from emp
group by rownum,Empno,Ename
having rownum =&n
9.Find the second Max salary for each department.
select deptno,max(sal) from emp where sal in
(select sal from emp where sal not in
(select max(sal) from emp group by deptno)) group by deptno
10.find out the n'th max salary for each department.
select distinct sal,deptno from emp y where &n in
(select count(distinct(sal)) from emp x where x.sal >= y.sal and x.deptno = y.deptno
group by deptno);
10.a. To find the Maximum salary for each department without using group by function
TABLE :- using above EMP1 table
Ans :
select distinct deptno,sal from emp1 a
where a.sal = (select max(sal) from emp1 where a.deptno= deptno)
/
11. To display male if he is a male otherwise female in the programmer table.
select prof1,sum(decode(sex,'M',1,0)) MALE,sum(decode(sex,'F',1,0)) FEMALE from programmer group by prof1
12.give the hierarchical order of emp table; (pg.no. 276)
13.To select last n rows from the table .
select * from emp where rowid >
(select max(rowid) from emp where rownum <=
(select count(*) - &n from emp))
Select Rownum,Empno,ename from emp
minus
select Rownum,Empno,ename from emp
where rownum <= (select count(*) - &n from emp)
14.To display the less than 5000 if the salary is less than 5000,else equal to , else greater than 5000 .
Select Sal, 'Equal to 5000' Remarks From emp
Where Sal = 5000
Union all
Select Sal,'Less than 5000' Remarks From emp
Where Sal < 5000
Union all
Select Sal,'Greater than 5000' Remarks From emp
Where Sal > 5000
15.select to_char(to_date(num1,'j'),'jsp') from num;
julian date must be between 1 and 5373484
16.To display employee details from the emp table for the given department from the dept table. (Hint: Using Cursor).
select d.deptno,loc,cursor(select empno,ename from emp e
where e.deptno = d.deptno) from dept d where d.deptno = &dept
17. select d.deptno,loc,cursor(select job,sum(sal) from emp e where
e.deptno = d.deptno group by job) from dept d where d.deptno = &dept
18.select * from all_users
where username like '&1'
19.To select employee details who joined between 6 months before the sysdate and sysdate
select * from emp
where hiredate < sysdate and hiredate >add_months(hiredate, -6);
20. select departments having more than 2 salespersons :
select d.dname from dept d where d.deptno = (select e.deptno from emp e where e.job ='SALESMAN' GROUP BY e.deptno having count(*) >= 2); (Correlated sub query)
select a.Dname from emp b, dept a
where
a.Deptno = b.Deptno and
b.job like 'SAL%'
Group by a.Dname
having Count(b.Job) > 2;
21.deptwise manager & salesperson
(correlated sub query)
select dname, sum(decode(job,'MANAGER ',1,0)) MANAGER,
sum(decode(job,'SALESMAN ',1,0)) SALESPERSON
from emp, dept where dept.deptno = emp.deptno
group by dname having sum(decode(job,'MANAGER ',1,'SALESMAN ',1,0)) > 0;
22. To delete duplicate rows
Method 1:
SQL> Delete from table_name where rowid not in(
select max(rowid) from table_name group by key_values);
delete from emp where rowid not in
(select max(rowid) from emp group by sal)
Method 2:
SQL> DELETE FROM table_name A WHERE ROWID > (
SELECT min(rowid) FROM table_name B
WHERE A.key_values = B.key_values);
Method 3:
Delete From emp a
where a.Rowid not in (select Max(b.rowid) from emp b
where a.sal = b.sal and a.rowid > b.rowid);
23. How does one select every n th row from a table ?
Method 1: Using a subquery
SELECT *
FROM emp
WHERE (ROWID,0) IN (SELECT ROWID, MOD(ROWNUM,&n)
FROM emp);
In this Query this where condition checks whether rowid = rowid and 0=mod(rownum,&n)
Any number of columns can be given like this.
Method 2: Use dynamic views (available from Oracle7.2):
SELECT *
FROM ( SELECT rownum rn, empno, ename
FROM emp
) temp
WHERE MOD(temp.ROWNUM,4) = 0;
24. Order by in Sub Query
SELECT * FROM
(SELECT empno FROM emp ORDER BY empno)
WHERE ROWNUM < 11;
25. Create a table based on another table without copying the data in it.
Create table tab2 as select * from emp where 1 > 2;
Create table e as select * from emp where rownum > 1;
26. U are asked to select the last 10 rows of a table, or Rows 10 to 15, or the first 5 rows of a table.
a) select rownum, empno, deptno from emp
--where rownum >= &1 and rownum <= &2
group by deptno,empno,rownum
having rownum >= &1 and rownum <= &2;
Explanation :
If U use the WHERE Clause (Commented out) then it returns "No rows selected" if the first value U enter is greater than 1. This is because initially the value of ROWNUM will be 1 when U are checking in the WHERE clause, and therefore when U enter a value other than 1, and because U have an AND clause and Here it is failing in the first condition itself i.e ROWNUM >= (any value U have entered other than 1 ) returns Failure and Hence the Result. But if U have a GROUP BY clause, and then U can put the limiting condition using the HAVING clause, and since here the ROWNUM values are already determined, U can check for any required value as shown in the above
27. Employees hired on or before the 15th of any month are paid on the last Friday of that month. Those hired after 15th are paid on the last Friday of the following month. Print list of Employees, their hiredate and first pay date.
The following Query selects the FIRST PAY DATE for the Employees .
select empno, ename, hiredate, next_day(last_day(hiredate) - 7,'Fri') "First Pay Day" from emp where to_number(to_char(hiredate,'dd')) <= 15
union
select empno, ename, hiredate, next_day(last_day(add_months(hiredate,1)) - 7,'Fri') "First Pay Day" from emp where to_number(to_char(hiredate,'dd')) > 15
28.How to create a temporary table?
create global temporary table xx (name varchar2(10)) on commit delete rows;
29. To find number of columns having Constraints in a table
SELECT CONSTRAINT_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME LIKE
'PERSONAL_DETAIL_INFO';
30. To Find the number of columns in the table
select count(*) from ALL_TAB_COLUMNS where table_name like 'PERSONAL_MASTER_INFO';
select count(*) from USER_TAB_COLUMNS where table_name like 'PERSONAL_MASTER_INFO';
31. To find the name of the connect string
select name from v$database;
32. can we open a cursor in the exception section???
YES
33. To define the editor in sql
define_editor = "Winpopup"
then give ed
34. what are the special characters are allowed for table name?
#,$,_
35.number(*,4) is it possible??
yes
create table emp1 (d number(*,4));
desc emp1;
Null? Type
------------------------------------- -------- -------------
NUMBER(38,2)
36.To find the hours b/w the dates
select floor(
(
(
(to_date('24-May-01','dd-mon-yy'))-
(to_date('22-May-01','dd-mon-yy'))
)
*24*60*60)
/3600) from dual;
37. To create Materialized view
Create materialized view mat_view
refresh with rowid
START WITH sysdate
NEXT SYSDATE + 1 AS
select * from mail_ids;
38. INSERT INTO JS VALUES(TO_DATE('25-MAY-01 14:22:10','DD-MON-YY HH24:MI:SS'))
SELECT TO_CHAR(A,'DD-MON-YY HH:MI:SS') FROM JS;
SELECT TO_CHAR(A,'DD-MON-YY HH24:MI:SS') FROM JS;
INSERT INTO JS VALUES(TO_DATE('25-MAY-01 14:22:10','DD-MON-YY HH24:MI:SS'))
39.SELECT TO_CHAR (hiredate, 'DAY/month/YEAR','nls_date_language =Italian') FROM EMP
40.SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart,
empno, mgr, job
FROM emp
START WITH job = 'PRESIDENT'
CONNECT BY PRIOR empno = mgr;
SELECT * FROM EMP WHERE ENAME LIKE 'GEE$%T%' ESCAPE '$'
LAB : 19th jun
1.1. select avg(scost) from software where dev_in='PASCAL';
1.2. select pname,round((sysdate-dob)/365) from programmer;
1.3. select pname from studies where course='DAP';
1.4. select max(sold) from software;
1.5. select pname,dob from programmer where
to_char(dob,'mon') = 'jan';
1.6. select min(ccost) from studies;
1.7. select count(pname) from studies where course ='PGDCA';
1.8. select sum(scost*sold) from software where dev_in='C';
1.9. select dev_in from software where pname='RAMESH';
1.10. select count(pname) from studies where splace='Sabhari';
1.11. select dev_in from software where dcost>=20000;
1.12. select pname,ceil(dcost/scost) from software where
sign(sold-(dcost/scost))=-1;
1.13. select dev_in from software where sign(sold-(dcost/scost))=-1;
1.14. select max(dcost) from software where dev_in='BASIC';
1.15. select count(pname) from software where dev_in='DBASE';
1.16. select count(pname) from studies where splace='Pragathi';
1.17. select count(pname) from studies where ccost>=5000
and ccost<=10000;
1.18. select avg(ccost) from studies;
1.19. select pname from programmer where prof1='C' or prof2='C';
1.20. select count(pname) from programmer where prof1='COBOL' or
prof1='PASCAL' or prof2='COBOL' or prof2='PASCAL';
1.21. select count(pname) from programmer where prof1='COBOL' or
prof1='PASCAL' or prof2='COBOL' or prof2='PASCAL';
1.22. select max(round((sysdate-dob)/365)) from programmer where sex='M';
1.23. select avg(round((sysdate-dob)/365)) from programmer where sex='F';
1.24. select pname,round((sysdate-doj)/365) from programmer
order by pname desc;
1.25. select pname from programmer where
to_char(dob,'mon')=to_char(sysdate,'mon');
1.26. select count(pname) from programmer where sex='F';
1.27. select prof1,prof2 from programmer where sex='M';
1.28. select avg(salary) from programmer;
1.29. select count(pname) from programmer where
salary > =2000 and salary<=4000;
1.30. select * from programmer where prof1 not in
('CLIPPER','COBOL','PASCAL') and
prof2 not in ('CLIPPER','COBOL','PASCAL');
1.31. select count(pname) from programmer where
(prof1='C' or prof2='C') and
((round((sysdate-dob)/365))>24) and (sex='F');
1.32. select pname from programmer where
to_char(dob,'dd-mon') || '-' || to_char(sysdate,'yyyy')
between trunc(sysdate) and trunc(sysdate)+7
or
to_char(dob,'dd-mon') || '-' || to_char(sysdate+7,'yyyy')
between trunc(sysdate) and trunc(sysdate)+7;
1.33. select * from programmer where
round((sysdate-doj)/365)<5;
1.34. select pname from programmer where
round((sysdate-doj)/365)=5;
1.35. select dcost-scost*sold from software where
sign(sold-(dcost/scost))=-1;
1.36. select * from software where sold <1;
1.37. select scost from software where pname = 'MARY';
1.38. select distinct(splace) from studies;
1.39. select count(distinct(course)) from studies;
1.40. select pname from studies where instr(pname,'a',1,2)>0;
select pname from studies where pname like '%a%a%';
1.41. select pname from programmer where vsize(pname)<=5;
1.42. select pname from programmer
where sex='F' and
prof1='COBOL' or prof2='COBOL'
and round((sysdate-doj)/365)>2
1.43. select min(vsize(pname)) from programmer;
1.44. select avg(dcost) from software where dev_in='COBOL';
1.45. ALTER SESSION
SET NLS_DATE_FORMAT = 'DD/MM/YY';
select dob,doj from programmer;
1.46. select pname,salary from programmer
where prof1 <> 'COBOL' and prof2<> 'COBOL'
1.47. select pname from programmer where dob=last_day(dob);
1.48. select title,dcost,scost,dcost-scost from software
order by dcost-scost desc;
1.49. select title from software where instr(title,' ') <>0;
1.50. select pname,dob,doj from programmer
where to_char(dob,'mon')=to_char(doj,'mon');
PART : 2
2.1. select dev_in,count(*) from software
group by dev_in;
2.2. select pname,count(*) from software
group by pname;
2.3.select sex,count(*) from programmer
group by sex;
2.4.select title,dcost,sold from software
where dcost in (select max(dcost) from software) or
sold in ( select max(sold) from software)
select dev_in,max(dcost),max(sold) from software
group by dev_in;
2.5. select to_char('dob,'yyyy'),count(*) from programmer
group by to_char('dob,'yyyy')
2.6.select to_char(doj,'yyyy'),count(*) from programmer
group by to_char(doj,'yyyy');
2.7. select to_char(dob,'mon'),count(*) from programmer
group by to_char(dob,'mon');
2.8. select to_char(doj,'mon'),count(*) from programmer
group by to_char(doj,'mon')
2.9. select prof1,count(*) from programmer
group by prof1;
2.10. select prof2,count(*) from programmer
group by prof2;
2.11. select salary,count(*) from programmer
group by salary;
2.12. select splace,count(*) from studies
group by splace;
2.13. select dev_in,sum(dcost) from software
group by dev_in;
2.14. select dev_in,sum(scost) from software
group by dev_in;
2.15. select pname,sum(dcost) from software
group by pname;
2.16. select pname,sum(scost) from software
group by pname;
2.17. select pname,sum(sold) from software
group by pname;
2.18.select sum(scost),pname from software
group by pname;
2.19. select sum(scost),pname,dev_in from software
group by pname,dev_in;
2.20. select pname,max(dcost),min(dcost) from software
group by pname;
2.21. select dev_in,avg(dcost),avg(scost),avg(scost/sold) "avg(copy)"
from software
where sold > 0
group by dev_in
2.22. select splace,count(distinct(course)),avg(ccost) from studies
group by splace;
2.23. select splace,count(pname) from studies
group by splace;
2.24. select decode(sex,'M',pname) "MALE",
decode(sex,'F',pname) "FEMALE" from programmer order by 1
2.25. select pname,title from software;
2.26. select dev_in,count(*) from software
where dev_in not in('C','C++')
group by dev_in;
2.27. select dev_in,count(*),dcost from software
group by dev_in,dcost
having dcost<1000;
2.28.select dev_in,avg(dcost-scost) from software
group by dev_in;
2.29. select pname,sum(dcost),sum(scost),sum(dcost-scost*sold) "Amount"
from software
where sign(sold-(dcost/scost))=-1
group by pname;
2.30. select max(salary),min(salary),avg(salary) from programmer
where salary >2000;
PART -3
3.1. select pname from programmer
where salary in
(select max(salary) from programmer where prof1='C' or prof2='C');
3.2. select pname,salary from programmer
where sex='F' and salary in
(select max(salary) from programmer
where (prof1='COBOL' or prof2='COBOL'));
3.3. select prof1,max(salary) from programmer group by prof1;
3.4.select min(round((sysdate-doj)/365)) from programmer;
select pname from programmer where doj=(select max(doj) from programmer);
3.5. select max(round((sysdate-doj)/365)) from programmer where sex='M';
3.8. select pname from programmer where dob in
(select max(dob) from programmer where prof1='DBASE' or prof2='DBASE');
3.9. select pname from programmer where prof1 not in ('C','C++','ORACLE','DBASE')
and prof2 not in ('C','C++','ORACLE','DBASE') and pname in
(select pname from programmer where sex ='F' and salary < 3000);
3.10. select splace from studies
group by splace
having count(*) = (select max(count(*)) from studies group by splace);
3.11. select course from studies
group by course
having count(*)=(select max(count(*)) from studies group by course);
3.12. select splace,course,avg(ccost) from studies
group by course,splace
having avg(ccost)=(select min(avg(ccost)) from studies group by splace);
3.13. select course from studies
where ccost in (select max(ccost) from studies);
3.14. select splace from studies
where ccost in (select max(ccost) from studies);
3.15. select course,count(*) from studies
group by course
having count(*) < (select avg(count(*)) from studies group by course);
3.16. select splace from studies
where course in (select course from studies group by course
having count(*) < (select avg(count(*)) from studies group by course));
3.17. select course from studies where
ccost >=(select avg(ccost)-1000 from studies) and
ccost <= (select avg(ccost)+1000 from studies);
3.18. select dev_in from software where dcost in
(select max(dcost) from software);
3.19. select dev_in from software where scost in
(select min(scost) from software);
3.20. select pname from software where sold in
(select min(sold) from software);
3.21. select dev_in from software where scost
in (select max(scost) from software);
3.22. select sold from software where dcost-scost in
(select min(dcost-scost) from software);
3.23. select title from software where dcost in
(select max(dcost) from software where dev_in='PASCAL');
3.24. select dev_in from software
group by dev_in
having count(*) = (select max(count(*)) from software group by dev_in);
3.25. select pname from software
group by pname
having count(*) = (select max(count(*)) from software group by pname);
3.26. select pname from software
where scost in
(select max(scost) from software);
3.27. select title from software
where sold < (select avg(sold) from software);
3.28. select pname from software where scost > 2*dcost;
3.29.
1) Which is the valid Tab canvas trigger? 1) Set_tab_page_property 2) 3)
Find_tab_page_property 4) None of the above(Ans)
2) Your form has two when-validate-item triggers, one at the block level and one
at the item level. Only one is executing, but you need both to run. What should
you do?
1.Change the Reference Information attribute of the block-level trigger.
2.Change the execution style property on the block level trigger. 3.Change
the execution style property on the item-level trigger.(Ans) 4.Create a
procedure that both triggers can call. 5.Copy all of the code from the
block-level into the item-level trigger.
3) ORA_FFI is used to invoke the
1) Foreign Function (Ans) 2) Triggers 3) Stored Procedures 4) Both 1 & 3
4) If u r having On-Message trigger in form and item level and the triggers are
created with default settings which trigger will fire
1) Form Level 2) Item level (ans) 3) form and item 4) None of the above
5) A LAST_RECORD command causes a form to do what?
1. Position the cursor on the first field of the last record in the current
block.(Ans) 2. Return a PL/SQL record containing all the column values in
the last record in a record group. 3. Move the cursor to the last
displayed field of the last block on the form. 4. Fetch all records into all
bas-table blocks on the form 5. None of the above
6) Sample Code If
What does the Raise Form_Trigger_Failure statement cause a form to do?
1.Rollback uncommitted Changes. 2.Transfer processing control to the On
-Error trigger. 3.Return to calling form or menu. 4.Cease running the
currently executing trigger. (Ans) 5.Advance to the next valid field in a
block.
7)Which one of the following is an unrestricted built-in? 1.NEW_FORM 2.CALL_FORM
(Ans) 3.OPEN_FORM 4.GO_BLOCK 5.NEXT_BLOCK
8) When does a form's On-Message trigger execute?
1.When the server returns an error message in response to an SQL command
2.When an informative type forms message of type FRM-nnnnn is displayed (Ans
) 3.Each time an item hint is automatically displayed 4.When any forms Error
message of type FRM-nnnnn is displayed 5.Each time the form's processing
encounters a Message () command
9) When an LOV is associated with a record group, which property, command, or
trigger affects how often the record group is populated?.
1. Populate Group 2. Record Group Type 3. Refresh Interval 4. On-Populate
-Details 5. Auto Refresh (Ans)
10) Which is NOT a valid forms built-in? 1. ID_NULL 2. DEFAULT_VALUE 3.
REDISPLAY 4. BREAK 5. None of the above (Ans)
11) Minimum number of queries is required to create a matrix report?
1. One (Ans) 2. Two 3. Three 4. Four 5. None of the above
12) U r creating a temporary table in the report and u wanted to insert values
into that Which function would u use
1. SRW.DO_SQL()
13) a := ID_NULL() is used for
1.to check for the existence of an object created dynamically at
runtime.
14) Which is the Valid form run time mode?
1. HTML 2. PDF 3. Bitmap (Ans) 4. All the above
15) If u r creating Master/detail report with 6 columns , each containing 3
columns what will the number of repeating frame
1. 1 2. 2 (Ans) 3. 3 4. None of the above
16) U r creating a Master/detail form with default settings. Which one of the
following statement is true
1. Prevents the deletion of a master record when associated detail records
exist in the database. (Ans) 2. Allows the master record
to be deleted and does not affect associated detail records in the
database. 3. Allows the master record to be deleted and automatically
deletes any associated detail records in the detail block's base
table at commit time. 4. Prevents the deletion of master either master or
detail record is present
17) How to read/write OS Files ?
1. TEXT_IO
18) Which is more correct regarding object group ?
1. An object group cannot contain another object group.
19 ) Suppose if u r having 3 columns in a report and
1. Anchor both the objects to the repeating frame
20 ) U want to increase u'r report's performance what will u do ?
1. Increase the number of queries 2. Delete unused data Model
objects (Ans) 3. Fixed size for graphic objects
21) If u getting an error like
Ora nnnn : Invalid column name if u want to trace out the error what u will do?
1. Execute the form again in Debug Mode 2. Again excuting the same 3.
Check in pre-commit 4. Use pre and post-DML's (Ans)
22) Is it possible to exit form programatically if so how and which trigger u
use?
23) About enter query mode is it possible to navigate from one item to another?
Yes
1. How pl/sql is procedural ? Ans : Pl/sql bridges the gap between the database
functionality and procedural languages. It has got the feature of 'cursors'
where you can process set of rows one by one Manipulation of variables of
different datatypes is possible. Different control statements like if/end if,
for loop/end loop, while loop/end loop and goto <
have been provided to implement the user's logic. Raising the user-defined,
system-defined exceptions are possible. Calling a pl/sql block from another
pl/sql block is possible.
These features make the pl/sql a procedural language.
2. What is difference between %type and %rowtype ? Ans :
* %TYPE will refer to corresponding database object's column. *
%ROWTYPE will refer columns in a row and corresponding fields in a
record have the same names and datatypes
3. How many type of cursors available in PL/SQL ? Ans : (Implicit and explicit.
Implicit cursors are executed by oracle itself, whereas explicit cursors
are declared explicitly in pl/sql)
4. What are the types of cursor attributes and explain ? Ans : explicit and
Implicit cursor attributes.
Explicit cursor attributes:- 1. %NOTFOUND 2. %FOUND 3. %ROWCOUNT 4.
%ISOPEN Implicit cursor attributes:- 1. SQL%NOTFOUND 2. SQL%FOUND 3.
SQL%ROWCOUNT 4. SQL%ISOPEN
5.Is it possible to use parameter in cursor? if it is possible, how?
ANS : possible.
eg. declare cursor c1(para1 number) is select * from emp where
empno = para1; begin open c1(7369); loop fetch c1 into ..... ..... end
loop; close c1; end;
6. Explain the Purpose of SQL%notfound cursor attribute ? Ans : If the DML
statement doesn't process any row, it will return FALSE. otherwise it is TRUE.
7. How to manipulate explicit cursors ? Ans : OPEN,FETCH and CLOSE statements.
8. What is the use of cursor FOR LOOP ? ANS : In cursor FOR loop , no need of
OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its
loop index as a %ROWTYPE record, opens a cursor, repeatedly
fetches rows of values from the result set into fields in the
record, and closes the cursor when all rows have been processed.
eg. DECLARE result temp.col1%TYPE; CURSOR c1 IS SELECT n1, n2, n3 FROM
data_table WHERE exper_num = 1; BEGIN FOR c1_rec IN c1 LOOP /* calculate
and store the results */ result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL); END LOOP; COMMIT; END;
9. What are the types of exceptions ? and give examples? Ans : 1. Named system
exceptions eg. NO_DATA_FOUND, TOO_MANY_ROWS, OTHERS, etc. 2. User defined
exceptions. eg. declare user_excep EXCEPTION; begin .... raise user_excep;
excption when user_excep then ..... end; 3.Unnamed system exceptions. eg.
declare exce_name EXCEPTION; PROGMA EXCEPTION_INIT (exce_name,error_code);
begin ... end; 4.Unnamed user defined exception. eg, exception when exce_name
then raise application_error(error_number,message); end;
10. can u use multiple exceptions in single handler ? ANS : we can use multiple
exceptions in single handler. eg., EXCEPTION WHEN invalid_number OR
STANDARD.INVALID_NUMBER THEN -- handle the error END;
11. How Exceptions Are Raised ?
Ans : using RAISE statement. eg., DECLARE out_of_stock EXCEPTION;
number_on_hand NUMBER(4); BEGIN ... IF number_on_hand < 1 THEN RAISE
out_of_stock; END IF; EXCEPTION WHEN out_of_stock THEN -- handle the error
END;
12. what are the types of PL/SQL blocks ? Ans : there are two type.
1.Anonymous block eg., declare ..... begin ... end;
2.Stored PL/SQL block eg., create procedure proc_name is ..... begin
... end;
13. What is differnce between Anonymous block and Stored PL/SQL block ?
Ans : * If you want to run Anonymous block,Each and Every time is complied
and executed. * but stored PL/SQL block is stored in combiled form in the
database.Then we can execute without recompling, using
EXECUTE command.
14. What are the methods used to execute stored Procedure,function & packages.
Ans : 1. EXECUTE Statement. ie., SQL> EXECUTE proc_name(arg1,arg2,....);
2. BEGIN .. END Statement. ie., SQL> BEGIN
proc_name(arg1,arg2,....); END;
15. What is the difference between PACKAGE Specification and PACKAGE Body ? Ans
: * The package specification contains public declarations of
procedures,functions,cursors,global variables and other packages also.
* The Package Specification Contains All The Code Behind The Package
Spec.
16. How many triggers you can assign to a single table.
Ans : * 12 Triggers. Ie., 1. Before Update Row / Statement 2. Before
Delete Row / Statement 3. Before Insert Row / Statement 4. After Update Row
/ Statement 5. After Delete Row / Statement 6. After Insert Row /
Statement
17. Create a trigger for restricting transaction on Saturday and Sunday.
Ans : create or replace trigger rest_trans before insert or update or delete
on route_details for each row declare v_day varchar2(10); begin select
to_char(sysdate,'dy') into v_day from dual; if v_day = 'sat' or v_day =
'sun' then
Raise_Application_Error(-20001, 'transaction Not Allowed In Saturday And Sunday');
1 ) For which one the pcode is not generated
a)Anonymous block (Ans)
b)Trigger
c)Name object
d)Stored procedure,function and package
e)None of the block
2 )What Will The Output Of This Pl/Sql Block?00
Declare
A Varchar2(3):=100;
Begin
A:= A+100;
Declare
A Varchar2(4) := 1000;
Begin
A:= A+100;
End;
A:=A+100;
Dbms_Output.Put_Line(A);
End;
/
A) 100
B) 300 (Ans)
C) 1100
D) 1200
E) Gives Error
3) Where U Can't Use Autonomous Transaction?
A) Top-Level (Not Nested) Anonymous Pl/Sql Blocks
B) Local, Stand-Alone, And Packaged Functions And Procedures
C) Methods Of A Sql Object Type
D) Database Triggers
E) Nested Anonymous Pl/Sql Blocks (Ans)
4) Create Sequence Myseq;
Select Myseq.Currval,Myseq.Nextval From Dual;
What Will The Output?
A) 1,1 (Ans)
B) 1,2
C) ORA-08002:Sequence MYSEQ.CURRVAL Is Not Yet Defined In This Session
D) None Of The Above
5) Which Is The Valid Column Name?
a) Column
b) 123cache
c) #Cache
d) Cache_#123 (Ans)
E) Both C & D
6) Which statement can't be used in Database Trigger ?
A) Commit When Used Gives The Error
B) Rollback When Used Gives The Error
C) Commit Can Be Used
D) Rollback Can Be Used
E) Both A & B (Ans)
Ans:E U Cannot Use Both Commit And Rollback. It Won't Give
Compilation Errors. But It Raises Error While The
Trigger Is Fired
7) Which will not give syntax error?
a) Function myfunction(id_n IN department.departmentno%type)
Return Varchar2 Is (Ans)
b) Function myfunction(id_n IN number(4,2)) return varchar2 is
c) Function myfunction(id_n IN varchar2(5)) return number is
d) Function myfunction(id_n IN number) return varchar2(20) is
e) None of the above
8) Suppose if u create a procedure in Scott schema as
Create or replace procedure mypro(tabname varchar2) is
Begin
Execute Immediate
'create Table' || Tabname || '(A Number);';
End;
U R Granting The Execute Procedure To User Demo When He
Is Executing What Will Happen?
a) Table will created in Scott schema
b) Table will created in Demo's Schema
c) Will give compilation error
d) will give execution error
9) If The Emp Table Is Having 14 Rows And If U R Giving
The Following Query
Select 6+8 From Emp;
A) 14 --> 14 Times (Ans)
B) 6+8 --> 14 Times
C) Error
D) None Of The Above
10)Raise_Application_Error(Error_Number, Message[, {TRUE | FALSE}]);
Where Error_Number Is A Negative Integer In The Range -20000 .. -20999
READONLY: (RUNTIME SETTINGS)
Requests read consistency across multiple queries in a report.
When accessing data from ORACLE, Read Consistency Is Accomplished By
a SET TRANSACTION READ ONLY statement (refer to your ORACLE7 Server
Sql Language Reference Manual For More Information On
SET TRANSACTION READ ONLY).
Options: YES requests read consistency.
NO means do not provide read consistency.
Default: NO
Rules:
1. The List Below Shows The Order Of Events When A Report Is Executed.
Notice Where The SET TRANSACTION READONLY occurs.
1.1 Before Form trigger is fired.
1.2 Runtime Parameter Form appears (if not suppressed).
1.3 After Form trigger is fired.
1.4 Report is "compiled."
1.5 Before Report trigger is fired and queries are parsed.
1.6 SET TRANSACTION READONLY is executed (if specified via
The READONLY Argument Or Setting).
1.7 The Report Is Executed And The Between Page Trigger Fires For
Each Page Except The Last One. (Note That Data Can Be Fetched
At Any Time While The Report Is Being Formatted.)
Note: COMMITs can occur during this time due to any of the following--user
Exit With DDL, SRW.DO_SQL with DDL,
Or If ONFAILURE=COMMIT, And The Report Fails.
1.8 COMMIT is executed (if READONLY is specified) to end the transaction.
1.9 After Report trigger is fired.
1.10 COMMIT/ROLLBACK/NOACTION is executed based on what was specified
via the ONSUCCESS argument or setting.
Caution: In steps 1.4 through 1.8, avoid DDL Statements That Would Modify
The Tables On Which The Report Is Based. Step 1.3 Takes A Snapshot
Of The Tables And The Snapshot Must Remain Valid Throughout
The Execution Of The Report. In Steps 1.6 Through 1.8, Avoid DML
Statements That Would Modify The Contents Of The Tables On Which
The Report Is Based. Queries May Be Executed In Any Order, Which
makes DML statements unreliable (unless performed on tables
Not Used By The Report).
Caution: If you specify READONLY, You Should Avoid DDL Altogether.
WHen You Execute A DDL Statement (e.g., via SRW.DO_SQL OR
A User Exit), a COMMIT is automatically issued. If you are
using READONLY, this will prematurely end the transaction
begun by SET TRANSACTION READONLY.
2. This Keyword Is Only Useful For Reports Using Multiple Queries,
Because ORACLE Automatically Provides Read Consistency, Without
Locking, For Single Query Reports.
1.Select The Nth Maximum Salary From The Emp Table.
Select Distinct Sal
From Emp X
Where &N =(Select Count(Distinct Sal) From Emp Y
Where X.Sal <= Y.Sal)
2. Select N Max Salaries From Emp Table.
Select Distinct Sal
From Emp X
Where &N >=(Select Count(Distinct Sal)
From Emp Y
Where X.Sal <= Y.Sal)
3.To Find The Count Of Employees
Getting Salaries > 1000 <1999 And So On...
Select Count(*),Sal-Mod(Sal,1000) From Emp Group By Sal-Mod(Sal,1000)
4.Display All The Column Along With Rowid Without Specifying Any Col Names.
Select Rowid,Emp.* From Emp
5.To Display The Remarks As Minimum And Maximum For The Respective Salaries.
6. To Print The Character ****** At Every 5Th Row Reterived From The Table.
Select Empno,Ename,Decode(Mod(Rownum,5),0,'*******',' ') Remark From Emp
7. To Convert The Salary Into Words.
Select To_Char(To_Date(Substr(To_Char(Sal),1),'j'),'jsp') From Emp;
8. Select The Nth Row From Emp.
Select * From Emp Where Rowid = ( Select Max(Rowid)
From Emp Where Rownum <= &N);
9.Find The Second Max Salary For Each Department.
Select Deptno,Max(Sal) From Emp Where Sal In
(Select Sal From Emp Where Sal Not In
(Select Max(Sal) From Emp Group By Deptno)) Group By Deptno
10.Find Out The N'th Max Salary For Each Department.
Select Distinct Sal,Deptno From Emp Y Where &N In
(Select Count(Distinct(Sal))
From Emp X Where X.Sal >= Y.Sal And X.Deptno = Y.Deptno
Group By Deptno);
11. To Display Male If He Is A Male Ortherwise Female In The Programmer Table.
Select Prof1,Sum(Decode(Sex,'m',1,0)) Male,
Sum(Decode(Sex,'f',1,0))Female
From Programmer
Group By Prof1
12.Give The Hierarchical Order Of Emp Table.
13.To select last n rows from the table.
select * from emp where rowid >
(select max(rowid) from emp where rownum <=
(select count(*) - &n from emp))
Select Rownum,Empno,Ename From Emp
Minus
Select Rownum,Empno,Ename From Emp
Where Rownum <= (Select Count(*) - &N From Emp)
14.To display the less than 5000 if the salary is less than 5000,less equal to , else greater than 5000 .
15.To Display Employee Details From The Emp Table For The Given Department
From The Dept Table. (Hint: Using Cursor).
select d.deptno,loc,cursor(select empno,ename from emp e where
e.deptno = d.deptno) from dept d where d.deptno = &dept
** SQL TEST **
select sum(sal) from emp;
/
select deptno,avg(sal) from emp
group by deptno;
/
select deptno,count(*) from emp
group by deptno;
/
select max(sal) from emp;
/
select deptno,count(*) from emp
where deptno =10 or deptno = 20
group by deptno;
/
select e2.empno,e2.ename manager,e2.deptno,e2.sal,
e1.mgr,e1.ename employee from emp e1,emp e2
where e2.mgr = e1.empno;
/
select empno,deptno,job from emp
where deptno in (select deptno from emp
where job = 'SALESMAN') and job = 'SALESMAN';
/
select deptno,count(job) from emp
where job = 'SALESMAN'
group by deptno
having count(job)>2;
/
select sum(decode(deptno,10,count(*))) dept_10,
sum(decode(deptno,20,count(*))) dept_20 from emp
where deptno in (10,20)
group by deptno
/
Select Empno,Empname,Resphone,Decode(Resphone,Null,'no Phone At Home')
Message From Suresh.Employee;
select deptno,sum(sal) total_salary from emp
group by deptno
having sum(sal) > 10000;
Select D.Deptno,D.Dname,A.Empno Empno,A.Ename Ename,
B.Empno Manager_Id,B.Ename Manager_Name
From Emp A,Emp B,Dept D
Where A.Mgr=B.Empno And A.Deptno = D.Deptno;
declare
n number := &value;
begin
for i in 1..n loop
for j in 1..i loop
dbms_output.put(i);
end loop;
dbms_output.put_line('');
end loop;
end;
/
Create Or Replace Procedure
Subbu_Name(Name Varchar2, X Varchar2,Y Varchar2) Is
type subbutabtype is table of varchar2(30)
index by binary_integer;
subbu_tab subbutabtype;
j number;
n binary_integer :=0;
v_length number;
k number := 1;
final number;
Begin
v_length := length(ltrim(rtrim(name)));
for i in 1..v_length loop
subbu_tab(i) := substr(ltrim(rtrim(name)),i,1);
end loop;
for i in 1..v_length loop
if substr(ltrim(rtrim(name)),i,1) = ' ' then
for j in k..i loop
dbms_output.put(subbu_tab(j));
end loop;
k := i;
final := i;
dbms_output.put('ems_'||x||'_ems_'||y);
dbms_output.put_line(' ');
end if;
end loop;
dbms_output.put_line(substr(ltrim(rtrim(name)),
final,v_length)||' ems_'||x||'_ems_'||y);
End;
/
Create Or Replace Procedure
subbu_name(name varchar2,x varchar2,y varchar2) is
v_len number;
v_pos number :=1;
v_name varchar2(50);
v_store varchar2(25);
v_name2 varchar2(50);
begin
v_name := ltrim(rtrim(name));
v_len := length(v_name);
while(v_pos <>0 ) loop
v_pos := instr(v_name,' ');
v_store := substr(v_name,1,v_pos);
v_name2 := substr(v_name,v_pos+1,v_len);
v_name := ltrim(rtrim(v_name2));
if v_pos <> 0 then
dbms_output.put_line(ltrim(rtrim(v_store))||
' '||'ems_'||x||'_ems_'||y);
end if;
end loop;
dbms_output.put_line(ltrim(rtrim(v_name))||
' '||'ems_'||x||'_ems_'||y);
End;
/
Declare
n number := &temp;
l number;
s number := 0;
Begin
l := length(n);
for i in 1..l loop
s := s + mod(n,10);
n := trunc(n/10);
End Loop;
Dbms_Output.Put_Line(S);
End;
/
*** SQL Commands ***
The SQL commands listed below are divided into these categories:
* Data Definition Language commands
* Data Manipulation Language commands
* Transaction Control commands
* Session Control commands
* System Control commands
SQL Data Definition Language commands include the following:
ALTER CLUSTER CREATE DATABASE DROP INDEX
ALTER DATABASE CREATE DATABASE LINK DROP PACKAGE
ALTER FUNCTION CREATE FUNCTION DROP PROCEDURE
ALTER INDEX CREATE INDEX DROP PROFILE
ALTER PACKAGE CREATE PACKAGE DROP ROLE
ALTER PROCEDURE CREATE PACKAGE BODY DROP ROLLBACK SEGMENT
ALTER PROFILE CREATE PROCEDURE DROP SEQUENCE
ALTER RESOURCE COST CREATE PROFILE DROP SNAPSHOT
ALTER ROLE CREATE ROLE DROP SNAPSHOT LOG
ALTER ROLLBACK SEGMENT CREATE ROLLBACK SEGMENT DROP SYNONYM
ALTER SEQUENCE CREATE SCHEMA DROP TABLE
ALTER SNAPSHOT CREATE SEQUENCE DROP TABLESPACE
ALTER SNAPSHOT LOG CREATE SNAPSHOT DROP TRIGGER
ALTER TABLE CREATE SNAPSHOT LOG DROP USER
ALTER TABLESPACE CREATE SYNONYM DROP VIEW
ALTER TRIGGER CREATE TABLE GRANT
ALTER USER CREATE TABLESPACE NOAUDIT
ALTER VIEW CREATE TRIGGER RENAME
ANALYZE CREATE USER REVOKE
AUDIT CREATE VIEW TRUNCATE
COMMENT DROP CLUSTER UPDATE
CREATE CLUSTER DROP DATABASE LINK
CREATE CONTROLFILE DROP FUNCTION
SQL Data Manipulation Language commands include the following:
DELETE
EXPLAIN PLAN
INSERT
LOCK TABLE
SELEC
SQL Transaction Control commands include the following:
COMMIT
ROLLBACK
SAVEPOINT
SET TRANSACTION
SQL Session Control commands include the following:
ALTER SESSION
SET ROLE
SQL System Control command (only one command):
ALTER SYSTEM
*** ***
1.Select The Nth Maximum Salary From The Emp Table
2.Select N Max Salaries From Emp Table.
3.To find the count of employees getting
salaries > 1000 <1999 and so on...
4.Display all the column along with rowid without
specifying any col names
5.To display the remarks as Minimum and Maximum for the respective
salaries
6.To print the character ****** at every 5th row reterived from the table
7.To convert the salary into words.
8.select the nth row from emp;
9.Find the second Max salary for each department.
10.Find Out The N'th Max Salary For Each Department.
10.A. To Find The Maximum Salary For Each Department
Without Using Group By Function
11.To Display Male If He Is A Male Otherwise Female In The
Programmer Table.
12.Give The Hierarchical Order Of Emp Table; (Pg.No. 276)
13.To Select Last N Rows From The Table .
14.To Display The Less Than 5000 If The Salary Is
Less Than 5000,Else Equal
to, else greater than 5000 .
15.Select To_Char(To_Date(Num1,'j'),'jsp') From Num;
16.To Display Employee Details From The Emp Table
For The Given Department
from the dept table. (Hint: Using Cursor).
17.Use Cursor In Sql Statement To Display The Dept
With Employee Details
(not in pl/sql block)
19.To select employee details who joined between
6 months before the sysdate and sysdate
20. Select Departments Having More Than 2 Salespersons :
21. Deptwise Manager & Salesperson(Correlated Sub Query)
22. Write Differemtn Methods To Delete Duplicate Rows
23. How does one select every n th row from a table ?
24. Use Order by in Sub Query
25. Create A Table Based On Another Table Without Copying The Data In It.
26. U Are Asked To Select The Last 10 Rows Of A Table,
Or Rows 10 To 15,Or The First 5 Rows Of A Table.
27. Employees Hired On Or Before The 15Th Of Any Month Are Paid On The
Last Friday Of That Month.Those Hired After 15Th Are Paid On The Last
Friday Of The Following Month. Print List Of Employees, Their Hiredate
And First Pay Date.The Following Query Selects The First Pay Date For
The Employees .
28. How To Create A Temporary Table?
29. To Find Number Of Columns Having Constraints In A Table
30. To Find The Number Of Columns In The Table
31. To Find The Name Of The Connect String
32. Can We Open A Cursor In The Exception Section???
33. To Define The Editor In Sql
34. What Are The Special Characters Are Allowed For Table Name?
35. Number(*,4) Is It Possible??
36. To Find The Hours B/W The Dates
37. To Create Materialized View
38. What Are All The Datatypes Used To Store The Time ?
40. Write Sql With Connect By Prior
QUESTIONS:
1. Sum Of Salary Of The Employees.
2. Avg Sal Of Each Department In Emp Table.
3. No. Of Employees In Each Department.
4. Maximum Salary Of Each Department.
5. No Of Employees In Deptpartment 1 & 2.
6. No Of Employees In Departments 1 & 2 In The Same Row.
7. Manager Name Of Each Employee.
8. Which Department Having More Than 2 Salesmans.
ANSWERS:
1.Select Sum(Sal) From Emp;
2.Select Deptid,Avg(Sal) From Emp Group By Deptid;
3.Select Dname,Count(Empid) From Emp A,Dept B
Where A.Deptid=B.Deptid Group By A.Deptid,Dname;
4.Select Dname,Max(Sal) From Emp A,Dept B
Where A.Deptid=B.Deptid Group By A.Deptid,Dname;
5.Select Dname,Count(Empid) No_Of_Employees From Emp A,Dept B
Where A.Deptid In (1,2) And A.Deptid=B.Deptid Group By A.Deptid,Dname;
6.SELECT SUM(DECODE(DEPTID,1,COUNT(*))),SUM(DECODE(DEPTID,2,COUNT(*)))
From Emp Group By Deptid
7.Select A.Name Employee,B.Name Manager
From Emp A,Emp B Where A.Mgr=B.Empid;
8.Select Dname From Emp A,Dept B Where A.Deptid=B.Deptid And
desig='SALESMAN' having count(desig) > 1
Group By A.Deptid,Dname;
1) You can do all the functionalities which database trigger does with forms
trigger itself. Then what is the necessity of database triggers.
If someone tries to change the record via sql/pl-sql still database
trigger will fire. Triggering BEFORE and AFTER UPDATE, INSERT, or DELET
E row trigger or a statement trigger Instead of Triggers System And
User Event Triggers
(2) What is a restricted procedure ? Is 'Execute_Query' a restricted procedure ?
Any built-in subprogram that initiates navigation is restricted. This
includes subprograms that move the input focus from one item to another, and
those that involve database transactions. EXECUTE_QUERY is a restricted
procedure.
(3) Why he has provided two objects 'Record Groups' and 'LOV' to create an LOV.
Why he has not provided all the functionality in the LOV object itself ?
With Record Group data will be retrieved from a query or static value
set. With dynamic record group values in LOV can be changed at run time.
(4) What features you appreciate most in Developer 2000 as a programmer ? Why ?
Designer Interface : Object Navigator, PL/SQL Editor
Object and Code Reusability : Copy/Paste Properties, Object Groups
GUI Control : Responding to Mouse Events, Combo Box
Style List Items
PL/SQL Interface : Source-Level Debugger, Editing Environment
for Stored Procedures
(5) What is the difference between procedure and functions ? Is it possible to
override the 'IN' parameter in procedure ?
Function returns a value procedure won't. It is not possible to override
IN parameter.
(6) 'It is always better to go for library than package'. Is the statement ok ?
Statement is incorrect. Library contains related packages. If the
package is used by many forms then u can keep in library and refer from
any form.
(7) Is it possible to change the colour of an text item dynamically ?
Not via Forms 4.5. But via operating system commnads color can be
changed. On Microsoft Windows,button color attributes are controlled
exclusively by the Color facility (within the Control Panel). To
define button color attributes (button face, button shadow, button
text color, button or highlight), you must use the Color facility.
(8) Is it possible to use 'COMMIT', 'ROLLBACK' statements in dbase triggers ?
Why ?
No, you can't use commit or rollbck in database trigger. It won't give any
compilation error but It gives runtime Mutation Error.
(9) I update a table in an anonymus pl/sql block. How can I find whether any
rows were actually updated meeting my 'where' condition ?
In Exception check sql%found or sql%notfound attribute to check whether
the rows are updated or not.
(1) Minimum how many groups should be there in a Matrix Report ? Explain them.
atleast 4. 2 group : to create matrix format across/down 1 cross product group
: it contains both the groups above with different direction in
display 1 group : to give detail to the cell in matrix
(2) If When-Validate-Item trigger is written on Block level & Item level which
will fire first ?
It depends on Execution style property of the trigger. If item level style is 1.
Override : Only item level trigger will fire.(Default) 2. After : block level
trigger will fire first & then item level will fire. 3. Before : item level
trigger will fire first & then block level will fire.
(3) What is the difference between Property class and Visual Attributes ?
Property classes u can have all(type, display, data, records, navigation,
database, functional, miscellaneous) attributes while in visual attributes
there are only display attributes & u can't add any attribute. U can attach
a trigger with the property class. U can attach property class to visual
attributes & also visual attribute to property class.
(4) Can one issue DDL statements from Forms ?
DDL (Data Definition Language) commands like CREATE, DROP and ALTER are not
directly supported from Forms because your Form is not suppose to manipulate
the database structure. You can use the FORMS_DDL built-in to execute DDL
statements. Eg : FORMS_DDL('CREATE TABLE X (A DATE)');
(5) What is global & system variable ?
A global variable is an Oracle Forms variable whose value is accessible to
triggers and subprograms in any module that is active during the current
session. System variables keep track of runtime status conditions during an
Oracle Forms Runform session. You can read the values of these variables in
any form trigger or user-named subprogram. All of the system variables are
read-only, except for these four : SYSTEM.MESSAGE_LEVEL,
SYSTEM.DATE_THRESHOLD, SYSTEM.EFFECTIVE_DATE, SYSTEM.SUPPRESS_WORKING
(6) What is the difference between SYSTEM.CURSOR_ITEM & SYSTEM.TRIGGER_ITEM
SYSTEM.TRIGGER_ITEM remains the same from the beginning to the end of given
trigger. This differs from SYSTEM.CURSOR_ITEM, which may change within a
given trigger when navigation takes place.
Which command would you use to remove all the rows
from the isotope table and not allow rollback? (one or more may be correct)
A) DROP TABLE isotope;
B) DELETE isotope;
C) TRUNCATE TABLE isotope;
D) There is no way to remove all rows and not allow rollback
C is correct: TRUNCATE TABLE isotope;
Explanation
The correct answer is c. The TRUNCATE command removes all of
the rows from a table. Because it is a DDL command, it does implicit commits,
thus not allowing rollback operations. Answer a is incorrect because the DROP
command would remove the entire table. Answer b is incorrect because the DELETE
command, which can delete rows, allows rollback because it is not a DDL
command. Answer d is incorrect because answer b is correct.
Use Tables 11.7 and 11.8 to evaluate this command: INSERT INTO inventory (id_number, manufacturer_id) VALUES (56023,'beth104ss'); Which type of constraint will be violated? (one or more may be correct)
A) Check
B) Not null
C) Primary key
D) Foreign key
Answer ..
B is correct: Not null
Explanation
The correct answer is b. The not-null constraint will be violated. Notice that the DESCRIPTION column is missing from the INSERT statement. DESCRIPTION has a not-null constraint, so you must include it in any INSERT activity on the table. Answer a is incorrect because none of the table items show a CHECK constraint as being assigned. Answer c is incorrect because the primary key is being inserted. Answer d is incorrect because a value for the foreign key column MANUFACTURER_ID is specified; also, because the exhibits don't show that the value exists in the MANUFACTURER table, you have to assume that it does.
In the executable section of a PL/SQL block, you include these statements: Isotope_record.isotope := 'XENON'; Isotope_record.group := 'NOBLE GAS'; Which task will be accomplished?
A) A record field will be assigned a character string value.
B) A record field will be created based on the isotope table.
C) A constant will be initialized.
D) A constant will be created.
A is correct: A record field will be assigned a character string value.
Explanation
The correct answer is a. A record field will be assigned a character string value. Answer b is incorrect because you aren't using a %ROWTYPE, which is used to create a record based on a complete table row. Answers c and d are incorrect because you aren't using the keyword CONSTANT.
What will the following operation return? [Choose two] SELECT TO_DATE('01-jan-00') - TO_DATE('01-dec-99') FROM dual;
A) 365 if the NLS_DATE_FORMAT is set to 'DD-mon-RR'
B) A VARCHAR2 value
C) An error; you can't do this with dates
D) -36493 if the NLS_DATE_FORMAT is set to the default value
Answer
A is correct: 365 if the NLS_DATE_FORMAT is set to 'DD-mon-RR'
D is correct: -36493 if the NLS_DATE_FORMAT is set to the default value
Explanation
The correct answers are a and d. When two dates are subtracted, you receive a numeric value that corresponds to the number of days between the dates-either positive if the first date is greater than the second, or negative if the first date is less than the second. Depending on the value of the NLS_DATE_FORMAT, the 00 will be either 1900 (default of dd-mon-yy) or 2000 (if set to dd-mon-rr), so both 365 and -36493 could result. Answer b is incorrect because date arithmetic returns a date or a number, not a ARCHAR2 value. Answer c is incorrect because answers a and d are correct.
Evaluate this command: CREATE TABLE purchase_items (id_number NUMBER(9), description VARCHAR2(25)) AS SELECT id_number, description FROM inventory WHERE quantity < 10; Why will this statement cause an error?
A) A clause is missing.
B) A keyword is missing.
C) The WHERE clause cannot be used when you're creating a table.
D) The data types in the new table must not be defined.
Answer
D is correct: The data types in the new table must not be defined.
Explanation
The correct answer is d. The data types in the new table must not be defined. Answers a and b are incorrect because the statements outside of the column definitions are syntactically correct. Answer c is incorrect because a WHERE clause can be used in a CREATE TABLE subselect.
When can an index be placed on a view?
A) When you only SELECT from the view
B) When you only DELETE from the view
C) When there is a WITH CHECK OPTION used to create the view
D) When you can UPDATE using the view
E) Never
Answer
E is correct: Never
Explanation
The correct answer is e. You cannot index a view. Whether you SELECT, DELETE, or UPDATE a view, index creation is not allowed, even with a WITH CHECK OPTION clause.
You have entered a three-line command in the command buffer of a SQL*Plus session. You press the Enter key twice. At the SQL prompt, you enter the following command followed by a carriage return (the Enter key): DEL What is the state of the buffer?
A) The buffer is cleared of all lines.
B) The buffer is holding the command DEL.
C) The buffer is holding the first two lines of the original text.
D) The buffer is holding the last two lines of the original text.
Answer
C is correct: The buffer is holding the first two lines of the original text.
Explanation
The correct answer is c. The buffer is holding the first three lines of the original text. Entering the DEL (delete) command in SQL*Plus removes the current line (in this case, the last line) from the buffer. Because you have three lines in the buffer, if you remove one, you have two left. Answer a is incorrect because you deleted only one line, not all lines. Answer b is incorrect because the editing commands and DEL are not placed in the buffer. Answer d is incorrect because you deleted only the current line, thus leaving the first two lines still in the buffer.
Using Table 11.3, evaluate this command: SELECT id_number FROM inventory WHERE price IN (0.25, 2.21); Which value would be displayed?
A) 36025
B) 36023
C) 43081
D) 36028 Table 11.3 Contents of the INVENTORY table. ID_NUMBER DESCRIPTION MANUFACTURER_ID QUANTITY PRICE ORDER_DATE 36025 Spike 1 in acme0525 234 2.45 12-May-97 36027 Nail 3/8 smith0626 134 0.25 15-Oct-97 36023 Chain Jones0426 245 8.25 20-Jun-97 36028 Can
Answer
D is correct: 36028 Table 11.3 Contents of the INVENTORY table. ID_NUMBER DESCRIPTION MANUFACTURER_ID QUANTITY PRICE ORDER_DATE 36025 Spike 1 in acme0525 234 2.45 12-May-97 36027 Nail 3/8 smith0626 134 0.25 15-Oct-97 36023 Chain Jones0426 245 8.25 20-Jun-97 36028 Can
Explanation
The correct answer is d. The key to evaluating the SELECT statement is to look at the IN clause. The command will return a value only if the price is either 0.25 or 2.21. Records 36027 and 36028 both meet these criteria, but the only record listed is 36028 (answer d). None of the other answers has a price value that is in the list of values specified in the IN clause.
What is a characteristic of only PL/SQL?
A) Accepts input of variables.
B) Allows shutdown of the database.
C) Allows use of exception handling routines based on error numbers.
D) None of the above.
Answer
C is correct: Allows use of exception handling routines based on error numbers.
Explanation
The correct answer is c. Only PL/SQL allows the use of exception handling routines based on error numbers. Answer a is incorrect because SQL allows input of variables, but no exception processing based on error numbers. Answer b is incorrect because PL/SQL cannot be used to shut down the database. Answer d is incorrect because answer c is correct.
When will a PL/SQL block not compile? [Choose two]
A) When an implicit cursor retrieves only one row
B) When an implicit cursor retrieves more than one row
C) When the data types within a SELECT statement are inconsistent
D) When an embedded SELECT statement is missing a required clause
Answer
C is correct: When the data types within a SELECT statement are inconsistent
D is correct: When an embedded SELECT statement is missing a required clause
Explanation
The correct answers are c and d. An error in the SELECT statement format for a cursor will result in a syntax error, prohibiting the build of the PL/SQL block. Answer a is incorrect because retrieving only one row is the correct behavior for an implicit cursor; thus, it wouldn't raise an exception or prohibit a block from being built internally. Answer b is incorrect because returning multiple rows to an implicit cursor will raise an exception when the block is executed, but it won't cause a compilation error.
What is the third stage of the system development cycle?
A) Build and document
B) Design
C) Transition
D) Strategy and analysis
E) Production
Answer
A is correct: Build and document
Explanation
The correct answer is a. The build and document step is the third stage of the system development cycle. The following is the order of the stages of the system development cycl5)
1. Strategy and analysis
2. Design
3. Build and document
4. Transition
5. Production
What function would you use to convert a numeric value into a VARCHAR2?
A) TO_CHAR
B) TO_NUM
C) TO_DATE
D) TO_VARCHAR
Answer
A is correct: TO_CHAR
Explanation
The correct answer is a. The TO_CHAR function is the only function used to convert a numeric data item into a VARCHAR2 format. Answer b is incorrect because the TO_NUM function is used to convert a VARCHAR2 into a NUMBER, not the other way around. Answer c is incorrect because TO_DATE is used to convert a CHAR, VARCHAR2, or NUMBER into a DATE value. Answer d is incorrect because TO_VARCHAR is not a valid Oracle8 function.
Which of the following activities would take place in the production phase of the system development cycle?
A) Interview users.
B) Develop ERDs.
C) Perform normal routine maintenance.
D) Code all program modules.
E) Test the system for user acceptance.
Answer
C is correct: Perform normal routine maintenance.
Explanation
The correct answer is c. In the production stage, only normal maintenance functions are performed. Answer a is incorrect because users are interviewed in the strategy and analysis phase. Answer b is incorrect because ERDs (entity relationship diagrams) are developed in the build and document phase. Answer d is incorrect because all program modules are coded in the build and document phase. Answer e is incorrect because testing the system for user acceptance is part of the transition phase.
What is the advantage of using the %TYPE attribute to declare a PL/SQL type?
A) The name of an unused column in the underlying table may change.
B) The data types or data type sizes of the underlying table columns may change by runtime.
C) The %TYPE attribute forces the data type of the underlying database table column to be what you specify.
D) All column constraints are applied to the variables declared using %TYPE.
Answer
B is correct: The data types or data type sizes of the underlying table columns may change by runtime.
Explanation
The correct answer is b. The %TYPE declaration allows flexibility in that it automatically allows an increase or decrease in the size columns in a table or allows changes in the size or data type of a column. Answer a is incorrect because you don't care about unused columns. Answer c is incorrect because nothing except an ALTER TABLE command will force a change in a table column. Answer d is incorrect because constraints are never applied to variables.
What is the purpose of the USER_ set of data dictionary views?
A) List all objects, of the specific type, that the user has created.
B) List all objects, of the specific type, that the user has been granted rights on.
C) List all objects, of the specific type, in the database.
D) List all dynamic data, of the specific type, about the database.
Answer
A is correct: List all objects, of the specific type, that the user has created.
Explanation
The correct answer is a. The purpose of the USER_ set of data dictionary views is to list all objects, of the specific type, that the user has created. Answer b is incorrect because this describes the ALL_ views and not the USER_ views. Answer c is incorrect because this describes the DBA_ views. Answer d is incorrect because this describes the V$ views.
Using Table 11.2, evaluate the following query: SELECT TO_CHAR(price, '$099999.99') FROM inventory; How is the price value 0.50 displayed?
A) 0.5
B) $0.50
C) $0.50
D) $0.50 Table 11.2 Instance chart for table INVENTORY. Column Name: ID_NUMBER DESCRIPTION MANUFACTURER_ID QUANTITY PRICE Key Type: PK FK Nulls/Unique: NN, U NN NN FK Table: MANUFACTURER FK Column: ID_NUMBER Data Type: NUM VARCHAR2 VARCHAR2 NUM NUM Le
Answer
C is correct: $0.50
Explanation
The correct answer is c, $000000.50. The leading $ and zero in the format statement tell Oracle to format the number such that if the leading numbers before the decimal are all zero, show a zero for each format character to the left of the decimal. (Because there is no answer that says, "The query will fail," you don't really need an exhibit to answer this question. You can safely assume that the PRICE column is numeric.) Answer a is incorrect because no leading zero or dollar sign is displayed. Answer b is incorrect because no leading zeros to the left of the decimal are displayed. Answer d is incorrect because this answer would be generated by the format $0.99, not the one shown.
You query the database with this command: SELECT atomic_weight FROM chart_n WHERE (atomic_weight BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95)) AND atomic_weight BETWEEN (25 AND 75) Which of the following values could the statement retrieve?
A) 51
B) 95
C) 30
D) 75
Answer
C is correct: 30
Explanation
The correct answer is c, 30. Answer a (51) is excluded by the BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95) clause. Answer b (95) is excluded by the AND atomic_weight BETWEEN (25 AND 75) clause. Answer d (75) is excluded by the atomic_weight (BETWEEN 1 AND 50) OR atomic_weight IN (25, 70, 95) clause.
In a SELECT statement, which character is used to pass in a value at runtime?
A) \
B) %
C) &
D) _ (underscore)
Answer
C is correct: &
Explanation
The correct answer is c. The ampersand character (&), either by itself or with a second ampersand, denotes substitution at runtime. Answer a is incorrect because the backslash (\) is used to escape the percent (%) and underscore (_) characters, unless something else is specified with the ESCAPE keyword. Answer b is incorrect because the percent sign (%) is the multicharacter wildcard. Answer d is incorrect because the underscore (_) is used as the single-character wildcard.
What is one of the purposes of a column constraint?
A) Enforce relationships between tables.
B) Ensure that a column value is numeric rather than character.
C) Enforce entity rules.
D) Enforce business rules.
Answer
B is correct: Ensure that a column value is numeric rather than character.
Explanation
The correct answer is b. Column constraints are used to verify values and enforce uniqueness as well as not-null. Answer a is incorrect because referential constraints are used to ensure data integrity between tables. Answer c is incorrect because there is no such thing as an entity constraint (as far as I know) to enforce entity rules. Finally, answer d is incorrect because user-defined constraints are used to enforce business rules and have nothing to do with data validation rules
Which of the following best describes a relationship?
A) A thing of significance
B) A distinct characteristic of a thing of significance
C) A named association between two things of significance
D) A description of the way that data flows
Answer
C is correct: A named association between two things of significance
Explanation
The correct answer is c. A relationship is a named association between two items of significance; this is the definition of a relationship. Answer a is incorrect because it describes an entity. Answer b is incorrect because it describes an attribute. Answer d is incorrect because it describes a data flow diagram.
Which privilege can be granted only on a DIRECTORY?
A) ALTER
B) DELETE
C) READ
D) INSERT
Answer
C is correct: READ
Explanation
The correct answer is c. The only allowed grant on a DIRECTORY is READ. Answers a, b, and d are incorrect because these privileges cannot be granted on a DIRECTORY.
Examine Table 11.6. Which value is displayed if you query the database with the following command? SELECT COUNT(DISTINCT(description)) FROM inventory;
A) 8
B) 1
C) 4
D) COUNT returns an error if it is not run against a primary key
Answer
C is correct: 4
Explanation
The correct answer is c. Four rows in the table are returned by the query. Answer a is incorrect because there aren't eight rows in the table. Answer b is incorrect because there is more than one row in the table with a unique description. Answer d is incorrect because you can count on any column in a table.
Evaluate this function created with SQL*Plus: CREATE OR REPLACE FUNCTION raise_price (start_value IN NUMBER) RETURN number IS BEGIN RETURN (start_value * 1.75); END lower_price; Why will this function cause an error?
A) A clause is missing.
B) The END clause is incorrect.
C) A keyword is missing.
D) The parameter mode should not be specified.
E) The CREATE OR REPLACE statement is invalid.
Answer
B is correct: The END clause is incorrect.
Explanation
The correct answer is b. The END clause specifies a function name of lower_price, but the CREATE OR REPLACE command specifies it to be raise_price. All the other lines are syntactically correct, so all the other answers are incorrect.
PRINT - OVER
Which statement would you use to query the database for the quantity and description of each item that was ordered before June 1, 1999, and whose price is less than 0.25 or greater than 10.00?
A) SELECT quantity, description FROM inventory WHERE price BETWEEN 0.25 and 10.00 OR order_date < '01-jun-1999';
B) SELECT quantity, description FROM inventory WHERE ( price < 0.25 OR price > 10.00) AND order_date<'01-jun-1999';
C) SELECT quantity, description FROM inventory WHERE price < 0.25 OR price > 10.00 AND order_date > '01-jun-1999';
D) SELECT quantity, description FROM inventory WHERE price IN (0.25, 10.00) OR order_date < '01-jun-1999';
Answer
B is correct: SELECT quantity, description FROM inventory WHERE ( price < 0.25 OR price > 10.00) AND order_date<'01-jun-1999';
Explanation
The correct answer is b. Answer a is incorrect because this statement uses a BETWEEN, and thus doesn't check for inequality (greater or less than). Answer c is incorrect because after order_date it uses the greater-than operator (>) and not the less-than operator (<), as would be required to find a date before the specified value. Answer d is incorrect because it limits the values to only those that are 0.25 or 10.00, not a range of values.
What is the purpose of a referential integrity constraint?
A) Enforce business rules.
B) Ensure that entities are internally consistent.
C) Validate data entries of a specified type.
D) Enforce the rule that a child foreign key must have a valid parent primary key.
Answer
D is correct: Enforce the rule that a child foreign key must have a valid parent primary key.
Explanation
The correct answer is d. Answer d-enforce the rule that a child foreign key must have a valid parent primary key-is actually the definition of a referential integrity constraint. Answer a is incorrect because a user-defined constraint is used to enforce business rules. Answer b is incorrect because entity constraints don't exist. Answer c is incorrect because a column constraint is used to validate data entry.
What is the purpose of the PL/SQL FETCH command?
A) To define a cursor to be used later
B) To retrieve values from the active set into local variables
C) To call the rows identified by a cursor query into the active set
D) To release the memory used by the cursor
Answer
B is correct: To retrieve values from the active set into local variables
Explanation
The correct answer is b. The FETCH command retrieves values returned by the cursor from the active set into the local variables. Answer a is incorrect because defining a cursor to be used later is the function of the CURSOR command. Answer c is incorrect because calling the rows identified by a cursor query into the active set is the function of the OPEN command. Answer d is incorrect because releasing memory used by the cursor is the function of the CLOSE command.
What is the purpose of the IN operator?
A) Compare two similar values.
B) Perform an equality comparison.
C) Evaluate a range of values.
D) Restrict results to a specified list of values.
Answer
D is correct: Restrict results to a specified list of values.
Explanation
The correct answer is d. The IN operator is used to compare a value or expression to a list of values. Answer a is incorrect because the LIKE operator is used to compare a wildcard search value against a column or expression that contains or yields similar values. Answer b is incorrect because the equal sign (=) is used to show equality, as in an equijoin, not to check a list of values. Answer c is incorrect because BETWEEN is used to compare a value or expression to a range of values, not to a list of values.
In the executable section of a PL/SQL block, you include this statement: Product.max_inventory1 := 30; Which task will this accomplish?
A) A composite variable will be assigned a value.
B) A constant will be assigned a value.
C) An index identifier will be assigned a value.
D) A record will be assigned a value.
Answer
D is correct: A record will be assigned a value.
Explanation
The correct answer is d. The format of the declaration shows that you're dealing with a record because dot notation indicates that a record type is being used. Answer a is incorrect because the statement does not contain a composite variable. Answer b is incorrect because nowhere do you see the keyword CONSTANT in the declaration. Answer c is incorrect because you are not dealing with an index identifier.
Use Tables 11.7 and 11.8 to evaluate this command: INSERT INTO inventory (id_number, manufacturer_id) VALUES (56023,'beth104ss'); Which type of constraint will be violated?
A) Check
B) Not null
C) Primary key
D) Foreign key
Answer
B is correct: Not null
Explanation
The correct answer is b. The not-null constraint will be violated. Notice that the DESCRIPTION column is missing from the INSERT statement. DESCRIPTION has a not-null constraint, so you must include it in any INSERT activity on the table. Answer a is incorrect because none of the table items show a CHECK constraint as being assigned. Answer c is incorrect because the primary key is being inserted. Answer d is incorrect because a value for the foreign key column MANUFACTURER_ID is specified; also, because the exhibits don't show that the value exists in the MANUFACTURER table, you have to assume that it does.
Which section of a PL/SQL routine contains functions for error trapping?
A) Declarative
B) Definition
C) Exception
D) Executable
Answer
C is correct: Exception
Explanation
The correct answer is c. The exception section is used specifically to trap errors. Answer a is incorrect because the declarative section of a PL/SQL routine is used to define variables, cursors, and exceptions. Answer b is incorrect because the definition section specifies the PL/SQL object type, its name, and the input and/or output variables. Answer d is incorrect because the executable section contains the procedural logic and performs the processing for the PL/SQL object; although it may RAISE exceptions, it doesn't process them.
Which command would you use to remove all the rows from the isotope table and not allow rollback?
A) DROP TABLE isotope;
B) DELETE isotope;
C) TRUNCATE TABLE isotope;
D) There is no way to remove all rows and not allow rollback
Answer
C is correct: TRUNCATE TABLE isotope;
Explanation
The correct answer is c. The TRUNCATE command removes all of the rows from a table. Because it is a DDL command, it does implicit commits, thus not allowing rollback operations. Answer a is incorrect because the DROP command would remove the entire table. Answer b is incorrect because the DELETE command, which can delete rows, allows rollback because it is not a DDL command. Answer d is incorrect because answer b is correct.
You query the database with this command: SELECT id_number, (quantity - 100 / 0.15 - 35 + 20) FROM inventory; Which expression is evaluated first?
A) quantity - 100
B) 0.15 - 35
C) 35 + 20
D) 100 / 0.15
Answer
D is correct: 100 / 0.15
Explanation
The correct answer is d because multiplication (*) and division (/) are evaluated first in the hierarchy of operations. The other two operators shown (+ and -) are below division in the hierarchy of operations.
You write a SELECT statement with two join conditions. What is the maximum number of tables you have joined together without generating a Cartesian product?
A) 0
B) 4
C) 2
D) 3
Answer
D is correct: 3
Explanation
The correct answer is d. You can determine the minimum number of joins based on the formula n-1, where n is the number of tables to be joined. Therefore, with two join conditions, the maximum number of tables that could be joined is three. Answer a is incorrect because a zero-table join is not possible. Answer b is incorrect because a four-table join would require three join conditions. Answer c is incorrect because, although you could use two join conditions to join two tables, the question specifically asks for the maximum number that could be joined without causing a Cartesian product.
Based on Table 11.1, evaluate this UPDATE statement: UPDATE inventory SET description = 'Sold Out' WHERE id_number = 'A12345' AND quantity = 0; Which clause will cause an error?
A) UPDATE inventory
B) SET description = 'Sold Out'
C) WHERE id_number = 'A12345'
D) AND quantity = 0;
E) None of the above
Answer
C is correct: WHERE id_number = 'A12345'
Explanation
The correct answer is c. The WHERE clause will cause an error. After examining the exhibit, you should notice that the ID_NUMBER column is a NUMBER data-type column. Attempting to compare this with 'A12345' would result in an error because the letter "A" cannot be implicitly converted to a number. The other lines have the correct syntax, so answers a, b, and d are incorrect. Answer e ("none of the above") is incorrect because answer c is correct.
Which two operators cannot be used in an outer join condition? [Choose two]
A) =
B) IN
C) AND
D) OR
Answer
B is correct: IN
D is correct: OR
Explanation
The correct answers are b and d. The IN operator and the OR operator cannot be used in an outer join. Answer a is incorrect because an equal sign (=) can be used in an outer join. Answer c is incorrect because an AND operator can also be used in an outer join.
For which of the following would you use the ALTER TABLE...MODIFY option?
A) Add a column to the table.
B) Disable a table constraint.
C) Drop a table column.
D) Increase the precision of a numeric column.
Answer
D is correct: Increase the precision of a numeric column.
Explanation
The correct answer is d. The MODIFY option of the ALTER TABLE command is used only to change the characteristics or the data type of a column. Answer a is incorrect because adding a column to the table must be done with an ADD clause. Answer b is incorrect because disabling a table constraint is not allowed in this version of Oracle. Answer c is incorrect because dropping a table column is not allowed.
1.Which of the following is a purpose of the user-defined constraint? (one or more may be correct)
A) To enforce not-null restrictions
B) To enforce referential integrity
C) To enforce business rules
D) To take action based on insertions, updates, or deletions in the base table
Answer:
C is correct: To enforce business rules
Explanation:
The correct answer is c. User-defined constraints are used to enforce business rules. Answer a is incorrect because enforcing not-null restrictions is the purpose of a column constraint. Answer b is incorrect because enforcing referential integrity is the purpose of a referential integrity constraint. Answer d is incorrect because taking action based on insertions, updates, or deletions in the base table is the purpose of a trigger.
2.You write a SELECT statement with two join conditions. What is the maximum number of tables you have joined together without generating a Cartesian product? (one or more may be correct)
A) 0
B) 4
C) 2
D) 3
Answer:
D is correct: 3
Explanation:
The correct answer is d. You can determine the minimum number of joins based on the formula n-1, where n is the number of tables to be joined. Therefore, with two join conditions, the maximum number of tables that could be joined is three. Answer a is incorrect because a zero-table join is not possible. Answer b is incorrect because a four-table join would require three join conditions. Answer c is incorrect because, although you could use two join conditions to join two tables, the question specifically asks for the maximum number that could be joined without causing a Cartesian
3)Evaluate this command: CREATE FORCE VIEW isotope_groups AS SELECT element, group_id, count(*) isotopes FROM chart_n WHERE atomic_weight>50 GROUP BY element,group_id ORDER BY atomic_weight; Which clause will cause an error? (one or more may be correct)
A) AS SELECT isotope, group_id
B) FROM chart_n
C) WHERE atomic_weight>50
D) ORDER BY atomic_weight;
Answer:
D is correct: ORDER BY atomic_weight;
Explanation:
The correct answer is d because you cannot use ORDER BY in a view. Answers a, b, and c are incorrect because they are syntactically correct.
4) Examine Table 11.6. Which value is displayed if you query the database with the following command? SELECT COUNT(DISTINCT(description)) FROM inventory; (one or more may be correct)
A) 8
B) 1
C) 4
D) COUNT returns an error if it is not run against a primary key
Answer:
C is correct: 4
Explanation:
The correct answer is c. Four rows in the table are returned by the query. Answer a is incorrect because there aren't eight rows in the table. Answer b is incorrect because there is more than one row in the table with a unique description. Answer d is incorrect because you can count on any column in a table.
5)What is the purpose of the SQL*Plus command GET?
A) Get the contents of a previously saved operating system file into the buffer.
B) Get a printer assignment.
C) Get the contents of the buffer for editing.
D) Return a storage location for the buffer contents.
Answer:
A is correct: Get the contents of a previously saved operating system file into the buffer.
Explanation:
The correct answer is a. The purpose of the SQL*Plus GET command is to get the contents of a previously saved operating system file into the buffer. Answer b is incorrect because getting a printer assignment is the purpose of the SPOOL command. Answer c is incorrect because getting the contents of the buffer for editing is the purpose of the LIST command. Answer d is incorrect because returning a storage location for the buffer contents is not a function of any SQL*Plus command.
6)Which type of PL/SQL statement would you use to increase the price values by 10 percent for items with more than 2,000 in stock and by 20 percent for items with fewer than 500 in stock?
A) An IF...THEN...ELSE statement
B) A simple INSERT loop
C) A simple UPDATE statement
D) A WHILE loop
Answer:
A is correct: An IF...THEN...ELSE statement
Explanation:
The correct answer is a. In this question, you are asked to perform conditional tests and take action based on the results of the test. The only PL/SQL structure capable of this is the IF...THEN...ELSE statement. Answer b is incorrect because a simple INSERT loop wouldn't use a condition complex enough to handle the conditions specified. Answer c is incorrect because a simple UPDATE statement couldn't do a conditional update as specified. Answer d is incorrect because a WHILE loop wouldn't properly handle the update specified.
7)In the executable section of a PL/SQL block, you include these statements: Isotope_record.isotope := 'XENON'; Isotope_record.group := 'NOBLE GAS'; Which task will be accomplished?
A) A record field will be assigned a character string value.
B) A record field will be created based on the isotope table.
C) A constant will be initialized.
D) A constant will be created.
Answer:
A is correct: A record field will be assigned a character string value.
Explanation:
The correct answer is a. A record field will be assigned a character string value. Answer b is incorrect because you aren't using a %ROWTYPE, which is used to create a record based on a complete table row. Answers c and d are incorrect because you aren't using the keyword CONSTANT.
8)Evaluate this statement: SELECT a.isotope, b.gamma_energy FROM chart_n a, g_energy b WHERE a.isotope ='IODINE' AND a.isotope = b.isotope AND a.mass_no='131' Which type of join is shown?
A) Equijoin
B) Nonequijoin
C) Self-join
D) Outer join
Answer:
A is correct: Equijoin
Explanation:
The correct answer is a. An equijoin occurs when an equality condition is used to join a table to one or more other tables. Notice that two tables are joined using an equal condition, making this an equijoin. Answer b is incorrect because the statement is an equijoin, not a nonequijoin. Answer c is incorrect because the statement is an equijoin of two tables, not a self-join of one table to itself. Answer d is incorrect because the outer join symbol (+) has not been used.
9)What is the default length of a CHAR column?
A) 38
B) 255
C) 4000
D) 1
Answer:
D is correct: 1
Explanation:
The correct answer is d. The default length of a CHAR column is 1.
10)Which of the following is a use of the TO_NUMBER function?
A) Convert a VARCHAR2 value into a DATE value.
B) Convert a DATE value into a VARCHAR2 value using a specified format.
C) Convert a VARCHAR2 value into a NUMBER value.
D) Convert a specified VARCHAR2 value into a CHAR value.
Answer:
C is correct: Convert a VARCHAR2 value into a NUMBER value.
Explanation:
The correct answer is c. Converting a VARCHAR2 value into a NUMBER value is a use of the TO_NUMBER function. Answer a is incorrect because this describes the TO_DATE function. Answer b is incorrect because this describes the TO_CHAR function. Answer d is incorrect because this is an implicit conversion and doesn't require a function.
11) Using Table 11.3, evaluate this command: SELECT id_number FROM inventory WHERE price IN (0.25, 2.21); Which value would be displayed?
A) 36025
B) 36023
C) 43081
D) 36028 Table 11.3 Contents of the INVENTORY table. ID_NUMBER DESCRIPTION MANUFACTURER_ID QUANTITY PRICE ORDER_DATE 36025 Spike 1 in acme0525 234 2.45 12-May-97 36027 Nail 3/8 smith0626 134 0.25 15-Oct-97 36023 Chain Jones0426 245 8.25 20-Jun-97 36028 Can
Answer :
D is correct: 36028 Table 11.3 Contents of the INVENTORY table. ID_NUMBER DESCRIPTION MANUFACTURER_ID QUANTITY PRICE ORDER_DATE 36025 Spike 1 in acme0525 234 2.45 12-May-97 36027 Nail 3/8 smith0626 134 0.25 15-Oct-97 36023 Chain Jones0426 245 8.25 20-Jun-97 36028 Can
Explanation:
The correct answer is d. The key to evaluating the SELECT statement is to look at the IN clause. The command will return a value only if the price is either 0.25 or 2.21. Records 36027 and 36028 both meet these criteria, but the only record listed is 36028 (answer d). None of the other answers has a price value that is in the list of values specified in the IN clause.
12) Which of the following activities would take place in the production phase of the system development cycle?
A) Interview users.
B) Develop ERDs.
C) Perform normal routine maintenance.
D) Code all program modules.
E) Test the system for user acceptance.
Answer:
C is correct: Perform normal routine maintenance.
Explanation:
The correct answer is c. In the production stage, only normal maintenance functions are performed. Answer a is incorrect because users are interviewed in the strategy and analysis phase. Answer b is incorrect because ERDs (entity relationship diagrams) are developed in the build and document phase. Answer d is incorrect because all program modules are coded in the build and document phase. Answer e is incorrect because testing the system for user acceptance is part of the transition phase.
13) You query the database with this command: SELECT atomic_weight FROM chart_n WHERE (atomic_weight BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95)) AND atomic_weight BETWEEN (25 AND 75) Which of the following values could the statement retrieve?
A) 51
B) 95
C) 30
D) 75
Answer:
C is correct: 30
Explanation:
The correct answer is c, 30. Answer a (51) is excluded by the BETWEEN 1 AND 50 OR atomic_weight IN (25, 70, 95) clause. Answer b (95) is excluded by the AND atomic_weight BETWEEN (25 AND 75) clause. Answer d (75) is excluded by the atomic_weight (BETWEEN 1 AND 50) OR atomic_weight IN (25, 70, 95) clause.
14) Evaluate this procedure: CREATE OR REPLACE FUNCTION found_isotope (v_energy_line IN BOOLEAN, v_proper_ratio IN BOOLEAN) RETURN NUMBER IS Ret_val NUMBER; BEGIN IF (v_energy_line AND v_proper_ratio) THEN ret_val:=1; ELSIF NOT (v_energy_line AND v_proper_ratio) THEN ret_val:=2; ELSIF (v_energy_line AND v_proper_ratio) IS NULL THEN ret_val:=-1; END IF; RETURN ret_val; END; If v_energy_line equals TRUE, and v_proper_ratio equals NULL, which value is assigned to ret_val?
A) 1
B) 2
C) -1
D) None of the above
Answer:
B is correct: 2
Explanation:
The correct answer is b. A combination of NULL and TRUE or of NULL and FALSE will result in a FALSE (a value of 2 in our function); a combination of TRUE and TRUE will result in a TRUE; and a combination of FALSE and FALSE will result in a FALSE. A combination of TRUE and FALSE also results in a FALSE. Answer a is incorrect because both conditions would have to be TRUE for the result to be TRUE (corresponding to 1 in our function). Answer c is incorrect because both conditions would have to be NULL for the answer to be -1, which corresponds to a NULL value. Answer d is incorrect because answer b is correct
15) Which statement is true about the TRUNCATE TABLE command?
A) It disables constraints in the target table.
B) It removes the target table from the database.
C) It can reset the highwater mark for a table.
D) Data removed is recoverable via the ROLLBACK command.
Answer:
C is correct: It can reset the highwater mark for a table.
Explanation:
The correct answer is c. The TRUNCATE TABLE command can reset the highwater mark for a table if the REUSE STORAGE clause is not used. Answer a is incorrect because TRUNCATE doesn't disable constraints and can't be used with active constraints in place. Answer b is incorrect because TRUNCATE removes data, not tables. Answer d is incorrect because TRUNCATE is a DDL statement and can't be rolled back with a ROLLBACK command.
16) Which character can be used in a table name if the name is not placed inside double quotes?
A) %
B) *
C) #
D) @
Answer:
C is correct: #
Explanation:
The correct answer is c because the only character that can be used is the pound sign (#). Answer a is incorrect because the percent sign (%) is a restricted character used for multicharacter wildcards. Answer b is incorrect because the asterisk (*) is used to denote multiplication and thus is a reserved character. Answer d is incorrect because the at sign (@) is used as a special character in SQL*Plus and thus is a reserved character
17) Evaluate this command: SELECT i.isotope, g.calibration FROM chart_n i, gamma_calibrations g WHERE i.energy = g.energy; What type of join is the command?
A) Equijoin
B) Nonequijoin
C) Self-join
D) The statement is not a join query
Answer:
A is correct: Equijoin
Explanation:
The correct answer is a. Because the SELECT statement is using an equality test (using an equal sign), this is an equijoin operation. Answer b is incorrect because this is not a not-equal (!=, <>) type of join. Answer c is incorrect because a self-join is a table joined to itself, and this statement has two tables being joined. Answer d is incorrect because answer a is correct.
18) Evaluate this command: ALTER TABLE customer DISABLE CONSTRAINT pk_customer CASCADE; Which task would this command accomplish?
A) Delete only the primary key values.
B) Disable all dependent integrity
C) Disable only the primary key constraint.
D) Alter all dependent integrity constraint values.
Answer:
B is correct: Disable all dependent integrity constraints.
Explanation:
The correct answer is b. The command disables the primary key constraint and cascades this to disable all dependent constraints. Answer a is incorrect because no data is deleted by this statement. Answer c is incorrect because this command disables the primary key and all dependent constraints. Answer d is incorrect because, once again, no values are altered; only constraints are altered.
19) What is a characteristic of only PL/SQL?
A) Accepts input of variables.
B) Allows shutdown of the database.
C) Allows use of exception handling routines based on error numbers.
D) None of the above.
Answer:
C is correct: Allows use of exception handling routines based on error numbers.
Explanation:
The correct answer is c. Only PL/SQL allows the use of exception handling routines based on error numbers. Answer a is incorrect because SQL allows input of variables, but no exception processing based on error numbers. Answer b is incorrect because PL/SQL cannot be used to shut down the database. Answer d is incorrect because answer c is correct.
20) Which two characters require the ESCAPE option to be used as literals?
A) _
B) $
C) /
D) %
Answer:
A is correct: _
D is correct: %
Explanation:
The correct answers are a and d. The underscore (_) is used as a single-character wildcard, and the percent sign (%) is used as a multicharacter wildcard; both must be escaped to be used as literals in a LIKE clause. The dollar sign ($), answer b, and the forward slash (/), answer c, can be used as literals, so they do not have to be escaped.
21) Which statement would you use to query the database for the quantity and description of each item that was ordered before June 1, 1999, and whose price is less than 0.25 or greater than 10.00?
A) SELECT quantity, description FROM inventory WHERE price BETWEEN 0.25 and 10.00 OR order_date < '01-jun-1999';
B) SELECT quantity, description FROM inventory WHERE ( price < 0.25 OR price > 10.00) AND order_date<'01-jun-1999';
C) SELECT quantity, description FROM inventory WHERE price < 0.25 OR price > 10.00 AND order_date > '01-jun-1999';
D) SELECT quantity, description FROM inventory WHERE price IN (0.25, 10.00) OR order_date < '01-jun-1999';
Answer:
B is correct: SELECT quantity, description FROM inventory WHERE ( price < 0.25 OR price > 10.00) AND order_date<'01-jun-1999';
Explanation:
The correct answer is b. Answer a is incorrect because this statement uses a BETWEEN, and thus doesn't check for inequality (greater or less than). Answer c is incorrect because after order_date it uses the greater-than operator (>) and not the less-than operator (<), as would be required to find a date before the specified value. Answer d is incorrect because it limits the values to only those that are 0.25 or 10.00, not a range of values.
22) Which two operators cannot be used in an outer join condition? [Choose two]
A) =
B) IN
C) AND
D) OR
Answer:
B is correct: IN
D is correct: OR
Explanation:
The correct answers are b and d. The IN operator and the OR operator cannot be used in an outer join. Answer a is incorrect because an equal sign (=) can be used in an outer join. Answer c is incorrect because an AND operator can also be used in an outer join.
23) Evaluate this command: CREATE TABLE purchase_items (id_number NUMBER(9), description VARCHAR2(25)) AS SELECT id_number, description FROM inventory WHERE quantity < 10; Why will this statement cause an error?
A) A clause is missing.
B) A keyword is missing.
C) The WHERE clause cannot be used when you're creating a table.
D) The data types in the new table must not be defined.
Answer:
D is correct: The data types in the new table must not be defined.
Explanation:
The correct answer is d. The data types in the new table must not be defined. Answers a and b are incorrect because the statements outside of the column definitions are syntactically correct. Answer c is incorrect because a WHERE clause can be used in a CREATE TABLE subselect.
24) What will the following operation return? [Choose two] SELECT TO_DATE('01-jan-00') - TO_DATE('01-dec-99') FROM dual;
A) 365 if the NLS_DATE_FORMAT is set to 'DD-mon-RR'
B) A VARCHAR2 value
C) An error; you can't do this with dates
D) -36493 if the NLS_DATE_FORMAT is set to the default value
Answer:
A is correct: 365 if the NLS_DATE_FORMAT is set to 'DD-mon-RR'
D is correct: -36493 if the NLS_DATE_FORMAT is set to the default value
Explanation:
The correct answers are a and d. When two dates are subtracted, you receive a numeric value that corresponds to the number of days between the dates-either positive if the first date is greater than the second, or negative if the first date is less than the second. Depending on the value of the NLS_DATE_FORMAT, the 00 will be either 1900 (default of dd-mon-yy) or 2000 (if set to dd-mon-rr), so both 365 and -36493 could result. Answer b is incorrect because date arithmetic returns a date or a number, not a ARCHAR2 value. Answer c is incorrect because answers a and d are correct.
25) What is the purpose of a referential integrity constraint?
A) Enforce business rules.
B) Ensure that entities are internally consistent.
C) Validate data entries of a specified type.
D) Enforce the rule that a child foreign key must have a valid parent primary key.
Answer:
D is correct: Enforce the rule that a child foreign key must have a valid parent primary key.
Explanation:
The correct answer is d. Answer d-enforce the rule that a child foreign key must have a valid parent primary key-is actually the definition of a referential integrity constraint. Answer a is incorrect because a user-defined constraint is used to enforce business rules. Answer b is incorrect because entity constraints don't exist. Answer c is incorrect because a column constraint is used to validate data entry.
26) When can an index be placed on a view?
A) When you only SELECT from the view
B) When you only DELETE from the view
C) When there is a WITH CHECK OPTION used to create the view
D) When you can UPDATE using the view
E) Never
Answer:
E is correct: Never
Explanation:
The correct answer is e. You cannot index a view. Whether you SELECT, DELETE, or UPDATE a view, index creation is not allowed, even with a WITH CHECK OPTION clause.
27) Use Tables 11.7 and 11.8 to evaluate this command: INSERT INTO inventory (id_number, manufacturer_id) VALUES (56023,'beth104ss'); Which type of constraint will be violated?
A) Check
B) Not null
C) Primary key
D) Foreign key
Answer :
B is correct: Not null
Explanation:
The correct answer is b. The not-null constraint will be violated. Notice that the DESCRIPTION column is missing from the INSERT statement. DESCRIPTION has a not-null constraint, so you must include it in any INSERT activity on the table. Answer a is incorrect because none of the table items show a CHECK constraint as being assigned. Answer c is incorrect because the primary key is being inserted. Answer d is incorrect because a value for the foreign key column MANUFACTURER_ID is specified; also, because the exhibits don't show that the value exists in the MANUFACTURER table, you have to assume that it does.
28) Which section of a PL/SQL routine contains functions for error trapping?
A) Declarative
B) Definition
C) Exception
D) Executable
Answer:
C is correct: Exception
Explanation:
The correct answer is c. The exception section is used specifically to trap errors. Answer a is incorrect because the declarative section of a PL/SQL routine is used to define variables, cursors, and exceptions. Answer b is incorrect because the definition section specifies the PL/SQL object type, its name, and the input and/or output variables. Answer d is incorrect because the executable section contains the procedural logic and performs the processing for the PL/SQL object; although it may RAISE exceptions, it doesn't process them.
29) What function would you use to convert a numeric value into a VARCHAR2?
A) TO_CHAR
B) TO_NUM
C) TO_DATE
D) TO_VARCHAR
Answer
A is correct: TO_CHAR
Explanation:
The correct answer is a. The TO_CHAR function is the only function used to convert a numeric data item into a VARCHAR2 format. Answer b is incorrect because the TO_NUM function is used to convert a VARCHAR2 into a NUMBER, not the other way around. Answer c is incorrect because TO_DATE is used to convert a CHAR, VARCHAR2, or NUMBER into a DATE value. Answer d is incorrect because TO_VARCHAR is not a valid Oracle8 function.
30) You attempt to create a view with this command: CREATE VIEW parts_view AS SELECT id_number, description, sum(quantity) FROM inventory WHERE id_number = 1234 GROUP BY id_number; Which clause causes an error?
A) CREATE VIEW parts_view
B) AS SELECT id_number, description, sum(quantity)
C) FROM inventory
D) WHERE id_number = 1234
E) GROUP BY id_number;
Answer:
E is correct: GROUP BY id_number;
Explanation:
The correct answer is e. The GROUP BY clause will cause an error because it doesn't include the DESCRIPTION column. All of the other lines are syntactically correct, so all the other answers are incorrect
31) You query the database with this command: SELECT manufacturer_desc FROM manufacturer WHERE manufacturer_id LIKE '%F\%B\%I\_%' ESCAPE '\' / For which character pattern will the LIKE operator be searching?
A) F%B%I_
B) FBI_
C) F\%B\%I%_
D) F\B\I\_
Answer:
A is correct: F%B%I_
Explanation:
The correct answer is a. The backslashes are used to escape the percent signs and the underscore, thus allowing them to be treated as literals. Answer b is incorrect because it doesn't include the percent signs (%) that have been escaped. Answer c is incorrect because the backslashes (\) would be ignored. Answer d is incorrect because the backslashes would be ignored and the percent signs (%) would be shown instead.
32) What is one of the purposes of a column constraint?
A) Enforce relationships between tables.
B) Ensure that a column value is numeric rather than character.
C) Enforce entity rules.
D) Enforce business rules.
Answer :
B is correct: Ensure that a column value is numeric rather than character.
Explanation:
The correct answer is b. Column constraints are used to verify values and enforce uniqueness as well as not-null. Answer a is incorrect because referential constraints are used to ensure data integrity between tables. Answer c is incorrect because there is no such thing as an entity constraint (as far as I know) to enforce entity rules. Finally, answer d is incorrect because user-defined constraints are used to enforce business rules and have nothing to do with data validation rules.
33) What is the third stage of the system development cycle?
A) Build and document
B) Design
C) Transition
D) Strategy and analysis
E) Production
Answer :
A is correct: Build and document
Explanation:
The correct answer is a. The build and document step is the third stage of the system development cycle. The following is the order of the stages of the system development cycl5)
1. Strategy and analysis
2. Design
3. Build and document
4. Transition
5. Production
34) What is the purpose of the IN operator?
A) Compare two similar values.
B) Perform an equality comparison.
C) Evaluate a range of values.
D) Restrict results to a specified list of values.
Answer:
D is correct: Restrict results to a specified list of values.
Explanation:
The correct answer is d. The IN operator is used to compare a value or expression to a list of values. Answer a is incorrect because the LIKE operator is used to compare a wildcard search value against a column or expression that contains or yields similar values. Answer b is incorrect because the equal sign (=) is used to show equality, as in an equijoin, not to check a list of values. Answer c is incorrect because BETWEEN is used to compare a value or expression to a range of values, not to a list of values.
35) Which of the following is executed automatically?
A) Anonymous PL/SQL block
B) Function
C) Procedure
D) Trigger
Answer :
D is correct: Trigger
Explanation:
The correct answer is d. Triggers are associated with tables and are automatically fired on specified actions against the table. Answer a is incorrect because an anonymous PL/SQL block must be called into the command buffer and executed; it is not executed automatically. Answer b is incorrect because functions must be implicitly called by a user, procedure, trigger, or other function, so they are not executed automatically. Answer c is incorrect because procedures must be implicitly called by a user, procedure, trigger, or other function, so they are not executed automatically.
36) What is the purpose of the USER_ set of data dictionary views?
A) List all objects, of the specific type, that the user has created.
B) List all objects, of the specific type, that the user has been granted rights on.
C) List all objects, of the specific type, in the database.
D) List all dynamic data, of the specific type, about the database.
Answer :
A is correct: List all objects, of the specific type, that the user has created.
Explanation:
The correct answer is a. The purpose of the USER_ set of data dictionary views is to list all objects, of the specific type, that the user has created. Answer b is incorrect because this describes the ALL_ views and not the USER_ views. Answer c is incorrect because this describes the DBA_ views. Answer d is incorrect because this describes the V$ views.
37) What is the advantage of using the %TYPE attribute to declare a PL/SQL type?
A) The name of an unused column in the underlying table may change.
B) The data types or data type sizes of the underlying table columns may change by runtime.
C) The %TYPE attribute forces the data type of the underlying database table column to be what you specify.
D) All column constraints are applied to the variables declared using %TYPE.
Answer :
B is correct: The data types or data type sizes of the underlying table columns may change by runtime.
Explanation:
The correct answer is b. The %TYPE declaration allows flexibility in that it automatically allows an increase or decrease in the size columns in a table or allows changes in the size or data type of a column. Answer a is incorrect because you don't care about unused columns. Answer c is incorrect because nothing except an ALTER TABLE command will force a change in a table column. Answer d is incorrect because constraints are never applied to variables.
