100+ Plsql Developer Interview Questions and Answers

Procedures are used to perform an action, while functions return a value.

• Procedures do not return a value, while functions do.

• Functions can be used in SQL queries, while procedures cannot.

• Functions can be called from within SQL statements, while procedures cannot.

• Procedures can have OUT parameters to return multiple values, while functions can only return a single value.

SQL query to find the nth highest salary

• Use the ORDER BY clause to sort the salaries in descending order

• Use the LIMIT clause to select the nth highest salary

Using GROUP BY clause with aggregate functions can obtain distinct values without using the DISTINCT keyword.

• Use GROUP BY clause with aggregate functions like COUNT, SUM, AVG, etc.

• Example: SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

A dynamic cursor in database management allows for the execution of different SQL queries at runtime.

• Dynamic cursors are used when the SQL query to be executed is not known until runtime.

• They allow for flexibility in querying the database based on user input or other conditions.

• Dynamic cursors can be used to handle varying result sets or conditions in a more efficient manner.

• Example: Using a dynamic cursor to search for different products based on user-selected criteria.

VARCHAR can store up to 2000 bytes while VARCHAR2 can store up to 4000 bytes.

• VARCHAR can store variable-length character strings up to 2000 bytes.

• VARCHAR2 can store variable-length character strings up to 4000 bytes.

• VARCHAR will occupy space for NULL values while VARCHAR2 will not.

• VARCHAR2 is more efficient in terms of storage and performance compared to VARCHAR.

GTT stands for Global Temporary Table, which is a temporary table used to store data temporarily during a session.

• GTT is a temporary table that is created and used within a session

• Data in GTT is visible only to the session that created it

• GTT is automatically dropped at the end of the session or when the transaction is committed or rolled back

Some built-in functions in SQL include AVG, COUNT, MAX, MIN, SUM, and CONCAT.

• AVG: Calculates the average value of a numeric column

• COUNT: Counts the number of rows in a result set

• MAX: Returns the maximum value in a column

• MIN: Returns the minimum value in a column

• SUM: Calculates the sum of values in a column

• CONCAT: Concatenates two or more strings together

Explanation of triggers and their types in PL/SQL

• Triggers are database objects that are automatically executed in response to certain events

• Types of triggers include DML, DDL, and system triggers

• Syntax for creating a trigger: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name [FOR EACH ROW] [WHEN condition] BEGIN ... END;

I have used both implicit and explicit cursors in PL/SQL development.

• Implicit cursors are used for single-row queries while explicit cursors are used for multi-row queries.

• I have used explicit cursors with parameters to make the query more dynamic.

• I have also used cursor variables to pass cursors as parameters to procedures and functions.

• Examples of cursor types I have used include FOR LOOP, FETCH, and UPDATE cursors.

Yes, it is possible to update data in a view using INSTEAD OF triggers.

• Views are virtual tables that display data from one or more tables.

• By using INSTEAD OF triggers, you can update data in a view by specifying the logic to handle the update operation.

• The trigger intercepts the update operation on the view and executes the specified logic to update the underlying tables.

• For example, you can create an INSTEAD OF trigger on a view to update data in multiple tables based on cer...read more

RDBMS is a type of DBMS that stores data in a structured manner using tables with relationships.

• DBMS is a software system that manages data stored in a computer's memory or disk storage.

• RDBMS is a type of DBMS that uses a relational model to store data in tables with relationships.

• RDBMS supports ACID properties (Atomicity, Consistency, Isolation, Durability) for data integrity.

• Examples of RDBMS include Oracle, MySQL, SQL Server, and PostgreSQL.

• Examples of DBMS include MongoDB...read more

Constraints are rules enforced on data in a table, while triggers are actions performed in response to certain events.

• Constraints ensure data integrity and consistency, while triggers automate actions based on specific events.

• Constraints are defined at the time of table creation, while triggers are created separately.

