Part 1: Core SQL and Database Fundamentals (40 Questions)

1.1 SQL Basics

1. What is SQL?

• Answer: SQL, or Structured Query Language, is a declarative language used to

manage and manipulate relational databases. Its primary purpose is to define, query,
and modify data by telling the database what to do, not how to do it.

2. What is a database? Differentiate it from a schema.

• Answer: A database is the physical container for all data. A schema, on the other
hand, is a logical collection of database objects (like tables, views, and procedures)
owned by a specific user within that database. A single database can contain multiple
schemas.

3. What are the main types of SQL commands?

• Answer: The main types are DDL (Data Definition Language) for defining object
structures, DML (Data Manipulation Language) for manipulating data, DCL (Data
Control Language) for managing access rights, and TCL (Transaction Control
Language) for managing transactions.

4. Explain the difference between DDL and DML with examples.

• Answer: DDL commands like CREATE, ALTER, and DROP are used to define or modify
database object structures, and they are automatically committed. DML commands
like INSERT, UPDATE, and DELETE manipulate the data within those objects and
require an explicit COMMIT or ROLLBACK.

5. What is a transaction? What are its properties?

• Answer: A transaction is a single logical unit of work, a sequence of one or more SQL
statements. Its properties are defined by the ACID acronym: Atomicity, Consistency,
Isolation, and Durability, which ensure data integrity and reliability.

6. What is a PRIMARY KEY?

• Answer: A PRIMARY KEY is a column or set of columns that uniquely identifies each
record in a table. It enforces entity integrity by ensuring every row is unique and non-
null, acting as the table's main identifier.
7. What is a FOREIGN KEY?

• Answer: A FOREIGN KEY is a column that links two tables by referencing the
PRIMARY KEY of another table. It enforces referential integrity, ensuring that
relationships between tables remain valid.

8. What is a UNIQUE constraint? How is it different from a PRIMARY KEY?

• Answer: A UNIQUE constraint ensures all values in a column or group of columns are
unique. Unlike a PRIMARY KEY, a table can have multiple UNIQUE constraints, and
they can contain a single NULL value.

9. What is the logical order of a SELECT statement?

• Answer: The logical order of execution is FROM, WHERE, GROUP BY, HAVING, SELECT,
and finally, ORDER BY. Understanding this order is crucial for writing efficient queries
and predicting their behavior.

10. What is a NULL value?

• Answer: A NULL value represents the absence of a value or unknown data. It is not
equivalent to zero or a blank space, and any arithmetic operation or comparison with
NULL will result in NULL.

1.2 Data Retrieval & Manipulation

11. What is the difference between DELETE and TRUNCATE?

• Answer: DELETE is a DML command that removes rows one by one, is logged, and
can be rolled back. TRUNCATE is a faster DDL command that deallocates data pages,
is not logged, and cannot be rolled back.

12. How do you find the number of rows in a table?

• Answer: The most common way is SELECT COUNT(*) FROM table_name;. COUNT(*)
counts all rows, including those with NULL values, and is generally the most efficient
method.

13. How do you find duplicate records in a table?

• Answer: To find duplicates, you can use GROUP BY with an aggregate function. The
query SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING
COUNT(*) > 1; will return the values that appear more than once.

14. What is the purpose of GROUP BY?

• Answer: The GROUP BY clause is used to partition the rows into groups based on the
values in specified columns. It is typically used in conjunction with aggregate
functions like SUM, AVG, or COUNT to perform calculations on each group.
15. Explain WHERE vs. HAVING.

• Answer: The WHERE clause filters individual rows before aggregation, while the
HAVING clause filters the groups of rows after the GROUP BY operation has been
applied. You can use WHERE with non-aggregate columns and HAVING with
aggregate results.

16. How do you use the DISTINCT keyword?

• Answer: The DISTINCT keyword is placed in the SELECT list to eliminate duplicate
rows from the final result set. For example, SELECT DISTINCT country FROM
customers; will return a unique list of countries.

17. What is a JOIN? Name the types.

• Answer: A JOIN is a clause used to combine rows from two or more tables based on a
related column between them. The main types are INNER, LEFT, RIGHT, FULL, and
CROSS join.

18. Explain the difference between INNER JOIN and LEFT JOIN.

• Answer: An INNER JOIN returns only the rows where there is a match in both tables.
A LEFT JOIN returns all rows from the left table, and the matching rows from the right
table. If there is no match on the right, the result will have NULL values.

19. What is a CROSS JOIN?

• Answer: A CROSS JOIN returns the Cartesian product of two tables. It combines every
row from the first table with every row from the second table, resulting in a large
result set. It is rarely used in production.

20. What is a self-join? Give an example.

• Answer: A self-join is a join of a table to itself, typically using aliases to differentiate

between the two instances. A classic example is finding a manager's name from an
employees table where the manager_id refers to another employee's id.

1.3 Data Dictionary & Object Management

21. What is a View?

• Answer: A view is a virtual table that does not store data itself. It is a stored SELECT
query that presents data from one or more underlying tables. Views are often used
to simplify complex queries and for security purposes.
22. Can you modify data through a view?

• Answer: Yes, you can modify data through a view, but only under certain conditions.
The view must be based on a single table and cannot contain GROUP BY, DISTINCT, or
joins with multiple key-preserved tables.

23. What is an Index? Why is it important?

• Answer: An index is a data structure that provides fast access to rows in a table. It is
crucial for improving the performance of queries, especially those with WHERE, JOIN,
or ORDER BY clauses.

24. What are the different types of indexes?

• Answer: The most common are B-tree indexes, which are best for columns with high
cardinality (many distinct values), and Bitmap indexes, which are ideal for columns
with low cardinality (few distinct values, like a gender column).

25. What is a synonym?

• Answer: A synonym is an alias for a database object like a table, view, or procedure.
It provides a more convenient way to reference an object and can hide the object's
real name and owner.

26. How do you find the source code of a procedure or function in Oracle?

