Sql Interview Questions for 10 Years Experience


As an experienced SQL professional, you might encounter challenging questions during a job interview. In this blog post, we will explore 10 common SQL interview questions and provide in-depth explanations along with relevant examples. Let's dive in!

Note on dialects: the examples below use MySQL syntax by default. Where a feature behaves differently in PostgreSQL, SQL Server or Oracle, it is called out explicitly — interviewers often probe exactly these differences.

What is SQL Injection, and how can you prevent it?

SQL Injection is a malicious attack where an attacker manipulates input data to execute unintended SQL statements. It can lead to unauthorized access, data breaches, and data manipulation. The root cause is always the same: user input ends up being treated as SQL code instead of as data. To prevent it, follow these practices:

1.1. Use Prepared Statements (the real fix): Prepared statements send the SQL structure and the values to the database separately, so a value can never change the meaning of the query. Here's an example in Java with JDBC:

String sql = "SELECT * FROM users WHERE username = ?";

try (PreparedStatement ps = connection.prepareStatement(sql)) {
    ps.setString(1, username);
    ResultSet rs = ps.executeQuery();
    // process rs
}

Even if username contains ' OR '1'='1, it is bound as a literal string value and the query still looks for a user with that exact name. Nothing is interpreted as SQL.

1.2. Validate identifiers with an allow-list: Prepared statements can only parameterize values, not identifiers — you cannot bind a table name, a column name, or the ASC/DESC direction in an ORDER BY. When those must come from user input, check them against a fixed allow-list:

private static final Set<String> ALLOWED_SORT_COLUMNS =
        Set.of("name", "email", "created_at");

String sortColumn = ALLOWED_SORT_COLUMNS.contains(userInput) ? userInput : "name";
String sql = "SELECT * FROM users ORDER BY " + sortColumn;

