10+ Plsql Developer Interview Questions and Answers for Freshers

Code to pull-up mass data from one table and load into another table efficiently

• Use INSERT INTO SELECT statement to pull data from source table and load into target table

• Use WHERE clause to filter data if required

• Use INDEXES on columns used in WHERE clause to improve performance

• Use BULK COLLECT to fetch data in batches to improve performance

• Use PARALLEL hint to execute the query in parallel if the table is large

• Use NOLOGGING option to improve performance by reducing redo log ...read more

Fetch even numbers from a table while excluding specific values using SQL queries.

• Use the MOD function to filter even numbers: SELECT * FROM table WHERE MOD(column_name, 2) = 0;

• To exclude specific values, use the NOT IN clause: SELECT * FROM table WHERE MOD(column_name, 2) = 0 AND column_name NOT IN (excluded_value1, excluded_value2);

• You can also use a subquery to exclude values: SELECT * FROM table WHERE MOD(column_name, 2) = 0 AND column_name NOT IN (SELECT excluded_column ...read more

Explanation of SQL Loader, Procedure, Package, Function, Bulk Collect, Cursor

• SQL Loader is a tool used to load data from external files into Oracle database tables

• Procedure is a named block of PL/SQL code that performs a specific task

• Package is a collection of related procedures, functions, variables, and cursors

• Function is a named block of PL/SQL code that returns a value

• Bulk Collect is a method used to fetch multiple rows of data from a query into a collection

• Cursor is a po...read more

I wrote a procedure to calculate the average salary of employees in a department.

• Used cursor to fetch employee data

• Calculated total salary using loop

• Divided total salary by number of employees

• Handled exceptions for zero employees in department

A temporary variable used to store data during program execution.

• Temp variables are used to hold data temporarily during program execution.

• They are typically used in loops or conditional statements.

• Once the program execution is complete, the temp variable is no longer needed.

• Example: int temp = 0; for(int i=0; i<10; i++) { temp += i; }

• In this example, the temp variable is used to store the sum of the numbers 0-9.

Join is used to combine rows from two or more tables based on a related column between them.

• Types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN

• INNER JOIN returns rows when there is at least one match in both tables

• LEFT JOIN returns all rows from the left table and the matched rows from the right table

• RIGHT JOIN returns all rows from the right table and the matched rows from the left table

• FULL JOIN returns rows when there is a match in one of the tables

Collections in PL/SQL are data structures used to store multiple elements of the same data type.

• Collections can be nested tables, varrays, or associative arrays.

• Nested tables are like one-dimensional arrays with no upper bounds.

• Varrays are like arrays with a maximum size that is specified at declaration.

• Associative arrays are like lookup tables where each element is accessed using a unique key.

• Collections can be used to store data temporarily or pass data between PL/SQL progr...read more

Char is fixed length while Varchar2 is variable length.

• Char takes up the same amount of space regardless of the data it contains.

• Varchar2 only takes up as much space as the data it contains.

• Char is useful for storing data that is always the same length, like a phone number.

• Varchar2 is useful for storing data that can vary in length, like a name or address.

PL/SQL is Oracle's procedural extension for SQL, enabling complex data manipulation and control structures in database applications.

• PL/SQL combines SQL with procedural features like loops and conditionals. Example: Using IF statements to control flow.

• It supports error handling through exceptions. Example: Using EXCEPTION blocks to manage runtime errors.

• PL/SQL allows the creation of stored procedures and functions for reusable code. Example: Creating a function to calculate di...read more

Developed a PL/SQL project for managing inventory and sales data in a retail store.

• Designed database tables to store product information, sales transactions, and customer details.

• Implemented PL/SQL procedures and functions for adding, updating, and querying data.

• Created triggers to enforce business rules and maintain data integrity.

• Developed reports using PL/SQL queries to analyze sales performance and inventory levels.

Bulk collect is a feature in PL/SQL that allows for fetching multiple rows of data at once.

• Improves performance by reducing context switches between the PL/SQL engine and SQL engine

• Reduces network traffic by fetching multiple rows in a single round trip

• Can be used with SELECT INTO or FETCH INTO statements