• Answer: You can query the data dictionary view user_source or all_source. A query
like SELECT text FROM user_source WHERE name = 'MY_PROCEDURE' ORDER BY line;
will retrieve the source code.

27. What is the purpose of SYSDATE and SYSTIMESTAMP?

• Answer: SYSDATE returns the current date and time of the database server.
SYSTIMESTAMP is more precise, returning the current date and time with fractional
seconds and the time zone.

28. What is the DUAL table?

• Answer: DUAL is a special, one-row, one-column table in Oracle. It's a useful utility
for running queries that don't need to select from a specific table, such as retrieving
system values like SYSDATE.

29. What is ROWNUM?

• Answer: ROWNUM is a pseudo-column in Oracle that assigns a sequential number to

each row in the result set as it is retrieved. The number starts at 1.
30. What is a common pitfall of ROWNUM?

• Answer: A common pitfall is that ROWNUM is assigned before the ORDER BY clause
is applied. To get a correctly ordered top-N result, you must use an inline view to sort
the data first, and then apply the ROWNUM filter to the sorted result.

1.4 Functions & Operators

31. Explain UNION vs. UNION ALL.

• Answer: Both operators combine the result sets of two or more SELECT statements.
UNION removes duplicate rows and is slower due to the sorting involved, while
UNION ALL retains all rows, including duplicates, and is more performant.

32. What is a subquery?

• Answer: A subquery is a SELECT statement nested inside another SQL statement. It is

often used to return a set of values that can be used in a WHERE or FROM clause of
the main query.

33. What is a correlated subquery? When is it a performance issue?

• Answer: A correlated subquery is a subquery that references a column from the

outer query. It's a performance issue because it is re-executed for every single row
returned by the outer query, which can be very inefficient for large datasets.

34. What is a WITH clause (CTE)?

• Answer: A WITH clause, or Common Table Expression, defines a temporary named

result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE
statement. It's excellent for improving query readability and simplifying complex
logic.

35. What is the purpose of the COALESCE function?

• Answer: COALESCE is a standard SQL function that returns the first non-null
expression in a list of arguments. It is useful for replacing NULL values with a default
or alternative value.

36. Explain NVL vs. NVL2.

• Answer: NVL is an Oracle-specific function that takes two arguments and returns the
second if the first is NULL. NVL2 takes three arguments: it returns the second if the
first is not NULL, and the third if it is NULL.

37. How do you get the last day of the month for a given date?

• Answer: The LAST_DAY function in Oracle can be used for this. For example, SELECT
LAST_DAY(SYSDATE) FROM DUAL; would return the last day of the current month.
38. How do you find the Nth highest salary?

• Answer: The most robust and efficient method is using a window function like
DENSE_RANK(). You would PARTITION BY and ORDER BY the salary, then filter for the
desired rank in an outer query.

39. What is the difference between RANK() and DENSE_RANK()?

• Answer: RANK() assigns the same rank to rows with the same value, but it skips the
next rank number. DENSE_RANK() also assigns the same rank to ties but does not
skip any numbers, creating a continuous sequence.

40. What is the DECODE function?

• Answer: DECODE is an Oracle-specific function that provides if-then-else logic based

on equality checks. CASE is the standard SQL equivalent and is generally preferred for
its readability and flexibility.

Part 2: Advanced SQL & Performance (60 Questions)

2.1 Advanced Queries & Analytics

41. How would you calculate a running total in a query?

• Answer: You can use the SUM window function with an OVER clause. The syntax
SUM(salary) OVER (ORDER BY employee_id) would calculate a cumulative sum of
salaries for each employee, ordered by their ID.

42. What are LEAD and LAG functions?

• Answer: LEAD and LAG are analytic functions that provide access to rows at a
physical offset from the current row. LEAD looks ahead, while LAG looks back.

43. Give a use case for the LAG function.

• Answer: A common use case for LAG is to calculate the difference between the
current row's value and the previous row's value. For example, to find the month-
over-month sales growth.

44. What is NTILE?

• Answer: NTILE is an analytic function that divides an ordered result set into a
specified number of groups (or tiles) and assigns a tile number to each row. This is
useful for distributing data into buckets, such as for a percentile analysis.
45. Explain the CONNECT BY clause for hierarchical queries.

• Answer: The CONNECT BY clause is an Oracle-specific feature used to query

hierarchical data (e.g., organizational charts). It specifies the relationship between a
parent row and a child row, with PRIOR referencing the parent's value.

46. How would you find all employees under a specific manager in a hierarchy?

• Answer: You would use a CONNECT BY query. The query SELECT employee_name
FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id
= manager_id; would return all employees reporting to the manager with ID 100.

47. What is the ROLLUP operator?

• Answer: The ROLLUP operator is an extension of the GROUP BY clause. It generates

subtotals for each specified group and a grand total for all groups, making it a
powerful tool for summary reports.

48. What is a PIVOT clause?

• Answer: The PIVOT clause is used to rotate rows into columns. It takes a column's
unique values and turns them into distinct columns, with an aggregate function
applied to the corresponding values.

49. What is a temporary table?

• Answer: A temporary table is a table designed to store intermediate results for a

short period, either for a single transaction or for the entire user session. It is useful
for simplifying complex, multi-step queries.

50. What is the MERGE statement?

• Answer: The MERGE statement is a DML command that allows for a conditional
INSERT, UPDATE, or DELETE on a target table based on a source table. It is highly
efficient for synchronizing data.

2.2 Performance Tuning & Optimization

51. How do you analyze a slow-running query?

• Answer: I start by using EXPLAIN PLAN to see the optimizer's execution plan. This
reveals if the query is performing full table scans. Then, I might use SQL Trace to get
detailed statistics on CPU time, physical reads, and logical reads to pinpoint
bottlenecks.
52. What is EXPLAIN PLAN? What key information does it provide?

• Answer: EXPLAIN PLAN is a tool that shows the query optimizer's planned execution
path for a SQL statement. Key information it provides includes the access method
(e.g., index scan, full table scan), join method (e.g., hash join, nested loops), and the
order of operations.

53. What is a full table scan? Is it always bad?

