2-4 years of experience - Query based SQL interview question and answer
Here’s a list of SQL interview questions and answers for candidates with 2–4 years of experience. These include conceptual, practical, and scenario-based questions that interviewers commonly ask.
SQL
1. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?
Answer
INNER JOIN – Returns records that have matching values in both tables.
LEFT JOIN – Returns all records from the left table and matched records from the right table.
RIGHT JOIN – Returns all records from the right table and matched records from the left table.
FULL JOIN – Returns all records when there is a match in either table.
2. What is the difference between WHERE and HAVING?
Answer:
WHERE filters rows before grouping (used with individual rows).
HAVING filters groups after grouping (used with aggregate functions).
SELECT department, COUNT(*) FROM employees WHERE salary > 50000 GROUP BY department HAVING COUNT(*) > 5;
3. What are Primary Key and Unique Key differences?
Answer:
Feature Primary Key Unique Key Uniqueness Must be unique Must be unique NULL values Not allowed Allowed (one NULL) Count per table One Multiple allowed
4. Explain Normalization and its types.
Answer:
Normalization reduces redundancy and improves data integrity.
1NF: Atomic values.
2NF: No partial dependency.
3NF: No transitive dependency.
BCNF: Every determinant is a candidate key.
5. What is a View and when would you use it?
Answer:
A view is a virtual table based on a SQL query result.
Simplifies complex queries.
Provides security by restricting access to underlying tables.
CREATE VIEW high_salary AS SELECT name, salary FROM employees WHERE salary > 100000;
6. Find the 2nd highest salary from an Employee table.
Answer 1 (Using LIMIT):SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
Answer 2 (Using Subquery):SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
7. What are window (analytic) functions?
Answer:
Window functions perform calculations across a set of rows related to the current row.
Example – find each employee’s salary rank:
SELECT name, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees;
8. What’s the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?
Answer:
Function Behavior RANK() Skips ranks if there are ties (1, 2, 2, 4) DENSE_RANK() No gaps in ranking (1, 2, 2, 3) ROW_NUMBER() Unique rank regardless of duplicates (1, 2, 3, 4)
9. What is the difference between DELETE, TRUNCATE, and DROP?
Answer:
Command Removes Data Rollback Removes Table Structure DELETE Yes (specific rows) Yes No TRUNCATE All rows No (in most DBs) No DROP Entire table No Yes
10. Explain indexing and its types.
Answer:
Indexes speed up data retrieval.
Clustered Index: Rearranges table data physically; only one per table.
Non-clustered Index: Separate structure; can have multiple per table.
📊 Scenario-Based / Query Writing Questions
11. Find employees who earn more than their manager.
SELECT e.name AS Employee, m.name AS Manager FROM employees e JOIN employees m ON e.manager_id = m.emp_id WHERE e.salary > m.salary;
12. Get department-wise highest salary.
SELECT department, MAX(salary) AS max_salary FROM employees GROUP BY department;
13. Find duplicate records in a table.
SELECT name, COUNT(*) FROM employees GROUP BY name HAVING COUNT(*) > 1;
14. Delete duplicate records but keep one.
DELETE FROM employees WHERE emp_id NOT IN ( SELECT MIN(emp_id) FROM employees GROUP BY name, department, salary );
15. Explain the difference between EXISTS and IN.
Answer:
IN compares a value against a list or subquery.
EXISTS checks for existence of rows in a subquery (faster with correlated subqueries).
Example:
SELECT name FROM employees e WHERE EXISTS ( SELECT 1 FROM departments d WHERE d.id = e.department_id );