Infosys Plsql Developer Interview Questions, Process, and Tips

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.

• PL/SQL can be used to create stored procedures, functions, and triggers i...read more

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

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.

Query to display 5th max salary in a table

• Use the RANK() function to assign a rank to each salary

• Filter the results to only show the row with rank 5

• Order the salaries in descending order to get the 5th max salary

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;

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

Performance tuning is the process of optimizing the speed and efficiency of a system or application.

• Identifying and resolving bottlenecks in the system

• Optimizing SQL queries and database design

• Improving code efficiency and reducing resource usage

• Monitoring system performance and making adjustments as needed

Merge statement in PL/SQL combines insert and update operations based on a condition.

• Use MERGE INTO statement followed by target table name

• Specify USING clause with source table or subquery

• Define ON condition to match rows for update or insert

• Use WHEN MATCHED THEN UPDATE SET for update operation

• Use WHEN NOT MATCHED THEN INSERT VALUES for insert operation

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;