Hello friends if you are looking for OC PLSQL SE AD MCQ | OC PLSQL SE AD Multiple choice questions | OC PLSQL SE AD Objective type questions | OC PLSQL SE AD Question answers dumps | OC PLSQL SE AD Accenture dumps
Please use ‘FIND IN PAGE OPTION’ in google chrome
1.What is the output of below query
SELECT SUBSTR('PARAMESHWAR',6,6)FROM DUAL;
a.ESHWAR(opt a)
2.Which of the following options is/ are valid with respect to explict cursor?
a.All of the above
3.Consider there are 10 rows in emp table
1.Delete from emp;
2.Commit;
3.rollback;
4.select count(*) from emp;
Which is the output of 4th SQL statement?
a.0
4.Which background process is involved in writing the content to and from data buffer cahche
a.DBWN(opt b)
5.Which of the folowing is the valied data dictionary views of constant database object?
Choose the approprate option
a.USER_CONSTRAINTS, USER_CONS_COLUMNS( opt Both)
6.What is the result of below selected statement?
Select instr('This is test string'.'string')from dual;
a.14
7.What is the output of below query
SELECT INSTR(PARAMASWHWER','ar',1,2)FROM DUAL:
a.10
8.Which of the following SQL statement executes with out an error?
a.opt b (Select 1 example....) opts lo annitikanna short di
9.Which of the folowing statement creates a new table EMP1 from exixting table EMP
a.CREATE TABLE EMP1 AS SELECT*FROM EMP;
10.What is the output of below query
SELECT LPAD("PARAM",10,'*')FROM DUAl;
a.*****PARAM
11.Considering the presence of the tables named 'departments' and 'emp', chooses the correct output for the below pr
ogram?
declare
type l1 is ref coursor;
v1 l1;
v_emprec emp%rowtype'
v_deprec......
begin
open v1 for select* feom departments;
close v1;
open v1 for select*emp;
close v1;
end;
/
a.Program executes sucessfully
12.A aggregate function ignores_____ value by default.Fill the blank with appropreate apt?
a.Null
13.Which of the below options is TRUE about the NVL function?
choose most appropriate opt
a.Allows the user to specift alternode value for null values(opt a)
14.Consider the SQL statemets below execute in a sequence
CREATE TABLE dept(deptno NUMBER(2),dname VARCHAR2(14), loc VARCHAR2(13));
ROLLBACK;
DESC DEPT
Which of the option is true?
a.
15.A PL/SQL subprogram can have multiple cursors with different names open at the same time state
TRUE/FALSE?
a.TRUE
16.A PL/SQL variable that stores a coloumn value must have the same name as this coloumn state
TRUW/FALSE?
a.FALSE
17.Which of the following arthematic operations on a date coloumn named HIRE_DATE of EMP is valid
a.select HIRE_DATE+2 FROM EMP;
18.When i apply a Primary Key constraint on a coloumn of table, which index is applied by default?
a.UNIQUE
19.identify the correct syntax of using SAMEPOINT and ROLLBACK
a.SQL>SAVEPOINT sp1;
SQL>ROLLBACK TO sp1;
20.Which data dictionary table is used to verify thecoloumn name on which a costraint is applied?
a.USER_CONS_COLOUMNS
21.What is the result of below select statement?
Select transtiate('this is test','test','nest')from dual;
a.nhis is nesn
22.Whch of the following check constraint is correct?
a.Create table emp(empno number, sal number, comm number,CONSTRAINT check_sal
check(sal+comm>1000))
23.Which of the following SQL statement executes without an error?
a.select empno from emp
union select sal from emp;
24.What is the input of the below Query
................('...','MAN','DAM')FROM DUAL;
a.DAMAGER
25.What is the output of the below qurey
SELECT RTRIM('RAMESH***)FROM DUAL;
a.RAMESH***
26.Create Emp table .........opt of below PL/SQL coe?
.....
...........EMP SELECT*FROM EMP,
...
...........................
.....
A.error: invalied cursor
27.which of the following option lets the user to lock the rwos explicitly?
a.EXPLICIT_LOCK
28.In a Select statement, which one of these clauses checkes for rows and does not compare coloumns.Choose the m
ost appropriate apt?
a.EXISTS
29.Compare the following DCL statements
1. Create user user1 identified by passwor;
2. Grant create section to user 1;
After the execution of two statements,which user login and try to execute the below statement
What will be the result?
Create table text1(c1 number);
a.insufficient priviliges
30.................
a.database writer
31.The EMPLOYEES table has these coloumns
LAST_NAME VARCHAR2(35)
SALARY NUMBER(8,2)
HIRE_DATE DATE
Management wants to add the default value to the salary coloumn. You paln to alter the table by using SQL statemen
ts
ALTER TABLE EMPLOYEES
......(SALARY DEFAULT 5000);
Which is true about the ALTER statement?
a.Coloumn definations cannot be attened to add ...... variables for coloumns with a .... type(opt d)
32.Considering the presence of 'salary' coloumn in 'emp' table Choose the correct output prog gives?
declare
type l1 is table of number;
v1 l1;
..... c1 is select salary from emp;
begin
open c1
.... c1 bulk collect into v1;
...................
.................
end loop;
close c1;
end;
/
a.It presects all that sustains of employees
33.In Oracle PLSQL, What is the name of implicit Cursor?
a.SQL
34.
35.A ............... on table column.Which omne of the below statements is true?
a.If we don't insert any value then default value will be considered
36.
37.Which of the following Alter statements will execute correctly for EMP table considering table is empty with col
oumns EMPNO,ENAME(where EMPNO is primary key)
1.ALTER TABLE EMP ADD COLOUMN10 NUMBER;
2.ALTER TABLE EMP ADD CONSTRAINT PK_C10 PRIMARY KEY(ename);
a.Only1
38.Asuming EMP is an existing table with oe coloum named ENAME what is wrong with the below ALTER consid
ering table empty?
ALTER TABLE emp
MODIFY ename varchar2(750 DEFAULT'Seattle'NOT NULL;
a.There is noting wrong with this statement
39.When I apply a primary key constant on the coloumn of table which index is applied by default?
a.UNIQUE
40.Identify the sequence of operations that PL/SQL performs inorder to execute a SQL statement in a program for b
oth implicit nad explicit
a.(opt a)PARSE ....OPEN EXECUTE FETCH CLOSE
41.What is the differenct between PL/SQL cursor and PL/SQL ref cursor
a.PL/SQL ref cursor is a PL/SQL type where as PL/SQL cursor is not
42.
43.Which of the following database models doesnot support many to many relationship?
a.RDBMS
44.
45.What is the output of below query
SELET TURN(^%>@*^<@)FROM DUAL:
a.D:65.28
46.Choose the right option for displaying the sum of salary of employees for the following criteria
1.some employees may not get compenssion
2.total salary is an addition of cal and comunication....
a.select sum(sal+nvl(comm,0))from emp;
47.Which of the following allows you to store data of external operating system file?
a.BFILE
48.Which of the following property guaranties that once a user is notified as sucesses in this action of database, the c
ompleted action wll persist
a.Durability
49.What is the output of the below query.
SELECT SUBSTR(PARAMESHWAR*,6,6) FROM DUAL;
a.ESHWAR
50.Which of the following is an example for declaring a strong ref cursor?
a.TYPE T1 LS REF CURSOR RETURN EMP%ROWTYPE
51.Consider the SQL stalement below and choose the nght opton
SELECT first paine, kast name, MiN(sabury)
FROM employees
GROUP BY department_
HAVING MIN(salary) >
(SELECT min(satary)
FROM employees
WHERE department_ = 100);
Choose most appropriate option.
Consider the following code snippet
A.It throws an error because the first_name and last_name are.......
52.Consider the following code snippet
DECLARE /1 insert some lines here
FETCH emp_cursor INTO emp_rec
IF emp rec ename-'SMITH THEN
DELETE FROM emp WHERE CURRENT OF emp cursor,
END IF.
END
Choose most appropriate option.
a.CURSOR emp_cursor IS SELECT * FROM EMP FOR UPDATE OF SAL
emp_rec emp_coursor%ROWTYPE
BEGIN
OPEN emp_cursor;
53.Identify the correct statement which is true regaurding SET opts
a.Uninon will give combination of two select statements execluding duplicates variables
54.Which of the following operations are used in multi row subqueries?
a.All opts are correct(IN ANY ALL)
55.Which of the following indicate the output of below query
SELECT REPLACE('PARAMESHWAR',;AR','MP')FROM DUAL;
a.PMPAMESHWMP
Can Project team customized pre configured validation rules in pivot?
YES
Validation report in pivot is in which format?
HTML
Which of the following pl/sql collection will allow index by of VARCHAR2 datatype?
ASSOCIATION ARRAY
Views create a copy of the data from the base table?
TRUE
Represent students data of discipline CSE in a college with maximum 60 seats?
ALL
which of these pl/sql collection datatype also known as associative array?
PL/SQL TABLE
Considering the presence of a column named “first_name” in ‘emp’ table.choose the output of the
below program?
ERROR AT LINE 6
Which built in cannot be used with associative array?
EXTEND
Which parameter mode in pl/sql needs a bind variable when procedure or function is?
OUT IN
Considering the presence of columns SALARY,EMPLOYEE_ID,ENAME in EMPLOYEES table
where EMPLOYEE_ID is primary key constrained column?
PROCEDURE WILL BE CREATED WITH COMPILATION ERROR AS PRIMARY KEY
CONSTRAINED COLUMN IS MISSED
Exception name in pl/sql?
NON-PREDEFINED EXCEPTIONS
Non predefined exceptions can be defined with name using.choose the most appropriate option?
PRAGMA EXCEPTION_INIT
Which of the following in pl/sql collection cannot be defined at schema level?
ASSOCIATIVE ARRAY
Which of the following package is used to implement dynamic sql?
DBMS_SQL
Which of the following is not a feature of a cursor FOR loop?
OPENING AND PARSING OF SQL STATEMENTS
Which of the following statements is true about implicit cursors?
Implicit cursors are used for SQL statements that are not named
Which line in the following statement will produce an error?
Into action_record
If left out, which of the following would cause an infinity loop to occur in a simple loop?
END loop
What is the maximum number of handlers processed before the pl/sql block is excited when
an exception occurs?
Only one
What happens when rows are found using a FETCH statement?
It causes the cursors to open
For which trigger timing can you reference the NEW and OLD qualifiers?
Oracle forms trigger
In which mode parameter lets you pass values to the subprogram beimg called?It cannot be
assigned a value.
Using the IN mode
Which of the following cannot be used with associative arrays?
Both A and B(EXTEND and TRIM)
Which of the following is not a schema object?
Public Synonyms
In the PL/SQL block below, how many rows will be inserted in the message table?
99
Any subprogram not in the package specification but coded in the package body is called a?
Private object
Which of the following has internal components that can be manipulated individually such as
elements of an array, recore=d or table?
Composite
Which of the following type of oracle pl/sql collection is used for sparse collection through
out its life time?
Associative array
Considering the presence of columns SALARY,EMPLOYEE_ID in EMPLOYEES table
andabsence of employee with EMPLOYEE_ID 100.predict the outcome of the below
function creation?
Function gets created successfully.
Which of the following can be a triggering event for a INSTEAD oftrigger in oracle
database?
DML
What does PIVOT stands for?
Project insights and visualization toolkit
1)Which of the following is the SQLCODE for exception with name TOO_MANY_ROWS?
ANS; 1422
2)What is the SQLCODE of exception with name NO_DATA_FOUND?
Ans: 100
3)Which Procedure can be used to create a customized error message? Choose most appropriate
option.
Ans: RAISE_ERROR
4)Which of the following is not a valid index by datatypes in associative arrays?
Ans: data
5)Which of the following type of exception will not have a builtin exception name in PLSQL?
Ans: Non Predefined exception
6)Considering the presence of a table STUDENT with coloumn MARKS
Predict the output of the below trigger execution.
Ans: It will raise an error as OLD and NEW
7)Which Procedure can be used to create a customized error message?
Ans: RAISE_APPLICATION_ERROR
8)Considering the presence of columns SALARY,EMPLOYEE_ID IN EMPLOYEES table and absence of
employee with EMPLOYEE_ID
Ans: Function get created Succesfully
9)Which of the following is a pre defined exception?
Ans: COLLECTION
10)Which PL/SQL Collection would fit the below scenario
Represent student data of discipline cse
Ans; ALL
11)Considering the presence of column ENAME in NEW_EMP table predict the outcome of following
trigger
Ans: Trigger Creation will throw error as you cannot use new and old in row level trigger
12)Which of the following is a bounded and dense collection in PL/SQL?
Ans; Array
13)considering the presence of columns SALARY,EMPLOYEE_ID in EMPLOYEES table and absence of
employee with EMPLOYEE_ID 100?
Ans : Function gets created Successfully
14)Can project beam customized pre configured validation rules in PIVOT?
Ans: YES
15)In RAISE_APPLICATION_ERROR what is the valid range of error number that develop can make
use of
Ans; -20999 to -20000
16)Which of the following type of exception will not have a bulitin exception name in PLSQL
Ans; Non-Predefined Exception
1. What are the different types of delete details we can have on master detail forms :– Non-isolate
2. Which of the following collection is a bounded collection :– ASSOCIATIVE ARRAY
3. What are the different default triggers created when Master deletes Property is set to :– Onpopulated-details
4. Which of the following bulletin is used to get the starting index of a PLSQL collection :– INITIAL
5. …………..is loaded n memory? :– WHEN-NEW-FORM-INSTANCE
6. In PLSQL what is the persistence of a bind variable in once declared in a session :– Exists within a
package
7. Which of the following contains the select statement associated with LOV in oracle forms :–
Record group
8. After declaring the cursor, which statement will execute the select statement associated with
the cursor :– At declaration itself the select statement gets executed
9. Which of the following is a valid canvas view :– all(content, stacked, horizontal)
10. Which of the following is not true about ref cursor :– there are two types of ref cursors, weak
and strong
11. Which of the following commands are used to create a file in unix :– cat
12. Which UNIX command will list out all the files in parent directory along with permissions :– ls l../
13. Which of the following ‘chmod’ command option (in binary format) is used to give only ‘read
and write’ permissions to a file :–
14. Which of the following is the correct order of different clauses in select statement:–
SELECT*/<COLLIST>FROM<table_name><WHERE_CLAUSE> <GROUP
BY_CLAUSE><HAVING_CLAUSE> <ORDER BY_CLAUSE>
15. Which data dictionary table contains the constraints information of columns in a table :–
16. Which of the following is not a valid data source in oracle reports :–
17. Which is not a object of paper layout in oracle reports :–
18. Which is not a part of paper report section in oracle reports :– trailer section
19. Which of the following is not a oracle report trigger :– between report
20. Which of the following builtin is used to print some message in oracle reports :–
Q. Considering the presence of columns SALARY,EMPLOYEE_ID in EMPLOYEE
Also the presence…..
CREATE OR REPLACE FUNCTION F1(P_ENO IN NUMBER (10))
RETURN NUMBER
IS
ANS. Function Creates With Compilation errors as…
Q. Consider the following code and predict the output of the program
DECLARE
TYPE Numlist is TABLE OF NUMBER
N Numlist=Numlist(10,……,80,90,100)
ANS. Deletes the elements 3 through 6
Q. Considering the presence of columns SALARY,EMPLOYEE_ID in EMPLOYEE
Table where EMPLOYEE_ID is a primary key constrained column
Predict the outcome of the below procedure creation
CREATE OR REPLACE PROCEDURE NEW_EMPLOYEE(P_ENAME VARCHAR2,P_SAL NUMBER)
IS
BEGIN
INSERT INTO EMPLOYES(ENAME,SALARY) VALUES(P_NAME,P_SAL);
COMMIT;
Ans. Procedure successfully gets created
Q. Predict the output of the below code
DECLARE
V_VAR1 NUMBER
V_VAR2 V_VAR1%TYPE
BEGIN
V_VAR2:=’A’;
Ans. Numeric or value error
Q. Predict the output of the following code
DECLARE
e_exception EXCEPTION
BEGIN
DECLARE
e_exception Exception
dbms_output_line(“HANDLED IN E_EXCEPTION HANDLING SECTION”);
WHEN OTHERS THEN
Dbms_output.put_line ()
Ans. HANDLED IN WHEN OTHERS SECTION
Q. Considering the presence of column ENAME in NEW_EMP table, predict the outcome
Of the following trigger
CREATE or REPLACE TRIGGER upper_trig BEFORE
Ans. Trigger gets Created Successfully
Q. Which of the following collection type can be created both at schema level and in PL/SQL Block
Ans. Nested Table
Q. Identify the External LOB
Choose most appropriate option
Ans. BFILE
Q. row level trigger will execute if the update statement is affecting 10 rows
Ans. 10
Q. what will happen if a function is defined in package body with out defining
The function specification in package specification
Ans. It is called as private function and you will able to aceess
Q. with respect to a condition predicate in a DML trigger, choose most appropriate option.
Ans. Exception at line number 6 as there is no employee.
Q. identify the component of a LOB that references to where the LOB value is physically stored
Choose appropriate option
Ans. LOB Locator
Q. with respect to a condiation predicate in a DML trigger, choose most appropriate option.
Ans. A Conditional predicate allows you to combine several DBM triggering
Q. Predict the outcome of the below procedure creation
CREATE OR REPLACE PROCEDURE DROP_TABLE(P_TNAME VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE DROP FROM
END;
Ans. Procedure gets created with compilation errors as there is an error at line 4
Q. which parameter mode in PL/SQL needs a bind variable when procedure or function is
Executed at schema level?
Ans. All
Q. attribute is used to declare a variable for handling table records and also for an explicit cursors
Records ..
Ans. %ROWTYPE
Q. Which of the following PLSQL collction has to be initially dense and can become sparse if nedded
Ans. Nested Table
Q. which of the following builtin is used to associate a exception name for a non-predefined
exception?
Ans. Exception_INIT()
Q. which of the following is predefined exception?
Ans. COLLECTION_NULL
Q. which of the following PL/SQL collection will allow INDEX BY of VARCHAR2 data type
Ans. Associative Array
Q. mode in PL/SQL a bind variable when procedure or function is executed at schema level?
Ans. All (in, out, out in)
Q. Oracle Pl/sql supports following data types?
Ans. BOTH (scalar, composite)
Q. which statement is true about triggers? Choose most appropriate option.
Ans. You use database trigger to fire when an INSERT statement occurs
Q. can project team customized pre-configured validation rules in PIVOT
Ans. YES
Q. SQL CODE of exception with name NO_DATA_FOUND?
Ans. 100
Q. Considering the presence of the columns named ‘ename’ and ‘sal’ absence of 1234
Ans. Exception at line 5 as there..
Q. considering the presence of columns SALARY,EMPLOYEE_ID also predict EMPLOYEE_ID 100..
Ans. Function creates with compailation error as size is mentioned…
Q. to a condition predicate in a DML trigger, choose most appropriate option.
Ans. A condition predicate allows you to combine several DML triggering events into one
Q. considering the presence of columns ENAME in NEW_EMP table, predict the outcome of the
following trigger
CREATE OR REPLACE TRIGGER upper_trig BEFORE INSERT or UPDATE OF ename on new_emp
BEGIN
new_name-upper(:old.ename)
end;
Ans.trigger gets created successfully
Q. students data of discipline CSE in a college with maximum 60
Ans. Varray
Q. Predict the following code?
DECLARE
e_exception EXCEPTION:
Ans. HANDLED IN WHEN OTHERS SECTION
Q. which of the following type of exception will not have a builtin exception name in PLSQL
Ans. NON-Predefined Exceptions
1. PL/SQL needs a ----- variable when procedure or function is --------------------OUT
2. Can project team customized pre configured validation rules in PIVOT ?
NO
3. Considering the presence of column SALARY,EMPLOYEE_ID,E_NAME in EMPLOYEE table
Where EMPLOYEE_ID is Primary key Constrained column
Predict the outcome of the below procedure creation
CREATE OR REPLACE PROCEDURE NEW_EMPLOYEE_,P E_NAME VARCHAR2.P_SAL NUMBER)
IS
BEGIN,
INSERT INTO EMPLOYEES(ENAME,SALARY) VALUES(P_ENMAE,P_SAL);
COMMIT;
END;
For as pk constrained column is missing
4. Consider EMP(EMPID.ENAME) has two rows with the below values
ROW1=(1000.'FIRST NAME')
ROW2=(1001.'SECOND NAME')
PREDICT THE OUTPUT OF BELOW PL/SOL CODE
DECLARE
TYPE type_enamo IS TABLE OF emp.enamo%TYPE;
TYPE type_empno IS TABLE OF emp.empid%TYPE;
TYPE type_emp IS RECORD (empno type_empno,ename type_ename);
v_emp type_emp:
BEGIN
SELECT empid,ename BULK COLLECT INTO v_emp FROM omp;
FOR i IN v_emp.empno.first..v_emp.empno.last
LOOP
dbms_output.put_line(v_emp.empno(i) Il ' 'II v_emp.ename(i));
END LOOP;
END;
5.HTML Validation report in PIVOT is used by
DU lead
6. Which database object cannot be nested with same type of database object?
Package
7.What will happen if a function is defined in package body with out defining the function
specification in package specification?
It is called as private function and you will not be able to access the function outside the package
8.Which of the following is Oracle PL/SQL collection cannot be defined at schema level ?
Pl/sql table
9. ________Attribute is used to declare a variable for handling table records and -------------------------%ROWTYPE
10. Which PL/SQL. collection type would fit in the below scenario Represent students data of
discipline CSE in a college with maximum 60 seats
11. Consider the following code, predict the output of the program.
DECLARE
TYPE NumList IS TABLE OF NUMBER
n NumList.=NumList(10.20,30,40,50,60,70,80,90,1001);
BEGIN
n DELETE(3,6);
END;
Deletes the elements 3 through 6
12. Oracle PL/SQL supports following data types
Both Scalar and composite
13. Consider the procedure given below
CREATE OR REPLACE PROCEDURE PROC_1 AS
num1 NUMBER(2),
num2 NUMBER(2),
ans NUMBER(3);
BEGIN
ans=num1*num2;
DBMS_OUTPUT.PUT_UINE(‘SUM IS’||ans);
END;
Assuming that the procedure is created successfully. identify the valid option that is used to
execute the procedure. Choose most appropriate option.
EXEC proc_1
14.Considering the presence of column named ‘department_name’ in 'departments' table and also
table named 'employees'. Choose the output of the below program.
declare
type t1 is table of employees%rowtype;
v1 t1;
v_deptrec departments%rowtype;
begin
select bulk collect into v1 from departments;
for i in v1.first..v1.last loop
dbms_output.put_line(v_deptrec.department_name);
end loop;
end;
Error at line number 5.
15. Which of the following is true regarding Packages?
1.Package body cannot be created without package specification
2.Package specification can be created without package body.
3.Package can have private subprogramms
1,2,and 3
16. Considering the presence of columns SALARY ,EMPLOYEE_ID in EMPLOYEES table. Also the
presence of employee with EMPLOYEE_ID 100. Predict the outcome of the below function creation.
CREATE OR REPLACE FUNCTION F1(P_ENO IN NUMBER(10))
RETURN NUMBER
IS
V_ASAL NUMBER;
BEGIN
SELECT SALARY 12 INTO V_ASAL FROM EMPLOYEES WHERE EMPLOYEE_ID ID = 100;
RETURN V_ASAL;
END;
Function creates with compilation errors as size -------mentioned for aparameter
17.
Considering the presence of a column named first_name' in 'emp' table. Choose the output of the
below program
declare
type t1 is table of varchar2(20);
v1 t1;
begin
select first_name bulk collect into v1 from emp;
dbms_output put_line(v1);
end;
error at line number –
18. ---- create a multi dimensional collection in PL/SQL?
True
19. Consider the presence of the table STUDENT with column MARKS
----------------trigger execution when an update operation is performed on MARKS
---------------------(----------SET SERVEROUTPUT ON)
CREATE OR REPLACE TRIGGER UPDATED_MARKS
---------------STUDENT
BEGIN
Dbms_output.put_line(‘old marks are’ || OLD.MARKS || ‘New marks are’ || NEW.MARKS );
END;
It will raise an error as :OLD and NEW cannot be used in this type of trigger
20. Predict the output of the following code?
DECLARE
e_exception EXCEPTION;
BEGIN
DECLARE
e exception EXCEPTION
BEGIN
RAISE e exception EXCEPTION
END;
EXCEPTION
WHEN exception THEN
Dbms_output.put_line(‘HANDLED IN E_EXCEPTION HANDLING SECTION);
WHEN OTHERS THEN
Dbms_output.put_line(‘HANDLED IN WHEN OTHERS SECTION);
END;
HANDLED IN WHEN OTHERS SECTION
1. A aggregate function ignores _____ value by default. Fill in the blank with an appropriate option.
Ans- Null
2. Which of the following database models does not support many-to-many relationships?
Ans- Hierarchical database model
3. What is the result of below select statement? Select instr(''This is test string', 'string') from dual. Ans14
4. What is the result of below select statement? SELECT translate('this is test', 'test', 'nest') from dual;
Ans- error
5. Identify the correct statement which is true regarding SET operators
Ans- union will give combination of two select statements excluding
6. Forgot To create a table without a primary key.
Ans- we can alter the table to add primary key later as long as tge table..
7. Ans- allows the user to specify alternate value for null values
8. What is the output of the below query. SELECT LPAD('PARAM', 10,'*') from dual
Ans- *****PARAM
9. In a banking application, there's a requirement to find the customer's name along with the
transaction details
Ans- SELECT T.TID,T.TDATE,T.TAMOUNT,C.NAME FROM CUSTOMER C, TRANSACTION T WHERE
C.CUSTID=T.CUSTID;
10. There are 3 columns named HIRE_DATE, SALARY AND ENAME of EMP table with DATE, NUMBER,
VARCHAR2 data types respectively. Which of the following SQL statement is valid?
Ans- All
11. What will happen when PL/SQL code triea to access a cursor which is not opened?
Ans- INVALID_CURSOR exception will be raised
12. Which of the following datatype allows you to store data of external operating system file?
Ans- BFILE
13. Which statement of cursor. Select statement will be executed?
Ans- fetch cursor_name into rec;
14. What is the output of below query SELECT ROUND(55.258,2) FROM DUAL
Ans- 55.26
15. Considering the presence of the tables named 'departments' and 'emp'. Choose the correct output
of the below program?
Ans- program executes successfully
16. What is the output of below query SELECT SUBSTR('PARAMESHWAR', 6,6) FROM DUAL;
Ans- ESHWAR
17. Which of the following option(s) is/are valid with respect to explicit cursor?
Ans- all of the above
18. Consider there are 10 rows in emp table.
1. Delete from emp; 2. Commit; 3. RollBack; Select count(*) from emp;
What is the output of the 4th SQL statement? Ans- 0
19. Which background process is involved in writing the content to and from Data Buffer Cache
Ans - DBWN
20. Which of the following is the valid data dictionary views of constraint database object?
Ans- BOTH( user_constraints, user_cons_columns)
21. SELECT INSTR(' PARAMESHWARA', 'AR', 1,2) FROM DUAL
ANS- 10
22. Which of the following statement creates a new table named EMP1 from existing table EMP
Ans- CREATE TABLE EMP1 AS SELECT * FROM EMP,
23. Which of the below options is TRUE about the NVL function?
Ans- Allows the user to specify alternate value for null values
24. A PL/SQL subprogram can have multiple cursors with different names open all the same time. Anstrue
25. A PL/SQL variable that stores a column value must have the same name as the column
Ans - false
26. Which of the following arithmetic operations on a date column name HIRE_DATE of EMP table is
valid. ANS- select HIRE_DATE+2 FROM emp;
27. When I apply a primary key constraint on a column of a table, which index is applied by default?
ANS- UNIQUE
28. Identify the correct syntax of using SAVEPOINT and ROLLBACK
ANS- SQL> SAVEPOINT sp1;
SQL> ROLLBACK TO sp1
29. Which data dictionary table is used to verify the column name on which a constraint is applied. Ansuser_cons_columns
30. Which of the following way of adding check constraint is correct
Ans- create table emp(empno number, sal number, comm number, CONSTRAINT check_sal
check(sal+comm>1000))
31. Which of the following sql statement executes without error?
Ans- select empno from emp
Union select sal from emp;
32. SELECT translate ( 'manager', 'man', 'dam') from dual
Ans- Damager
33. Select RTRIM('ramesh***') from dual;
Ans- RAMESH***
34. Which of the following option lets user to lock the rows explicitly?
Ans- Explicit_lock
35. In select statement, which one of these clauses checks for rows and does not compare columns.
Ans- exists
36. Consider the following DCL statements
Create user user1 identified by password ;
Grant create session to user1;
After the execution of the two statements, when user login and try to execute below statements what
will be the result?
Create table test1(c1 number) ;
Ans- insufficient privileges
37. The employees tables has these columns
Last_bame Varchar2(35)
Salary number(8,2)
Hire_date date
Management wants to add a default value to the salary column. You plan to alter the table by using sql
statement. Ans- column definitions cannot be altered to add DEFAULT values for columns..
38. Considering the presence of salary column in emp table. Choose the right output that below
program gives. Ans- it prints all the salaries of employees.
39. In oracle pl/sql, what is the name of implicit cursor? Ans- sql
40. If we don't insert any value then default value will be considered
41. A developer forgot to create a table without a primary key
Ans- we can alter the table to add primary key..
42. Which of the following alter statements will execute correctly for emp table considering table is
empty with columns empno,ename(where empno is primary key)
Ans- alter table emp add column10 number;
43. Assuming EMP is an existing table with one column name ENAME. What is wrong with the below
ALTER statement considering table is empty?
ALTER TABLE emp
MODIFY ename varchar2(75) DEFAULT 'Seattle' NOT NULL;
ANS- there is nothing wrong with the statement
44. When I apply a primary key constraint on a column of a table. Which index is applied by default? Ans
- UNIQUE
45. identify the sequence of operations that pl/sql performs in order to execute a sql statement in a
program for both implicit and explicit cursors.
Ans- PARSE, BIND, OPEN, EXECUTE, FETCH, CLOSE
46. What is the difference between a PL/SQL cursor and PL/SQL ref cursor?
Ans- PL/SQL REF Cursor is a PL/SQL type where as PL/SQL cursor is not
47. Which of the following datebase models does not support many-to-many relationship?
Ans- RDBMS
48. The column named SALARY in emp table is not null constrained and the constraint name is
NOT_NULL_CONST. If i wabt to allow NULL in the SALARY column going ahead, which statement needs
to be executed?
Ans- ALTER TABLE EMP DROP NOT_NULL_CONST
49. SELECT Trunc(65.286,2) from dual. Ans- 65.25
50. Choose the right query for displaying the sum of salary of all employees
1. Some employees might not get commission
2. Total salary is an addition of sal and commission columns
Ans- select sum(sal+nvl(comm, 0)) from emp;
51. Which of the following datatype allows you to store data of external operating system file?
Ans- BFILE
52. Which of the following property guarantees that once a user is notified of success in his action on
the database, the completed action will persist
Ans- Durability
53.which of the following is an example for declaring a strong ref cursor
Ans- type t1 ref cursor return emp%row type...
54. Select replace('parameshwar', 'ar', 'mp') from dual. Ans- PMPAMESHWMP
55.Which of the following operators are used in multi-row sub queries? Ans- all the options
55. Identify the correct statement which is true regarding SET Operators
Ans- union will give combination of two select statements exluding duplicate values
1.With respect to explicit cursor?
Ans: all the above
2.select count(*) from emp ; what is the output 4th sql statement ?
Ans 0
3.background process involved in writing content to and from data duffer cache
Ans : dbwn
4.data dictonary views of constraint database object ?
Ans : both
5.select instr(‘this is test string’,’string’) from dual ANS :14
14 because the string is starting at 14th letter
6.select instr('paramaeshwar','ar',1,2) from dual ANS :11
Ans : Is 11 because 1 represent start from 1 and 2nd ‘ ‘ar’
7. new table named emp1 from existing table emp
Ans : create table emp1 as select * from emp
8.select LPAD(‘PARAM’,10,’*’)
ANS :*****PARAM
9. TABLE NAMED DEPARTMENT AND EMP
ANS : PROGRAM SUCCESS FULLY EXICUTED
10. A AGGRIGATE FUNCTION IGNORES NULL VALUE BY DEAULT
11.TRUE ABOUT THE NVL FUNCTION :
ANS : ALLOWS THE USER TO SPECIFY VALUE TO THE NULL VALUE
12. A PL/SQL SUBPROGRAM CAN HAVE MULTIPLE CUSRORS WITH
ANS : TRUE
13. A PL/sql variable that stores a column value
Ans : false
14. select hire date +2 from emp;
15. when I apply a primary key constraint on a colomn of a table
Ans: unique
16. save point and rollback
Ans: save sp1;
Rollback to sp1;
17.data dictionary table is used
Ans: user_cons_colums
18. select TRANSLATE('this is test','test ','nest') from dual
19.check constraint
Ans: CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);
20. select translate(‘manager’,’man’,’dam’) from dual
Ans : damager
21. let user to lock the rows explicitly ?
Ans : explicit_lock
22. check for rows and does not compare columns .
Ans : exists
23. 1. Create user user1 identified by password;
Ans : insufficient privileges
24.
The employees table has these columns ;
SALARY DEFAULT 5000
ANS: column definition cannot be altered to add defaults ……….
25.
parse ,bind ,open,execute ,fetch ,close
26.pl/sql ref cursor is a pl/sql type
27. not support many to many relationship?
Ans : rdbms
28. select trunc(65.285,2) from dual
Ans 65.28
29. NVL( string1, replace_with )
30. data type allows you to store data external operating system file ?
Ans : BFILE
31. Which of the folowing command are used to create a file in UNIX?
ANS : CAT
32. Which of the following is the correct order of different clauses in select statement,
ANS :
33.FETCH STATEMENT OF A CURSOR …..
ANS : TRUE
Q) A fetch statement of a cursor returns multiple rows.state true or false
Ans; True
Q) Which of the following options is/are valid with respect to explicit cursor?
Ans; All of the above
Q) Consider Emp Table is empty .Predict the output of below pl/sql code?
Declare
Cursor C_Emp
Ans: 0
Q) Which of the following Statement creates a new table named EMP1 from exsisting EMP?
Ans;
Q) A PL/SQL variable that stores a column value must have the same name as the column. True/false
Ans; false
Q) What will happen when PL/SQL code tries to access a cursor which is not opened?
Ans; INVALID_CURSOR exception will be raised
Q) Which data dictionary table is used to verify the column name on which a constraint is applied ?
Ans; USER_CONSTRAINTS_COLUMNS
Q) What cause applies a lock on the table?
Ans; for update
Q) presence of the column named ‘sal’ in ‘emp’ table and also 7839 employee number,output of the
program
Declare
v_sal number;
Begin
Select sal*12 into v_sal from emp where empno=7839;
Dbms_output.put_line(v_sal);
End;
Ans; error at line 2, as the size of the variable is not declined
Q) What is the result of the below statement
Select translate ( “this is test”,’test’,’nest’)from dual;
Ans; nhis is nesn
Q) Which data dictionary table is used to verify the column name on which a constant is applied
Ans;USER_CONS_COLUMNS
Q) When I apply a primary key constraint a column of a table , which index is applied by default
Ans; UNIQUE
Q) SUBSTR(‘PARAMESHWAR’,6,6) FROM DUAL;
Ans; ESHWAR
Q) What will happen when PL/SQL code tries to access a cursor which is not opened?
Ans; INVALID_CURSOR exception will be raised
Q) Identify the correct statement which is true regarding SET operators
Ans; union will give combination of two select statements excluding duplicate values
Q) Which of the following property guarantees that once a user is notified of success in his action on
the table
Ans; durability
Q) A aggregate function ignores _____ value by default
Ans; NULL
Q) CREATE TABLE DEPT (deptno NUMBER (2), dname VARCHAR2(14),loc VARCHAR2(13)); ROLLBACK;
ANS; THE DESC DEPT statement displays the structure of the table DEPT.ROLLBACK does not apply
on CREATE statement
Q) Which one of these is used to write the actual data to data file?
Ans; DATABASE WRITER
Q) Consider foll DCL statements
1. create user 1 identified by password
2. grant create session to user 1
When user login and try to execute the below statement.result ?
Create table test1(c1 number);
Ans; INSUFFICIENT PRIVILAGES
Q) CREATE TABLE T1(SNAME CHAR);
ANS; TABLE is created but the user can insert data with single character
Q) A default constraint is mentioned on a table column. which statement is true?
Ans; if we do not insert any value then default value will be considered
Q) What is the difference between a PL/SQL cursor and PL/SQL Ref CURSOR?
Ans; PL/SQL cursor in dynamic in nature whereas PL/SQL Ref cursor is static in nature
Q) Which of the following is an example for declaring strong ref cursor?
Ans; type T1 ref cursor return emp%rowtype
Q) Which of the following way of adding check constraint is correct?
Ans; CREATE TABLE EMP (empno number,sal number , comm number , constraint check_ sal check
(sal+comm>1000));
Q) SELECT RTRIM(“RAMESH ***’,’*’) FROM DUAL ;
Ans; RAMESH**
Q) Which of the following statement creates a new table named EMP1 from existing table EMP
Ans ; CREATE TABLE EMP1 AS SELECT* FROM EMP
Q)Consider a SQL statement below executed inn a sequence
CREATE TABLE DEPT (deptno NUMBER(2))
ANS ; big option ending with
Create statement
Q)The EMPLOYEES table has these columns
LAST_NAME varchar(35)
Salary Number(8,2)
Ans; A change to the default
Q) Which of the below options is true about the NVL Function
Ans ; Allows the clue to specify
Q) Which of the following arithmetic operations on a date column named HRE_DATE of EMP Table is
Valid
Ans; select here date 1 from EMP
Q)What is the output of the below query
SELECT TRANSLATE(‘MANAGER’,’MAN’,’DAM’)
ANS; DAMAGER
Q) What is the output of the below query
SELECT INSTR(‘PARAMESHWAR’,’AR’,1,2) FROM DUAL
Ans: C-10
A.
A conditional predicate allows you to specify a WHEN-LOGGING-ON condition in the trigger
body.
B.
A conditional predicate means you use the NEW and OLD qualifiers in the trigger body as a
condition.
C.
A conditional predicate allows you to combine several DBM triggering events into one in the
trigger body.
D.
A conditional predicate allows you to specify a SHUTDOWN or STARTUP condition in the trigger
body.
1)Which of the following is the SQLCODE for exception with name TOO_MANY_ROWS?
ANS; 1422
2)What is the SQLCODE of exception with name NO_DATA_FOUND?
Ans: 100
3)Which Procedure can be used to create a customized error message? Choose most appropriate
option.
Ans: RAISE_ERROR
4)Which of the following is not a valid index by datatypes in associative arrays?
Ans: data
5)Which of the following type of exception will not have a builtin exception name in PLSQL?
Ans: Non Predefined exception
6)Considering the presence of a table STUDENT with coloumn MARKS
Predict the output of the below trigger execution.
Ans: It will raise an error as OLD and NEW
7)Which Procedure can be used to create a customized error message?
Ans: RAISE_APPLICATION_ERROR
8)Considering the presence of columns SALARY,EMPLOYEE_ID IN EMPLOYEES table and absence of
employee with EMPLOYEE_ID
Ans: Function get created Succesfully
9)Which of the following is a pre defined exception?
Ans: COLLECTION
10)Which PL/SQL Collection would fit the below scenario
Represent student data of discipline cse
Ans; ALL
11)Considering the presence of column ENAME in NEW_EMP table predict the outcome of following
trigger
Ans: Trigger Creation will throw error as you cannot use new and old in row level trigger
12)Which of the following is a bounded and dense collection in PL/SQL?
Ans; Array
13)considering the presence of columns SALARY,EMPLOYEE_ID in EMPLOYEES table and absence of
employee with EMPLOYEE_ID 100?
Ans : Function gets created Successfully
14)Can project beam customized pre configured validation rules in PIVOT?
Ans: YES
15)In RAISE_APPLICATION_ERROR what is the valid range of error number that develop can make
use of
Ans; -20999 to -20000
16)Which of the following type of exception will not have a bulitin exception name in PLSQL
Ans; Non-Predefined Exception
1. What are the different types of delete details we can have on master detail forms :– Non-isolate
2. Which of the following collection is a bounded collection :– ASSOCIATIVE ARRAY
3. What are the different default triggers created when Master deletes Property is set to :– Onpopulated-details
4. Which of the following bulletin is used to get the starting index of a PLSQL collection :– INITIAL
5. …………..is loaded n memory? :– WHEN-NEW-FORM-INSTANCE
6. In PLSQL what is the persistence of a bind variable in once declared in a session :– Exists within a
package
7. Which of the following contains the select statement associated with LOV in oracle forms :–
Record group
8. After declaring the cursor, which statement will execute the select statement associated with
the cursor :– At declaration itself the select statement gets executed
9. Which of the following is a valid canvas view :– all(content, stacked, horizontal)
10. Which of the following is not true about ref cursor :– there are two types of ref cursors, weak
and strong
11. Which of the following commands are used to create a file in unix :– cat
12. Which UNIX command will list out all the files in parent directory along with permissions :– ls l../
13. Which of the following ‘chmod’ command option (in binary format) is used to give only ‘read
and write’ permissions to a file :–
14. Which of the following is the correct order of different clauses in select statement:–
SELECT*/<COLLIST>FROM<table_name><WHERE_CLAUSE> <GROUP
BY_CLAUSE><HAVING_CLAUSE> <ORDER BY_CLAUSE>
15. Which data dictionary table contains the constraints information of columns in a table :–
16. Which of the following is not a valid data source in oracle reports :–
17. Which is not a object of paper layout in oracle reports :–
18. Which is not a part of paper report section in oracle reports :– trailer section
19. Which of the following is not a oracle report trigger :– between report
20. Which of the following builtin is used to print some message in oracle reports :–
Q. Considering the presence of columns SALARY,EMPLOYEE_ID in EMPLOYEE
Also the presence…..
CREATE OR REPLACE FUNCTION F1(P_ENO IN NUMBER (10))
RETURN NUMBER
IS
ANS. Function Creates With Compilation errors as…
Q. Consider the following code and predict the output of the program
DECLARE
TYPE Numlist is TABLE OF NUMBER
N Numlist=Numlist(10,……,80,90,100)
ANS. Deletes the elements 3 through 6
Q. Considering the presence of columns SALARY,EMPLOYEE_ID in EMPLOYEE
Table where EMPLOYEE_ID is a primary key constrained column
Predict the outcome of the below procedure creation
CREATE OR REPLACE PROCEDURE NEW_EMPLOYEE(P_ENAME VARCHAR2,P_SAL NUMBER)
IS
BEGIN
INSERT INTO EMPLOYES(ENAME,SALARY) VALUES(P_NAME,P_SAL);
COMMIT;
Ans. Procedure successfully gets created
Q. Predict the output of the below code
DECLARE
V_VAR1 NUMBER
V_VAR2 V_VAR1%TYPE
BEGIN
V_VAR2:=’A’;
Ans. Numeric or value error
Q. Predict the output of the following code
DECLARE
e_exception EXCEPTION
BEGIN
DECLARE
e_exception Exception
dbms_output_line(“HANDLED IN E_EXCEPTION HANDLING SECTION”);
WHEN OTHERS THEN
Dbms_output.put_line ()
Ans. HANDLED IN WHEN OTHERS SECTION
Q. Considering the presence of column ENAME in NEW_EMP table, predict the outcome
Of the following trigger
CREATE or REPLACE TRIGGER upper_trig BEFORE
Ans. Trigger gets Created Successfully
Q. Which of the following collection type can be created both at schema level and in PL/SQL Block
Ans. Nested Table
Q. Identify the External LOB
Choose most appropriate option
Ans. BFILE
Q. row level trigger will execute if the update statement is affecting 10 rows
Ans. 10
Q. what will happen if a function is defined in package body with out defining
The function specification in package specification
Ans. It is called as private function and you will able to aceess
Q. with respect to a condition predicate in a DML trigger, choose most appropriate option.
Ans. Exception at line number 6 as there is no employee.
Q. identify the component of a LOB that references to where the LOB value is physically stored
Choose appropriate option
Ans. LOB Locator
Q. with respect to a condiation predicate in a DML trigger, choose most appropriate option.
Ans. A Conditional predicate allows you to combine several DBM triggering
Q. Predict the outcome of the below procedure creation
CREATE OR REPLACE PROCEDURE DROP_TABLE(P_TNAME VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE DROP FROM
END;
Ans. Procedure gets created with compilation errors as there is an error at line 4
Q. which parameter mode in PL/SQL needs a bind variable when procedure or function is
Executed at schema level?
Ans. All
Q. attribute is used to declare a variable for handling table records and also for an explicit cursors
Records ..
Ans. %ROWTYPE
Q. Which of the following PLSQL collction has to be initially dense and can become sparse if nedded
Ans. Nested Table
Q. which of the following builtin is used to associate a exception name for a non-predefined
exception?
Ans. Exception_INIT()
Q. which of the following is predefined exception?
Ans. COLLECTION_NULL
Q. which of the following PL/SQL collection will allow INDEX BY of VARCHAR2 data type
Ans. Associative Array
Q. mode in PL/SQL a bind variable when procedure or function is executed at schema level?
Ans. All (in, out, out in)
Q. Oracle Pl/sql supports following data types?
Ans. BOTH (scalar, composite)
Q. which statement is true about triggers? Choose most appropriate option.
Ans. You use database trigger to fire when an INSERT statement occurs
Q. can project team customized pre-configured validation rules in PIVOT
Ans. YES
Q. SQL CODE of exception with name NO_DATA_FOUND?
Ans. 100
Q. Considering the presence of the columns named ‘ename’ and ‘sal’ absence of 1234
Ans. Exception at line 5 as there..
Q. considering the presence of columns SALARY,EMPLOYEE_ID also predict EMPLOYEE_ID 100..
Ans. Function creates with compailation error as size is mentioned…
Q. to a condition predicate in a DML trigger, choose most appropriate option.
Ans. A condition predicate allows you to combine several DML triggering events into one
Q. considering the presence of columns ENAME in NEW_EMP table, predict the outcome of the
following trigger
CREATE OR REPLACE TRIGGER upper_trig BEFORE INSERT or UPDATE OF ename on new_emp
BEGIN
new_name-upper(:old.ename)
end;
Ans.trigger gets created successfully
Q. students data of discipline CSE in a college with maximum 60
Ans. Varray
Q. Predict the following code?
DECLARE
e_exception EXCEPTION:
Ans. HANDLED IN WHEN OTHERS SECTION
Q. which of the following type of exception will not have a builtin exception name in PLSQL
Ans. NON-Predefined Exceptions
1. PL/SQL needs a ----- variable when procedure or function is --------------------OUT
2. Can project team customized pre configured validation rules in PIVOT ?
NO
3. Considering the presence of column SALARY,EMPLOYEE_ID,E_NAME in EMPLOYEE table
Where EMPLOYEE_ID is Primary key Constrained column
Predict the outcome of the below procedure creation
CREATE OR REPLACE PROCEDURE NEW_EMPLOYEE_,P E_NAME VARCHAR2.P_SAL NUMBER)
IS
BEGIN,
INSERT INTO EMPLOYEES(ENAME,SALARY) VALUES(P_ENMAE,P_SAL);
COMMIT;
END;
For as pk constrained column is missing
4. Consider EMP(EMPID.ENAME) has two rows with the below values
ROW1=(1000.'FIRST NAME')
ROW2=(1001.'SECOND NAME')
PREDICT THE OUTPUT OF BELOW PL/SOL CODE
DECLARE
TYPE type_enamo IS TABLE OF emp.enamo%TYPE;
TYPE type_empno IS TABLE OF emp.empid%TYPE;
TYPE type_emp IS RECORD (empno type_empno,ename type_ename);
v_emp type_emp:
BEGIN
SELECT empid,ename BULK COLLECT INTO v_emp FROM omp;
FOR i IN v_emp.empno.first..v_emp.empno.last
LOOP
dbms_output.put_line(v_emp.empno(i) Il ' 'II v_emp.ename(i));
END LOOP;
END;
1000 FIRST NAME
1001 SECOND NAME
5.HTML Validation report in PIVOT is used by
DU lead
6. Which database object cannot be nested with same type of database object?
Package
7.What will happen if a function is defined in package body with out defining the function
specification in package specification?
It is called as private function and you will not be able to access the function outside the package
8.Which of the following is Oracle PL/SQL collection cannot be defined at schema level ?
Pl/sql table
9. ________Attribute is used to declare a variable for handling table records and -------------------------%ROWTYPE
10. Which PL/SQL. collection type would fit in the below scenario Represent students data of
discipline CSE in a college with maximum 60 seats
11. Consider the following code, predict the output of the program.
DECLARE
TYPE NumList IS TABLE OF NUMBER
n NumList.=NumList(10.20,30,40,50,60,70,80,90,1001);
BEGIN
n DELETE(3,6);
END;
Deletes the elements 3 through 6
12. Oracle PL/SQL supports following data types
Both Scalar and composite
13. Consider the procedure given below
CREATE OR REPLACE PROCEDURE PROC_1 AS
num1 NUMBER(2),
num2 NUMBER(2),
ans NUMBER(3);
BEGIN
ans=num1*num2;
DBMS_OUTPUT.PUT_UINE(‘SUM IS’||ans);
END;
Assuming that the procedure is created successfully. identify the valid option that is used to
execute the procedure. Choose most appropriate option.
EXEC proc_1
14.Considering the presence of column named ‘department_name’ in 'departments' table and also
table named 'employees'. Choose the output of the below program.
declare
type t1 is table of employees%rowtype;
v1 t1;
v_deptrec departments%rowtype;
begin
select bulk collect into v1 from departments;
for i in v1.first..v1.last loop
dbms_output.put_line(v_deptrec.department_name);
end loop;
end;
Error at line number 5.
15. Which of the following is true regarding Packages?
1.Package body cannot be created without package specification
2.Package specification can be created without package body.
3.Package can have private subprogramms
1,2,and 3
16. Considering the presence of columns SALARY ,EMPLOYEE_ID in EMPLOYEES table. Also the
presence of employee with EMPLOYEE_ID 100. Predict the outcome of the below function creation.
CREATE OR REPLACE FUNCTION F1(P_ENO IN NUMBER(10))
RETURN NUMBER
IS
V_ASAL NUMBER;
BEGIN
SELECT SALARY 12 INTO V_ASAL FROM EMPLOYEES WHERE EMPLOYEE_ID ID = 100;
RETURN V_ASAL;
END;
Function creates with compilation errors as size -------mentioned for aparameter
17.
Considering the presence of a column named first_name' in 'emp' table. Choose the output of the
below program
declare
type t1 is table of varchar2(20);
v1 t1;
begin
select first_name bulk collect into v1 from emp;
dbms_output put_line(v1);
end;
error at line number –
18. ---- create a multi dimensional collection in PL/SQL?
True
19. Consider the presence of the table STUDENT with column MARKS
----------------trigger execution when an update operation is performed on MARKS
---------------------(----------SET SERVEROUTPUT ON)
CREATE OR REPLACE TRIGGER UPDATED_MARKS
---------------STUDENT
BEGIN
Dbms_output.put_line(‘old marks are’ || OLD.MARKS || ‘New marks are’ || NEW.MARKS );
END;
It will raise an error as :OLD and NEW cannot be used in this type of trigger
20. Predict the output of the following code?
DECLARE
e_exception EXCEPTION;
BEGIN
DECLARE
e exception EXCEPTION
BEGIN
RAISE e exception EXCEPTION
END;
EXCEPTION
WHEN exception THEN
Dbms_output.put_line(‘HANDLED IN E_EXCEPTION HANDLING SECTION);
WHEN OTHERS THEN
Dbms_output.put_line(‘HANDLED IN WHEN OTHERS SECTION);
END;
HANDLED IN WHEN OTHERS SECTION
1. A aggregate function ignores _____ value by default. Fill in the blank with an appropriate option.
Ans- Null
2. Which of the following database models does not support many-to-many relationships?
Ans- Hierarchical database model
3. What is the result of below select statement? Select instr(''This is test string', 'string') from dual. Ans14
4. What is the result of below select statement? SELECT translate('this is test', 'test', 'nest') from dual;
Ans- error
5. Identify the correct statement which is true regarding SET operators
Ans- union will give combination of two select statements excluding
6. Forgot To create a table without a primary key.
Ans- we can alter the table to add primary key later as long as tge table..
7. Ans- allows the user to specify alternate value for null values
8. What is the output of the below query. SELECT LPAD('PARAM', 10,'*') from dual
Ans- *****PARAM
9. In a banking application, there's a requirement to find the customer's name along with the
transaction details
Ans- SELECT T.TID,T.TDATE,T.TAMOUNT,C.NAME FROM CUSTOMER C, TRANSACTION T WHERE
C.CUSTID=T.CUSTID;
10. There are 3 columns named HIRE_DATE, SALARY AND ENAME of EMP table with DATE, NUMBER,
VARCHAR2 data types respectively. Which of the following SQL statement is valid?
Ans- All
11. What will happen when PL/SQL code triea to access a cursor which is not opened?
Ans- INVALID_CURSOR exception will be raised
12. Which of the following datatype allows you to store data of external operating system file?
Ans- BFILE
13. Which statement of cursor. Select statement will be executed?
Ans- fetch cursor_name into rec;
14. What is the output of below query SELECT ROUND(55.258,2) FROM DUAL
Ans- 55.26
15. Considering the presence of the tables named 'departments' and 'emp'. Choose the correct output
of the below program?
Ans- program executes successfully
16. What is the output of below query SELECT SUBSTR('PARAMESHWAR', 6,6) FROM DUAL;
Ans- ESHWAR
17. Which of the following option(s) is/are valid with respect to explicit cursor?
Ans- all of the above
18. Consider there are 10 rows in emp table.
1. Delete from emp; 2. Commit; 3. RollBack; Select count(*) from emp;
What is the output of the 4th SQL statement? Ans- 0
19. Which background process is involved in writing the content to and from Data Buffer Cache
Ans - DBWN
20. Which of the following is the valid data dictionary views of constraint database object?
Ans- BOTH( user_constraints, user_cons_columns)
21. SELECT INSTR(' PARAMESHWARA', 'AR', 1,2) FROM DUAL
ANS- 10
22. Which of the following statement creates a new table named EMP1 from existing table EMP
Ans- CREATE TABLE EMP1 AS SELECT * FROM EMP,
23. Which of the below options is TRUE about the NVL function?
Ans- Allows the user to specify alternate value for null values
24. A PL/SQL subprogram can have multiple cursors with different names open all the same time. Anstrue
25. A PL/SQL variable that stores a column value must have the same name as the column
Ans - false
26. Which of the following arithmetic operations on a date column name HIRE_DATE of EMP table is
valid. ANS- select HIRE_DATE+2 FROM emp;
27. When I apply a primary key constraint on a column of a table, which index is applied by default?
ANS- UNIQUE
28. Identify the correct syntax of using SAVEPOINT and ROLLBACK
ANS- SQL> SAVEPOINT sp1;
SQL> ROLLBACK TO sp1
29. Which data dictionary table is used to verify the column name on which a constraint is applied. Ansuser_cons_columns
30. Which of the following way of adding check constraint is correct
Ans- create table emp(empno number, sal number, comm number, CONSTRAINT check_sal
check(sal+comm>1000))
31. Which of the following sql statement executes without error?
Ans- select empno from emp
Union select sal from emp;
32. SELECT translate ( 'manager', 'man', 'dam') from dual
Ans- Damager
33. Select RTRIM('ramesh***') from dual;
Ans- RAMESH***
34. Which of the following option lets user to lock the rows explicitly?
Ans- Explicit_lock
35. In select statement, which one of these clauses checks for rows and does not compare columns.
Ans- exists
36. Consider the following DCL statements
Create user user1 identified by password ;
Grant create session to user1;
After the execution of the two statements, when user login and try to execute below statements what
will be the result?
Create table test1(c1 number) ;
Ans- insufficient privileges
37. The employees tables has these columns
Last_bame Varchar2(35)
Salary number(8,2)
Hire_date date
Management wants to add a default value to the salary column. You plan to alter the table by using sql
statement. Ans- column definitions cannot be altered to add DEFAULT values for columns..
38. Considering the presence of salary column in emp table. Choose the right output that below
program gives. Ans- it prints all the salaries of employees.
39. In oracle pl/sql, what is the name of implicit cursor? Ans- sql
40. If we don't insert any value then default value will be considered
41. A developer forgot to create a table without a primary key
Ans- we can alter the table to add primary key..
42. Which of the following alter statements will execute correctly for emp table considering table is
empty with columns empno,ename(where empno is primary key)
Ans- alter table emp add column10 number;
43. Assuming EMP is an existing table with one column name ENAME. What is wrong with the below
ALTER statement considering table is empty?
ALTER TABLE emp
MODIFY ename varchar2(75) DEFAULT 'Seattle' NOT NULL;
ANS- there is nothing wrong with the statement
44. When I apply a primary key constraint on a column of a table. Which index is applied by default? Ans
- UNIQUE
45. identify the sequence of operations that pl/sql performs in order to execute a sql statement in a
program for both implicit and explicit cursors.
Ans- PARSE, BIND, OPEN, EXECUTE, FETCH, CLOSE
46. What is the difference between a PL/SQL cursor and PL/SQL ref cursor?
Ans- PL/SQL REF Cursor is a PL/SQL type where as PL/SQL cursor is not
47. Which of the following datebase models does not support many-to-many relationship?
Ans- RDBMS
48. The column named SALARY in emp table is not null constrained and the constraint name is
NOT_NULL_CONST. If i wabt to allow NULL in the SALARY column going ahead, which statement needs
to be executed?
Ans- ALTER TABLE EMP DROP NOT_NULL_CONST
49. SELECT Trunc(65.286,2) from dual. Ans- 65.25
50. Choose the right query for displaying the sum of salary of all employees
1. Some employees might not get commission
2. Total salary is an addition of sal and commission columns
Ans- select sum(sal+nvl(comm, 0)) from emp;
51. Which of the following datatype allows you to store data of external operating system file?
Ans- BFILE
52. Which of the following property guarantees that once a user is notified of success in his action on
the database, the completed action will persist
Ans- Durability
53.which of the following is an example for declaring a strong ref cursor
Ans- type t1 ref cursor return emp%row type...
54. Select replace('parameshwar', 'ar', 'mp') from dual. Ans- PMPAMESHWMP
55.Which of the following operators are used in multi-row sub queries? Ans- all the options
55. Identify the correct statement which is true regarding SET Operators
Ans- union will give combination of two select statements exluding duplicate values
1.With respect to explicit cursor?
Ans: all the above
2.select count(*) from emp ; what is the output 4th sql statement ?
Ans 0
3.background process involved in writing content to and from data duffer cache
Ans : dbwn
4.data dictonary views of constraint database object ?
Ans : both
5.select instr(‘this is test string’,’string’) from dual ANS :14
14 because the string is starting at 14th letter
6.select instr('paramaeshwar','ar',1,2) from dual ANS :11
Ans : Is 11 because 1 represent start from 1 and 2nd ‘ ‘ar’
7. new table named emp1 from existing table emp
Ans : create table emp1 as select * from emp
8.select LPAD(‘PARAM’,10,’*’)
ANS :*****PARAM
9. TABLE NAMED DEPARTMENT AND EMP
ANS : PROGRAM SUCCESS FULLY EXICUTED
10. A AGGRIGATE FUNCTION IGNORES NULL VALUE BY DEAULT
11.TRUE ABOUT THE NVL FUNCTION :
ANS : ALLOWS THE USER TO SPECIFY VALUE TO THE NULL VALUE
12. A PL/SQL SUBPROGRAM CAN HAVE MULTIPLE CUSRORS WITH
ANS : TRUE
13. A PL/sql variable that stores a column value
Ans : false
14. select hire date +2 from emp;
15. when I apply a primary key constraint on a colomn of a table
Ans: unique
16. save point and rollback
Ans: save sp1;
Rollback to sp1;
17.data dictionary table is used
Ans: user_cons_colums
18. select TRANSLATE('this is test','test ','nest') from dual
19.check constraint
Ans: CREATE TABLE table_name
(column1 datatype null/not null,
column2 datatype null/not null,
CONSTRAINT constraint_name CHECK (column_name condition) [DISABLE]
);
20. select translate(‘manager’,’man’,’dam’) from dual
Ans : damager
21. let user to lock the rows explicitly ?
Ans : explicit_lock
22. check for rows and does not compare columns .
Ans : exists
23. 1. Create user user1 identified by password;
Ans : insufficient privileges
24.
The employees table has these columns ;
SALARY DEFAULT 5000
ANS: column definition cannot be altered to add defaults ……….
25.
parse ,bind ,open,execute ,fetch ,close
26.pl/sql ref cursor is a pl/sql type
27. not support many to many relationship?
Ans : rdbms
28. select trunc(65.285,2) from dual
Ans 65.28
29. NVL( string1, replace_with )
30. data type allows you to store data external operating system file ?
Ans : BFILE
31. Which of the folowing command are used to create a file in UNIX?
ANS : CAT
32. Which of the following is the correct order of different clauses in select statement,
ANS :
33.FETCH STATEMENT OF A CURSOR …..
ANS : TRUE
Henry wants to use a standard method to get the starting index of a
PL/SQL collection.
Which is the correct option?
Question 1 options:
START
FIRST
INITIAL
INDEX
INDEX BY
Question 2 (1 point)
Saved
Which of the following options are valid Database Models?
A. Network Database Model
B. Hierarchical Database Model
C. File Based Data Management
D. All of them
Question 2 options:
A
B
C
A and B
D
Question 3 (1 point)
Saved
Ben had created CCP_Employees table in the database.
What is the expected outcome for the below query ?
SELECT * FROM CCP_Employees ORDER BY 4;
Question 3 options:
The query fails as ORDER BY clause needs a column name instead of 4.
Rows are displayed in the ascending order for all the first 4 columns.
The rows are sorted in ascending order and only the first 4 rows are displayed.
Rows are displayed in ascending order based on the 4th column.