IAP Company Plsql Developer Interview Questions and Answers

Q1. Difference between delete, drop and truncate
Ans. 
Delete removes rows from a table, drop removes the entire table, and truncate removes all rows from a table.
• Delete is a DML operation and can be rolled back, but it is slower than truncate.

• Drop is a DDL operation and cannot be rolled back. It removes the table and all associated objects.

• Truncate is a DDL operation and cannot be rolled back. It removes all rows from the table but keeps the table structure.

• Delete and tru...read more
Answered by AI
Add your answer
Q2. Difference between table and view
Ans. 
Table stores data in a structured format while view is a virtual table created from a query.
• Table is a physical object while view is a logical object.

• Table can be modified while view cannot be modified directly.

• Table can have indexes while view cannot have indexes.

• Table can have constraints while view cannot have constraints.

• View can be used to simplify complex queries by joining multiple tables.

• View can be used to res...read more
Answered by AI
Add your answer
Q3. What type of cursors did you use
Ans. 
I have used both implicit and explicit cursors in PL/SQL development.
• Implicit cursors are used for single-row queries while explicit cursors are used for multi-row queries.

• I have used explicit cursors with parameters to make the query more dynamic.

• I have also used cursor variables to pass cursors as parameters to procedures and functions.

• Examples of cursor types I have used include FOR LOOP, FETCH, and UPDATE cursors.
Answered by AI
Add your answer
Q4. What is primary key and unique key
Ans. 
Primary key uniquely identifies a record in a table, while unique key ensures uniqueness of a column or set of columns.
• Primary key is a column or set of columns that uniquely identifies each record in a table

• Unique key ensures that a column or set of columns have unique values

• Primary key cannot have null values, while unique key can have null values

• A table can have only one primary key, but multiple unique keys
Answered by AI
Add your answer

Q1. What is plsql and sql
Ans. 
PL/SQL is a procedural extension of SQL used for writing stored procedures, functions, triggers, and packages in Oracle databases.
• PL/SQL stands for Procedural Language/Structured Query Language

• It is used to write procedural code like stored procedures, functions, triggers, and packages in Oracle databases

• SQL (Structured Query Language) is a standard language for accessing and manipulating databases

• SQL is used to query,...read more
Answered by AI
Add your answer

Questions on plsql and sql in general

Q1. Write query to get result based on hughest salary
Ans. 
Use SQL query with ORDER BY clause to get result based on highest salary
• Use SELECT statement to retrieve data from the table

• Use ORDER BY clause with DESC keyword to sort salaries in descending order

• Use LIMIT or FETCH FIRST clause to limit the result to only one row
Answered by AI
Add your answer
Q2. Writw merge query
Ans. 
A MERGE query combines INSERT and UPDATE operations to synchronize two tables based on a condition.
• The MERGE statement is used to perform DML operations based on a condition.

• Syntax: MERGE INTO target_table USING source_table ON (condition) WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED THEN INSERT ...

• Example: MERGE INTO employees e USING new_employees ne ON (e.id = ne.id) WHEN MATCHED THEN UPDATE SET e.salary = ne.s...read more
Answered by AI
Add your answer
Q3. Bulk collect and bulk binding difference
Ans. 
Bulk collect is used to fetch multiple rows into collections at once, while bulk binding is used to bind collections to SQL statements for improved performance.
• Bulk collect fetches multiple rows into collections in a single operation, reducing context switches and improving performance.

• Bulk binding binds collections to SQL statements, reducing the number of context switches between the PL/SQL engine and SQL engine.

• Bulk...read more
Answered by AI
Add your answer

Q1. CREATE A FUNCTION BASED ONLOGIC SHARED
Ans. 
Create a function based on shared logic
• Identify common logic that can be reused in multiple functions

• Create a separate function to encapsulate the shared logic

• Call the shared function from other functions where the logic is needed
Answered by AI
Add your answer
Q2. FOR AN ENTITY GIVE DTAILS OF PREVOUS AND NEXT DATE FOR PARTICULAR DEPATMENT
Ans. 
Retrieve previous and next dates for a specific department's entity in a database using PL/SQL.
• Use a SELECT statement with JOINs to fetch data from relevant tables.

• Utilize the LEAD() and LAG() analytic functions to get next and previous dates.

• Example: SELECT department_id, date_column, LAG(date_column) OVER (PARTITION BY department_id ORDER BY date_column) AS previous_date, LEAD(date_column) OVER (PARTITION BY departme...read more
Answered by AI
Add your answer

Q1. Basic SQL related question
Add your answer
Q2. Good knowledge on pl SQL is preferred, few in-depth questions . Keep your cool and answer every question with best of your knowledge
Add your answer

Q1. Triggers , procedures , exception handling , collections, package
Add your answer
Q2. Type , array, partition, performance tuning, AWR
Add your answer

Q1. Asked few SQL queries from basics to intermediate level, and asked to write plsql code with exceptions . Increased difficulty level in the same program step by step.
Add your answer