• Constraints can be used to enforce rules such as unique values, primary keys, and foreign keys, while triggers can be used to perform actions such as logging cha...read more

Using PL/SQL to create a procedure with a ref cursor to find the average and factorial of a given number.

• Create a procedure that takes in a number as input and returns the average of that number using a ref cursor.

• Use the EXISTS function to check if a factorial exists for a given number.

• Write a block of code to calculate the factorial of a number using a loop.

Stored procedure is a precompiled program that is stored in a database and can be called by other programs.

• Stored procedures are used to encapsulate business logic and improve performance.

• PL/SQL is a procedural language used to write stored procedures in Oracle databases.

• Stored procedures can accept input parameters and return output parameters or result sets.

• They can also be used to enforce security and data integrity rules.

• Example: CREATE PROCEDURE get_employee_details (emp...read more

A cursor is a database object used to retrieve data from a result set one row at a time.

• There are two types of cursors in PL/SQL: Implicit and Explicit.

• Implicit cursors are automatically created by Oracle when a SQL statement is executed.

• Explicit cursors are defined by the programmer using the DECLARE, OPEN, FETCH, and CLOSE statements.

• Cursors are used to process individual rows returned by a query, allowing for more control over data manipulation.

Delete is a DML operation that removes specific rows from a table, while truncate is a DDL operation that removes all rows from a table.

• Delete is slower than truncate as it generates undo logs and triggers

• Delete can be rolled back, while truncate cannot be rolled back

• Delete operation maintains the integrity of the transaction log, while truncate operation does not

• Delete operation fires delete triggers, while truncate operation does not

• Delete operation can have a WHERE clause ...read more

Exception handling is a mechanism to handle errors or unexpected events in a program.

• Syntax: BEGIN -- code block EXCEPTION -- exception handling block END;

• Exceptions can be predefined or user-defined

• Common predefined exceptions include NO_DATA_FOUND, TOO_MANY_ROWS, etc.

• User-defined exceptions can be declared using the EXCEPTION keyword

I wrote a procedure to calculate the average salary of employees in a department.

• Used cursor to fetch employee data

• Calculated total salary using loop

• Divided total salary by number of employees

• Handled exceptions for zero employees in department

Delete, drop, and truncate are SQL commands used to remove data from a table, but they differ in their functionality.

• DELETE is used to remove specific rows from a table based on a condition.

• DROP is used to remove an entire table from the database.

• TRUNCATE is used to remove all rows from a table, but keeps the structure intact.

• DELETE and TRUNCATE can be rolled back, but DROP cannot.

• DELETE triggers the delete trigger, while TRUNCATE and DROP do not.

SQL is a query language used to interact with databases, while PL/SQL is a procedural language extension for SQL.

• SQL is used for querying and manipulating data in databases.

• PL/SQL is used for writing procedural code like loops, conditions, and functions.

• SQL statements are executed one at a time, while PL/SQL blocks can contain multiple statements.

• SQL is declarative, while PL/SQL is procedural.

• Example: SQL - SELECT * FROM employees; PL/SQL - DECLARE x NUMBER := 10; BEGIN DBMS_...read more

Procedures do not return any value while functions return a value.

• Procedures are used to perform an action, while functions are used to calculate and return a value.

• Procedures do not have a return statement, while functions must have a return statement.

• Functions can be called from SQL queries, while procedures cannot be called directly in SQL queries.

Aggregate functions in SQL are functions that operate on a set of values and return a single value as output.

• Aggregate functions include functions like SUM, AVG, COUNT, MIN, and MAX.

• They are used with the GROUP BY clause to perform calculations on groups of rows.

• Examples: SELECT SUM(salary) FROM employees; SELECT AVG(age) FROM students GROUP BY class;

Analytical functions in SQL are used to perform calculations across a set of rows related to the current row.

• Analytical functions operate on a group of rows and return a single result for each row.

• They can be used to calculate running totals, moving averages, rank, percentiles, etc.

• Examples include ROW_NUMBER(), RANK(), DENSE_RANK(), SUM() OVER(), AVG() OVER().

Different types of indexes include B-tree, Bitmap, Function-based, and Reverse key indexes.

• B-tree indexes are the most common type and are suitable for most indexing needs.

• Bitmap indexes are used for columns with low cardinality, such as gender or status columns.

• Function-based indexes are created based on expressions or functions applied to columns.

• Reverse key indexes store keys in reverse order to reduce contention in high insert environments.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

• Normalization helps to eliminate data redundancy and inconsistencies

• It ensures that each piece of data is stored in only one place

• It reduces the chances of data anomalies and inconsistencies

• Normalization is achieved through a series of steps called normal forms

• Examples of normalization include breaking down a customer's address into separate fields like street, city, ...read more

Primary key uniquely identifies a record in a table, while unique key ensures uniqueness of a column or set of columns.

• Primary key is a column or set of columns that uniquely identifies each record in a table

• Unique key ensures that a column or set of columns have unique values

• Primary key cannot have null values, while unique key can have null values

• A table can have only one primary key, but multiple unique keys

Views and materialized views, collection data types.

• Views are virtual tables that display data from one or more tables.

• Materialized views are physical copies of the data in a view.

• Collection data types include arrays, nested tables, and varrays.

• Arrays are fixed-size, ordered collections of elements of the same data type.

• Nested tables are dynamic, unordered collections of elements of the same data type.

• Varrays are variable-size, ordered collections of elements of the same data...read more

Named and unnamed exceptions in PL/SQL with examples

• Named exceptions are user-defined exceptions with specific names like 'custom_exception'

• Unnamed exceptions are predefined exceptions like 'NO_DATA_FOUND' or 'TOO_MANY_ROWS'

• Named exceptions can be raised using RAISE statement with the exception name

• Unnamed exceptions are raised automatically by the system in case of errors

• Example of named exception: DECLARE custom_exception EXCEPTION; RAISE custom_exception;

• Example of unnamed...read more

In performance tuning, init refers to the initialization parameter file used by Oracle Database to configure various settings.

• init is a text-based configuration file that contains parameters to optimize the performance of Oracle Database.

• It is used to set parameters such as memory allocation, parallel processing, and resource utilization.

• By tuning the init file, developers can improve the overall performance of the database.

• Example: adjusting the buffer cache size in the init...read more

I work on performance optimization by analyzing query execution plans, indexing, and code refactoring.

• Analyzing query execution plans to identify bottlenecks

• Creating appropriate indexes to improve query performance

• Refactoring code to optimize resource usage

• Using tools like Explain Plan and SQL Tuning Advisor

Join operator is used to combine rows from two or more tables based on a related column between them.

• Join operator is used in SQL to retrieve data from multiple tables.

• It combines rows from two or more tables based on a related column between them.

• Types of join operators include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

• Join operator can be used with WHERE clause to filter the results.

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.colum...read more

Analytical functions are used in SQL to perform calculations across a set of rows related to the current row.

• Analytical functions operate on a group of rows and return a single result for each row.

• They can be used to calculate running totals, moving averages, rank, percentiles, etc.

• Examples of analytical functions include ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().

Performance tuning is the process of optimizing the performance of a system or application.

• Identifying and resolving performance bottlenecks

• Optimizing database queries and indexes

• Improving hardware and network configurations

• Reducing resource usage and improving response time

• Monitoring and analyzing system performance

row_number() function assigns a unique number to each row within a result set.

• It is a window function in SQL

• It is used to generate a unique sequential number for each row in a result set

• It is often used for pagination and ranking purposes

• It can be used with ORDER BY clause to specify the order of the rows

A temporary variable used to store data during program execution.

• Temp variables are used to hold data temporarily during program execution.

• They are typically used in loops or conditional statements.

• Once the program execution is complete, the temp variable is no longer needed.

• Example: int temp = 0; for(int i=0; i<10; i++) { temp += i; }

• In this example, the temp variable is used to store the sum of the numbers 0-9.

Returning clause is used to return a value from a function or procedure in PL/SQL.

• Used to return a single value from a function or procedure

• Can be used to return multiple values using OUT parameters

• Helps in passing values back to the calling program

Execution plan is a roadmap created by the database optimizer to determine the most efficient way to execute a query.

• Execution plan shows the steps the database will take to execute a query.

• Query optimization involves finding the most efficient way to execute a query.

• Optimization techniques include index usage, join methods, and access paths.

• Understanding execution plans helps in tuning queries for better performance.

Delete removes rows from a table, drop removes the entire table, and truncate removes all rows from a table.

• Delete is a DML operation and can be rolled back, but it is slower than truncate.

• Drop is a DDL operation and cannot be rolled back. It removes the table and all associated objects.

• Truncate is a DDL operation and cannot be rolled back. It removes all rows from the table but keeps the table structure.

• Delete and truncate can be used with a WHERE clause to specify which row...read more

Use a self-join query to delete duplicates from the same table.

• Use a self-join query to identify the duplicate records based on a unique identifier column.

• Delete the duplicate records using the DELETE statement with the self-join condition.

Triggers are PL/SQL blocks that are automatically executed when certain events occur on a table.

• Triggers can be used to enforce business rules, audit changes, or maintain data integrity.

• Syntax: CREATE OR REPLACE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name FOR EACH ROW BEGIN -- trigger logic here END;

A cursor is a pointer to a result set for a SQL query. It allows you to iterate through the rows of the result set.

• Cursors are used in PL/SQL to process individual rows returned by a query.

• Attributes of a cursor include %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN.

• Example: OPEN cursor_name; FETCH cursor_name INTO variable; CLOSE cursor_name;

A view is a virtual table created by a query. It does not store data itself but displays data from one or more tables. Views can be used for querying data as if it were a table.

• Views are created using a SELECT statement with optional joins, WHERE clauses, etc.

• Views can simplify complex queries by pre-defining joins and filters.

• Views do not store data themselves, they display data from underlying tables.

• Views can be updated if they meet certain criteria, such as not containing...read more

Optimizing stored procedures involves using proper indexing, reducing unnecessary loops, and minimizing database calls.

• Use proper indexing on columns used in WHERE clauses to improve query performance

• Avoid using cursors and loops whenever possible, as they can be inefficient

• Minimize the number of database calls by combining multiple queries into a single query or using temporary tables

• Consider using bulk processing techniques for handling large amounts of data efficiently

A package in PL/SQL is a collection of related procedures, functions, variables, and other PL/SQL constructs.

• Packages help organize and encapsulate code for easier maintenance and reuse.

• They consist of a specification (header) and a body.

• Example: CREATE PACKAGE my_package AS PROCEDURE my_procedure; END my_package;

• Example: CREATE PACKAGE BODY my_package AS PROCEDURE my_procedure IS BEGIN NULL; END my_procedure; END my_package;

SQL is a structured query language used to communicate with databases. SQL extensions add additional functionality to the language.

• SQL is a standard language used to interact with databases

• SQL extensions like PL/SQL add procedural programming capabilities to SQL

• PL/SQL is an extension of SQL used in Oracle databases

Group by and having clause are used together to filter groups based on specified conditions.

• Group by clause is used to group rows that have the same values into summary rows.

• Having clause is used to filter groups based on specified conditions.

• Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 5000;

Procedures and functions are both PL/SQL program units, but they have some differences.

• Procedures do not return a value, while functions do.

• Procedures can have OUT parameters to pass values back to the caller, while functions cannot.

• Functions can be used in SQL queries, while procedures cannot.

• Functions must return a value, while procedures do not have to.

• Functions can be called directly in PL/SQL code, while procedures need to be called using the CALL statement.