How Would You Write an SQL Query to Fetch Duplicate Records from a Table?

Duplicate records are rows where one or more column values appear multiple times in a table. Identifying duplicates is a common requirement in database management because duplicate data can lead to:

• inconsistent reporting
• incorrect analytics
• storage inefficiency
• data integrity issues

The most common approach for finding duplicates is using:

• GROUP BY
• HAVING
• COUNT()

These SQL clauses help group records and identify repeated values.

Example Query

Suppose we have an Employees table:

employee_id | name | email

To find duplicate email addresses:

SELECT email, COUNT(*) AS duplicate_count
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

How This Query Works

GROUP BY

Groups rows based on the email column.

COUNT(*)

Counts how many times each email appears.

HAVING COUNT(*) > 1

Filters only duplicated values.

Finding Complete Duplicate Rows

SELECT name, email, COUNT(*)
FROM employees
GROUP BY name, email
HAVING COUNT(*) > 1;

Fetching Full Duplicate Records

To retrieve all duplicate rows:

SELECT *
FROM employees
WHERE email IN (
    SELECT email
    FROM employees
    GROUP BY email
    HAVING COUNT(*) > 1
);

Real-World Example

In an e-commerce platform:

• multiple customer accounts may accidentally share the same email

Duplicate detection helps maintain:

• clean customer data
• accurate reporting
• proper authentication behavior

Handling duplicates is an important part of database optimization and data quality management.

What is a Prepared Statement, and Why Would You Use One?

A Prepared Statement is a precompiled SQL statement that allows applications to execute SQL queries securely and efficiently using parameterized inputs. Prepared statements are widely used in Java applications through JDBC because they improve:

• security
• performance
• maintainability

Unlike normal SQL queries that concatenate user input directly into query strings, prepared statements separate SQL logic from input values.

Example Without Prepared Statement

String query = "SELECT * FROM users WHERE username='" + username + "'";

This approach is dangerous because it is vulnerable to SQL injection attacks.

Example Using Prepared Statement

String query = "SELECT * FROM users WHERE username = ?";
PreparedStatement ps = connection.prepareStatement(query);
ps.setString(1, username);

How Prepared Statements Work

1. SQL query is precompiled
2. Placeholders (?) represent parameters
3. Input values are bound separately
4. Database executes query securely

Advantages of Prepared Statements

Prevent SQL Injection

User input is treated as data rather than executable SQL.

Better Performance

Frequently executed queries can reuse execution plans.

Improved Readability

Code becomes cleaner and easier to maintain.

Efficient Parameter Handling

Supports multiple data types safely.

Reusability

Same query structure can execute with different values repeatedly.

Real-World Example

Login query:

SELECT * FROM users WHERE email = ? AND password = ?

Prepared statements protect applications from malicious input such as:

' OR '1'='1

Importance in Enterprise Applications

Prepared statements are considered a best practice in:

• Spring Boot applications
• JDBC applications
• Hibernate frameworks
• REST APIs

because secure database interaction is critical in production systems.

What is the N+1 Query Problem and How Can You Solve It?

The N+1 Query Problem is a common performance issue in database-driven applications, especially when using ORM frameworks like Hibernate or JPA. It occurs when an application executes one query to fetch parent records and then executes additional queries for each related child record individually.

This results in excessive database calls and severe performance degradation.

How N+1 Problem Occurs

Suppose we have:

• Users
• Orders

One query fetches all users:

SELECT * FROM users;

Then for each user:

SELECT * FROM orders WHERE user_id = ?;

If there are 100 users:

• 1 query fetches users
• 100 additional queries fetch orders

Total:

• 101 queries

This is called the N+1 problem.

Why N+1 is Dangerous

Increased Database Load

Too many queries overload the database.

Higher Latency

Applications become slower.

Poor Scalability

Performance degrades significantly with large datasets.

Excessive Network Overhead

Many small queries increase communication costs.

How to Solve the N+1 Query Problem

Use JOIN FETCH

In Hibernate/JPA:

SELECT u FROM User u JOIN FETCH u.orders

