SQL QUIZ with Answer Part 2 #DOTC_Mdb

SQL QUIZ with Answer Part 2.

Hello Everyone,

Today I will start SQL quiz part 2 , this is important for those guys who is preparing for OCA(Oracle certified Associate) certification. I suggest you to go through my previous Oracle SQL and PL/SQL blogs in Archive folder for your reference.

Note: This Type of SQL and PL/SQL questions asked in Oracle Certification exams.

Before part 2 please explore my below blog SQL quiz Part1.

https://dheeraj60.blogspot.com/2020/07/sql-quiz-practice-for-oca-with-answer.html

Let’s Start the quiz :

1. Which statement about implicit cursors is true?

A. Implicit cursors are declared implicitly only for DML statements.

B. Implicit cursors are declared implicitly for all the DML and SELECT

statements.

C. Programmers need to close all the implicit cursors before the end of the PL/SQL

program.

D. Programmers can declare implicit cursors by using the cursor type in the

declaration section.

Answer: B

Explanation:

Answer B is correct because implicit cursors are declared implicitly for all DML and SELECT statements.

2. Evaluate this PL/SQL block:

DECLARE

v-result NUMBER(2);

BEGIN

DELETE

FROM employee

WHERE dep_id IN (10,20,30);

v-result:= SQL%ROWCOUNT;

COMMIT;

END;

3. What will be the value of v_result if no rows are deleted?

A. 0

B. 1

C. True

D. Null

Answer: A

Explanation:

Answer A is correct because if no rows are deleted SQL%ROWCOUNT attribute of cursor  will return 0 – number of deleted rows.

3. Which two conditions in a PL/SQL block cause an exception error to occur?

(Choose two)

A. Select statement does not return a row.

B. Select statement returns more than one row.

C. Select statement contains a group by clause.

D. Select statement does not have where clause.

E. The data type in the select list are inconsistent with the data types in the into

clause.

Answer: A, B

Explanation:

Answers A and B is correct because there are two common exceptions can cause an error :

NO_DATA_FOUND, if no rows were selected or changed by the SQL operation or

TOO_MANY_ROWS, if more than one row was obtained by a single-row subquery, or in another SQL statement operation where Oracle was expecting one row.

 

4. You want of display the details or all employees whose last names is Dotc. But you are not sure in which case last names are stored. Which statement will list all the employees whose last name is Smith?

A. Select last name, first name.

FROM emp

WHERE last name= 'Dotc';

B. Select last name, first name.

FROM emp

WHERE UPPER (last name)= 'Dotc';

C. Select last name, first name.

FROM emp

WHERE last name=UPPER ('Dotc');

D. Select last name, first name.

FROM emp

WHERE LOWER (last name)= 'Dotc';

Answer: D

Explanation:

Select last name, first name.

FROM emp

WHERE LOWER (last name)= 'Dotc'

Answer D shows all records with last name Dotc because function LOWER returns the column value passed into all lowercase.

5. Which statement about multiple sub-queries is True?

A. A pair wise comparison produces a cross product.

B. A non-pair wise comparison produces a cross product.

C. In a pair wise sub query, the values returned from the sub query are compared

individually to the values in the outer query.

D. In a non-pair wise subquery, the values returned from the sub query are

compared as a group to the values in the outer query.

 

Answer: B

Explanation:

Answer B is correct because a non-pair wise comparison really produces a cross product.

 

6. In which section of a PL/SQL block is a user defined exception waste?

A. Heading

B. Executable

C. Declarative

D. Exception handling

Answer: B

Explanation:

Answer B is correct because in the executable section of PL/SQL block a user-defined exception waste.

7. Examine the code:

SET SERVER OUTPUT ON

DECLARE

v_char_val varchar2(100);

BEGIN

v_char_val:= 'Hello World',

DBMS_OUTPUT.PUT_LINE(v_char_val);

END

SET SERVER OUTPUT OFF

This code is stored in a script file name “myproc,sql”. Which statement executes the

code in the script file?

A. Myproc.sql

B. RUN myproc,sql

C. START myproc.sql

D. EXECUTE myproc.sql

E. BEGIN myproc.sql END;

Answer: C

Explanation:

Answer C is correct because command START is used to execute the code in the script file.

 

8. Which statement is valid within the executable section of Pl/SQL block?

A. BEGIN

emp_rec emp%ROWTYPE

END;

B. WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT.LINE('No records found');

C. Select ename,sal

into v_ename,v_sal

from emp where

empno=101;

D. Procedure cal_max(n1 NUBER n2 NUMBER, p_max OUT NUMBER)

IS

BEGIN

If n1>n2 then

p_max:=n1;

Else

p_max=n2;

END.

 

Answer: C

Explanation:

Answer C is correct because this statement populates variables v_ename and v_sal with data from EMP table. Syntax is correct.

9. You want to create report to show different jobs in each department. You do not want to display any duplicate roles in the report. Which SELECT statement do you

use to create the report?

A. SELECT deptno, job

FROM emp;

B. SELECT no duplicate deptno, job

FROM emp;

C. SELECT distinct deptno, job

FROM emp;

D. CREATE report

DISPLAY deptno, job

FROM emp;

E. SELECT distinct deptno, distinct job

FROM emp;

Answer: C

Explanation:

Answer C is correct because this query uses keyword DISTINCT which allows to avoid duplications in displayed results.

 

10 . In which situation should you use outer join query?

A. The employee table has two columns that correspond.

B. The employee and region tables have corresponding columns.

C. The employees and region tables have no correspondence.

D. The employee table column correspond to the region table column contains null

values for rows that need to be displayed.

Answer: D

Explanation:

Because to show all data including null values for rows that need to be displayed you need to use OUTER join between two tables.

 

11. In the declarative section of a PL/SQL block, you created but did not initialize a

number variable. When the block executes what will be the initial value of the

variable?

A. 0.

B. Null.

C. It depends on the scale and precision of the variable.

D. The block will not execute because the variable was not initialized.

Answer: B

Explanation:  Because if a number variable is not initialized it has NULL value by

Default.                                           

 

Please go through this Blog carefully and let me know if you have any doubts in questions. Also go through my previous blog SQL quiz part 1 for questions related to SQL and PL/SQL which commonly asked in OCA exam . For more understanding you can go through my topics of each section which is available in my Archive May and June folder.  In next Blog I will come up with more questions which commonly asked in OCA exams and also this type of questions is useful for you for preparing interview as well.

Thanks.