Interview Questions on Data Base Prepared by Srikanth Mamillapalli

1. How to increase the performance or tune the queries

Understand the Query

Use EXPLAIN / EXPLAIN ANALYZE

Use the database’s query plan tool to see how the query is executed.

Optimize the SELECT Clause

Add or Improve Indexes

Create indexes on columns used in WHERE, JOIN, GROUP BY, or ORDER BY

Rewrite the Query

Use Proper Joins

Make sure joins use indexed keys and are selective:

Limit Result Set

Consider Materialized Views or Caching

Database-Specific Tools

Each DB has tools:

What is index and what are the types of index

An index in a database is a data structure that improves the speed of data retrieval operations on a table at the cost of additional storage and write time

2.1 Why Use Indexes?

2.2 Types of Indexes in PostgreSQL

2.3 Interview Question

What is the cursor and refcursor in pl/sql

In databases, cursors and refcursors are tools used in procedural SQL (like PL/pgSQL in PostgreSQL or PL/SQL in Oracle) to work with query results row-by-row, especially inside stored procedures or functions.

Cursor :

A cursor is a database object used to retrieve a set of rows returned by a query and process them one at a time. It's useful when you need to perform operations on each row individually.

How it works:

DECLARE

my_cursor CURSOR FOR SELECT id, name FROM users;

user_record RECORD;

BEGIN

OPEN my_cursor;

LOOP

FETCH my_cursor INTO user_record;

EXIT WHEN NOT FOUND;

-- Do something with user_record

END LOOP;

CLOSE my_cursor;

END;

A refcursor is a cursor variable—a pointer or reference to a result set. It's more dynamic and flexible than a regular cursor and is often used when you want to return a result set from a function or procedure.

Why use refcursors?

CREATE OR REPLACE FUNCTION get_users()

RETURNS refcursor AS $$

DECLARE

ref refcursor;

BEGIN

OPEN ref FOR SELECT * FROM users;

RETURN ref;

END;

$$ LANGUAGE plpgsql;

Triggers in database:

A trigger is a special kind of stored procedure that automatically runs (is "triggered") in response to certain events on a table or view.

When Do Triggers Fire?

Triggers can be fired:

Use Cases for Triggers

IN,OUT,IN_OUT parameters in database stored procedure

IN Parameter

Input only: Passes a value into the procedure.

Value is read-only inside the procedure.

Default type if nothing is specified.

Cannot be modified within the procedure (any changes won't reflect outside).

OUT Parameter

Used to return a value from the procedure to the caller.

Cannot be used to pass input into the procedure.

Acts like a return variable.

INOUT Parameter

Works both ways:

Passes a value into the procedure.

Allows the procedure to modify it and return the updated value.

Raise an exception in stored procedure

RAISE EXCEPTION 'Balance cannot be negative';

PostgreSQL lets you use RAISE NOTICE, RAISE WARNING, or RAISE EXCEPTION

BEGIN

-- normal code

EXCEPTION

WHEN <exception_name> THEN

-- handle the exception

WHEN OTHERS THEN

-- handle any other exception

END;

Different types of joins

execute a stored procedure from java/springboot/jpa

To execute a stored procedure from Java/Spring Boot/JPA, you have multiple ways to achieve this depending on the JPA provider (e.g., Hibernate) and the type of procedure (i.e., IN, OUT, or INOUT parameters).

Different queries

Emp b on a.manager_id=b.manager_id;

CTE common table expressions

A CTE (Common Table Expression) in databases is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It's defined using the WITH keyword and is often used to improve readability and structure of complex queries, especially those involving recursion or subqueries.

SELECT ...

)

SELECT * FROM cte_name;

SELECT ...

),

cte2 AS (

SELECT ...

)

SELECT * FROM cte2;

Benefits of Using CTEs

SnowFlake and Databrics

Snowflake: Snowflake is a cloud-based data warehouse platform. It provides a Software-as-a-Service (SaaS) solution that allows companies to store, process, and analyze large volumes of structured and semi-structured data.

Key Features:

Use Cases:

Databricks

Databricks is a data lakehouse platform that combines the best features of data lakes and data warehouses. It’s built on top of Apache Spark and supports advanced data engineering, data science, and machine learning workflows.

