Infosys Plsql Developer Interview Questions and Answers

Editing data in complex views can affect underlying tables, but it depends on the view's structure and rules.

• Complex views often involve joins, aggregations, or groupings, making them non-updatable.

• If a view is updatable, changes made to it will reflect in the underlying tables.

• Example: A simple view on a single table can be updated directly.

• Example: A view that aggregates data (e.g., SUM, COUNT) cannot be updated dire...read more

Performance tuning involves optimizing database queries and configurations for improved efficiency and speed.

• Analyze execution plans to identify slow queries. For example, use EXPLAIN PLAN to see how Oracle executes a query.

• Use indexing strategically. For instance, create indexes on columns frequently used in WHERE clauses to speed up data retrieval.

• Optimize SQL queries by avoiding SELECT *, using WHERE clauses effecti...read more

Regular expressions (regex) are sequences of characters that define search patterns for strings, used for pattern matching and manipulation.

• Regex is used for validating input formats, e.g., email addresses: ^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

• It can extract specific patterns from text, such as phone numbers: \d{3}-\d{3}-\d{4}

• Regex supports various metacharacters like '.', '*', and '+', which have special me...read more

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

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;

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;

Packages and types are two important components in PL/SQL used for organizing and defining reusable code and data structures.

• Packages are containers for related procedures, functions, variables, and other PL/SQL constructs.

• Types are user-defined data structures that can be used to define new data types in PL/SQL.

• Packages can contain types as well as other PL/SQL constructs, providing a way to organize and encapsulate r...read more