Interview Questions on Oracle Sql for Experienced | 1 to 4 Years of Experience
These 100 Oracle SQL interview questions and answers are designed for individuals with intermediate experience (1-4 years) in working with Oracle databases. The questions cover a wide range of essential topics, including joins, subqueries, advanced DML operations, hierarchical queries, performance tuning, and database security. Each question is carefully crafted to test the candidate’s ability to write efficient SQL queries, optimize database performance, and understand key concepts in database management. This set is perfect for preparing for interviews and enhancing your understanding of Oracle SQL’s more complex features and functionalities.
Section Level: Intermediate (1-4 Years Experience)
Chapter 1 — Joins and Subqueries
Question 1:
What is an INNER JOIN in SQL?
Answer: An INNER JOIN returns rows that have matching values in both tables involved in the join. It excludes rows that do not match.
Question 2:
What is a LEFT JOIN (or LEFT OUTER JOIN)?
Answer: A LEFT JOIN returns all rows from the left table, and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.
Question 3:
What is a RIGHT JOIN (or RIGHT OUTER JOIN)?
Answer: A RIGHT JOIN returns all rows from the right table, and the matching rows from the left table. If there is no match, NULL values are returned for columns from the left table.
Question 4:
What is a FULL OUTER JOIN?
Answer: A FULL OUTER JOIN returns all rows when there is a match in either the left or right table. If there is no match, NULL values are returned for columns from the table that lacks a matching row.
Question 5:
What is a Self-Join?
Answer: A self-join is a join in which a table is joined with itself, often using table aliases to distinguish between the multiple references to the same table.
Question 6:
What is a CROSS JOIN?
Answer: A CROSS JOIN returns the Cartesian product of two tables, meaning it will return every combination of rows from the first table and rows from the second table.
Question 7:
What is a subquery?
Answer: A subquery is a query within another SQL query, often used to retrieve data that will be used in the main query. It can be a single-row or multi-row subquery.
Question 8:
What is a single-row subquery?
Answer: A single-row subquery returns only one row of data. It is typically used with operators like =, <, >, BETWEEN, etc.
Question 9:
What is a multi-row subquery?
Answer: A multi-row subquery returns multiple rows. It is typically used with operators like IN, ANY, ALL, etc.
Question 10:
What is a correlated subquery?
Answer: A correlated subquery references columns from the outer query and is executed for each row of the outer query.
Chapter 2 — Advanced DML Operations
Question 11:
What is the MERGE statement in SQL?
Answer: The MERGE statement combines the functionality of INSERT, UPDATE, and DELETE operations in a single query. It is used to merge data from one table into another based on a condition.
Question 12:
Can you provide an example of a MERGE statement?
Answer:
MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.value
WHEN NOT MATCHED THEN
INSERT (id, value) VALUES (s.id, s.value);
Question 13:
What is the purpose of multi-table inserts?
Answer: Multi-table inserts allow you to insert data into multiple tables simultaneously. This is particularly useful when dealing with complex data transformations.
Question 14:
Can you give an example of a multi-table insert?
Answer:
INSERT ALL
INTO table1 (column1, column2) VALUES (value1, value2)
INTO table2 (column1, column2) VALUES (value3, value4)
SELECT * FROM dual;
Question 15:
What are conditional DML operations?
Answer: Conditional DML operations apply conditions (like WHERE or CASE statements) to determine which rows should be inserted, updated, or deleted.
Question 16:
How do you use the CASE statement in DML operations?
Answer: The CASE statement can be used to apply conditional logic within DML operations such as UPDATE and INSERT.
UPDATE employees
SET salary = CASE
WHEN department_id = 10 THEN salary * 1.1
ELSE salary * 1.05
END;
Question 17:
What is the purpose of the COMMIT statement in DML?
Answer: The COMMIT statement is used to permanently save changes made during a transaction, making them visible to other users.
Question 18:
What is the ROLLBACK statement in DML?
Answer: The ROLLBACK statement undoes changes made during the current transaction, reverting the data to its previous state.
Question 19:
How is a SAVEPOINT used in DML?
Answer: A SAVEPOINT allows you to set a point within a transaction to which you can roll back without affecting the entire transaction.
SAVEPOINT savepoint_name;
Question 20:
What is the difference between a COMMIT and a ROLLBACK in terms of DML?
Answer: COMMIT makes all changes permanent, while ROLLBACK undoes changes made since the last COMMIT or SAVEPOINT.
Chapter 3 — Grouping and Hierarchical Queries
Question 21:
What does the GROUP BY clause do in SQL?
Answer: The GROUP BY clause groups rows that have the same values into summary rows, often used with aggregate functions like SUM, AVG, COUNT, etc.
Question 22:
Can you provide an example of using GROUP BY with an aggregate function?
Answer:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;
Question 23:
What is the HAVING clause used for in SQL?
Answer: The HAVING clause is used to filter groups based on a condition, and it is often used in conjunction with GROUP BY.
Question 24:
Can you provide an example of using HAVING with GROUP BY?
Answer:
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 5000;
Question 25:
What is the purpose of the ROLLUP operator?
Answer: The ROLLUP operator is used to generate subtotals and grand totals in result sets, extending GROUP BY results.
Question 26:
Can you provide an example of using ROLLUP?
Answer:
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id);
Question 27:
What is the CUBE operator in SQL?
Answer: The CUBE operator generates all possible combinations of subtotals and grand totals for the specified grouping columns.
Question 28:
Can you provide an example of using CUBE?
Answer:
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE(department_id, job_id);
Question 29:
What is the purpose of GROUPING SETS?
Answer: GROUPING SETS allow you to specify multiple GROUP BY clauses in a single query, generating different levels of aggregation.
Question 30:
Can you provide an example of using GROUPING SETS?
Answer:
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id, job_id), (department_id), ());
Question 31:
What is a hierarchical query in SQL?
Answer: A hierarchical query retrieves data based on a parent-child relationship, often using the CONNECT BY clause.
Question 32:
What is the CONNECT BY clause used for in hierarchical queries?
Answer: The CONNECT BY clause is used to define the relationship between parent and child rows in a hierarchical query.
Question 33:
Can you provide an example of a hierarchical query?
Answer:
SELECT employee_id, manager_id, name
FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;
Question 34:
What does the LEVEL keyword do in hierarchical queries?
Answer: The LEVEL keyword returns the level number of a row in a hierarchical structure, where the root has LEVEL 1.
Question 35:
Can you provide an example of using LEVEL?
Answer:
SELECT employee_id, name, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id
START WITH manager_id IS NULL;
Chapter 4 — Indexes and Performance Tuning Basics
Question 36:
What is an index in SQL?
Answer: An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional space and slower insertions, deletions, and updates.
Question 37:
What are B-tree indexes?
Answer: B-tree (Balanced Tree) indexes are the most common type of index used in databases. They are used to store sorted data and allow for efficient retrieval based on key values.
**Question 38
:**
What is a bitmap index?
Answer: A bitmap index uses bitmaps for indexing, and it is particularly useful for columns with a low cardinality (few distinct values).
Question 39:
How do index types impact performance?
Answer: The choice of index type (e.g., B-tree, bitmap) can impact query performance. B-tree indexes are efficient for range queries, while bitmap indexes are more efficient for queries with low cardinality.
Question 40:
What are hints in SQL?
Answer: Hints are directives used to instruct the database’s optimizer on how to execute a query. They can be used to improve query performance.
Question 41:
Can you provide an example of using a hint?
Answer:
SELECT /*+ INDEX(employees emp_idx) */ * FROM employees WHERE department_id = 10;
Question 42:
What is an execution plan in SQL?
Answer: An execution plan is a detailed description of how a SQL query will be executed, showing the steps the database will take to retrieve the requested data.
Question 43:
How can you view an execution plan in Oracle?
Answer: You can use the EXPLAIN PLAN statement to view the execution plan for a query.
EXPLAIN PLAN FOR SELECT * FROM employees WHERE department_id = 10;
Question 44:
What is query optimization?
Answer: Query optimization is the process of improving the performance of SQL queries, often by rewriting them or using indexes, hints, and execution plans.
Question 45:
What tools can be used to analyze query performance in Oracle?
Answer: Tools such as EXPLAIN PLAN, AUTOTRACE, and Oracle’s SQL Trace and TKPROF can be used to analyze query performance.
Chapter 5 — Data Control Language (DCL) and Security
Question 46:
What is the GRANT statement in SQL?
Answer: The GRANT statement is used to assign privileges to users or roles, allowing them to perform specific actions on database objects.
Question 47:
Can you provide an example of the GRANT statement?
Answer:
GRANT SELECT, INSERT ON employees TO user_name;
Question 48:
What is the REVOKE statement?
Answer: The REVOKE statement is used to remove privileges that were previously granted to users or roles.
Question 49:
Can you provide an example of the REVOKE statement?
Answer:
REVOKE SELECT, INSERT ON employees FROM user_name;
Question 50:
What are roles in SQL?
Answer: Roles are named groups of privileges that can be assigned to users, simplifying the management of database permissions.
Question 51:
How can you create a role in SQL?
Answer:
CREATE ROLE role_name;
GRANT SELECT, INSERT ON employees TO role_name;
Question 52:
How do you assign a role to a user?
Answer:
GRANT role_name TO user_name;
Question 53:
What is the purpose of auditing in a database?
Answer: Auditing tracks database activities, providing a history of actions performed by users, such as DML operations or changes in database structure.
Question 54:
What is the AUDIT statement used for in Oracle?
Answer: The AUDIT statement is used to enable auditing of specific database operations or objects.
Question 55:
Can you provide an example of an AUDIT statement?
Answer:
AUDIT SELECT ON employees BY ACCESS;
Question 56:
What is a user in SQL?
Answer: A user is an account that can access the database and perform operations depending on the privileges granted to it.
Question 57:
How do you create a user in Oracle SQL?
Answer:
CREATE USER user_name IDENTIFIED BY password;
Question 58:
What is the purpose of roles in security?
Answer: Roles group privileges and are assigned to users to streamline security management.
Question 59:
What is the importance of database security?
Answer: Database security ensures that sensitive data is protected from unauthorized access, modification, or destruction, ensuring compliance with legal and organizational standards.
Question 60:
What are the different types of privileges in SQL?
Answer: Privileges in SQL can be categorized into system privileges (e.g., CREATE, ALTER) and object privileges (e.g., SELECT, INSERT on specific tables).
Chapter 1 — Joins and Subqueries (Continued)
Question 61:
What is the difference between an INNER JOIN and a LEFT JOIN?
Answer: An INNER JOIN returns only the rows with matching values in both tables, whereas a LEFT JOIN returns all rows from the left table, with matching rows from the right table. If no match is found, NULL values are returned for the right table’s columns.
Question 62:
What is the difference between a RIGHT JOIN and a FULL OUTER JOIN?
Answer: A RIGHT JOIN returns all rows from the right table, with matching rows from the left table. A FULL OUTER JOIN returns all rows from both tables, with NULL values for non-matching rows from either table.
Question 63:
How do you perform a subquery in the SELECT clause?
Answer: A subquery can be used in the SELECT clause to calculate values that are derived from another query.
SELECT name, (SELECT AVG(salary) FROM employees) AS avg_salary FROM employees;
Question 64:
What is the difference between a correlated subquery and a non-correlated subquery?
Answer: A non-correlated subquery does not depend on the outer query, whereas a correlated subquery references columns from the outer query and is executed for each row of the outer query.
Question 65:
What does the keyword EXISTS do in a subquery?
Answer: EXISTS is used to check whether the subquery returns any rows. It returns TRUE if the subquery returns one or more rows, and FALSE otherwise.
SELECT name FROM employees WHERE EXISTS (SELECT * FROM departments WHERE employees.department_id = departments.department_id);
Chapter 2 — Advanced DML Operations (Continued)
Question 66:
What is the purpose of the INSERT ALL statement?
Answer: The INSERT ALL statement allows inserting multiple rows into multiple tables in one operation.
INSERT ALL
INTO employees (id, name) VALUES (1, 'John')
INTO employees (id, name) VALUES (2, 'Jane')
SELECT * FROM dual;
Question 67:
What is the difference between INSERT INTO and MERGE?
Answer: INSERT INTO adds new rows to a table, whereas MERGE is a combination of INSERT, UPDATE, and DELETE. It inserts new rows, updates existing rows, or deletes rows based on a condition.
Question 68:
What is the role of a SAVEPOINT in SQL?
Answer: A SAVEPOINT allows you to set a specific point in a transaction, so you can roll back to that point if needed without rolling back the entire transaction.
Question 69:
What is a sequence in Oracle SQL?
Answer: A sequence is an object that generates a series of unique numbers, often used to create unique identifiers for rows in a table.
Question 70:
How do you create a sequence in Oracle SQL?
Answer:
CREATE SEQUENCE seq_name
START WITH 1
INCREMENT BY 1;
Chapter 3 — Grouping and Hierarchical Queries (Continued)
Question 71:
What is the difference between ROLLUP and CUBE?
Answer: ROLLUP generates subtotals and grand totals for hierarchical grouping, whereas CUBE generates all possible combinations of grouping columns and their subtotals.
Question 72:
What is the purpose of the GROUPING function?
Answer: The GROUPING function is used to identify which columns in a result set are aggregated when using ROLLUP, CUBE, or GROUPING SETS.
SELECT department_id, job_id, SUM(salary), GROUPING(department_id)
FROM employees
GROUP BY ROLLUP(department_id, job_id);
Question 73:
What is the use of the LEVEL keyword in hierarchical queries?
Answer: The LEVEL keyword is used to return the level number of a row in a hierarchical structure, where the root has a level of 1.
Question 74:
What does the PRIOR keyword do in a hierarchical query?
Answer: The PRIOR keyword refers to the previous row in the hierarchy. It is used in the CONNECT BY clause to establish the parent-child relationship between rows.
Question 75:
What is a hierarchical query in Oracle?
Answer: A hierarchical query is used to retrieve data that is organized in a parent-child relationship. The CONNECT BY clause is used to define the relationship.
Chapter 4 — Indexes and Performance Tuning Basics (Continued)
Question 76:
What is the difference between a B-tree index and a bitmap index?
Answer: A B-tree index is efficient for columns with high cardinality and is used for range queries. A bitmap index is more efficient for low cardinality columns (few distinct values) and works well for exact matches.
Question 77:
What is query optimization?
Answer: Query optimization involves improving the performance of a query by choosing the most efficient execution plan based on factors like indexing, joins, and the query structure.
Question 78:
What are the common techniques for query optimization in SQL?
Answer: Techniques include using indexes, avoiding SELECT *, reducing subqueries, ensuring appropriate joins, and analyzing execution plans.
Question 79:
What is an execution plan, and how can it help in performance tuning?
Answer: An execution plan shows how the database will execute a query. By analyzing the plan, you can identify bottlenecks, such as full table scans, and take corrective actions like adding indexes.
Question 80:
What is the role of the EXPLAIN PLAN command?
Answer: The EXPLAIN PLAN command provides a detailed description of the execution plan for a query, showing the steps taken by the optimizer to execute the query.
Question 81:
What is an index scan?
Answer: An index scan occurs when the database uses an index to locate rows in a table based on a query’s search criteria, which is faster than a full table scan.
Question 82:
What is a full table scan?
Answer: A full table scan occurs when the database scans all rows in a table to fulfill a query, typically when no appropriate index exists.
Chapter 5 — Data Control Language (DCL) and Security (Continued)
Question 83:
What is the difference between GRANT and REVOKE statements?
Answer: The GRANT statement is used to provide specific privileges to a user or role, while REVOKE is used to remove those privileges.
Question 84:
What is a role in SQL security?
Answer: A role is a named collection of privileges that can be granted to a user or another role, simplifying the management of permissions.
Question 85:
How can you list all the privileges granted to a user in Oracle?
Answer:
SELECT * FROM dba_tab_privs WHERE grantee = 'USER_NAME';
Question 86:
How do you revoke a role from a user in Oracle SQL?
Answer:
REVOKE role_name FROM user_name;
Question 87:
What is an Oracle user schema?
Answer: An Oracle user schema is a collection of database objects (like tables, views, and procedures) owned by a specific user.
Question 88:
What is the difference between a user and a schema in Oracle?
Answer: A user is an account that can access a database, while a schema is the collection of database objects owned by that user.
Question 89:
What is auditing in Oracle?
Answer: Auditing in Oracle tracks and records database activities, such as user login/logout, DML operations, and changes to database objects, for security and compliance purposes.
Question 90:
How do you create a user in Oracle SQL?
Answer:
CREATE USER user_name IDENTIFIED BY password;
Question 91:
How can you grant a user the DBA role in Oracle?
Answer:
GRANT DBA TO user_name;
Question 92:
What is the role of database profiles in Oracle?
Answer: A database profile defines a set of limits, such as password policies and session limits, that help control user behavior and resource usage.
Question 93:
How do you change a user’s password in Oracle SQL?
Answer:
ALTER USER user_name IDENTIFIED BY new_password;
Question 94:
What is data encryption, and why is it important?
Answer: Data encryption is the process of converting data into a secure format to prevent unauthorized access. It is crucial for protecting sensitive data from breaches.
Question 95:
What are the common database security practices?
Answer: Practices include using strong passwords, encrypting sensitive data, granting the least privilege, auditing access, and using roles for permissions.
Question 96:
**What is the difference between system privileges and
object privileges in SQL?**
Answer: System privileges allow users to perform actions on the database level (e.g., CREATE, ALTER), while object privileges allow users to perform actions on specific database objects (e.g., SELECT, INSERT on tables).
Question 97:
What are database roles, and how do they help in security?
Answer: Roles group related privileges together, making it easier to manage permissions for users and ensuring that only necessary access is granted.
Question 98:
What is the purpose of database profiles in Oracle?
Answer: Database profiles enforce resource limits and password policies to manage user behavior and ensure compliance with organizational standards.
Question 99:
How do you track user activity in Oracle SQL?
Answer: User activity can be tracked using Oracle’s auditing features, where database operations are logged for review.
Question 100:
What is the principle of least privilege in database security?
Answer: The principle of least privilege means granting users the minimum level of access necessary to perform their job functions, minimizing the risk of unauthorized access or accidental damage.
This concludes the set of 100 interview questions and answers for Intermediate level Oracle SQL, covering key topics such as joins, subqueries, advanced DML operations, performance tuning, and security.