Top 30 PL SQL Interview Questions and Answers for Freshers

No matter, you are a fresher QA engineer or a beginner in the DBA field. PL/SQL is a skill which is essential for engineers of both these profiles. Hence, we curated this post with thirty PL SQL interview questions that confront most candidates in DBA/QA interviews.

Before you drive in to read the questions, it’s customary to learn a few basic facts about the PL/SQL.

1. PL/SQL is a modular programming extension of SQL cast by Oracle in the late 1980’s.

2. It offers an interpreted development environment for creating platform independent scripts.

3. It supports executing from the external programming tools to operate on databases.

4. PL/SQL provides a no. of data types, programming constructs, functions, and procedures.

5. It inculcates object-oriented features and supports web applications and server-side scripting.

Looking at the gist of PL/SQL features, you can imagine how powerful this language is. So don’t miss to read out even a single PL SQL interview questions from the below list. We’ve designed it in a quiz format so that it’ll keep your interest until the end.

Also, if you sincerely want to be successful in a DBA or a QA interview, then do follow the posts listed below. These would guide you further on the theoretical questions asked during the job interviews.

PL SQL Interview Questions and Answers for Freshers.

PL SQL Interview Questions and Answers for Freshers

PL SQL Interview Questions.

Q-1. What will be the value of a variable declared as type NUMBER which is not initialized but getting used in the executable section of the PL/SQL block?

A. NULL
B. 0
C. Results in a compilation error
D. Raise an exception

Click here to view the answer.
Answer. A

Q-2. Which of the following literals are not available in PL/SQL?

A. String
B. Date and Time
C. Boolean
D. Integer

Click here to view the answer.
Answer. D

Q-3. Which of the following sections is mandatory for a PL/SQL block?

A. Exception-handling Section
B. Executable Section
C. Declaration Section
D. All Sections are mandatory

Click here to view the answer.
Answer. B

Q-4. Which of the following statements hold true for the declaration section of a PL/SQL block?

A. It starts with the DECLARE keyword.
B. It is a mandatory section.
C. It defines all variables, cursors, subprograms, and other elements to be used in the program.
D. All of the above.

Click here to view the answer.
Answer. A and C

Q-5. What is the command to get the server output and display it on the screen?

A. set serveroutput on
B. set server output on
C. set dbmsoutput on
D. set dbms output on

Click here to view the answer.
Answer. A

Q-6. Which of the following section of a function declaration typically contains a return keyword?

A. Header Only
B. Declarative
C. Executable and Header
D. Executable and exception handling

Click here to view the answer.
Answer. C

Q-7. What will be the output of the following code?

A. Apple
B. No such fruit
C. Pineapple
D. Hard Luck

Click here to view the answer.
Answer. C

Q-8. What will be the output of the following code?

A. 20
B. 60
C. 80
D. 40

Click here to view the answer.
Answer. C

Q-9. Which of the following control structures provide an unconditional approach to a particular part of a complex PL/SQL block?

A.  If-Then-Else
B.  While loop
C.  GoTo
D.  Decode

Click here to view the answer.
Answer. C

Q-10. How will you define a variable using referential datatypes, if the variable name is EMPLOYEE_LASTNAME and the corresponding table and column name is EMPLOYEE and LNAME respectively?

A. Use employee.lname%type
B. Use employee.lname%rowtype
C. Search data type of LNAME column in EMPLOYEE table to use it.
D. Declare it to be of type LONG.

Click here to view the answer.
Answer. A

Q-11. Which of the following are the implicit cursor attributes?

A. %found
B. %too_many_rows
C. %notfound
D. %rowcount
E. %rowtype

Click here to view the answer.
Answer. A, C and D

Q-12. Which of the following may cause an infinite loop to occur in a simple loop?

A. LOOP
B. END LOOP
C. IF-THEN
D. EXIT

Click here to view the answer.
Answer. B

Q-13. Which of the following holds true when FETCH statement is used to retrieve rows from the cursor?

A. It causes the cursor to close.
B. It causes the cursor to open.
C. It loads the current row values of the cursor into variables.
D. None of the Above

Click here to view the answer.
Answer. C

Q-14. Which of the following statements are correct for PL/SQL cursors?

A. Explicit cursors are automatically created by Oracle.
B. Implicit cursors are programmer defined cursors.
C. The latest implicit cursor is called the SQL cursor and has the attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.
D. All of the above.

Click here to view the answer.
Answer. C

Q-15. What are the maximum number of handlers that get processed before the PL/SQL block is exited, in case an exception occurs?

