SQL Interview Questions

 What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN? Explain with examples.

Table: Employees

EmpID EmpName DeptID

1         Alice         10

2        Bob                 20

3       Charlie         NULL

4       David         30

Table: Departments

DeptID DeptName

10         HR

20         IT

40         Finance

1. INNER JOIN

-------------

Returns only the matching rows from both tables.

Non-matching rows are excluded.

EX: SELECT e.EmpName, d.DeptName FROM Employees e

INNER JOIN Departments d ON e.DeptID = d.DeptID;

Result:

EmpName DeptName

Alice         HR

Bob                 IT

2. LEFT JOIN (LEFT OUTER JOIN)

-----------------------------

Returns all rows from the left table (Employees), and matching rows from the right table (Departments).

If no match, right side columns are NULL.

SELECT e.EmpName, d.DeptName FROM Employees e

LEFT JOIN Departments d ON e.DeptID = d.DeptID;

Result:

EmpName DeptName

Alice         HR

Bob                 IT

Charlie         NULL

David         NULL

3. RIGHT JOIN (RIGHT OUTER JOIN)

-------------------------------

Returns all rows from the right table (Departments), and matching rows from the left table (Employees).

If no match, left side columns are NULL.

SELECT e.EmpName, d.DeptName FROM Employees e

RIGHT JOIN Departments d ON e.DeptID = d.DeptID;

Result:

EmpName DeptName

Alice         HR

Bob                 IT

NULL         Finance

4. FULL OUTER JOIN

-----------------

Returns all rows from both tables, matching where possible.

If no match, missing side will have NULL.

SELECT e.EmpName, d.DeptName FROM Employees e

FULL OUTER JOIN Departments d ON e.DeptID = d.DeptID;

Result:

EmpName DeptName

Alice HR

Bob         IT

Charlie NULL

David NULL

NULL Finance

=================================================

What are the differences between DELETE, TRUNCATE, and DROP?

DELETE:

Deletes specific rows from a table based on a WHERE condition.

Can be rolled back.

Performance Slower (row-by-row operation).

Its support WHERE Clause.

DELETE FROM Employees WHERE EmpID = 101;

TRUNCATE:

Deletes all rows from a table quickly.

Doesn't Rollback data.

Performance Faster than DELETE.

Not support WHERE Clause.

TRUNCATE TABLE Employees;

DROP:

Completely removes the table structure and data.

cannot be rolled back.

Performance Fastest – removes table and data dictionary entry.

Not applicable WHERE Clause.

DROP TABLE Employees;

Use Cases:

DELETE – When you want to remove specific records, e.g., delete inactive users.

TRUNCATE – When you want to clear the table before a fresh import or data load.

DROP – When you no longer need the table (e.g., after a refactoring or cleanup).

=================================================

What is the difference between WHERE and HAVING clause?

The WHERE and HAVING clauses are both used to filter records in SQL, but they 

work at different stages of query execution and are used for different types of filtering.

WHERE Clause Example:

Filters rows before grouping or aggregation happens.

SELECT EmpName, Salary FROM Employees

WHERE Salary > 50000;

HAVING Clause Example:

Filters after aggregation is performed.

SELECT DeptID, SUM(Salary) AS TotalSalary

FROM Employees GROUP BY DeptID

HAVING SUM(Salary) > 100000;

Combined Example:

SELECT DeptID, COUNT(*) AS EmpCount, AVG(Salary) AS AvgSal

FROM Employees WHERE Status = 'Active'

GROUP BY DeptID HAVING AVG(Salary) > 60000;

=================================================

Write a query to find the second highest salary from an Employee table?

EX:

SELECT DISTINCT Salary FROM Employee

ORDER BY Salary DESC LIMIT 1 OFFSET 1;

LIMIT 1 OFFSET 1 skips the highest and returns the second highest.

Simple and efficient in MySQL/PostgreSQL.

EX: 

SELECT MAX(Salary) AS SecondHighestSalary

