TCS Oracle SQL and PLSQL Developer Interview Questions and Answers

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;

Use CONNECT BY LEVEL to print numbers from 1 to 10 in Oracle SQL

• Use CONNECT BY LEVEL to generate rows from 1 to 10

• Select the generated numbers in the query

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.

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

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