Top 50 SQL Interview Questions and Answers for Freshers
This SQL interview questions and answers guide helps new graduates and Freshers to prepare for database job interviews with questions made especially for freshers entering the field. We included basic SQL questions about database queries, table operations, and data management that entry-level positions require.
The guide covers everything from simple statements to joins and basic database concepts that companies test beginners on. It’s very helpful for learning SQL concepts even if you’re completely new to databases. Use this guide to practice SQL basics and get your first database or developer job.
Table of Contents
Basic SQL Interview Questions and Answers for Freshers
Que 1. What is the difference between WHERE and HAVING clauses in SQL?
Answer:
The WHERE clause is used to filter rows before any grouping is performed, whereas the HAVING clause filters groups after aggregation.
- WHERE works on individual rows.
- HAVING works on aggregated data.
Example:
SELECT department, COUNT(*) AS employee_count
FROM employees
WHERE status = 'Active'
GROUP BY department
HAVING COUNT(*) > 5;Que 2. How can you find duplicate rows in a table?
Answer:
You can identify duplicates by grouping the relevant columns and checking if their count is greater than 1.
Example:
SELECT column1, column2, COUNT(*) AS duplicate_count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;Que 3. What is the difference between UNION and UNION ALL?
Answer:
| Feature | UNION | UNION ALL |
|---|---|---|
| Duplicate Removal | Removes duplicates | Keeps all duplicates |
| Performance | Slightly slower due to deduplication | Faster as no deduplication |
| Use Case | When unique results are needed | When performance is important and duplicates are acceptable |
Example:
SELECT name FROM employees
UNION
SELECT name FROM managers;Que 4. How do you retrieve the second highest salary from an Employee table?
Answer:
You can use the ORDER BY clause with LIMIT or window functions depending on the SQL dialect.
Example using LIMIT:
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;Example using window function:
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_no
FROM employees
) AS ranked
WHERE rank_no = 2;Que 5. How can you update multiple columns in SQL with a single query?
Answer:
You can use the UPDATE statement by specifying multiple column assignments separated by commas.
Example:
UPDATE employees
SET salary = salary * 1.1,
department = 'HR'
WHERE employee_id = 101;Que 6. How do you find the total number of rows in a table without counting duplicates in a specific column?
Answer:
Use the COUNT function with the DISTINCT keyword.
Example:
SELECT COUNT(DISTINCT department)
FROM employees;Que 7. What is the difference between INNER JOIN and LEFT JOIN?
Answer:
- INNER JOIN returns rows that have matching values in both tables.
- LEFT JOIN returns all rows from the left table and matching rows from the right table; unmatched rows from the right table will have NULL values.
Example:
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;Que 8. How can you delete duplicate rows but keep one instance?
Answer:
You can use a common table expression (CTE) with ROW_NUMBER to identify and remove duplicates.
Example:
WITH cte AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY id) AS row_num
FROM table_name
)
DELETE FROM cte
WHERE row_num > 1;Que 9. How do you retrieve the first N rows in SQL?
Answer:
- In MySQL and PostgreSQL: Use LIMIT.
- In SQL Server: Use Top.
Example (MySQL):
SELECT * FROM employees
LIMIT 10;Example (SQL Server):
SELECT TOP 10 * FROM employees;Que 10. How can you find employees who do not belong to any department?
Answer:
You can use a LEFT JOIN and check for NULL in the joined table’s key column.
Example:
SELECT e.*
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_id IS NULL;Que 11. How do you retrieve all rows where a column value starts with a specific letter?
Answer:
Use the LIKE operator with the % wildcard.
Example:
SELECT * FROM employees
WHERE name LIKE 'A%';Que 12. How can you calculate the running total in SQL?
Answer:
You can use window functions like SUM with an OVER clause.
Example:
SELECT order_id, order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;Que 13. How can you pivot rows into columns in SQL?
Answer:
You can use conditional aggregation or the PIVOT function (in SQL Server).
Example (conditional aggregation):
SELECT department,
SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS male_count,
SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department;Que 14. How can you select only even or odd rows from a table?
Answer:
You can use the MOD or % operator with a unique column like ID.
Example:
SELECT * FROM employees
WHERE MOD(employee_id, 2) = 0; -- Even rowsQue 15. How can you find the highest salary in each department?
Answer:
You can use GROUP BY with the MAX function.
Example:
SELECT department_id, MAX(salary) AS highest_salary
FROM employees
GROUP BY department_id;Advanced SQL Interview Questions and Answers for Freshers
Que 16. What is a CTE and how is it different from a subquery?
Answer:
A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a single SQL statement. It improves query readability and can be referenced multiple times in the same query.
Differences between CTE and Subquery:
| Feature | CTE | Subquery |
|---|---|---|
| Reusability | Can be reused multiple times | Cannot be reused |
| Readability | Improves readability | Can become complex to read |
| Recursion support | Supports recursion | Does not support recursion |
Example:
WITH EmployeeCTE AS (
SELECT EmployeeID, ManagerID
FROM Employees
)
SELECT * FROM EmployeeCTE;
Que 17. Explain the concept of a Window Function with an example.
Answer:
A Window Function performs calculations across a set of rows related to the current row, without collapsing them into a single output row like aggregate functions do.
Example: Ranking employees by salary:
SELECT EmployeeID, Salary,
RANK() OVER (ORDER BY Salary DESC) AS RankPosition
FROM Employees;
This assigns a rank to each employee based on salary without grouping results.
Que 18. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
Answer:
- RANK(): Gives rank with gaps if there are ties.
- DENSE_RANK(): Gives rank without gaps in ties.
- ROW_NUMBER(): Assigns a unique number regardless of ties.
Example:
SELECT Name, Salary,
RANK() OVER (ORDER BY Salary DESC) AS RankVal,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRankVal,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
FROM Employees;
Que 19. How does an Index work in SQL, and what are the types?
Answer:
An index is a database object that speeds up data retrieval operations by creating a quick lookup reference for the data.
Types of Indexes:
- Clustered Index
- Non-Clustered Index
- Unique Index
- Composite Index
- Full-Text Index
Indexes improve performance but can slow down INSERT, UPDATE, and DELETE operations due to maintenance overhead.
Que 20. What is the purpose of the PARTITION BY clause?
Answer:
PARTITION BY is used in window functions to divide result sets into partitions to which the function is applied independently.
Example:
SELECT Department, EmployeeID, Salary,
AVG(Salary) OVER (PARTITION BY Department) AS DeptAvgSalary
FROM Employees;
Here, the average salary is calculated separately for each department.
Que 21. Explain the difference between INNER JOIN and CROSS JOIN.
Answer:
- INNER JOIN: Returns rows where there is a match in both tables.
- CROSS JOIN: Returns the Cartesian product of both tables (all possible combinations).
Example:
-- INNER JOIN
SELECT A.Name, B.DepartmentName
FROM Employees A
INNER JOIN Departments B ON A.DeptID = B.ID;
-- CROSS JOIN
SELECT A.Name, B.DepartmentName
FROM Employees A
CROSS JOIN Departments B;
Que 22. How can you delete duplicate rows in SQL while keeping one copy?
Answer:
You can use CTE with ROW_NUMBER() to identify and remove duplicates.
WITH DuplicateCTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Name, Department ORDER BY ID) AS RowNum
FROM Employees
)
DELETE FROM DuplicateCTE WHERE RowNum > 1;
This keeps only the first occurrence of each duplicate.
Que 23. What is the difference between UNION and UNION ALL?
Answer:
- UNION: Combines result sets and removes duplicates.
- UNION ALL: Combines result sets without removing duplicates (faster performance).
Example:
SELECT Name FROM EmployeesA
UNION
SELECT Name FROM EmployeesB;
Que 24. Explain the concept of a correlated subquery.
Answer:
A correlated subquery depends on the outer query for its values and is executed once for each row of the outer query.
Example:
SELECT Name, Salary
FROM Employees E
WHERE Salary > (SELECT AVG(Salary)
FROM Employees
WHERE Department = E.Department);
Here, the inner query uses a column from the outer query.
Que 25. How do you implement pagination in SQL?
Answer:
Pagination can be implemented using OFFSET and FETCH.
Example: Fetch rows 11 to 20:
SELECT * FROM Employees
ORDER BY EmployeeID
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Que 26. What is the difference between DELETE, TRUNCATE, and DROP?
Answer:
| Command | Removes Data Only | Removes Structure | Rollback Possible | Speed |
|---|---|---|---|---|
| DELETE | Yes | No | Yes | Slower |
| TRUNCATE | Yes | No | No | Faster |
| DROP | Yes | Yes | No | Fastest |
Que 27. How can you find the second highest salary in SQL?
Answer:
SELECT MAX(Salary) AS SecondHighest
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);
This retrieves the maximum salary that is less than the overall highest salary.
Que 28. What is the difference between a Primary Key and a Unique Key?
Answer:
- Primary Key: Ensures unique and non-null values in a column. Only one per table.
- Unique Key: Ensures unique values but allows one null value. Multiple unique keys can exist in a table.
Que 29. What is query execution plan and how do you use it?
Answer:
A query execution plan is a detailed breakdown of how SQL Server or another RDBMS will execute a query. It helps in identifying performance bottlenecks like missing indexes or expensive joins.
You can view it in most databases using:
EXPLAIN SELECT * FROM Employees;
This displays the execution steps.
Que 30. How can you optimize a slow SQL query?
Answer:
- Use proper indexes.
- Avoid SELECT *, retrieve only required columns.
- Use WHERE filters efficiently.
- Avoid unnecessary joins.
- Use query execution plans to identify bottlenecks.
- Normalize or denormalize tables based on the use case.
PL/SQL Interview Questions and Answers for Freshers
Que 31. What are Cursors in PL/SQL and when should you use Explicit Cursors?
Answer: In PL/SQL, cursors are pointers to the result set of a query. They are used to process multiple rows returned by a SELECT statement.
- Implicit Cursors are automatically created by PL/SQL for single-row queries such as SELECT INTO, INSERT, UPDATE, or DELETE.
- Explicit Cursors are defined by the programmer for queries returning more than one row.
You should use explicit cursors when:
- You need to fetch rows one by one.
- You want better control over the retrieval process.
- You require post-processing on fetched data.
Example:
DECLARE
CURSOR emp_cur IS SELECT empno, ename FROM employees;
v_empno employees.empno%TYPE;
v_ename employees.ename%TYPE;
BEGIN
OPEN emp_cur;
LOOP
FETCH emp_cur INTO v_empno, v_ename;
EXIT WHEN emp_cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' - ' || v_ename);
END LOOP;
CLOSE emp_cur;
END;
/Que 32. How do Triggers work in PL/SQL and what are the common types?
Answer: Triggers are stored PL/SQL blocks that automatically execute in response to specific events on a table or view. They are useful for enforcing business rules, maintaining audit trails, and validating data.
Common trigger types:
- BEFORE Triggers: Execute before the triggering statement.
- AFTER Triggers: Execute after the triggering statement.
- INSTEAD OF Triggers: Used mainly for views to perform custom actions instead of the triggering statement.
- Row-Level Triggers: Fire once for each row affected.
- Statement-Level Triggers: Fire once for the entire SQL statement.
Que 33. What is the difference between %TYPE and %ROWTYPE in PL/SQL?
Answer:
| Feature | %TYPE | %ROWTYPE |
|---|---|---|
| Purpose | Defines a variable with the same data type as a table column or another variable | Defines a record with the same structure as a row in a table or cursor |
| Usage | For single variables matching column types | For composite variables matching full row structure |
| Benefits | Automatic data type synchronization with the referenced column or variable | Allows handling of multiple columns in a single variable |
Que 34. How can you handle exceptions effectively in PL/SQL?
Answer: PL/SQL provides an EXCEPTION block to handle runtime errors gracefully.
Best practices:
- Always handle predictable exceptions such as NO_DATA_FOUND and TOO_MANY_ROWS.
- Use OTHERS for unexpected errors but log details for debugging.
- Keep exception handling blocks specific to avoid masking errors.
Example:
BEGIN
SELECT salary INTO v_sal FROM employees WHERE empno = 100;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error occurred');
END;
/Que 35. How do Packages improve PL/SQL program organization?
Answer: Packages group related procedures, functions, variables, and cursors into a single unit, improving code organization, reusability, and security.
Advantages:
- Modularity: Keeps related logic together.
- Encapsulation: Hides implementation details in the package body.
- Performance: Reduces parsing time by loading all package objects into memory at once.
- Overloading: Allows multiple subprograms with the same name but different parameters.
Que 36. How can Bulk Collect and FORALL improve PL/SQL performance?
Answer: Bulk Collect retrieves multiple rows into collections with a single fetch, reducing context switches between SQL and PL/SQL engines. FORALL allows bulk DML operations using collections, improving performance.
Example:
DECLARE
TYPE t_ids IS TABLE OF employees.empno%TYPE;
v_ids t_ids;
BEGIN
SELECT empno BULK COLLECT INTO v_ids FROM employees;
FORALL i IN v_ids.FIRST..v_ids.LAST
DELETE FROM employees WHERE empno = v_ids(i);
END;
/Que 37. How do Autonomous Transactions work in PL/SQL?
Answer: An autonomous transaction is independent of the main transaction. It can commit or roll back changes without affecting the main transaction.
Use cases include logging, auditing, and sending notifications.
Example:
PRAGMA AUTONOMOUS_TRANSACTION;Que 38. What are Collection Types in PL/SQL and their differences?
Answer: PL/SQL supports three main collection types:
- Associative Arrays: Indexed by strings or numbers, unbounded in size.
- Nested Tables: Similar to arrays but can be stored in the database.
- VARRAYs: Fixed-size arrays stored in-line with table data.
Que 39. How can you optimize PL/SQL code for better performance?
Answer:
- Use bulk operations (BULK COLLECT, FORALL) instead of row-by-row processing.
- Avoid unnecessary context switches between SQL and PL/SQL.
- Use bind variables for dynamic SQL.
- Minimize loop iterations and redundant queries.
- Analyze execution plans to identify bottlenecks.
Que 40. How do you implement Dynamic SQL in PL/SQL?
Answer: Dynamic SQL allows execution of SQL statements whose structure is not known until runtime. PL/SQL provides:
- EXECUTE IMMEDIATE for immediate execution.
- DBMS_SQL package for more complex dynamic SQL with unknown numbers of columns.
Example:
EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * 1.1 WHERE deptno = :dept'
USING v_deptno;Oracle SQL Interview Questions and Answers for Freshers
Que 41. What is the difference between ROWNUM and ROW_NUMBER() in Oracle SQL?
Answer:
ROWNUM is a pseudocolumn in Oracle that assigns a temporary number to rows returned by a query, starting at 1. It is applied before sorting, so it is not ideal for retrieving specific ordered rows. ROW_NUMBER() is an analytic function introduced in Oracle 9i that assigns a unique sequential number to rows based on the ORDER BY clause and is applied after sorting.
Example:
-- Using ROWNUM
SELECT * FROM employees WHERE ROWNUM <= 5;
-- Using ROW_NUMBER()
SELECT *
FROM (
SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees e
)
WHERE rn <= 5;ROWNUM is faster but limited in ordering control, while ROW_NUMBER() offers flexibility in ranking and filtering after sorting.
Que 42. How can you find duplicate rows in a table in Oracle SQL?
Answer:
Duplicates can be identified by grouping records on relevant columns and using the HAVING clause to filter groups with counts greater than 1.
Example:
SELECT first_name, last_name, COUNT(*)
FROM employees
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;This approach ensures only repeated records are retrieved. For actual row details, a subquery with analytic COUNT() can be used.
Que 43. How do you use the MERGE statement in Oracle SQL?
Answer:
The MERGE statement (also known as UPSERT) allows inserting new rows or updating existing ones in a single statement. It is useful for synchronizing tables.
Example:
MERGE INTO employees e
USING new_employees n
ON (e.emp_id = n.emp_id)
WHEN MATCHED THEN
UPDATE SET e.salary = n.salary
WHEN NOT MATCHED THEN
INSERT (emp_id, first_name, salary)
VALUES (n.emp_id, n.first_name, n.salary);MERGE improves performance by avoiding multiple separate INSERT and UPDATE statements.
Que 44. How do you retrieve the top N salaries from a table in Oracle SQL?
Answer:
Using the ROW_NUMBER() or RANK() function with ORDER BY is the most efficient way.
Example to fetch top 3 salaries:
SELECT *
FROM (
SELECT e.*, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees e
)
WHERE rnk <= 3;DENSE_RANK() avoids skipping ranks when salaries are tied, while ROW_NUMBER() assigns unique row numbers regardless of duplicates.
Que 45. What are inline views in Oracle SQL and how are they used?
Answer:
An inline view is a subquery in the FROM clause that acts like a temporary table. It is useful for simplifying complex queries and applying functions like ROW_NUMBER().
Example:
SELECT *
FROM (
SELECT emp_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees
)
WHERE rn <= 5;This eliminates the need for creating a separate table or view.
Que 46. How do you use the CONNECT BY clause for hierarchical queries?
Answer:
The CONNECT BY clause retrieves hierarchical data such as organizational structures or parent-child relationships.
Example:
SELECT employee_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;Here, LEVEL shows the depth in the hierarchy. START WITH defines the root row(s), and CONNECT BY defines the relationship between parent and child rows.
Que 47. What is the difference between DELETE, TRUNCATE, and DROP in Oracle SQL?
Answer:
| Command | Removes Data Only | Removes Structure | Transaction Rollback | Speed |
|---|---|---|---|---|
| DELETE | Yes | No | Yes | Slow |
| TRUNCATE | Yes | No | No | Fast |
| DROP | Yes | Yes | No | Fastest |
DELETE is a DML command and can be rolled back. TRUNCATE and DROP are DDL commands and commit immediately.
Que 48. How can you retrieve the nth highest salary in Oracle SQL?
Answer:
Using DENSE_RANK():
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
WHERE rnk = 3;This retrieves the 3rd highest salary, including ties. ROW_NUMBER() can be used if exact positional ranking without ties is required.
Que 49. How do you find employees who do not have any corresponding records in another table?
Answer:
A LEFT JOIN combined with a NULL check on the right table’s key column works well.
Example:
SELECT e.*
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;This method identifies records in employees that are not assigned to any department.
Que 50. How do you use analytic functions like LAG and LEAD in Oracle SQL?
Answer:
LAG retrieves a previous row’s value, and LEAD retrieves a following row’s value without using self-joins.
Example:
SELECT emp_id, salary,
LAG(salary, 1) OVER (ORDER BY emp_id) AS prev_salary,
LEAD(salary, 1) OVER (ORDER BY emp_id) AS next_salary
FROM employees;These functions are useful for trend analysis, comparing current rows with preceding or following one