FROM Employee

WHERE Salary < (

    SELECT MAX(Salary)

    FROM Employee);

Finds the max salary less than the highest.

Works in all RDBMS including Oracle and SQL Server.

EX:

SELECT Salary

FROM (

    SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn

    FROM Employee

) AS Ranked WHERE rn = 2;

Only returns the second row in salary order (even if multiple people tie for first).

====================================================

How do you find duplicate records in a table? How do you delete them?

Assume we have a table called Employee:

EmpID EmpName Email

1     Alice     alice@example.com

2     Bob     bob@example.com

3     Alice     alice@example.com

4     Charlie charlie@email.com

To find duplicates based on EmpName and Email:

SELECT EmpName, Email, COUNT(*) AS count

FROM Employee

GROUP BY EmpName, Email

HAVING COUNT(*) > 1;

Delete Duplicate Records (Keep One)

WITH CTE AS (

    SELECT *,

           ROW_NUMBER() OVER (PARTITION BY EmpName, Email ORDER BY EmpID) AS rn

    FROM Employee)

DELETE FROM CTE

WHERE rn > 1;

Keeps only the first record (lowest EmpID) per duplicate group.

Works in PostgreSQL, SQL Server, Oracle (12c+), MySQL 8.0+

Using Subquery (Older approach for MySQL < 8.0):

DELETE FROM Employee

WHERE EmpID NOT IN (

    SELECT MIN(EmpID)

    FROM Employee

    GROUP BY EmpName, Email);

Keeps the record with minimum EmpID from each duplicate group.

Not efficient on large tables; be careful with NOT IN and NULLs.

Optional: Preview What You’ll Delete:

SELECT *

FROM (

    SELECT *,

           ROW_NUMBER() OVER (PARTITION BY EmpName, Email ORDER BY EmpID) AS rn

    FROM Employee) AS Temp

WHERE rn > 1;

Always check your delete logic with SELECT first.

====================================================

What is a CTE (Common Table Expression)? How is it different from subqueries?

A CTE (Common Table Expression) is a temporary result set that you can reference 

within a SELECT, INSERT, UPDATE, or DELETE query. It's defined using the WITH clause.

Syntax:

WITH CTE_Name AS (

    SELECT column1, column2

    FROM SomeTable

    WHERE condition)

SELECT * FROM CTE_Name

WHERE another_condition;

You can reuse the CTE in the main query.

Acts like a named subquery that improves readability and modularity.

Example:

Suppose you want to get departments where total salary > 1 lakh:

WITH DeptSalary AS (

    SELECT DeptID, SUM(Salary) AS TotalSalary

    FROM Employee

    GROUP BY DeptID)

SELECT DeptID

FROM DeptSalary

WHERE TotalSalary > 100000;

====================================================

What are RANK(), DENSE_RANK() and ROW_NUMBER()? Where have you used them?

1. ROW_NUMBER()

Assigns a unique sequential number to each row within a partition.

No ties — even if rows have the same value, they get different numbers.

SELECT EmpName, Salary,

       ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum

FROM Employee;

EmpName Salary RowNum

Bob         85000 1

Alice 85000 2

David 70000 3

Charlie 60000 4

Use case: pagination, or keeping just one row per group.

 2. RANK()

Assigns the same rank to tied rows.

Skips ranks after ties (non-continuous).

SELECT EmpName, Salary,

       RANK() OVER (ORDER BY Salary DESC) AS SalaryRank

FROM Employee;

EmpName Salary Rank

Bob         85000 1

Alice   85000 1

David 70000 3

Charlie 60000 4

Use case: When you want to rank rows with potential ties.

3. DENSE_RANK()

Like RANK(), but does not skip the next rank after a tie (continuous).

SELECT EmpName, Salary,

       DENSE_RANK() OVER (ORDER BY Salary DESC) AS SalaryRank

FROM Employee;

EmpName Salary Rank

Bob          85000 1

