LTIMindtree Plsql Developer Interview Questions and Answers

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

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...read more

Procedure is used to perform an action, while function returns a value.

• Procedure does not return a value, while function does

• Functions can be used in SQL queries, procedures cannot

• Functions can be called from SQL statements, procedures cannot

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 dat...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_exce...read more

Temp table is a table created temporarily in memory. Temp variable is a variable that holds temporary data.

• Temp table is used to store data temporarily during a session

• Temp variable is used to hold temporary data that is not needed after a certain point

• Temp table and variable are created using the 'CREATE GLOBAL TEMPORARY' and 'DECLARE' statements respectively

• Example: CREATE GLOBAL TEMPORARY TABLE temp_table (id NUMBER...read more

A mutating table or mutating trigger occurs when a trigger tries to update a table that is currently being modified.

• Mutating table occurs when a trigger references the table that is being modified.

• It can happen when a trigger is fired by an INSERT, UPDATE, or DELETE statement on the table.

• This can lead to unpredictable results or errors, such as ORA-04091: table is mutating, trigger/function may not see it.

• To avoid mut...read more

Autonomous transaction is a separate transaction initiated by a parent transaction.

• It allows a subtransaction to commit or rollback independently of the parent transaction.

• It is useful for logging or auditing purposes.

• It can be created using the PRAGMA AUTONOMOUS_TRANSACTION statement.

• Example: A parent transaction updates a table, while an autonomous transaction logs the changes made.

• Example: An autonomous transaction ...read more

Cursors are used to retrieve and manipulate data from a database in PL/SQL.

• Cursors are like pointers to a result set, allowing us to fetch and process rows one by one.

• There are two types of cursors: implicit and explicit.

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

• Explicit cursors are declared and used by the programmer.

• Explicit cursors provide more control and flexibility compar...read more

Triggers are database objects that automatically execute in response to certain events.

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

• They can be defined to execute before or after an event, such as a row being inserted, updated, or deleted.

• Triggers can also be nested, meaning one trigger can execute another trigger.

• Examples of triggers include automatically updat...read more

Truncate removes all data, delete removes selected data.

• Truncate is faster than delete as it doesn't log individual row deletions.

• Truncate cannot be rolled back, delete can be.

• Truncate resets identity columns, delete doesn't.

• Truncate doesn't fire triggers, delete does.

• Truncate is a DDL operation, delete is a DML operation.

A procedure in PL/SQL is a named block of code that can be called and executed multiple times.

• Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [mode1] datatype1 [, parameter2 [mode2] datatype2]...)] IS

• Difference between procedure and function: Procedures do not return a value, while functions return a value.

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

To find if two tables have similar data, compare the records in both tables using a join or a subquery.

• Use a join operation to compare the records in both tables based on a common column.

• If the tables have a primary key, you can join them on that key to check for similar data.

• Alternatively, you can use a subquery to compare the data in both tables and check for matching records.

• Consider using the MINUS operator to find...read more

HAVING is used to filter groups while GROUP BY is used to group rows based on a column.

• HAVING is used with GROUP BY to filter groups based on a condition

• GROUP BY is used to group rows based on a column

• HAVING is used after GROUP BY in a query

• GROUP BY is used before HAVING in a query

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

Factorial of a number is the product of all positive integers up to that number.

• To calculate factorial of a number, multiply all positive integers up to that number.

• Factorial of 0 is 1.

• Example: Factorial of 5 is 5*4*3*2*1 = 120.

Modelling of schema involves designing the structure of a database to organize and represent data.

• Identify entities and their relationships

• Define attributes for each entity

• Establish primary and foreign keys

• Normalize the schema to reduce redundancy

• Consider performance and scalability