A. One
B. All that apply
C. All referenced
D. None

Click here to view the answer.
Answer. A

Q-16. What is the value of emp_id within the nested block in the sample code?

A. 12939
B.  30
C.  GN56
D.  155.65

Click here to view the answer.
Answer. C

Q-17. Which of the following statement holds true about the PL/SQL data structure VARRAY?

A. It has a maximum size that cannot be changed.
B. A VARRAY type is created with the CREATE VARRAY statement, at the schema level.
C. The maximum size of a VARRAY can be changed using the ALTER TYPE statement.
D. ALTER VARRAY statement can be used to modify the size of a VARRAY.

Click here to view the answer.
Answer. C

Q-18. Which of the following parameters do cursors support?

A. IN
B. OUT
C. %ROWTYPE
D. IN OUT

Click here to view the answer.
Answer. A, B and D

Q-19. What is the correct sequence of commands to process a set of records on using explicit cursors?

A. INITIALIZE, GET, CLOSE
B. CURSOR, GET, FETCH, CLOSE
C. OPEN, FETCH, CLOSE
D. CURSOR, FETCH, CLOSE
E. GET, SEEK, HIDE

Click here to view the answer.
Answer. C

Q-20. Which of the following statements holds true when you add the keyword “FOR UPDATE” at the end of a cursor?

A. Alert the DBA about the update on the table.
B. Create a bind variable.
C. Lock the rows before doing any update.
D. Free up rollback segments before any update.

Click here to view the answer.
Answer. C

Q-21. What are the three parameter modes for procedures?

A. IN, OUT, IN OUT
B. R(ead), W(rite), A(ppend)
C. CONSTANT, VARIABLE, DEFAULT
D. COPY, NOCOPY, REF

Click here to view the answer.
Answer. A

Q-22. Which of the following aggregate function ignore NULL?

A. Distinct
B. Count(*)
C. Average()
D. None of the above.

Click here to view the answer.
Answer. B

Q-23. Which of the following statement is not correct for large object data types in PL/SQL?

A. BFILE is used to store large binary objects in operating system files outside the database.
B. BLOB is used to store character data in the database.
C. CLOB is used to store large blocks of character data in the database.
D. NCLOB is used to store large blocks of NCHAR data in the database.

Click here to view the answer.
Answer. B

Q-24. What will be the output of the following code snippet?

A. Tech
B.  Beamers
C.  Beam
D. None of the above.

Click here to view the answer.
Answer. B

Q-25. Which of the following line represents the correct syntax to open a cursor named cursor_sql?

A. OPEN cursor_sql;
B. OPEN CURSOR cursor_sql;
C. FETCH cursor_sql;
D. FETCH CURSOR cursor_sql;

Click here to view the answer.
Answer. A

Q-26. What will be the output of the following PL/SQL block?

A. 10
B.  12
C.  2
D. Syntax Error

Click here to view the answer.
Answer. B

Q-27. Which of the following events require execution of trigger as its response?

A. A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).
B. A database definition (DDL) statement (CREATE, ALTER, or DROP).
C. A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).
D. All of the above.

Click here to view the answer.
Answer. D

Q-28. Which of the following statement holds true for the inheritance of PL/SQL Objects?

A. PL/SQL allows creating an object from existing base objects.
B. Base objects should be declared as NOT FINAL to implement inheritance.
C. The NOT INSTANTIABLE clause allows you to declare an abstract object.
D. All of the above.

Click here to view the answer.
Answer. D

Q-29. What operation does the {INSERT [OR] | UPDATE [OR] | DELETE} clause indicate in the syntax below?

A. DDL operation.
B. DML operation.
C. DCL operation.
D. All of the above

Click here to view the answer.
Answer. B

Q-30. Which of the following statement holds true for PL/SQL package specifications?

A. The specification is the interface to the package.
B. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package.
C. It contains all information about the content of the package and the code for the subprograms.
D. All of the above.

Click here to view the answer.
Answer. C

Summary – PL SQL Interview Questions for Freshers.

It was one more post where we took up a key topic for QA Engineers. Also, PL/SQL skill is becoming more and more important for the end to end validation. That’s why we came up with this blog post on the top thirty PL SQL interview questions for fresher DBA/QA engineers.

It would be great if you let us know your feedback on this post.

Also, you can ask us to write on a topic of your choice. We’ll add it to our writing roadmap.

Lastly, if you’d enjoyed the post, then please care to share it with friends and on social media.

Keep Learning,

TechBeamers.

Leave a Reply


*