Use Cases:

JPA Repository and CURD Repository

public interface PagingAndSortingRepository<T, ID> extends CrudRepository<T, ID>

@NoRepositoryBean Annotation to exclude repository interfaces from being picked up and thus in consequence getting an instance being created.

public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {

CrudRepository: Provides basic CRUD (Create, Read, Update, Delete) operations

Use CrudRepository when you only need simple CRUD functionality.

JpaRepository:

JpaRepository → PagingAndSortingRepository → CrudRepository

Pagination logic

TypedQuery<Campaign> typedQuery = entityManager.createQuery(query);

final int pageSize = (searchDto.getPageSize() > 0 ? searchDto.getPageSize() : Integer.MAX_VALUE);

final int pageNumber = Math.max(searchDto.getPageNumber(), 1);

typedQuery.setFirstResult((pageNumber - 1) * pageSize);

typedQuery.setMaxResults(pageSize);

countQuery.select(criteriaBuilder.count(fromCount));

Long totalElements = entityManager.createQuery(countQuery).getSingleResult();

final List<Campaign> list = typedQuery.getResultList();

final List<CampaignSearchOutDto> result = new ArrayList<>();

if (list != null) {

for (Campaign campaign : list) {

result.add(buildDto(campaign));

}

}

int availablePages = totalElements.intValue() / pageSize + (totalElements.intValue() % pageSize != 0 ? 1 : 0);

Integer nextPage = pageNumber + 1 == availablePages ? null : pageNumber + 1;

Integer nextItems = nextPage == null ? 0 : totalElements.intValue() - (pageSize * (pageNumber + 1));

Integer previousPage = availablePages == 1 ? null : pageNumber - 1;

Integer previousItems = previousPage == null ? 0 : pageSize * pageNumber;

logSpentTime("searchCampaignsByPaginationFilterV2", startTime);

LOGGER.debug(LOG2_, result, searchDto);

return new PaginatedOutDto<CampaignSearchOutDto>(totalElements.intValue(), availablePages, nextPage, previousPage, nextItems, previousItems, availablePages - 1,

result);

Transaction

A transaction is a sequence of one or more operations that must all be completed successfully; otherwise, none of them should be applied. It follows the ACID principles:

Where to Maintain Transactions?

Best Practice: Service Layer in Spring Boot

Why at the service layer?

Backed by: Relational Database (like MySQL/PostgreSQL)

Propagation (Spring Transaction Propagation Behavior)

Propagation defines how transaction boundaries behave when a method is called within an existing transaction.

Isolation Levels (Database Transaction Isolation)

Isolation controls how visible the changes made in one transaction are to others, affecting data consistency and concurrency.

Common Mistakes to Avoid

What is @Transactional in Spring?

Maintain transactions in the service layer using @Transactional backed by a relational database. This is accurate, scalable, and aligns with both Spring's architecture and database ACID guarantees.

@Transactional is a Spring annotation used to manage transaction boundaries declaratively. It ensures that the logic inside a method runs within a database transaction, and can be committed or rolled back based on success or failure.

What are the default properties of @Transactional?

Defaults:

What happens if an exception is caught and not rethrown in a @Transactional method?

Spring only rolls back on uncaught runtime exceptions. If you catch and swallow the exception, the transaction won’t roll back.

To manually trigger rollback:

TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();

What happens when one @Transactional method calls another in the same class?

It bypasses the proxy — transaction will not apply. Solution: Move the method to another bean or use AspectJ weaving instead of proxy-based AOP.

What is the role of readOnly=true?

@Transactional(readOnly = true)

Used for read-only operations, it:

How is transaction managed in Spring Boot JPA?

How does @Transactional work internally?

Spring uses AOP proxies (JDK or CGLIB) to intercept method calls and apply transaction behavior before and after the method execution.

Rollback example

@Transactional(rollbackFor = { CustomBusinessException.class, IOException.class })

What is transaction propagation?

This behavior is controlled by the propagation setting inside @Transactional.By default, Propagation.REQUIRED is used — meaning if a transaction exists, it will join it, else it creates a new one.