No matter, whether you are a fresher QA engineer or a beginner in the DBA field. PL/SQL is a skill that 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 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 external programming tools to operate on databases.
4. PL/SQL provides a number 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 reading out even a single PL SQL interview question 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.
- Top 20 Interview Questions and Answers for QA Engineers
- Top 20 SQL Interview Questions and Answers for QA Engineers
PL SQL Interview Questions and Answers for Freshers.
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
Q-2. Which of the following literals are not available in PL/SQL?
A. String
B. Date and Time
C. Boolean
D. Integer
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
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.
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
Q-6. Which of the following sections of a function declaration typically contains a return keyword?
A. Header Only
B. Declarative
C. Executable and Header
D. Executable and exception handling
Q-7. What will be the output of the following code?
DECLARE
fruit char(1) := 'P';
BEGIN
case
when fruit = 'A' then dbms_output.put_line('Apple');
when fruit = 'B' then dbms_output.put_line('Banana');
when fruit = 'C' then dbms_output.put_line('Chiku');
when fruit = 'P' then dbms_output.put_line('Pineapple');
when fruit = 'F' then dbms_output.put_line('Hard Luck');
else dbms_output.put_line('No such fruit');
end case;
END;A. Apple
B. No such fruit
C. Pineapple
D. Hard Luck
Q-8. What will be the output of the following code?
DECLARE
num1 number;
num2 number;
num3 number;
FUNCTION fx(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > 2*y THEN
z:= x;
ELSE
z:= 2*y;
END IF;
RETURN z;
END;
BEGIN
num1:= 20;
num2:= 40;
num3 := fx(num1, num2);
dbms_output.put_line(num3);
END;A. 20
B. 60
C. 80
D. 40
Q-9. Which of the following control structures provides an unconditional approach to a particular part of a complex PL/SQL block?
A. If-Then-Else
B. While loop
C. GoTo
D. Decode
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 the data type of the LNAME column in the EMPLOYEE table to use it.
D. Declare it to be of type LONG.
Q-11. Which of the following are the implicit cursor attributes?
A. %found
B. %too_many_rows
C. %notfound
D. %rowcount
E. %rowtype
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
Q-13. Which of the following holds true when the 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
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 attributes like %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.
D. All of the above.
Q-15. What is 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
Q-16. What is the value of emp_id within the nested block in the sample code?
/* Start main block */ DECLARE emp_id NUMBER(9) := 12939; emp_age NUMBER(2) := 30; BEGIN /* Start nested block */ DECLARE emp_id VARCHAR2(9) := 'GN56'; current_balance NUMBER(10,2) := 155.65; BEGIN -- what is the value of emp_id at this point? NULL; END; END;
A. 12939
B. 30
C. GN56
D. 155.65
Q-17. Which of the following statements 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.
Q-18. Which of the following parameters do cursors support?
A. IN
B. OUT
C. %ROWTYPE
D. IN OUT
Q-19. What is the correct sequence of commands to process a set of records using explicit cursors?
A. INITIALIZE, GET, CLOSE
B. CURSOR, GET, FETCH, CLOSE
C. OPEN, FETCH, CLOSE
D. CURSOR, FETCH, CLOSE
E. GET, SEEK, HIDE
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 updates.
D. Free up rollback segments before any update.
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
Q-22. Which of the following aggregate functions ignore NULL?
A. Distinct
B. Count(*)
C. Average()
D. None of the above.
Q-23. Which of the following statements 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.
Q-24. What will be the output of the following code snippet?
message varchar2(11) := 'Tech Beamers'; dbms_output.put_line ( SUBSTR (message, 6, 7));
A. Tech
B. Beamers
C. Beam
D. None of the above.
Q-25. Which of the following lines 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;
Q-26. What will be the output of the following PL/SQL block?
DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 10;
b:= 12;
findMin(a, b, c);
dbms_output.put_line(c);
END;A. 10
B. 12
C. 2
D. Syntax Error
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.
Q-28. Which of the following statements holds true for the inheritance of PL/SQL Objects?
A. PL/SQL allows the creation of 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.
Q-29. What operation does the {INSERT [OR] | UPDATE [OR] | DELETE} clause indicate in the syntax below?
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;A. DDL operation.
B. DML operation.
C. DCL operation.
D. All of the above
Q-30. Which of the following statements 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.
Must Read: 15 PHP Interview Questions and Answers for Experienced
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 skills are becoming more and more important for 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.
Top SQL Queries Asked in Interviews
Check out the 50 most-asked SQL query interview questions.
We need your support to run this blog, so share this post on your social media accounts like Facebook / Twitter. This is how you will encourage us to come up with more informative stuff.
Keep Learning,
TechBeamers.
