Infosys Oracle SQL and PLSQL Developer Interview Questions and Answers

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

• Analytical functions are used to calculate aggregate values based on a group of rows.

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

• Examples include functions like ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER().

Context switching is the process of storing and restoring the state of a CPU so that multiple processes can share the same CPU.

• Context switching allows the CPU to switch from one process to another, enabling multitasking.

• In Oracle PLSQL, collection types are used to store multiple values in a single variable.

• Examples of collection types in PLSQL include arrays, nested tables, and associative arrays.

Set operators are used to combine the result sets of two or more SELECT statements. UNION ALL keeps duplicates.

• Set operators include UNION, UNION ALL, INTERSECT, and MINUS

• UNION ALL retains duplicate rows from the result sets

• Example: SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2

No, we cannot use commit or roll back within a trigger.

• Triggers are automatically committed or rolled back as part of the transaction that fired them.

• Using commit or rollback within a trigger can lead to unpredictable behavior and is not recommended.

Materialized views store the results of a query and can be refreshed to update the data.

• Materialized views store the results of a query in a table-like structure

• They can be refreshed manually or automatically based on a schedule

• Refresh options include full, fast, and force refresh

• Example: CREATE MATERIALIZED VIEW mv_name AS SELECT * FROM table_name;