This fetches users and orders together in a single query.

Use Eager Loading Carefully

Load related data upfront when necessary.

Use Batch Fetching

Fetch related entities in batches instead of individually.

DTO Projections

Retrieve only required fields.

Optimize ORM Relationships

Avoid unnecessary lazy loading.

Use Pagination

Limit large result sets.

Real-World Example

In an e-commerce application:

• Order Service fetches customer orders
• each order fetches products separately

Without optimization:

• hundreds of queries execute

With JOIN FETCH:

• data loads efficiently using fewer queries

Tools for Detecting N+1 Problems

• Hibernate SQL logs
• Spring Boot Actuator
• APM tools
• Query analyzers

The N+1 Query Problem is one of the most common ORM performance issues in enterprise Java applications, and solving it is essential for building scalable database-driven systems.

Explain the Function of GROUP BY and HAVING Clauses in SQL

GROUP BY and HAVING are SQL clauses used for grouping and filtering aggregated data. They are commonly used in reporting, analytics, and business intelligence queries.

GROUP BY groups rows that have the same values in specified columns, while HAVING filters grouped results after aggregation.

GROUP BY Clause

GROUP BY organizes rows into groups based on column values.

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department;

Result:

• employees are grouped by department
• COUNT() calculates total employees per department

Common Aggregate Functions

• COUNT()
• SUM()
• AVG()
• MAX()
• MIN()

HAVING Clause

HAVING filters grouped data after aggregation.

Example:

SELECT department, COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

This query returns only departments having more than 5 employees.

Difference Between WHERE and HAVING

WHERE

Filters rows before grouping.

HAVING

Filters groups after aggregation.

Example:

SELECT department, AVG(salary)
FROM employees
WHERE salary > 30000
GROUP BY department
HAVING AVG(salary) > 50000;

How This Query Works

1. WHERE filters employees with salary > 30000
2. GROUP BY groups employees by department
3. AVG() calculates department averages
4. HAVING filters departments with average salary > 50000

Real-World Example

In an e-commerce platform:

• GROUP BY category calculates total sales per category
• HAVING filters categories with sales above certain thresholds

Importance of GROUP BY and HAVING

These clauses are essential for:

• reporting
• analytics
• dashboards
• business intelligence
• data aggregation

Efficient use of grouping and aggregation is very important in enterprise database systems handling large datasets.

What are Indexes and How Do They Work in Databases?

Indexes are database objects used to improve the speed of data retrieval operations. They work similarly to indexes in books, allowing databases to locate records quickly without scanning entire tables.

Without indexes, databases often perform full table scans, which become very slow for large datasets.

Indexes significantly improve query performance for:

• SELECT statements
• JOIN operations
• WHERE conditions
• ORDER BY clauses

How Indexes Work

Indexes store references to table data in a sorted structure such as:

• B-Tree
• Hash index

When queries execute:

• database searches index first
• matching row locations are identified quickly

Example

Suppose Employees table contains millions of rows.

Without index:

SELECT * FROM employees WHERE email='abc@gmail.com';

Database scans entire table.

With index:

CREATE INDEX idx_email ON employees(email);

Database locates records much faster.

Types of Indexes

Primary Index

Automatically created for primary keys.

Unique Index

Prevents duplicate values.

Composite Index

Built on multiple columns.

Clustered Index

Determines physical row storage order.

Non-Clustered Index

Stores separate lookup structure.

Advantages of Indexes

Faster Query Performance

Improves retrieval speed dramatically.

Faster Sorting

Optimizes ORDER BY operations.

Better Join Performance

Improves relational query execution.

Disadvantages of Indexes

Increased Storage

Indexes consume additional disk space.

Slower INSERT/UPDATE/DELETE

Indexes require maintenance during data modification.

Over-Indexing

Too many indexes may reduce overall performance.

Real-World Example

In a banking application:

• account_number index enables fast customer lookup

Without indexes:

• queries become slow with millions of records

Indexes are critical for database optimization because enterprise systems often handle massive datasets requiring high-performance querying.