• Answer: A full table scan reads every row in a table. It's not always bad; it can be
more efficient for small tables or when the query needs to retrieve a large
percentage of the table's data, making an index lookup more costly.

54. When would you recommend a B-tree index?

• Answer: A B-tree index is recommended for columns with high cardinality (many
distinct values), especially those used frequently in WHERE clauses, JOIN conditions,
and ORDER BY clauses.

55. When would you recommend a Bitmap index?

• Answer: A Bitmap index is best suited for columns with low cardinality (few distinct
values), such as a gender or status column. They are particularly effective for queries
with multiple AND or OR conditions.

56. What is a composite index?

• Answer: A composite index is an index on two or more columns of a table. It is

effective for queries that frequently filter on a combination of columns, as it allows
the optimizer to perform a single index scan to satisfy the conditions.

57. What are table statistics? How do you gather them?

• Answer: Table statistics are metadata about the data distribution within a table and
its indexes. The Oracle Optimizer uses this information to choose the most efficient
execution plan. They are gathered using the DBMS_STATS.GATHER_TABLE_STATS
procedure.

58. What is a query hint? Give an example.

• Answer: A query hint is a comment placed in a SQL statement (/*+ ... */) that directs
the optimizer to use a specific execution plan. An example is SELECT /*+ FULL(emp)
*/ * FROM employees;, which forces a full table scan.

59. What is a materialized view?

• Answer: A materialized view is a physical database object that stores the result of a
query. It is a pre-calculated cache of data that is useful for speeding up complex
queries and reports, as the data doesn't need to be computed at runtime.
60. How would you use DBMS_PROFILER?

• Answer: DBMS_PROFILER is a package for profiling PL/SQL code. I would use it to

identify performance bottlenecks in my procedures by measuring the execution time
for each line of code, which is invaluable for optimization.

Part 3: PL/SQL Expert (60 Questions)

3.1 PL/SQL Fundamentals & Blocks

61. What is PL/SQL? How does it extend SQL?

• Answer: PL/SQL is a procedural extension to SQL. It adds procedural programming

constructs like variables, loops, conditional statements, and error handling, allowing
developers to write complex logic that executes multiple SQL statements as a single
programmatic unit.

62. What are the three types of PL/SQL blocks?

• Answer: The three types are Anonymous blocks (run once), Named blocks
(procedures and functions, stored in the database), and Packages (a collection of
related named blocks and variables).

63. What are the components of a PL/SQL anonymous block?

• Answer: A PL/SQL anonymous block has four parts: the DECLARE section for variable
declarations, the BEGIN section for executable statements, the EXCEPTION section
for error handling, and the END statement.

64. Explain %TYPE and %ROWTYPE.

• Answer: %TYPE is an attribute used to declare a variable with the same data type
and size as a table column. %ROWTYPE is used to declare a record variable that can
hold an entire row of data from a table or a cursor.

65. What are the parameter modes in PL/SQL subprograms?

• Answer: The parameter modes are IN (the default, read-only), OUT (write-only,
returns a value), and IN OUT (read-write, passes a value in and returns a new value).

66. What is an implicit cursor?

• Answer: An implicit cursor is a cursor that Oracle automatically manages for SELECT
INTO statements that return a single row, as well as for all INSERT, UPDATE, and
DELETE statements.
67. What is an explicit cursor?

• Answer: An explicit cursor is a named control structure used for processing a multi-
row query. The developer must explicitly DECLARE, OPEN, FETCH from, and CLOSE it,
giving precise control over the result set.

68. What are the benefits of a cursor FOR loop?

• Answer: A cursor FOR loop is a simplified way to process an explicit cursor. Its main
advantage is that it automatically handles the OPEN, FETCH, and CLOSE operations,
reducing code and the chance of errors.

69. How do you handle exceptions in PL/SQL?

• Answer: Exceptions are handled in the EXCEPTION section of a PL/SQL block. We can
catch specific predefined exceptions like NO_DATA_FOUND or TOO_MANY_ROWS or
use the generic WHEN OTHERS THEN clause for unexpected errors.

70. What are SQLCODE and SQLERRM?

• Answer: SQLCODE is a built-in function that returns the Oracle error number for the
most recent error. SQLERRM returns the corresponding error message. They are
typically used in the EXCEPTION block for logging errors.

3.2 Procedures, Functions & Packages

71. What is the difference between a PROCEDURE and a FUNCTION?

• Answer: A PROCEDURE is a subprogram that performs an action and does not have
to return a value. A FUNCTION must return a single value and is typically used for
computations. Functions can be called from within SQL statements, while procedures
cannot.

72. Can a function perform DML? If so, why is it bad practice?

• Answer: Yes, a function can perform DML, but it's considered a bad practice and is
restricted when the function is called from a SELECT statement. This is because
functions should be "pure" and not have side effects on the database state, which
can lead to data inconsistency.

73. What is a PL/SQL PACKAGE?

• Answer: A PL/SQL package is a schema object that groups logically related PL/SQL
types, variables, procedures, and functions. It consists of a Package Specification (the
public interface) and a Package Body (the implementation).
74. What are the main advantages of using packages?

• Answer: Packages offer modularity, information hiding (encapsulation), improved

performance (due to a single memory load), and the ability for package variables to
maintain their state for the duration of a session.

75. What is a package state?

• Answer: The package state refers to the values of package-level variables that persist
for the entire duration of a user's session. They act like global variables that are
initialized on the first call to a packaged subprogram and retain their values until the
session ends.

76. How would you expose a variable to a user session from a package?

• Answer: You declare the variable in the package specification. Any program unit with
EXECUTE rights on the package can then read or write to this variable directly,
providing a shared state.

77. What is a REF CURSOR? When would you use it?

• Answer: A REF CURSOR is a pointer to a cursor's result set. I would use it when I need
to return a dynamic result set from a stored procedure to a client application (e.g., a
Java program) or to another PL/SQL block.

78. What is EXECUTE IMMEDIATE?

• Answer: EXECUTE IMMEDIATE is a PL/SQL command used to execute dynamic SQL

