10+ Mphasis Plsql Developer Interview Questions and Answers

Joins in SQL are used to combine rows from two or more tables based on a related column between them.

• Joins are used to retrieve data from multiple tables based on a related column.

• Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Types of cursors in database management systems include implicit, explicit, and parameterized cursors.

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

• Explicit cursors are defined by the programmer and give more control over the result set.

• Parameterized cursors allow for dynamic SQL statements with parameters.

• Examples: SELECT statement using implicit cursor, DECLARE, OPEN, FETCH, CLOSE for explicit cursor, using parameters in a curso...read more

Use a subquery to find the third highest salary in a table.

• Use the RANK() function to assign a rank to each salary in descending order.

• Filter the results to only include rows with a rank of 3.

• Consider handling ties in salaries appropriately.

A package 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 can contain both public and private elements.

• Packages can be used to group related functionality together, improving code modularity.

• Example: CREATE PACKAGE my_package AS ... END my_package;

Resolved a critical bug causing data loss and implemented a new feature for better user experience.

• Identified root cause of data loss bug by analyzing database queries and logs

• Collaborated with cross-functional teams to prioritize and implement a fix

• Designed and implemented a new feature based on user feedback to enhance usability

I conducted thorough analysis by reviewing code, logs, and discussing with team members.

• Reviewed code to identify potential issues

• Analyzed logs for error messages and patterns

• Discussed with team members to gather insights and perspectives

• Used debugging tools to trace the root cause

A trigger in database management is a special type of stored procedure that is automatically executed when certain events occur in a database.

• Triggers can be used to enforce business rules, maintain referential integrity, and automate repetitive tasks.

• There are two main types of triggers: row-level triggers and statement-level triggers.

• Row-level triggers are fired for each row affected by a triggering statement, while statement-level triggers are fired once for each triggerin...read more

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.

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

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.

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().

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;