What Impact Do JOIN Operations Have on Database Performance?

JOIN operations combine data from multiple tables based on relationships between columns. Since relational databases normalize data into separate tables, joins are essential for retrieving related information.

However, joins can significantly impact database performance depending on:

• table size
• indexing
• query complexity
• join type

How JOINs Affect Performance

Increased CPU Usage

Database must compare matching rows across tables.

Memory Consumption

Large joins may require temporary memory allocation.

Disk I/O Overhead

Poorly optimized joins may trigger full table scans.

Network Overhead

Complex joins may generate large result sets.

Types of JOINs

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

INNER JOIN is generally faster because it processes matching rows only.

Performance Factors

Indexing

Indexes on join columns improve performance significantly.

Table Size

Joining large tables increases processing cost.

Query Complexity

Multiple joins may increase execution time.

Join Order

Database optimizer determines efficient execution plans.

Filtering Conditions

WHERE clauses reduce unnecessary data processing.

Optimization Strategies

Create Indexes on Join Columns

Example:

CREATE INDEX idx_department_id ON employees(department_id);

Select Only Required Columns

Avoid:

SELECT *

Use Proper Join Types

Use INNER JOIN when possible.

Analyze Execution Plans

Use:

EXPLAIN

Reduce Large Dataset Scans

Use pagination and filtering.

Real-World Example

In an e-commerce platform:

• Orders join with Customers
• Products join with Inventory

Poor joins may slow:

• dashboards
• reporting
• API responses

Proper indexing and optimized joins are critical for maintaining high database performance in enterprise systems.

Define What a Subquery is and Provide a Use Case for It

A subquery is a query written inside another SQL query. It is also known as an inner query or nested query. The outer query uses the result returned by the subquery to perform further operations.

Subqueries are commonly used to:

• filter data
• compare values
• retrieve intermediate results
• simplify complex queries

A subquery can appear inside:

• SELECT clause
• WHERE clause
• FROM clause
• HAVING clause

How a Subquery Works

The inner query executes first, and its result is passed to the outer query.

Example

Suppose we want to find employees earning more than the average salary.

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

How This Query Works

Inner Query

SELECT AVG(salary) FROM employees

Calculates average salary.

Outer Query

Retrieves employees whose salary exceeds the average.

Types of Subqueries

Single-Row Subquery

Returns one row.

Multiple-Row Subquery

Returns multiple rows.

Correlated Subquery

Depends on outer query values.

Nested Subquery

Contains another subquery inside it.

Advantages of Subqueries

Simplifies Complex Queries

Breaks large queries into manageable parts.

Improves Readability

Makes business logic easier to understand.

Dynamic Filtering

Allows comparisons with calculated values.

Better Data Analysis

Useful for reporting and aggregation.

Real-World Example

In an e-commerce application:

• find customers who placed orders above average order amount

SELECT customer_name
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

Subqueries are extremely useful in enterprise SQL development because many business scenarios require multi-step data retrieval and filtering logic.

What is a Correlated Subquery?

A correlated subquery is a type of subquery that depends on values from the outer query. Unlike normal subqueries that execute independently, correlated subqueries execute once for every row processed by the outer query.

This makes correlated subqueries more dynamic but often more expensive in terms of performance.

How Correlated Subqueries Work

1. Outer query processes one row
2. Inner query executes using outer row values
3. Process repeats for every outer row

Example

Suppose we want employees earning more than the average salary of their department.

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e1.department_id = e2.department_id
);

How This Query Works

Outer Query

Processes employees one by one.

Inner Query

Calculates department-wise average salary dynamically for each employee.

Correlation

e1.department_id = e2.department_id

links inner query with outer query.

Characteristics of Correlated Subqueries

Dependent Execution

Inner query depends on outer query values.

Repeated Execution

Subquery executes multiple times.

More Flexible Logic

Supports advanced filtering conditions.

Higher Processing Cost

Can become slower for large datasets.

Performance Considerations

Correlated subqueries may cause:

• high CPU usage
• repeated scans
• slower execution