statements. It allows you to build and run SQL statements at runtime, which is
essential for tasks where the table or column names are not known at compile time.

79. How do you prevent SQL injection with dynamic SQL?

• Answer: The primary way to prevent SQL injection is by using bind variables with the
USING clause. Instead of concatenating user input into the SQL string, you pass the
input as a separate variable, which ensures it is treated as data, not executable code.

80. What are BULK COLLECT and FORALL?

• Answer: BULK COLLECT is a clause for fetching a multi-row query into a collection in a
single context switch. FORALL is a statement for executing a DML operation on all
elements of a collection with a single context switch. Together, they dramatically
improve performance for large datasets.
3.3 Triggers & Advanced Topics

81. What is a TRIGGER?

• Answer: A trigger is a PL/SQL block that is stored in the database and automatically
executes in response to a DML (INSERT, UPDATE, DELETE) or DDL event (CREATE,
ALTER, DROP) on a table.

82. Explain the difference between a row-level and a statement-level trigger.

• Answer: A row-level trigger is specified with FOR EACH ROW and fires once for every
row affected by the triggering statement. A statement-level trigger fires only once
per statement, regardless of how many rows it affects.

83. What is a MUTATING TABLE error?

• Answer: A mutating table error (ORA-04091) occurs when a row-level trigger queries
or modifies the same table that is being changed by the triggering DML statement.
Oracle raises this error to prevent data inconsistency.

84. How do you resolve a MUTATING TABLE error?

• Answer: The modern and most effective way is to use a compound trigger. You can
collect data in the AFTER EACH ROW section and then perform the necessary DML
operations on the same table in the AFTER STATEMENT section, which fires after all
row-level changes are complete.

85. What is an AUTONOMOUS TRANSACTION?

• Answer: An autonomous transaction is an independent transaction that can be

committed or rolled back without affecting the main transaction that initiated it. It is
declared with the PRAGMA AUTONOMOUS_TRANSACTION directive.

86. Give an example of where an AUTONOMOUS TRANSACTION is useful.

• Answer: A perfect use case is logging. If a procedure fails and its transaction is rolled
back, the error log entry (which is in an autonomous transaction) will be committed
and saved regardless, providing a record of the failure.

87. What is a compound trigger?

• Answer: A compound trigger combines all trigger timing points (BEFORE STATEMENT,
BEFORE EACH ROW, AFTER EACH ROW, AFTER STATEMENT) into a single PL/SQL
block. It is the ideal solution for complex trigger logic and for avoiding mutating table
errors.
88. What is RETURNING INTO?

• Answer: RETURNING INTO is a clause in INSERT, UPDATE, or DELETE statements that

returns values from the modified rows into PL/SQL variables. It's a highly efficient
way to get a newly generated ID or an updated value without a separate SELECT
statement.

89. What is a pipelined function?

• Answer: A pipelined function is a special type of table function that returns rows as a
collection. Instead of returning the entire collection at once, it "pipes" the rows back
to the consuming query one by one, which is excellent for large datasets and
streaming data.

90. What is native vs. interpreted PL/SQL compilation?

• Answer: Interpreted PL/SQL is compiled into an intermediate code (bytecode) that

the PL/SQL engine executes. Native PL/SQL is compiled directly into machine code,
bypassing the engine and potentially offering better performance, though at the cost
of longer compilation times.

Part 4: Modern Developer Toolkit (30 Questions)

4.1 Git

91. What is Git?

• Answer: Git is a distributed version control system that allows developers to track
changes in code, collaborate with a team, and manage different versions of a project.

92. Explain the difference between git pull and git fetch.

• Answer: git fetch downloads the latest changes from the remote repository to your
local repository but does not merge them. git pull is a combination of git fetch
followed by git merge, so it downloads and immediately integrates the changes.

93. What are the three states of a Git workflow?

• Answer: The three states are the Working Directory (your local files), the Staging
Area (the files you've prepared for the next commit), and the Local Repository (the
committed changes).

94. How do you create a new branch and switch to it?

• Answer: The most common command for this is git checkout -b new-branch-name.
This single command creates a new branch and immediately switches your working
directory to that branch.
95. How do you resolve a merge conflict?

• Answer: When a merge conflict occurs, Git marks the conflicting areas in the files. I
would manually edit the files to choose the correct code, remove the conflict
markers, git add the resolved files, and then git commit to finalize the merge.

96. How do you revert a commit that's already been pushed?

• Answer: You use git revert <commit_hash>. This is the safest way to undo a change
on a shared branch because it creates a new commit that undoes the changes of the
previous commit, preserving the history for all team members.

97. What is git stash?

• Answer: git stash is a command that temporarily saves your uncommitted changes.
It's useful when you need to switch branches to handle an urgent task without
committing your incomplete work.

98. What is the purpose of .gitignore?

• Answer: The .gitignore file is used to specify which files and directories Git should
ignore. This prevents non-essential files, such as IDE configuration files, temporary
files, or compiled binaries, from being committed to the repository.

99. What's the difference between git merge and git rebase?

• Answer: git merge combines two branches by creating a new merge commit. git
rebase rewrites history by moving the commits of one branch on top of another,
creating a cleaner, linear commit history.

100. How do you undo an un-pushed commit?

• Answer: You can use git reset --hard HEAD~1 to undo the last commit and discard all
changes. If you want to undo the commit but keep the changes in your working
directory, you can use git reset HEAD~1.

4.2 Unix / Shell Scripting

101. What is a shell script?

• Answer: A shell script is a text file that contains a series of commands for the
Unix/Linux shell. It's a powerful tool for automating repetitive tasks, such as running
a database job or performing file cleanup.

102. How do you make a shell script executable?

• Answer: You use the chmod command to change the file permissions. The command
chmod +x script_name.sh adds execute permissions to the script, allowing it to be
run.
103. What is a cron job?

• Answer: A cron job is a scheduled task in Unix/Linux that runs automatically at a

specific time or interval. It is managed by a daemon called crond and is configured
using the crontab utility.

