30+ 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.

Deleting a child table column linked by a foreign key requires careful consideration of referential integrity.

• Foreign keys enforce referential integrity between parent and child tables.

• You cannot directly delete a column from a child table if it is part of a foreign key constraint.

• To delete the column, first drop the foreign key constraint using: ALTER TABLE child_table DROP CONSTRAINT fk_name;

• After dropping the constraint, you can delete the column using: ALTER TABLE child_t...read more

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.

Use Oracle External Tables and SQL Loader to process files in different formats and from different sources to load a single table.

• Create External Tables in Oracle to define the structure of the files from different sources.

• Use SQL Loader to load data from the files into the External Tables.

• Transform and manipulate the data as needed using SQL queries before inserting into the final table.

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;

Joining tables is done using JOIN keyword. Types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

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

• INNER JOIN returns only the matching rows from both tables.

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

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

• FULL JOIN returns all row...read more

Common null handling functions in Oracle SQL and PLSQL.

• NVL function replaces null values with a specified default value.

• NVL2 function returns one value if a specified expression is not null, otherwise returns another value.

• NULLIF function returns null if two expressions are equal, otherwise returns the first expression.

• COALESCE function returns the first non-null expression in a list of expressions.

Collections are data structures used to store multiple values of the same data type.

• Types of collections in Oracle are Associative arrays, Nested tables, and Varrays.

• Associative arrays are indexed by string values, Nested tables are like arrays with no fixed size, and Varrays are like arrays with a fixed size.

• Collections can be used to pass multiple values to a stored procedure or function.

The log file structure in concurrent program

• The log file is a text file that contains information about the execution of a concurrent program

• It includes details like start and end time, parameters passed, and any errors encountered

• The log file is stored in the $APPLCSF/$APPLLOG directory on the application server

• The file name follows a specific naming convention: _.log

Answers to common questions in Oracle SQL and PLSQL Developer interview.

• Data is a collection of facts, figures, and statistics that can be processed to derive information.

• A database is a structured collection of data that is stored and organized in a way that allows for efficient retrieval and manipulation.

• Delete removes specific rows from a table while truncate removes all rows from a table.

• Primary key is a column or set of columns that uniquely identifies each row in a tabl...read more

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

• Inner join returns only the rows that have matching values in both tables

• Left join returns all the rows from the left table and the matched rows from the right table

• Inner join is more restrictive as it requires a match in both tables, while left join is less restrictive

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

• Example: SELECT * FROM table1 LEFT JOIN tabl...read more

Record is a composite data type that stores related fields together.

• A record is a user-defined data type that contains a group of related data items.

• It is also known as a row or tuple in a table.

• Each field in a record can have a different data type.

• Records are commonly used in PL/SQL to hold query results or to pass data between procedures.

• Example: DECLARE emp_rec employees%ROWTYPE;

• This creates a record variable emp_rec that contains all the columns of the employees table.

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

Reference cursor in procedure is used to return result sets from a stored procedure.

• Declare a cursor variable in the procedure using TYPE keyword

• Open the cursor variable using OPEN keyword

• Fetch data from the cursor using FETCH keyword

• Close the cursor using CLOSE keyword after fetching all data

Merge statement is used to perform insert, update, or delete operations in a single statement based on a condition.

• Combines INSERT, UPDATE, and DELETE operations into a single statement

• Used to synchronize two tables based on a condition

• Improves performance by reducing the number of SQL statements needed

• Can be used to insert new records, update existing records, or delete records in a single operation

Cannot answer without more context. Need to know what 'cp' refers to.

• Need more information about what 'cp' is referring to in order to provide a full code explanation.

• Without context, it is impossible to provide a meaningful answer.

• Please provide more information about the specific code or program in question.

SQL is the language used for managing and manipulating databases.

• SQL stands for Structured Query Language

• It is used to communicate with databases to perform tasks like querying data, updating records, and creating tables

• Examples of SQL commands include SELECT, INSERT, UPDATE, and DELETE

OLAP systems are designed for analyzing and managing large volumes of data from multiple perspectives.

• OLAP stands for Online Analytical Processing

• OLAP systems allow users to analyze data in real-time

• They provide advanced analytics capabilities like data mining, forecasting, and trend analysis

• Examples of OLAP systems include Oracle Essbase, Microsoft Analysis Services, and IBM Cognos

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

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

• UNION combines the result sets of two SELECT statements, removing duplicates.

• UNION ALL combines the result sets of two SELECT statements, including duplicates.

• INTERSECT returns only the rows that appear in both result sets.

• MINUS returns only the rows that appear in the first result set but not in the second.

Yes, you can update in a function in Oracle SQL and PLSQL.

• You can use the UPDATE statement within a PLSQL function to update data in a table.

• Make sure to commit the changes using COMMIT statement.

• Example: CREATE OR REPLACE FUNCTION update_employee_salary(emp_id IN NUMBER, new_salary IN NUMBER) RETURN NUMBER IS BEGIN UPDATE employees SET salary = new_salary WHERE employee_id = emp_id; COMMIT; RETURN 1; END;

No, you cannot commit in a trigger as it can cause mutating table errors.

• Triggers cannot contain COMMIT or ROLLBACK statements.

• Committing in a trigger can lead to mutating table errors.

• Instead, use triggers for data validation, not for committing transactions.

Autonomous transaction is a separate transaction that is independent of the main transaction.

• Autonomous transactions are used to perform certain tasks independently of the main transaction.

• They are useful for logging, auditing, or error handling purposes.

• Autonomous transactions are started with the PRAGMA AUTONOMOUS_TRANSACTION statement.

• Changes made in an autonomous transaction are not visible to the main transaction until they are committed.

• Example: Creating a logging proce...read more

Primary key is a column or set of columns that uniquely identifies each row in a table.

• Primary key ensures data integrity by preventing duplicate rows.

• Primary key can be a single column or a combination of columns.

• Primary key is used as a reference in foreign keys of other tables.

• Primary key values cannot be null.

• Primary key values must be unique.

To create a table using another table, use the CREATE TABLE AS SELECT statement.

• Use the CREATE TABLE AS SELECT statement to create a new table based on the structure and data of an existing table.

• Specify the new table name after CREATE TABLE and the existing table name after AS SELECT.

• You can also add conditions or filters to the SELECT statement to customize the data being copied.

• Example: CREATE TABLE new_table AS SELECT * FROM existing_table WHERE condition;

Truncate is a DDL command that removes all rows from a table, while delete is a DML command that removes specific rows.

• Truncate is faster than delete as it does not generate undo logs.

• Truncate resets the high water mark of the table, while delete does not.

• Truncate cannot be rolled back, while delete can be rolled back using a transaction.

• Truncate does not fire any triggers, while delete does.

• Truncate does not require a WHERE clause, while delete does.

An index is a database object that improves the speed of data retrieval operations on a table.

• Indexes can be created on one or more columns of a table.

• Syntax to create an index: CREATE INDEX index_name ON table_name(column_name);

• Indexes can be unique or non-unique.

• Indexes can be used to enforce uniqueness constraints.

• Indexes can improve query performance by reducing the number of rows that need to be scanned.