Alice 85000 1

David 70000 2

Charlie 60000 3

Use case: When you need dense/compact ranks even with ties.

====================================================

How do you optimize a slow SQL query? What steps do you take?

1. Use Proper Indexing

Index columns used in WHERE, JOIN, ORDER BY, and GROUP BY.

Avoid functions on indexed columns (e.g., WHERE UPPER(name) = ...).

WHERE EmpID = 123 (Good)

WHERE YEAR(created_at) = 2023 (Bad)

2. **Avoid SELECT ***

Always select only the required columns.

SELECT EmpName, Salary FROM Employee; (Good)

SELECT * FROM Employee; (Bad)

3. Use Joins Smartly

Use INNER JOIN instead of LEFT JOIN if nulls are not needed.

Join on indexed columns whenever possible.

4. Filter Early with WHERE

Apply filters before GROUP BY or JOIN to reduce row counts early.

5. Optimize GROUP BY and Aggregates

Group only on necessary columns.

Use HAVING only for aggregate filters, not row-level conditions.

6. Use LIMIT / OFFSET for Pagination

Prevents returning large result sets in APIs.

SELECT EmpName FROM Employee ORDER BY EmpName LIMIT 20 OFFSET 40;

7. Avoid N+1 Queries

In Java (JPA/Hibernate), avoid multiple small queries by using fetch joins or batch fetching.

Tools for Query Optimization:

Tool                 Usage

EXPLAIN               Execution plan

AUTO TRACE (Oracle)   Query profiling

pgAdmin (PostgreSQL)  Visual plan analysis

SQL Server Profiler   SQL Server query performance

Hibernate show_sql,format_sql Debugging JPA queries

==================================================

Popular posts from this blog

java 8 interview questions

 Core Java 8 Features ==================== What are the main features introduced in Java 8 ? Lambda Expressions Functional Interfaces Stream API Default and Static Methods in Interfaces java.time (New Date and Time API) Optional class Method references and constructor references Lambda and Functional Interfaces What is a functional interface? Can you create a custom one? Interface with a single abstract method (SAM). Use @FunctionalInterface annotation. Difference between Predicate, Function, and Consumer interfaces? Predicate<T> – returns boolean Function<T,R> – returns a result Consumer<T> – consumes and returns nothing How does lambda expression improve code readability or performance? Streams and Collections What is the difference between map() and flatMap() in Streams? map() transforms each element. flatMap() flattens nested streams. How does Stream API help in functional-style programming? Explain lazy evaluation in streams with examples. What are terminal v...
Read more

Java Input Output Streams

 What the actual functionalities of OOS and OIS? Performing serialization and deserialization What is Serialization? Serialization is the process of Converting an object into stream of bytes and sending them to underling outputstream. Using serialization we can store object state permanently in a destination EX: File or Remote Computer(RMI, Socket Programing). Serialization operation is performed by writeObeject(). What is Deserialization? Deserialization is the process of Converting stream of bytes into original object. Deserialization operation is performed by readObeject(). Rule on Serialization? Serializable is a marker interface.  only Serializable type objects are serializable. It provides special permition or identity to JVM to serializable object. If the given object isn't type serializable interfacethen writeObeject() will  throw an unchecked exception called NotSeriableException. What are the methods with serializable interface? No methods are there in serializa...
Read more

Java 8 Interview Programs

  Find the first non-repeating character in a String using Java 8 javabestinterviewquestion.blogspot.com import java.util.LinkedHashMap; import java.util.Map; import java.util.stream.Collectors; public class Main {     public static void main(String[] args) {         String input = "programming";         Character result = input.chars()                 .mapToObj(c -> (char) c)                 .collect(Collectors.groupingBy(c -> c, LinkedHashMap::new, Collectors.counting()))                 .entrySet().stream()                 .filter(e -> e.getValue() == 1)                 .map(Map.Entry::getKey)                 .findFirst()                 .orElse(...
Read more