104. How do you list all files in a directory, including hidden files?

• Answer: The ls command is used to list files. The -a option lists all files, including
those starting with a dot (.), which are hidden. So, the command is ls -a.

105. What is grep used for?

• Answer: grep is a powerful command-line utility for searching for lines that match a
specific pattern in one or more text files. It's essential for log analysis and finding
specific strings in code.

106. How do you pass output of one command as input to another?

• Answer: You use a pipe (|). The standard output of the first command is redirected
to become the standard input of the second command. For example, ls -l | grep 'file'
lists files and then filters the output for the word "file."

107. How do you redirect stdout and stderr to a single file?

• Answer: The standard way is command > [Link] 2>&1. This first redirects standard
output (>) to the file and then redirects standard error (2>) to the same location
(&1).

108. What is the find command? Give an example.

• Answer: The find command is used to search for files and directories in a file system.
A common example is find . -name "*.log" to find all files ending with .log in the
current directory and its subdirectories.

109. How do you check the exit status of the last command?

• Answer: You can use the special variable $?. The value 0 indicates success, while any
non-zero value indicates an error.

110. How would you read user input into a shell script?

• Answer: You would use the read command. A good practice is to use the -p flag for a
prompt, like read -p "Enter your name: " name_var, which stores the input in the
name_var variable.
Part 5: Behavioral & HR Questions (20 Questions)

111. Tell me about yourself.

• Answer: "I'm a PL/SQL Developer with a strong foundation in database design and
optimization. For the past two years, I've focused on building robust and scalable
solutions, from writing complex SQL queries to developing and maintaining stored
procedures and packages. My key strength lies in translating business needs into
efficient database logic. I'm now looking for a challenging role where I can apply my
skills and grow with a forward-thinking team."

112. What are your biggest strengths?

• Answer: "My biggest strength is my analytical and problem-solving skills. I don't just
focus on the code; I take the time to understand the underlying data and business
logic. This approach allows me to create solutions that are not only correct but also
highly performant and scalable for the long term."

113. What is your biggest weakness?

• Answer: "In the past, my biggest weakness was getting too focused on achieving the
'perfect' solution, which sometimes delayed a project. I've learned to be more
pragmatic by setting time limits for a task and prioritizing a good, working solution
over a perfect, delayed one. I also now involve team members earlier to get their
perspective."

114. Why do you want to leave your current job?

• Answer: "I'm very grateful for the experience I've gained, especially in [mention a
specific skill like performance tuning]. However, I've reached a point where I'm
seeking more advanced challenges. I'm particularly drawn to this role because of
[mention something specific about the company or the job description], and I believe
it's the right environment for my professional growth."

115. Where do you see yourself in five years?

• Answer: "In five years, I see myself as a senior PL/SQL developer or a team lead,
contributing to architectural decisions and mentoring junior developers. I want to
continue deepening my expertise in performance optimization and data modeling,
and I believe this company provides the ideal platform to achieve those goals."
116. How do you handle a tight deadline?

• Answer: "When faced with a tight deadline, my first step is to break the project into
smaller, manageable tasks and prioritize them. I communicate proactively with my
manager about my progress and any potential risks. I'm a firm believer in the 'no
surprises' principle, and I'm always willing to put in the extra effort to meet a critical
deadline."

117. Describe a time you made a mistake at work and how you handled it.

• Answer: "I once made a logical error in a script that caused an incorrect data
calculation. The moment I realized it, I immediately informed my manager. I then
focused on diagnosing the root cause, providing a clear plan to fix the data, and
implementing a permanent solution. I learned the importance of peer reviews and a
more rigorous testing process."

118. How do you handle a disagreement with a team member?

• Answer: "I believe in resolving disagreements professionally and constructively. I

would first listen to their perspective to understand their reasoning. Then, I would
present my viewpoint with a focus on data and technical facts, not personal opinions.
We would work collaboratively to find a solution that is best for the project, not just
one of us."

119. What motivates you in your work?

• Answer: "I'm motivated by the challenge of solving complex problems and the
satisfaction of seeing my work directly contribute to business success. I'm also highly
motivated by a collaborative team environment where I can learn from my
colleagues and share my knowledge."

120. Do you have any questions for me?

• Answer: "What does a typical day look like for a PL/SQL developer on your team?
Could you describe the biggest technical challenges the team is currently facing?
What is the team's process for code reviews and quality assurance?"
Part 6: Advanced Real-Time Scenarios (80+ Questions)

6.1 Practical SQL & Performance Scenarios

121. Scenario: A query is taking too long to run. EXPLAIN PLAN shows TABLE ACCESS FULL on
a large table. What are your first steps?

• Answer: My first step is to check if the WHERE clause is filtering on columns that are
good candidates for an index. If an index is missing, I would recommend creating
one. I would also verify that the table's statistics are up-to-date using DBMS_STATS.

122. Scenario: You need to find the top 5 highest-paid employees in each department. How
would you do this?

• Answer: I would use the ROW_NUMBER() analytic function with a PARTITION BY

clause. The query would be SELECT * FROM (SELECT employee_name, salary,
department_id, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY
salary DESC) as rn FROM employees) WHERE rn <= 5;.

123. Scenario: A new requirement needs a new column on a live production table with
millions of rows. How would you handle this to minimize downtime?

• Answer: I would use ALTER TABLE ADD column_name datatype default NULL;. This is
a non-locking DDL operation. If a non-null default value is required, I would first add
the column as NULL and then update the value in batches to avoid locking the entire
table for a long period.

124. Scenario: A report requires a running total of salaries, but only for the current
department.

• Answer: I would use the SUM analytic function with a PARTITION BY clause. The
query SELECT salary, department_id, SUM(salary) OVER (PARTITION BY
department_id ORDER BY employee_id) FROM employees; would calculate the
running total independently for each department.

125. Scenario: How do you find which session is locking a row in a table?

• Answer: I would query the Oracle data dictionary views. I'd join v$session and v$lock
to find the blocking session's SID, serial#, and the object it is locking. This provides a
clear picture of who and what is causing the lock.