Optimization techniques:

• indexing
• replacing with JOINs
• query restructuring

Real-World Example

In a banking application:

• identify transactions above average transaction amount for each customer

Correlated subqueries are useful when calculations depend dynamically on outer row values.

Difference Between Subquery and Correlated Subquery

Normal Subquery

• executes once independently

Correlated Subquery

• executes repeatedly per outer row

Correlated subqueries are powerful but should be used carefully because they may affect performance significantly in large enterprise databases.

Describe How You Would Optimize a Slow SQL Query

Optimizing slow SQL queries is one of the most important responsibilities in database performance tuning. Poorly optimized queries can cause:

• high CPU usage
• excessive memory consumption
• slow application response
• database bottlenecks

Query optimization involves analyzing execution behavior and improving how data is retrieved.

Steps to Optimize Slow SQL Queries

Analyze Query Execution Plan

Use:

EXPLAIN

to understand:

• table scans
• joins
• indexes
• execution order

Create Proper Indexes

Indexes improve query performance dramatically.

Example:

CREATE INDEX idx_email ON users(email);

Avoid SELECT *

Retrieve only required columns.

Instead of:

SELECT * FROM employees;

Use:

SELECT employee_id, name FROM employees;

Optimize JOIN Operations

Ensure join columns are indexed properly.

Reduce Nested Subqueries

Replace expensive subqueries with JOINs when appropriate.

Use Pagination

Avoid loading huge result sets at once.

Example:

LIMIT 10 OFFSET 0

Avoid Functions on Indexed Columns

Functions may prevent index usage.

Normalize or Denormalize Appropriately

Balance consistency and performance.

Optimize Database Schema

Use proper:

• data types
• constraints
• relationships

Cache Frequently Accessed Data

Redis reduces repeated database load.

Real-World Example

Suppose order retrieval becomes slow:

• analyze execution plan
• identify missing indexes
• optimize joins
• reduce unnecessary columns

Performance improves significantly.

Common Causes of Slow Queries

• missing indexes
• full table scans
• excessive joins
• large datasets
• poor query design
• unoptimized schema

Query optimization is critical in enterprise applications because database performance directly impacts scalability and user experience.

Explain the EXPLAIN Statement and How You Use it in Query Optimization

The EXPLAIN statement is a SQL command used to analyze how a database executes a query. It provides detailed information about the query execution plan, helping developers identify performance bottlenecks and optimize slow queries.

EXPLAIN is one of the most important tools in database performance tuning because it reveals:

• table scans
• index usage
• join operations
• sorting behavior
• execution order

How EXPLAIN Works

When EXPLAIN is placed before a query:

EXPLAIN SELECT * FROM employees WHERE email='abc@gmail.com';

the database shows how it plans to execute the query instead of actually running it.

Important Information in EXPLAIN Output

Table Access Type

Indicates how tables are accessed.

Examples:

• ALL (full table scan)
• index
• range
• ref
• const

Possible Keys

Indexes that could be used.

Key

Actual index chosen by optimizer.

Rows

Estimated number of rows scanned.

Extra Information

Additional operations such as:

• Using where
• Using filesort
• Using temporary

How EXPLAIN Helps in Optimization

Detect Full Table Scans

Large scans often indicate missing indexes.

Verify Index Usage

Confirms whether indexes are utilized correctly.

Optimize JOINs

Identifies inefficient join operations.

Reduce Query Cost

Helps minimize unnecessary row processing.

Improve Filtering

Analyzes WHERE clause efficiency.

Real-World Example

Suppose query performance is poor:

SELECT * FROM orders WHERE customer_id = 100;

Running:

EXPLAIN SELECT * FROM orders WHERE customer_id = 100;

may reveal:

• full table scan

Solution:

CREATE INDEX idx_customer_id ON orders(customer_id);

Benefits of EXPLAIN

• better query tuning
• improved performance
• reduced database load
• faster troubleshooting
• optimized indexing

Database administrators and backend developers use EXPLAIN regularly because query performance is critical for scalable enterprise applications.