A common misconception: stripping "dangerous" characters from input — something like userInput.replaceAll("[^a-zA-Z0-9]", "") — is not a defence against SQL injection. It breaks legitimate data (names such as O'Brien, email addresses, passwords), it is easy to bypass, and it gives a false sense of security. Parameterize the query instead; that is what actually closes the hole.

1.3. An ORM does not make you automatically safe: Spring Data JPA and Hibernate use bind parameters for derived queries, but a hand-built JPQL or native query with string concatenation is just as injectable as raw JDBC:

// Vulnerable — user input concatenated into JPQL
String jpql = "SELECT u FROM User u WHERE u.email = '" + email + "'";

// Safe — named parameter
String jpql = "SELECT u FROM User u WHERE u.email = :email";
em.createQuery(jpql, User.class)
  .setParameter("email", email)
  .getResultList();

1.4. Implement Least Privilege: Limit the privileges of the database user executing the SQL queries. An application account that only needs to read and write rows should not hold DROP or ALTER rights. This does not prevent injection, but it limits the blast radius of a successful attack.

How can you normalize the database?

Normalization is the process of organizing data in a database to eliminate redundancy and avoid update, insert and delete anomalies. There are several normal forms (NF), including:

2.1. First Normal Form (1NF): Every column must hold a single, atomic value. No multi-valued cells, and no repeating groups of columns. For example, consider a table storing customer orders:

Orders Table (violates 1NF — Products holds multiple values):
OrderID | CustomerName | Products
1       | John         | Laptop, Phone
2       | Sarah        | Laptop

To bring the table to 1NF, split each value into its own row:

Orders Table (1NF):
OrderID | CustomerName | Product
1       | John         | Laptop
1       | John         | Phone
2       | Sarah        | Laptop

A repeating group such as Product1 | Product2 | Product3 violates 1NF for the same reason — it forces a fixed limit on how many products an order can have, and every query has to check three columns instead of one.

2.2. Second Normal Form (2NF): Meet the requirements of 1NF and ensure that non-key attributes depend on the entire key. This only becomes an issue when the primary key is composite. For example, consider a table storing student grades where the key is (StudentID, CourseID):

Grades Table:
StudentID | CourseID | Grade | CourseName
1         | 101      | A     | Math
1         | 102      | B     | Science
2         | 101      | A     | Math

CourseName depends only on CourseID, not on the full key — that is a partial dependency. To normalize the table to 2NF, we create two tables:

Courses Table:
CourseID | CourseName
101      | Math
102      | Science

Grades Table:
StudentID | CourseID | Grade
1         | 101      | A
1         | 102      | B
2         | 101      | A

2.3. Third Normal Form (3NF): Meet the requirements of 2NF and ensure that no non-key attribute depends on another non-key attribute — that is, eliminate transitive dependencies. For example, consider a table storing employee information:

Employees Table (violates 3NF):
EmployeeID | EmployeeName | DeptID | DeptName
1          | John         | D1     | IT
2          | Sarah        | D2     | HR
3          | Ravi         | D1     | IT

Here DeptName depends on DeptID, and DeptID depends on EmployeeID. So DeptName depends on the key only transitively. The practical cost: renaming the IT department means updating every employee row, and if you delete the last employee in a department you lose the department entirely.

To normalize the table to 3NF, we create two tables:

Departments Table:
DeptID | DeptName
D1     | IT
D2     | HR

Employees Table:
EmployeeID | EmployeeName | DeptID
1          | John         | D1
2          | Sarah        | D2
3          | Ravi         | D1

Note that DeptID stays in the Employees table as a foreign key. Normalization splits tables, but it must never lose a relationship — if you drop the linking column, you have not normalized the data, you have destroyed it.

Explain the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

3.1. INNER JOIN: INNER JOIN returns only the matching rows between two tables based on the specified join condition. For example:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

3.2. LEFT JOIN: LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for the right table. For example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

3.3. RIGHT JOIN: RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for the left table. For example:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

3.4. FULL OUTER JOIN: A FULL OUTER JOIN returns all rows from both tables, filling in NULLs wherever a row on one side has no match on the other. The standard syntax is:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Important — MySQL does not support FULL OUTER JOIN. It is available in PostgreSQL, SQL Server and Oracle, but MySQL and SQLite will reject the query with a syntax error. This is a favourite interview question, and the expected answer is that you emulate it by taking the union of a LEFT JOIN and a RIGHT JOIN:

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID

UNION

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Use UNION rather than UNION ALL here — the rows that match on both sides appear in both halves, and UNION removes those duplicates for you.

What are indexes in SQL, and why are they important?

Indexes in SQL are database structures used to improve query performance. They provide quick access to data based on the values of specific columns. Here's why indexes are important:

4.1. Faster Query Execution: Indexes allow the database engine to locate and retrieve data more efficiently, resulting in faster query execution times.

4.2. Reduced Disk I/O: By using indexes, the database engine can minimize the amount of data it needs to read from disk, reducing disk I/O operations.

4.3. Data Integrity: Indexes can enforce data integrity constraints, such as unique values or primary key constraints, ensuring data consistency.

4.4. Sorting and Grouping: Indexes can speed up sorting and grouping operations by providing pre-sorted data or organized data blocks.

Indexes can be created using the CREATE INDEX statement. For example:

CREATE INDEX idx_customers_name ON Customers (CustomerName);

What is a self-join, and how does it work?

A self-join is a technique used to join a table with itself. It allows you to combine rows from the same table based on a related column. Here's an example:

SELECT e1.EmployeeName AS Employee, e2.EmployeeName AS Manager
FROM Employees e1
JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

In this example, we retrieve the names of employees and their corresponding managers by joining the Employees table with itself using the ManagerID column. Note that an INNER JOIN here drops any employee with no manager — the CEO, typically. Use a LEFT JOIN if you want to keep them.

What is the difference between UNION and UNION ALL?

6.1. UNION: UNION combines the result sets of two or more SELECT statements into a single result set, removing duplicate rows. For example:

SELECT CustomerName FROM Customers
UNION
SELECT CustomerName FROM Suppliers;

6.2. UNION ALL: UNION ALL also combines the result sets of two or more SELECT statements into a single result set but includes all rows, including duplicates. It is generally faster than UNION because it does not perform duplicate elimination. For example:

SELECT CustomerName FROM Customers
UNION ALL
SELECT CustomerName FROM Suppliers;

Explain ACID properties in the context of database transactions.

ACID (Atomicity, Consistency, Isolation, Durability) properties ensure the reliability and integrity of database transactions. Here's what each property entails:

7.1. Atomicity: Atomicity guarantees that a transaction is treated as a single unit of work. It either completes successfully or is rolled back entirely if any part fails. For example, transferring funds between bank accounts:

START TRANSACTION;
UPDATE Account1 SET Balance = Balance - 100 WHERE AccountID = 123;
UPDATE Account2 SET Balance = Balance + 100 WHERE AccountID = 456;
COMMIT;

If any update fails, the transaction will be rolled back, ensuring that both accounts remain consistent. (MySQL and PostgreSQL use START TRANSACTION; SQL Server uses BEGIN TRANSACTION.)

7.2. Consistency: Consistency ensures that a transaction brings the database from one consistent state to another. Any data modifications must adhere to defined integrity constraints and rules. For example, if a primary key constraint is violated, the transaction will be rolled back.

7.3. Isolation: Isolation ensures that concurrent transactions do not interfere with each other. Each transaction must be isolated from others until it is committed or rolled back. This prevents issues such as dirty reads, non-repeatable reads, and phantom reads.

7.4. Durability: Durability guarantees that once a transaction is committed, its changes are permanent and will survive any subsequent system failures. The changes are stored in non-volatile storage, such as disk drives, to ensure data durability.

How can you optimize SQL queries for better performance?

Optimizing SQL queries is crucial for improving database performance. Here are some techniques to achieve better query performance:

8.1. Use Indexes: Indexes improve query execution by providing quick access to data. Analyze query patterns and create indexes on frequently accessed columns.

8.2. Write Efficient Queries: Optimize query logic, eliminate unnecessary joins and subqueries, and minimize data retrieval to improve query performance.

8.3. Limit Result Sets: Use the LIMIT clause (TOP in SQL Server) to retrieve only the required number of rows from large result sets.

8.4. Normalize the Database: Normalize the database schema to eliminate redundancy and improve efficiency. Refer to the explanation provided in question 2 for a detailed example.

8.5. Update Statistics: Periodically update table and index statistics so the query optimizer has accurate information for query planning. In MySQL this is ANALYZE TABLE.

8.6. Consider Denormalization: In certain scenarios, denormalizing the database by introducing calculated columns or redundant data can improve query performance.

8.7. Use Stored Procedures: Stored procedures can reduce network traffic and improve performance by executing pre-compiled SQL code on the database server.

8.8. Partition Large Tables: Partitioning splits a large table into smaller physical pieces while keeping a single logical table. Queries that filter on the partition key can then skip entire partitions.

8.9. Monitor Query Performance: Use EXPLAIN and the slow query log to identify and optimize slow-performing queries.

Explain the difference between a clustered index and a non-clustered index.

9.1. Clustered Index: A clustered index determines the physical order in which rows are stored. Because rows can only be stored in one order, a table can have at most one clustered index.

How you get one depends on the database. In SQL Server you create it explicitly:

-- SQL Server syntax
CREATE CLUSTERED INDEX idx_employees_id ON Employees (EmployeeID);

MySQL has no CREATE CLUSTERED INDEX statement. In InnoDB the clustering is decided automatically, in this order of preference:

1. The PRIMARY KEY becomes the clustered index.
2. If there is no primary key, InnoDB uses the first
   UNIQUE index whose columns are all NOT NULL.
3. If neither exists, InnoDB generates a hidden
   6-byte row ID and clusters on that.

So in MySQL, choosing your primary key is choosing your clustered index.

9.2. Non-Clustered (Secondary) Index: A non-clustered index is a separate structure from the data rows, so a table can have many of them. It does not affect the physical order of the data. Non-clustered indexes are typically created on columns frequently used for searching or filtering:

CREATE INDEX idx_customers_name ON Customers (CustomerName);

In InnoDB, a secondary index stores the primary key value of the matching row rather than a direct pointer to it. Two consequences worth knowing for interviews:

Two lookups per read. Finding a row through a secondary index means traversing the secondary index to get the primary key, then traversing the clustered index to reach the actual row. A covering index — one that already contains every column the query needs — avoids the second traversal.

Primary key size multiplies. Every secondary index carries a copy of the primary key, so a wide primary key inflates all of them. This is the main reason an auto-increment BIGINT is usually preferred over a random UUID as a primary key in InnoDB — the UUID is both larger and inserts in random order, which fragments the clustered index.

How can you handle and prevent database deadlocks?

Deadlocks occur when two or more transactions wait for each other to release resources, so none of them can proceed. Here's how you can handle and prevent deadlocks:

10.1. Detection and Resolution: Database management systems detect deadlocks automatically and resolve them by choosing a victim transaction to roll back. Your application must be prepared to catch that error and retry the transaction — deadlocks cannot be fully prevented in a busy system, so retry logic is not optional.

10.2. Set Transaction Isolation Levels: A lower isolation level takes fewer and shorter locks, which reduces the window in which deadlocks can form. The trade-off is weaker consistency guarantees, so choose deliberately rather than by default.

10.3. Use Proper Indexing: Without a suitable index, an UPDATE or DELETE may scan and lock far more rows than it actually modifies. Good indexes narrow the locked range and cut deadlock risk substantially.

10.4. Limit Transaction Time: Break down long-running transactions into smaller units. Never hold a database transaction open across a network call or user interaction.

10.5. Access Resources in a Consistent Order: This is the single most effective preventive measure. If every transaction locks tables and rows in the same order, a circular wait cannot form. Most real-world deadlocks come from two code paths touching the same two tables in opposite order.

10.6. Inspect the Deadlock Details: In MySQL, SHOW ENGINE INNODB STATUS reports the most recent deadlock, including both transactions and the locks each was holding and waiting for. That output tells you exactly which two code paths collided.

By following these techniques, you can handle and prevent database deadlocks effectively.

Related categories:

Comments

Post a Comment

Popular Posts on Code Katha

Java Interview Questions for 10 Years Experience

Image
Welcome to the comprehensive guide on "Java Interview Questions for 10 Years Experience." Java has been one of the most popular programming languages for decades, and its widespread adoption means that the demand for skilled Java developers has never been higher. If you are someone who has been working with Java for the last decade, you have accumulated a wealth of experience and knowledge that you can showcase during interviews. Recommended  to read and brush up for your interview: Must know interview questions and concept for any Java Interview Spring boot interview Questions for 10 years experiance These simple and short articles help you to add AI to your resume and enhance the chance of selection: Java Spring Boot AI with Ollama Java Spring Boot AI with Open AI (Chat GPT) This blog post will provide you with a list of commonly asked interview questions for 10 years experienced Java developers. It will also include detailed answers to each question, with explanat...
Read more

Spring Boot Interview Questions for 10 Years Experience

Image
Welcome to the comprehensive guide on "Spring Boot Interview Questions for 10 Years Experience". Spring Boot has become one of the most popular frameworks for developing enterprise-grade applications, and its widespread adoption means that the demand for skilled Spring Boot developers has never been higher. If you are someone who has been working with Spring Boot for the last decade, you have accumulated a wealth of experience and knowledge that you can showcase during interviews. Recommended  to read and brush up for your interview: Must know interview questions and concept for any Java Interview Java Interview Questions for 10 Years Experience These simple and short articles help you to add AI to your resume and enhance the chance of selection: Spring Boot AI with Ollama Spring Boot AI with Open AI (Chat GPT) This blog post will provide you with a list of commonly asked interview questions for 10 years expe...
Read more

Java interview questions - Must to know concepts

Contents [ hide ] How does Java achieve platform independence, and what is the underlying principle? Java programs can run on diverse operating systems and hardware setups without requiring modification. This feat is primarily achieved through the concept of the Java Virtual Machine (JVM). The Java Virtual Machine (JVM) The JVM acts as a bridge between the Java code you write and the underlying hardware and operating system . When you compile your Java code, it is translated into an intermediate form called bytecode, which is not tied to any specific platform. The JVM then comes into play. It takes this bytecode and dynamically translates it into machine-specific instructions that the host system can execute. This means that a Java program can be written once and executed on any system with the appropriate JVM for that system. Example: Running a Java Program on Different Platforms Consider a simple Java program that calculates the sum of two numbers: package codeKatha...
Read more

Visual Studio Code setup for Java and Spring with GitHub Copilot

Image
Introduction- In this tutorial blog, we will explore the process of setting up and utilising Visual Studio Code for Java programming, as well as incorporating GitHub Copilot as a valuable coding tool. Visual Studio Code is a powerful and versatile code editor that offers a wide range of features and extensions for Java developers. By using Visual Studio Code for Java programming, developers can benefit from features such as code completion, debugging, and live coding. Additionally, with the integration of GitHub Copilot, developers can streamline their coding process with AI-assisted suggestions for code snippets and functions. This blog will provide a comprehensive guide for setting up and using these tools for Java development. Contents [ hide ] Setup Visual Studio Code for Java and Spring Visual Studio Code (VS Code) is an open-source code editor that supports a wide range of programming languages, including Java. It is a powerful tool...
Read more

Spring AI with Ollama

Image
🔄 Updated: September 2026  ·  Rewritten for Spring AI 2.0.1 Artifact IDs, properties and the response API have all changed since this guide was first published in 2024 Introduction to Spring AI with Ollama Welcome to a comprehensive guide on leveraging Spring AI with Ollama to build AI-powered applications in Java — running large language models entirely on your own machine, with no API key and no per-token cost. This tutorial covers installing Ollama, wiring it into a Spring Boot project, and building endpoints for chat, streaming, structured output, reasoning models and tool calling. Contents [ hide ] Setting Up Ollama Installing Ollama Download and install Ollama for your OS from the official download page . It runs as a background service on http://localhost:11434 — no Docker required. The source lives on the official GitHub page . Pulling a model Once Ollama is installed, pull a model. The original version of this guide used mistral , which still...
Read more

Data Structures & Algorithms Tutorial with Coding Interview Questions

Image
About the Course This course offers comprehensive coverage of coding questions that serve as a solid foundation for understanding DSA concepts. These questions are frequently asked in coding interviews, either directly or with some variations, by many companies, including Adobe, Facebook (Meta), Paytm, Amazon, Google, and others. Our primary objective is to enhance your problem-solving skills and enable you to devise optimal solutions. The course includes a wide range of topics, such as Arrays, Strings, Searching and Sorting, Greedy Algorithms, Trees, Graphs, Dynamic Programming, and more. Upon completion, you will possess the necessary skills to ace DSA interviews for typical companies or excel in the initial rounds of product-based companies such as Google, Microsoft and similar others. We are started with Graph data Structure and add others soon. Contents [ hide ] Approach Our course is  organised into chapters ...
Read more

Spring Data JPA

Image
Introduction- We will learn here Spring Data JPA, perform CRUD operation, Clear concept of Relationship mapping like one-to-many, many-to-one with Example. Spring Data JPA is a powerful framework that simplifies the process of working with relational databases in Java applications. It provides an easy-to-use API for working with JPA, allowing developers to focus on their business logic instead of worrying about the details of how JPA works. With Spring Data JPA, you can easily define entities that map to database tables, and then use them to perform CRUD (create, read, update, delete) operations on those tables. In addition, Spring Data JPA provides support for more advanced features like querying and pagination, making it an excellent choice for both small and large projects. Contents [ hide ] In this blog, we will explore Spring Data JPA by building a sample application that uses it to manage users and packs. But, before let...
Read more

Bit Manipulation and Bit Masking Concepts

Image
Introduction- A bit is the fundamental unit of information used in computing and digital communication.We generally worked on decimal numbers having values from 0,1,2.. to 9. Similarly binary system is characterized by two possible values, usually expressed as 0 or 1. In terms of electronic switch we can say 0 denotes an OFF switch and 1 denotes an ON switch. In terms of magnetic domain on a hard drive, we can say 0 denotes an unmagnetized domain, and 1 denotes a magnetized domain. Contents [ hide ] Uses of bit manipulation concepts in our coding Bit manipulation is a technique that can give programmers a significant performance advantage in certain algorithms. It can help to reduce the time complexity. By working directly with binary data using bitwise operators, we can perform operations faster than with traditional arithmetic operations. The reason for this is simple: computers understand binary data. When we perform any operation, it g...
Read more

Topological Sort in Graph

Welcome to our beginner's guide to Topological Sort in Graph Theory! Topological Sort is an essential algorithm used to order the vertices of a directed graph such that for every directed edge uv from vertex u to vertex v, u comes before v in the ordering. Let's explore this important concept together! Contents [ hide ] Understanding the Problem Statement Before delving into Topological Sort, let's understand the problem it addresses. Consider a scenario where tasks depend on one another; for example, in a project where certain tasks must be completed before others can begin. Such dependencies can be modeled using directed graphs, where nodes represent tasks and directed edges represent dependencies between tasks. The goal of Topological Sort is to find a linear ordering of the vertices of the graph such that for every directed edge uv, the vertex u comes before the vertex v in the ordering. This ordering provides a sequence in which the tasks can be perf...
Read more