CGI Group Plsql Developer Interview Questions and Answers

Procedures and functions are both PL/SQL program units, but they have some key differences.

• Procedures do not return a value, while functions do.

• Procedures can have OUT parameters to pass values back to the caller, while functions cannot.

• Functions can be used in SQL statements, while procedures cannot.

• Functions must return a value, while procedures do not necessarily have to.

• Procedures are typically used for performing ...read more

Implicit cursors are automatically created by the Oracle server, while explicit cursors are explicitly declared by the programmer.

• Implicit cursors are used for single-row queries, while explicit cursors are used for multi-row queries.

• Implicit cursors are automatically opened, fetched, and closed by the Oracle server, while explicit cursors need to be manually opened, fetched, and closed by the programmer.

• Implicit curso...read more

Soft parsing is reusing the existing execution plan while hard parsing is generating a new execution plan.

• Soft parsing is faster as it avoids the overhead of generating a new execution plan.

• Hard parsing is slower as it involves generating a new execution plan.

• Soft parsing occurs when the SQL statement is already in the shared pool.

• Hard parsing occurs when the SQL statement is not in the shared pool or needs a new execu...read more

RDBMS is a type of DBMS that stores data in a structured manner using tables with relationships.

• DBMS is a software system that manages data stored in a computer's memory or disk storage.

• RDBMS is a type of DBMS that uses a relational model to store data in tables with relationships.

• RDBMS supports ACID properties (Atomicity, Consistency, Isolation, Durability) for data integrity.

• Examples of RDBMS include Oracle, MySQL, S...read more

SQL query to find the nth highest salary

• Use the ORDER BY clause to sort the salaries in descending order

• Use the LIMIT clause to select the nth highest salary

SQL query to delete duplicate records from a table.

• Use the DELETE statement with a subquery to identify and delete duplicate records.

• The subquery should select the duplicate records based on the criteria for duplication.

• Ensure to keep at least one copy of the duplicate records to avoid deleting all instances.

Delete is a DML operation that removes specific rows from a table, while truncate is a DDL operation that removes all rows from a table.

• Delete is slower than truncate as it generates undo logs and triggers

• Delete can be rolled back, while truncate cannot be rolled back

• Delete operation maintains the integrity of the transaction log, while truncate operation does not

• Delete operation fires delete triggers, while truncate o...read more