Oracle SQL Interview Questions and Answers
Oracle is a highly well-liked safe database that is extensively utilized by global corporations. Thus, you can review your knowledge before the interview by reading this post about Oracle interview questions and answers.
Oracle SQL Interview Questions and Answers for Freshers
1. What is the connection between the data file, tablespace, and database?
- Tablespaces are logical storage units that are present in one or more Oracle databases.
- One or more files known as datafiles make up each tablespace in an Oracle database.
- All of the database’s data is stored in these tablespaces combined.
- Datafiles are the actual physical structures that verify with the operating system that the Oracle application is executing.
2. How can images be added to a database for storage?
The use of the Long Raw Data type allows images to be stored in databases. 2 GB of binary data can be stored in this form. However, only the Long Raw data type is allowed in the table.
3. How does a query’s execution plan get determined by the Oracle Query Optimizer?
Based on available indexes, table size, and query complexity, it determines the most efficient execution plan using statistics and heuristics, or rules of thumb.
4. How may two tables be merged in Oracle, and what does the term “merge” mean?
The data from two tables is then merged using a merge statement. After choosing the data from the source table, it updates or inserts it into the other table under the query’s specified condition. Applications for data warehousing can also benefit from it.
5. Which Oracle SQL bind variables have the following benefits?
Through caching and reusing, bind variables reduce the requirement for parsing and increase performance. Additionally, bound variables minimize maintenance, lower memory usage, and defend against SQL injection attacks.
6. What kind of data does a ‘DUAL’ table contain?
The Oracle database has a single-column table called the dual table. The only Varchar2(1) column in this table, Dummy, has the value “X.”
7. Describe the distinction between Oracle SQL’s RANK and DENSE_RANK functions.
Rankings are assigned to result rows using both RANK and DENSE_RANK.
- With RANK, the next rank will be skipped when two or more rows have the same values and are assigned the same rank.
- Even when there are duplicate values, DENSE_RANK maintains a continuous ranking without any gaps.
8. Describe database roles and privileges. How do you give users and roles in Oracle access to and permissions against them?
Groups of linked privileges are called database roles. They make security administration easier by enabling users to be granted or denied roles with varying privileges. Privileges can be granted or revoked using the GRANT and REVOKE statements, respectively.
9. Describe a subquery.
A SELECT statement embedded in another SELECT statement’s clause is called a subquery. Where having and from clauses are used, subqueries can be used.
10. What are the guidelines for using a subquery?
- The sub-queries need to be enclosed in parentheses.
- Put these subqueries on the comparison condition’s right-hand side.
- When using single-row subqueries, use single-row operators.
- When using multiple-row subqueries, use multiple-row operators.
11. What are the types of subqueries?
Single-row subquery: Subqueries with a single row returned from the inner select statement are known as single-row subqueries.
The comparison operators for a single row are =, >, >=, \, \=, <>.
Multiple-row subquery: Subqueries that return more than one row from the inner Select statement are known as multiple-row subqueries.
Additionally, there are subqueries with multiple columns that return numerous columns from the inner select expression. IN, ANY, and ALL are examples of operators.
12. What does Oracle SQL’s UNION operator serve as?
The UNION operator creates a single result set that appears to have come from a single SELECT query by combining the results of two or more queries. It offers a cohesive result by combining the rows from several queries and eliminating duplicate entries.
13. Describe the differences between Oracle SQL’s HAVING and WHERE clauses.
- Before grouping, or before the rows are included in the result set, the WHERE clause filters the data. Certain conditions are also the basis for filtering.
- Conversely, the HAVING clause filters data post-grouping, or following aggregation.
14. Why do we use the double ampersand (&&) in SQL queries?
If you want to reuse the value of the variable without asking the user each time, you can use &&. Using the employee order by column name as an example, select empno, ename, and &column_name;
15. How does a query’s execution plan get determined by the Oracle Query Optimizer?
Based on available indexes, table size, and query complexity, it determines the most efficient execution plan using statistics and heuristics, or rules of thumb.
16. What attributes does the cursor have?
Oracle Cursors each have a set of attributes that allow an application program to check the Cursor’s current status. Row count found or not found, and whether the cursor is open or closed can all be determined using attributes.
17. List the several constraints that Oracle uses.
The following constraints are in use:
NULL: This stands for the possibility of NULL values appearing in a specific column.
NOT NULL: This indicates that there are no NULL values allowed in that specific column.
Check: Verify that the values in the specified column satisfy the requirements.
DEFAULT: It serves as a notice that a default value has been assigned to the value.
18. What distinguishes Oracle triggers at the ROW level from the STATEMENT level?
- Row-specific actions are possible since ROW-level triggers only fire once for each impacted row.
- Triggers at the statement level are only run once for the whole statement. This applies to all impacted rows equally and is better suited for actions that don’t rely on specific rows.
19. Which query technique retrieves data from the table the quickest?
Using the row ID is the quickest query technique to retrieve data from the table. RowID can be used to retrieve a row from a table.
20. What are the functions of Oracle SQL’s COMMIT and ROLLBACK statements?
The COMMIT command saves all modifications made during a transaction permanently to the database. The database is returned to its pre-transaction state and the transaction modifications are undone with the ROLLBACK statement.
21. Create an Oracle SQL query to determine each department’s average salary.
SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;
22. To make sure that only users who possess the manager role can add rows to the performance_reviews table, create an Oracle SQL query.
CREATE OR REPLACE TRIGGER enforce_manager_insert
BEFORE INSERT ON performance_reviews
FOR EACH ROW
DECLARE
BEGIN
IF NOT (IS_ROLE_ENABLED(‘manager’)) THEN
RAISE_APPLICATION_ERROR(-20001, ‘Only users with the “manager” role can insert into this table.’);
END IF;
END;
/
- To identify employees who make more money than their bosses, create an Oracle SQL query.
SELECT emp.*
FROM Employee emp
INNER JOIN Employee mgr ON emp.manager_id = mgr.employee_id
WHERE emp.salary > mgr.salary;
24. The names of the employees and their corresponding managers are listed in columns of your Employees table. How are you going to determine which employee has the longest reporting chain?
WITH RECURSIVE ReportingChain AS (
SELECT employee_id, manager_id, employee_name, 1 AS chain_length
FROM Employees
WHERE manager_id IS NOT NULL
UNION ALL
SELECT e.employee_id, e.manager_id, e.employee_name, rc.chain_length + 1
FROM Employees e
INNER JOIN ReportingChain rc ON e.manager_id = rc.employee_id)
SELECT employee_id, employee_name, MAX(chain_length) AS longest_chain
FROM ReportingChain
GROUP BY employee_id, employee_name;
25. Get the top five products from the product_reviews table that have received the highest ratings from customers.
SELECT product_id, product_name, AVG(review_rating) AS average_rating
FROM product_reviews
GROUP BY product_id, product_name
ORDER BY average_rating DESC
FETCH FIRST 5 ROWS ONLY;
Conclusion
We hope these Oracle SQL interview questions and answers will be helpful for you to gain expertise and confidence to ace the interviews of top companies. Utilize our Oracle SQL training in Chennai for a promising career in the database management domain.