Introduction
In today’s data-driven world, proficiency in SQL (Structured Query Language) is essential for any aspiring data professional. Oracle SQL, in particular, is widely used in enterprise environments for managing and manipulating relational databases. If you’re preparing for an interview that involves Oracle SQL, it’s crucial to familiarize yourself with common queries and concepts that are often tested. This blog will cover various Oracle SQL queries and concepts that you should master to excel in your Oracle SQL Queries for Interview.
Basic SQL Queries
Selecting Data
The most fundamental operation in SQL is the SELECT statement, used to retrieve data from a database.
SELECT * FROM employees;
This query retrieves all columns from the employees table. However, it’s often more efficient to specify only the columns you need:
SELECT employee_id, first_name, last_name FROM employees;
Filtering Data
The WHERE clause is used to filter records based on specified conditions.
SELECT first_name, last_name FROM employees WHERE department_id = 10;
This query retrieves the first and last names of employees who work in department 10.
Sorting Data
To sort the result set, you use the ORDER BY clause.
SELECT first_name, last_name FROM employees ORDER BY last_name ASC;
This query retrieves the first and last names of employees sorted by their last name in ascending order.
Aggregating Data
Aggregation functions, such as COUNT, SUM, AVG, MAX, and MIN, are used to perform calculations on a set of values.
SELECT COUNT(*) FROM employees WHERE department_id = 10;
This query counts the number of employees in department 10.
Intermediate SQL Queries
Joining Tables
Joins are used to combine rows from two or more tables based on a related column between them.
Inner Join
sqlCopy codeSELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
This query retrieves the first name, last name, and department name of employees by joining the employees and departments tables.
Left Join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
This query retrieves all employees’ names and their respective departments, including employees who are not assigned to any department.
Grouping Data
The GROUP BY clause groups rows that have the same values in specified columns into summary rows.
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;
This query retrieves the number of employees in each department.
Subqueries
Subqueries are nested queries used within another SQL query to perform additional operations.
SELECT first_name, last_name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
This query retrieves the first and last names of employees whose salary is above the average salary.
Recommended To Raed Also: Manual Testing Online Course with Certificate
Advanced SQL Queries
Using Window Functions
Window functions perform calculations across a set of table rows related to the current row.
SELECT first_name, last_name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
This query assigns a rank to each employee based on their salary, in descending order.
Common Table Expressions (CTEs)
CTEs are temporary result sets defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement.
WITH department_employees AS (
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
)
SELECT d.department_name, de.employee_count
FROM departments d
JOIN department_employees de ON d.department_id = de.department_id;
This query uses a CTE to count the number of employees in each department and then joins it with the departments table to retrieve department names.
Handling NULL Values
Oracle SQL provides functions to handle NULL values, such as NVL, COALESCE, and NULLIF.
SELECT first_name, last_name, NVL(commission_pct, 0) AS commission
FROM employees;
This query retrieves the first name, last name, and commission percentage of employees, replacing NULL values with 0.
Hierarchical Queries
Hierarchical queries are used to retrieve data based on a hierarchical relationship within the same table.
SELECT employee_id, first_name, last_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;
This query retrieves the hierarchy of employees starting with the top-level managers.
Pivoting Data
Pivoting involves rotating rows into columns to present summary data.
SELECT *
FROM (SELECT department_id, job_id, salary FROM employees)
PIVOT (SUM(salary) FOR job_id IN ('IT_PROG', 'ST_CLERK', 'SA_MAN'));
This query pivots the salaries of employees by their job roles within each department.
Performance Tuning Tips
Indexes
Indexes improve the speed of data retrieval operations. However, they can slow down INSERT, UPDATE, and DELETE operations.
TE INDEX idx_employee_last_name ON employees(last_name);
This query creates an index on the last_name column of the employees table.
Execution Plans
Analyzing execution plans helps identify performance bottlenecks in SQL queries.
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE last_name = 'Smith';
This query provides the execution plan for retrieving employees with the last name ‘Smith’.
Using Hints
Oracle SQL allows you to use hints to influence the optimizer’s choice of execution plan.
SELECT /*+ INDEX(employees idx_employee_last_name) */
first_name, last_name FROM employees WHERE last_name = 'Smith';
This query uses a hint to suggest using the idx_employee_last_name index.
Avoiding Full Table Scans
Full table scans can be costly in terms of performance. Ensure your queries use indexes where appropriate.
SELECT first_name, last_name FROM employees WHERE employee_id = 100;
This query uses the primary key index on employee_id for efficient retrieval.
Optimizing Joins
Ensure that your join conditions are indexed and that you’re joining on the most restrictive condition first.
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000;
This query joins the employees and departments tables, filtering employees with a salary greater than 50,000.
Common Interview Questions and Solutions
1. Retrieve the Top N Salaries
Interviewers often ask you to retrieve the top N salaries in a table.
SELECT * FROM (
SELECT first_name, last_name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
) WHERE salary_rank <= 5;
This query retrieves the top 5 highest salaries.
Find Employees with the Highest Salary in Each Department
SELECT department_id, first_name, last_name, salary
FROM (
SELECT department_id, first_name, last_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees
) WHERE salary_rank = 1;
This query retrieves the employees with the highest salary in each department.
Identify Duplicate Records
Interviewers might ask you to identify duplicate records in a table.
SELECT first_name, last_name, COUNT(*)
FROM employees
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;
This query retrieves duplicate employee names.
Calculate the Difference Between Two Dates
SELECT first_name, last_name, hire_date,
SYSDATE - hire_date AS days_worked
FROM employees;
This query calculates the number of days an employee has worked.
Update Records Conditionally
UPDATE employees
SET salary = salary * 1.10
WHERE department_id = 10;
This query increases the salary of employees in department 10 by 10%.
Recommended To Raed Also: Manual Testing free Course with Certificate
Conclusion
Mastering Oracle SQL queries is crucial for acing any interview related to database management and data analysis. This guide has covered a range of queries from basic to advanced, as well as performance tuning tips and common interview questions. By practicing these queries and understanding the underlying concepts, you’ll be well-prepared to tackle any Oracle SQL interview questions that come your way. Happy learning, and good luck with your interview!