126. Scenario: A table has two columns, start_date and end_date. How do you find all
overlapping date ranges?

• Answer: I would perform a self-join on the table where the join condition is
a.end_date >= b.start_date AND a.start_date <= b.end_date. This condition correctly
identifies all pairs of date ranges that overlap.
127. Scenario: You need to generate a series of dates without a permanent table. How
would you do it?

• Answer: I would use a recursive Common Table Expression (CTE). For example, WITH
dates (dt) AS (SELECT TRUNC(SYSDATE) FROM DUAL UNION ALL SELECT dt + 1 FROM
dates WHERE dt < TRUNC(SYSDATE) + 7) SELECT dt FROM dates; would generate a list
of dates for the next week.

128. Scenario: A SELECT statement in a PL/SQL block is slow. How would you optimize it?

• Answer: I would first get the execution plan using EXPLAIN PLAN to identify
bottlenecks. I'd look for full table scans on large tables and consider adding an index.
I would also check for correlated subqueries and try to convert them into joins for
better performance.

129. Scenario: You need to conditionally update a table based on values from another table.

• Answer: The MERGE statement is the ideal solution for this. It allows you to perform
an UPDATE, INSERT, or DELETE in a single pass, which is far more efficient than
separate DML statements.

130. Scenario: A query with multiple UNION ALL statements is taking a long time. What's a
potential alternative?

• Answer: If the aggregation logic is similar across the UNION ALL branches, using
GROUP BY GROUPING SETS could be a more efficient and readable alternative. It
processes the data once and then creates the different grouping combinations.

6.2 Practical PL/SQL & Bulk Processing

131. Scenario: You have a nightly job that needs to process 1 million records. A simple FOR
loop with an UPDATE statement is too slow. What's a better approach?

• Answer: The slowness is due to context switching. The best approach is to use BULK
COLLECT to fetch rows in batches into a collection and then use FORALL to perform
the DML operation on the entire collection at once, which significantly reduces
context switching.

132. Scenario: Write a procedure to log an error to a log table, but the log must be
committed even if the main procedure is rolled back.

• Answer: I would create a separate logging procedure and declare it as an

AUTONOMOUS TRANSACTION using PRAGMA AUTONOMOUS_TRANSACTION. This
ensures the log entry is committed independently of the calling procedure's
transaction.
133. Scenario: You have an AFTER UPDATE row-level trigger that needs to update a summary
table. What if the trigger's DML causes a mutating table error?

• Answer: To avoid the mutating table error, I would use a compound trigger. I would
store the primary keys of the changed rows in a package-level collection in the AFTER
EACH ROW section, and then perform the UPDATE on the summary table in the
AFTER STATEMENT section.

134. Scenario: A procedure takes a table name as a parameter and needs to delete its
contents. What's the best way to implement this safely?

• Answer: I would use dynamic SQL with EXECUTE IMMEDIATE 'TRUNCATE TABLE ' ||
p_table_name;. To ensure safety, I would first validate the table name against
USER_TABLES or DBA_TABLES to prevent a malicious user from executing an
unintended command.

135. Scenario: How do you test a package after you've created it?

• Answer: I would write an anonymous PL/SQL block or a dedicated test procedure. I

would call each public procedure and function in the package with various input
parameters and use DBMS_OUTPUT.PUT_LINE to print the results and verify their
correctness.

136. Scenario: A nightly batch job needs to run automatically. How would you schedule it?

• Answer: I would use the DBMS_SCHEDULER package. It's the modern and robust way
to schedule jobs in Oracle. I can define the job, its schedule, and the program it
executes, and the database will manage its execution.

137. Scenario: A procedure needs to return a dynamic list of employees based on a filter.
How would you implement this to be consumed by a front-end application?

• Answer: I would use a REF CURSOR as an OUT parameter. The procedure would open
the cursor for the dynamic SELECT statement, and the front-end application would
then fetch and process the rows from this cursor.

138. Scenario: You have a FOR loop that fetches rows one by one. The query is fast, but the
loop is slow. Why?

• Answer: The slowness is due to the context switching between the PL/SQL engine
and the SQL engine for each row fetched. This overhead becomes significant with a
large number of rows. The solution is to use BULK COLLECT to fetch in batches.
139. Scenario: You have a procedure that updates a record and needs to get the value of a
newly generated sequence ID.

• Answer: I would use the RETURNING INTO clause. The statement would look like
INSERT INTO employees (id, name) VALUES (my_seq.NEXTVAL, 'John') RETURNING id
INTO v_new_id;. This is a single, efficient operation.

140. Scenario: A junior developer wrote a trigger to perform complex business logic. What
would you advise them to do differently?

• Answer: I would advise them to keep the trigger as simple as possible. The complex
business logic should be moved into a stored procedure or a packaged procedure,
which the trigger can then call. This makes the code more modular, reusable, and
easier to debug.

141. Scenario: How do you handle a custom business error in PL/SQL?

• Answer: I would use RAISE_APPLICATION_ERROR. This built-in procedure allows me

to raise a custom error message with a unique error number, which can then be
caught and handled by the calling program.

142. Scenario: You need to read a file from the server's file system using PL/SQL. What
package would you use?

• Answer: I would use the UTL_FILE package. This package provides file I/O
capabilities. Before using it, I would need to have the database administrator create a
directory object and grant read/write permissions to my user.

143. Scenario: How would you implement a simple, multi-level hierarchy using PL/SQL?

• Answer: The most direct way is to use a hierarchical query with a CONNECT BY
clause. This allows you to traverse a tree structure by defining the parent-child
relationship.

144. Scenario: A procedure needs to handle a NO_DATA_FOUND exception. Write the

EXCEPTION block.

BEGIN

-- some SELECT INTO statement

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No records found for the given criteria.');

-- optionally, handle the error gracefully

END;
145. Scenario: You need to optimize an UPDATE statement that is performing a full table
scan.

• Answer: I would first check if the WHERE clause is using an indexed column. If not, I
would recommend creating a B-tree index on that column. If the WHERE clause
contains a function on the column, I would consider creating a function-based index.

146. Scenario: What is the difference between a table function and a pipelined function?

• Answer: A table function is a function that returns a collection of rows, and its
output can be used in the FROM clause of a SELECT statement. A pipelined function
is a special type of table function that returns rows one at a time, which is much
more efficient for large result sets as it avoids a large memory footprint.

147. Scenario: What is a common way to manage different data access privileges in a
PL/SQL-based application?

• Answer: A common and secure practice is to grant EXECUTE privileges on a package

that contains all the DML logic, rather than granting direct DML privileges on the
base tables to the application user. This enforces a data access layer and ensures all
changes go through a controlled API.

148. Scenario: How do you find all the procedures in the database that were created by your
user?

• Answer: I would query the user_objects data dictionary view. The query would be
SELECT object_name, status FROM user_objects WHERE object_type = 'PROCEDURE';

149. Scenario: How would you ROLLBACK an UPDATE statement in a PL/SQL block?

• Answer: The ROLLBACK command will undo all DML changes back to the last
COMMIT or ROLLBACK. In a PL/SQL block, if an exception is raised in the BEGIN
section, the ROLLBACK command can be placed in the EXCEPTION block to undo the
changes.

150. Scenario: What is the DBMS_SCHEDULER package used for?

• Answer: DBMS_SCHEDULER is a powerful, built-in Oracle package used for creating,

managing, and running jobs within the database. It is a more robust and flexible
alternative to the older DBMS_JOB package.
Part 7: Further Concepts and Questions (50+ Questions)

151. What is an INSTEAD OF trigger?

• Answer: An INSTEAD OF trigger is a trigger on a view that executes a PL/SQL block

instead of the triggering DML statement. It is used to perform DML on non-updatable
views by redirecting the DML to the underlying tables.

152. How do you find the session ID of a current PL/SQL session?

• Answer: You can use SELECT SYS_CONTEXT('USERENV', 'SID') FROM DUAL; to get the
session ID.

153. What are the benefits of using a stored procedure over embedding SQL in an
application?

• Answer: Stored procedures offer performance benefits (pre-compiled code),

improved security (access control via EXECUTE privileges), and reduced network
traffic (single call to the database). They also promote code reuse and
maintainability.

154. Explain the difference between CREATE and CREATE OR REPLACE.

• Answer: CREATE will fail if the object already exists. CREATE OR REPLACE will create
the object if it doesn't exist, and if it does, it will drop and recreate it, preserving any
privileges granted on it.

155. What is a function-based index? When would you use one?

• Answer: A function-based index is an index built on the result of a function or

expression. It is useful for optimizing queries where the WHERE clause contains a
function on a column, such as WHERE UPPER(last_name) = 'SMITH'.

156. How do you use the RAISE_APPLICATION_ERROR procedure?

• Answer: RAISE_APPLICATION_ERROR is used in a PL/SQL block to raise a user-defined

error with an error number in the range of -20000 to -20999 and a custom error
message.

157. What is DBA_TABLES vs. ALL_TABLES vs. USER_TABLES?

• Answer: USER_TABLES shows all tables owned by the current user. ALL_TABLES
shows all tables the current user has access to. DBA_TABLES shows all tables in the
database, and only a DBA can access it.
158. How can you commit and rollback within a loop?

• Answer: It is generally a bad practice to commit or rollback within a loop because it

can lead to data inconsistency. A better approach is to use bulk processing with a
single commit after the loop, or to use an autonomous transaction if a commit is
absolutely necessary within the loop.

159. How would you handle a ORA-01403 error when using SELECT INTO?

• Answer: The ORA-01403 (NO_DATA_FOUND) error occurs when a SELECT INTO

statement returns no rows. You handle this by enclosing the statement in an
EXCEPTION block and catching WHEN NO_DATA_FOUND THEN.

160. What is DBA_OBJECTS?

• Answer: DBA_OBJECTS is a powerful data dictionary view that lists every database
object in the entire database, along with its owner, type, and status.

161. How would you optimize a large FOR loop that iterates through a cursor?

• Answer: I would replace the FOR loop with BULK COLLECT and FORALL. This
approach fetches and processes data in batches, dramatically reducing the number of
context switches and improving performance.

162. How do you find the current user's name in a PL/SQL block?

• Answer: SELECT user FROM DUAL; or SELECT SYS_CONTEXT('USERENV',

'CURRENT_USER') FROM DUAL;

163. How do you pass a variable from a shell script to a SQL script?

• Answer: You can pass a variable to sqlplus as a command-line argument. The SQL
script would then use &1, &2, etc., to reference these arguments.

164. How would you handle a VARCHAR2 string that might exceed its defined length?

• Answer: The best way is to use a SUBSTR function to truncate the string to the
maximum allowed length before inserting it. Another way is to declare the variable
using %TYPE to match the column's size automatically.

165. What is a row lock?

• Answer: A row lock (or DML lock) is a lock on a single row of data. It is acquired
automatically by an UPDATE or DELETE statement to prevent other transactions from
modifying the same row until the transaction is committed or rolled back.
166. What is a table lock?

• Answer: A table lock locks the entire table. It is acquired automatically by certain
DDL statements to prevent any other session from modifying the table structure or
data until the statement is complete.

167. How do you debug a procedure that is running slow using a trace?

• Answer: I would use DBMS_MONITOR.SESSION_TRACE_ENABLE to start tracing a

session. After running the procedure, I would use the TKPROF utility on the trace file
to generate a readable report showing execution times, reads, and other
performance metrics.

168. How do you use DBMS_OUTPUT?

• Answer: DBMS_OUTPUT is a package used to display output from PL/SQL blocks to

the screen. You must first enable it with SET SERVEROUTPUT ON, and then use
DBMS_OUTPUT.PUT_LINE to print messages.

169. What is a recursive CTE? Give an example.

• Answer: A recursive CTE is a WITH clause that can reference itself. It's used for
processing hierarchical data. An example is a query that finds all employees reporting
to a specific manager and then all employees reporting to them, and so on.

170. How would you design a package for a data loading module?

• Answer: I would design the package with a clear separation of concerns. The
specification would contain public procedures for starting, stopping, and monitoring
the job. The body would contain private procedures for the actual loading logic, error
handling, and transaction management.

171. How do you handle NULL values in a GROUP BY clause?

• Answer: GROUP BY treats all NULL values as a single group. If you want to replace
NULL with a different value for grouping, you can use the NVL or COALESCE function
within the GROUP BY clause.

172. How do you find the schema a table belongs to?

• Answer: SELECT owner FROM all_tables WHERE table_name = 'MY_TABLE';

173. What is the difference between V$SESSION and ALL_SESSIONS?

• Answer: V$SESSION is a dynamic performance view that shows information about all
current sessions connected to the database. ALL_SESSIONS is an obsolete view that
has been replaced by V$SESSION.
174. What is a subprogram in PL/SQL?

• Answer: A subprogram is a reusable PL/SQL block that can be invoked by other

PL/SQL blocks or applications. It refers to a PROCEDURE or a FUNCTION.

175. What are the advantages of using JOIN over a subquery?

• Answer: Joins are often more performant than subqueries because the database
optimizer can process them more efficiently. Joins are also more readable and easier
to debug, especially for a complex query.

176. How would you find all tables with a specific column name?

• Answer: SELECT table_name FROM user_tab_columns WHERE column_name =

'MY_COLUMN';

177. How can you find the execution plan of a specific query in v$sql?

• Answer: You can find the query in v$sql and then use
DBMS_XPLAN.DISPLAY_CURSOR with the SQL_ID and CHILD_NUMBER to display the
execution plan.

178. What is a CURSOR?

• Answer: A cursor is a pointer or a handle to a private SQL work area in memory. It's
used to process the result set of a query, typically row by row.

179. What is the difference between PL/SQL tables and nested tables?

• Answer: PL/SQL tables (or associative arrays) are sparse, have a VARCHAR2 or
NUMBER index, and exist only in PL/SQL memory. Nested tables are dense, have a
NUMBER index, and can be stored in the database.

180. How would you handle a DELETE statement in a BEFORE INSERT trigger?

• Answer: This is a mutating table scenario. It would not be possible to delete from the
same table in a BEFORE INSERT trigger. The correct approach would be to use a
compound trigger or a separate statement that handles the deletion outside the
trigger.

181. What is SQL Injection?

• Answer: SQL Injection is a code injection technique where malicious SQL is inserted
into an input field for execution. The solution is to always use bind variables and not
concatenate user input directly into a SQL statement.
182. How do you use git blame?

• Answer: git blame <file_name> shows the author of each line of a file, along with the
commit hash and timestamp. It's useful for finding out who last modified a specific
line of code.

183. How do you manage database scripts in Git?

• Answer: I would store DDL and DML scripts in a version-controlled repository. I

would use a tool like Liquibase or Flyway to manage schema changes and ensure
they are applied in the correct order across different environments.

184. What is the purpose of chmod?

• Answer: chmod is a Unix command to change the permissions of a file or directory,

specifying who can read, write, or execute it.

185. How do you find files that have been modified in the last 7 days?

• Answer: find . -mtime -7

186. How would you monitor a running cron job?

• Answer: I would redirect the cron job's output and error to a log file. I would then
check the log file for success or failure messages to monitor its execution.

187. What is EXPLAIN PLAN FOR SELECT * FROM EMP?

• Answer: This is an Oracle statement that provides the execution plan for the SELECT
query without actually running it.

188. What is the UNION operator?

• Answer: The UNION operator combines the result sets of two or more SELECT
statements and removes duplicate rows.

189. What is a VARRAY?

• Answer: A VARRAY is a PL/SQL collection type that is dense and has a fixed size. It can
be stored as a column in a database table.

190. How do you FETCH multiple rows from a cursor?

• Answer: You use BULK COLLECT to fetch multiple rows into a collection in a single
operation.

191. What is a cursor variable?

• Answer: A cursor variable (or REF CURSOR) is a pointer to a cursor. It allows a cursor
to be passed as a parameter to a subprogram or returned from a function.
192. What is dbms_scheduler?

• Answer: DBMS_SCHEDULER is a powerful package used for scheduling and managing

jobs within the Oracle database.

193. How would you implement a case statement in sql?

• Answer: SELECT CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS
salary_band FROM employees;

194. What is a GOTO statement?

• Answer: A GOTO statement transfers control to a labeled statement. It's generally

considered bad practice as it can make code unstructured and difficult to read.

195. How do you make a variable constant in PL/SQL?

• Answer: By using the CONSTANT keyword during declaration. my_constant

CONSTANT NUMBER := 10;

196. How would you find the total size of a table?

• Answer: You would query user_segments or dba_segments for the table's segment
name and sum the bytes column.

197. How would you find the number of rows inserted by a recent DML operation?

• Answer: You can use the SQL%ROWCOUNT cursor attribute, which returns the
number of rows affected by the most recent DML statement.

198. What are bind variables?

• Answer: Bind variables are placeholders in a SQL statement that are used to pass
data to the statement at runtime. They prevent SQL injection and allow the optimizer
to reuse the execution plan, improving performance.

199. What is a table alias?

• Answer: A table alias is a temporary, shorter name given to a table in a query. It

improves readability, especially with complex joins or self-joins.

200. What is a VIEW?

• Answer: A view is a virtual table that is based on the result of a SQL query.

201. What is a TRIGGER?

• Answer: A trigger is a stored PL/SQL block that automatically executes in response to

a specific event.
202. What is a COMMIT?

• Answer: COMMIT is a TCL command that saves all DML changes permanently to the
database.

203. What is a ROLLBACK?

• Answer: ROLLBACK is a TCL command that undoes all DML changes since the last
COMMIT or ROLLBACK.

This guide provides the complete set of questions and detailed, professional answers you
requested, ensuring you are thoroughly prepared for any interview.