MODULE 4 – JAVA INTERVIEW Q&A
(BEGINNER → ADVANCED)
🔹 BEGINNER LEVEL (Q1–Q15)
1. What is Java and why is it platform-independent?
Answer:
Java is an object-oriented, high-level programming language developed by Sun
Microsystems (now Oracle).
It is platform-independent because Java code is compiled into bytecode, which runs on the
Java Virtual Machine (JVM), allowing it to work on any OS that supports JVM.
Example:
Write once, run anywhere — the same .class file runs on Windows, macOS, or Linux.
Real-Time Application:
Used for enterprise software, Android apps, and backend web services due to its
portability.
2. What are the main features of Java?
Answer:
Platform Independent
Object-Oriented
Robust and Secure
Multithreaded
Distributed
Automatic Memory Management (Garbage Collection)
Example:
Garbage collector removes unused objects automatically.
Real-Time Application:
Ensures stable, scalable applications — ideal for banking and enterprise-level systems.
3. What is the difference between JDK, JRE, and JVM?
Answer:
Component Description
Includes tools to develop Java apps (compiler,
JDK (Java Development Kit)
debugger)
JRE (Java Runtime
Environment to run Java apps
Environment)
JVM (Java Virtual Machine) Executes compiled bytecode
Example:
JDK = Development + Runtime tools,
JRE = Runtime only.
Real-Time Application:
Developers use JDK; end-users need JRE to run applications.
4. What are data types in Java?
Answer:
Two main categories:
Primitive: int, float, double, char, boolean, etc.
Non-Primitive: String, Arrays, Classes, Objects
Example:
int age = 25;
String name = "Gajendra";
Real-Time Application:
Used in all Java programs to handle different types of data effectively.
5. What is Object-Oriented Programming (OOP) in Java?
Answer:
OOP organizes software as a collection of objects, each containing data (fields) and behavior
(methods).
OOP Principles:
1. Encapsulation
2. Inheritance
3. Polymorphism
4. Abstraction
Example:
A Car class inherits from Vehicle.
Real-Time Application:
Promotes code reuse and modular design in complex systems.
6. What is a Class and Object in Java?
Answer:
Class: Blueprint for objects.
Object: Instance of a class.
Example:
class Car {
void drive() { [Link]("Driving"); }
}
Car c = new Car();
[Link]();
Real-Time Application:
Classes and objects form the foundation of enterprise software in Java.
7. What is the difference between Constructor and Method?
Answer:
Feature Constructor Method
Purpose Initializes object Defines behavior
Return Type None Has return type
Called Automatically Manually
Example:
class Car {
Car() { [Link]("Car Created"); }
void drive() { [Link]("Driving"); }
}
Real-Time Application:
Used in object initialization and setting up default configurations.
8. What is Method Overloading?
Answer:
Method Overloading allows multiple methods with the same name but different
parameters in a class.
Example:
void add(int a, int b)
void add(double a, double b)
Real-Time Application:
Simplifies code readability and flexibility in utility and math libraries.
9. What is Method Overriding?
Answer:
Method Overriding occurs when a subclass provides a new implementation for a method in
its parent class.
Example:
class Animal { void sound() { [Link]("Generic"); } }
class Dog extends Animal { void sound() { [Link]("Bark"); } }
Real-Time Application:
Used in runtime polymorphism and framework design (e.g., Spring beans overriding
behavior).
10. What is Encapsulation?
Answer:
Encapsulation means binding data and methods into a single unit and restricting access
using access modifiers.
Example:
class Employee {
private int salary;
public void setSalary(int s) { salary = s; }
public int getSalary() { return salary; }
}
Real-Time Application:
Used to protect sensitive data and maintain security in applications.
11. What are Access Modifiers in Java?
Answer:
Modifier Scope
public Accessible everywhere
protected Accessible within package + subclasses
default Within same package
private Within same class
Example:
private int id; restricts direct access.
Real-Time Application:
Used in API design to enforce encapsulation and data hiding.
12. What is the difference between Static and Non-Static Members?
Answer:
Static: Belongs to class, shared by all objects.
Non-static: Unique to each instance.
Example:
static int count;
void increment() { count++; }
Real-Time Application:
Used for counters, utility methods, and common constants.
13. What is the difference between Stack and Heap Memory?
Answer:
Memory Type Used For Managed By
Stack Local variables JVM automatically
Heap Objects Garbage Collector
Example:
Object created using new is stored in heap.
Real-Time Application:
Understanding memory allocation helps prevent memory leaks.
14. What is Garbage Collection in Java?
Answer:
Java’s Garbage Collector (GC) automatically removes unused objects from memory to free
up space.
Example:
obj = null;
[Link]();
Real-Time Application:
Used in long-running applications to maintain performance.
15. What is the difference between equals() and == in Java?
Answer:
== → Compares references (memory addresses).
equals() → Compares values (content).
Example:
String a = new String("Hi");
String b = new String("Hi");
[Link](a == b); // false
[Link]([Link](b)); // true
Real-Time Application:
Used in data comparison, authentication, and collection lookups.
16. What is Exception Handling in Java?
Answer:
Exception Handling is a mechanism to handle runtime errors gracefully and maintain the
normal flow of the program.
It prevents abrupt termination of the application.
Keywords:
try, catch, finally, throw, throws
Example:
try {
int a = 10 / 0;
} catch (ArithmeticException e) {
[Link]("Cannot divide by zero!");
}
Real-Time Application:
Used in banking, billing, and web services to prevent crashes due to invalid user inputs or
failed API responses.
17. What is the difference between Checked and Unchecked Exceptions?
Answer:
Type Description Examples
Checked Checked at compile time IOException, SQLException
Unchecked Checked at runtime NullPointerException, ArithmeticException
Example:
try {
FileReader file = new FileReader("[Link]");
} catch (IOException e) { ... }
Real-Time Application:
Used in file handling, database operations, and network communications.
18. What is the use of the finally block?
Answer:
The finally block always executes, regardless of whether an exception is handled or not.
It is used for cleanup operations like closing files or database connections.
Example:
try {
File f = new File("[Link]");
} catch(Exception e) {
[Link]("Error");
} finally {
[Link]("Cleanup complete");
}
Real-Time Application:
Used for closing resources and releasing memory locks in production systems.
19. What is the difference between throw and throws?
Answer:
Keyword Description Usage
throw Used to explicitly throw an exception Inside method
throws Declares exceptions that a method can throw Method signature
Example:
void readFile() throws IOException {
throw new IOException("File not found");
}
Real-Time Application:
Used in custom exception design and framework-level error handling.
20. What is a Custom Exception in Java?
Answer:
You can create your own exception class by extending Exception or RuntimeException.
Example:
class InvalidAgeException extends Exception {
InvalidAgeException(String msg) { super(msg); }
}
Real-Time Application:
Used in business validation logic, e.g., "Invalid transaction amount."
21. What is the Java Collections Framework?
Answer:
Collections Framework provides ready-made classes and interfaces for storing and
manipulating data in groups.
Core Interfaces:
List
Set
Map
Queue
Example:
ArrayList<String> list = new ArrayList<>();
[Link]("Java");
Real-Time Application:
Used in inventory systems, APIs, and data-driven applications to handle dynamic data.
22. What is the difference between ArrayList and LinkedList?
Answer:
Feature ArrayList LinkedList
Storage Dynamic array Doubly linked list
Access speed Fast (index-based) Slower
Insertion/deletion Slower Faster
Example:
Use ArrayList for frequent access; use LinkedList for frequent insertions.
Real-Time Application:
Used in queue management systems and dynamic lists.
23. What is the difference between HashSet and TreeSet?
Answer:
Feature HashSet TreeSet
Order No order Sorted
Performance Faster Slower
Null values Allowed (1) Not allowed
Example:
HashSet<Integer> hs = new HashSet<>();
TreeSet<Integer> ts = new TreeSet<>();
Real-Time Application:
Used for unique collections — like storing unique usernames or IPs.
24. What is a HashMap in Java?
Answer:
A HashMap stores data in key-value pairs and allows fast lookup using hashing.
Example:
HashMap<Integer, String> map = new HashMap<>();
[Link](1, "Java");
[Link](2, "Python");
Real-Time Application:
Used in caching, configuration management, and data mapping.
25. What is the difference between HashMap and Hashtable?
Answer:
Feature HashMap Hashtable
Synchronization Non-synchronized Synchronized
Null Keys Allowed Not allowed
Speed Faster Slower
Example:
Prefer ConcurrentHashMap in multi-threaded applications.
Real-Time Application:
Used in thread-safe caching systems.
26. What is Iterator in Java?
Answer:
An Iterator provides a way to traverse elements in a collection sequentially.
Example:
Iterator<String> it = [Link]();
while([Link]()) [Link]([Link]());
Real-Time Application:
Used in data iteration and filtering pipelines.
27. What is Multithreading in Java?
Answer:
Multithreading allows concurrent execution of multiple threads to achieve multitasking.
Each thread runs independently but shares resources.
Example:
class Task extends Thread {
public void run() { [Link]("Thread Running"); }
}
new Task().start();
Real-Time Application:
Used in real-time systems, servers, and chat apps.
28. What are the advantages of Multithreading?
Answer:
Increases CPU utilization
Improves performance
Allows concurrent processing
Example:
Web server handling multiple requests simultaneously.
Real-Time Application:
Used in network servers and gaming engines.
29. What is Synchronization in Java?
Answer:
Synchronization ensures that only one thread accesses a shared resource at a time,
preventing data inconsistency.
Example:
synchronized void display() {
[Link]("Synchronized Method");
}
Real-Time Application:
Used in banking systems to ensure transaction safety.
30. What is Deadlock in Multithreading?
Answer:
A deadlock occurs when two threads wait for each other to release resources, causing both to
freeze.
Example:
Thread A locks X and waits for Y; Thread B locks Y and waits for X.
Real-Time Application:
Handled using timeouts and lock ordering in multi-threaded applications.
31. What is the difference between wait(), sleep(), and notify()?
Answer:
Method Description
wait() Releases lock and waits
sleep() Pauses thread, does not release lock
notify() Wakes up waiting threads
Example:
Used for thread communication in producer-consumer models.
32. What is File Handling in Java?
Answer:
Java provides the [Link] and [Link] packages for reading/writing files.
Example:
FileWriter fw = new FileWriter("[Link]");
[Link]("Hello Java");
[Link]();
Real-Time Application:
Used for logging, data persistence, and configuration storage.
33. What is Serialization in Java?
Answer:
Serialization converts an object into a byte stream to save it or send it over a network.
Serializable interface is used.
Example:
ObjectOutputStream out = new ObjectOutputStream(new
FileOutputStream("[Link]"));
[Link](obj);
Real-Time Application:
Used in network communication, caching, and API response transfer.
34. What is Deserialization?
Answer:
Deserialization reconstructs an object from its byte stream.
Example:
ObjectInputStream in = new ObjectInputStream(new
FileInputStream("[Link]"));
obj = (MyClass) [Link]();
Real-Time Application:
Used in data recovery, inter-service communication, and distributed systems.
35. What are Wrapper Classes in Java?
Answer:
Wrapper classes convert primitive data types into objects.
Example:
int x = 10;
Integer obj = [Link](x);
Real-Time Application:
Used in collections and frameworks that require objects instead of primitives.
36. What is JDBC in Java?
Answer:
JDBC (Java Database Connectivity) is an API that allows Java applications to interact with
relational databases using SQL commands.
It provides a standard way to connect, query, and update databases.
Steps:
1. Load the driver
2. Establish a connection
3. Create a statement
4. Execute query
5. Close connection
Example:
Connection con =
[Link]("jdbc:mysql://localhost:3306/db", "root",
"pass");
Statement stmt = [Link]();
ResultSet rs = [Link]("SELECT * FROM users");
Real-Time Application:
Used in enterprise applications for data persistence (e.g., banking systems, CRMs).
37. What is the difference between Statement and PreparedStatement in
JDBC?
Answer:
Type Description Performance
Statement Executes static SQL queries Slower
PreparedStatement Executes parameterized queries Faster & secure
Example:
PreparedStatement ps = [Link]("SELECT * FROM users WHERE id =
?");
[Link](1, 101);
Real-Time Application:
Used to prevent SQL Injection and optimize query performance.
38. What is ResultSet in JDBC?
Answer:
ResultSet is an object that holds data retrieved from a database after executing a query.
Example:
while([Link]()) {
[Link]([Link]("name"));
}
Real-Time Application:
Used to fetch and process data from relational databases.
39. What is the purpose of JDBC Driver?
Answer:
A JDBC driver is a software component that enables Java applications to communicate with
a specific database.
Types:
1. JDBC-ODBC Bridge
2. Native-API Driver
3. Network Protocol Driver
4. Thin Driver (Pure Java)
Example:
MySQL uses [Link].
Real-Time Application:
Used in database integration and backend application development.
40. What are Streams in Java?
Answer:
Streams were introduced in Java 8 to process data collections in a functional and declarative
way.
Example:
List<Integer> list = [Link](1, 2, 3, 4);
[Link]().filter(n -> n % 2 == 0).forEach([Link]::println);
Real-Time Application:
Used in data filtering, aggregation, and analytics modules.
41. What are Lambda Expressions in Java?
Answer:
Lambda Expressions provide a way to write anonymous functions — short blocks of code
that can be passed around as data.
Syntax:
(parameter) -> expression
Example:
Runnable r = () -> [Link]("Running thread");
Real-Time Application:
Used in event handling, thread creation, and collection filtering.
42. What are Functional Interfaces in Java?
Answer:
A Functional Interface has only one abstract method.
It enables the use of Lambda Expressions.
Example:
@FunctionalInterface
interface Calculator {
int add(int a, int b);
}
Real-Time Application:
Used in Java 8 Streams API, Lambda, and custom callback systems.
43. What is the difference between Collection and Stream API?
Answer:
Feature Collection Stream
Nature Stores elements Processes elements
Storage Yes No
Iteration External Internal
Example:
[Link]().map(x -> x*2).forEach([Link]::println);
Real-Time Application:
Used for data transformation, filtering, and parallel processing.
44. What are Generics in Java?
Answer:
Generics allow you to define classes, interfaces, and methods with type parameters.
It provides compile-time type safety and eliminates casting.
Example:
List<Integer> list = new ArrayList<>();
[Link](10);
Real-Time Application:
Used in Collections API and custom reusable components.
45. What is the difference between Comparable and Comparator?
Answer:
Interface Purpose Method
Comparable Defines natural order compareTo()
Comparator Defines custom order compare()
Example:
[Link](list, (a, b) -> [Link] - [Link]);
Real-Time Application:
Used in sorting user-defined objects, e.g., sorting customers by balance or name.
46. What is the difference between Abstract Class and Interface?
Answer:
Feature Abstract Class Interface
Methods Can have abstract & concrete Only abstract (till Java 7)
Multiple Inheritance Not allowed Allowed
Variables Can have non-final variables Only static & final
Example:
Use Abstract Class for base class hierarchy, Interface for contract behavior.
Real-Time Application:
Used in framework and API design (Spring Boot, JavaFX).
47. What are Design Patterns in Java?
Answer:
Design Patterns are reusable solutions to common software design problems.
Types:
Creational: Singleton, Factory
Structural: Adapter, Decorator
Behavioral: Observer, Strategy
Example (Singleton):
class Config {
private static Config instance = new Config();
private Config() {}
public static Config getInstance() { return instance; }
}
Real-Time Application:
Used in framework design and system architecture.
48. What is a Singleton Class?
Answer:
A Singleton ensures that only one instance of a class exists throughout the application
lifecycle.
Example:
class DBConnection {
private static DBConnection instance;
private DBConnection() {}
public static DBConnection getInstance() {
if (instance == null) instance = new DBConnection();
return instance;
}
}
Real-Time Application:
Used for database connections, logging, and configuration management.
49. What is Reflection in Java?
Answer:
Reflection allows Java programs to inspect or modify runtime behavior of classes, methods,
and fields.
Example:
Class cls = [Link]("[Link]");
Method[] methods = [Link]();
Real-Time Application:
Used in frameworks like Spring and Hibernate for dependency injection and object
creation.
50. What are Stream Collectors in Java 8?
Answer:
Collectors are used to accumulate elements from a stream into collections or aggregated
results.
Example:
List<String> names = [Link]()
.map(emp -> [Link]())
.collect([Link]());
Real-Time Application:
Used in report generation, analytics, and transformation of datasets.
🧠 Scenario-Based / Practical Java Questions
1. How would you design a class to connect to a database securely?
Use Singleton pattern for one shared connection.
Use environment variables for credentials.
Close resources in finally or use try-with-resources.
2. How would you prevent memory leaks in Java?
Remove unused object references.
Use WeakReference for caches.
Always close I/O streams and DB connections.
3. How would you handle concurrent access to shared data?
Use synchronized blocks.
Or use ReentrantLock and ConcurrentHashMap.
4. How would you implement logging in Java?
Use [Link] or Log4j/SLF4J.
Log errors, not passwords or confidential data.
5. How can you make your class immutable?
Declare class final.
Make fields private and final.
No setters.
Return copies of mutable objects.
6. How would you test exception handling?
Use JUnit with assertThrows() method.
Example:
assertThrows([Link], () -> readFile());
7. How can you implement a thread-safe Singleton?
Use double-checked locking pattern with volatile instance variable.
8. How can you serialize sensitive data securely?
Mark sensitive fields as transient.
Encrypt data before serialization.
9. How would you handle millions of data records efficiently?
Use Streams with parallel processing.
Use batch JDBC updates.
Avoid loading all records in memory.
10. How do you integrate Java applications with APIs?
Use HttpClient, RestTemplate, or Spring WebClient.
Parse JSON responses with libraries like Jackson.
MODULE 5 – SQL INTERVIEW Q&A
(BEGINNER → ADVANCED)
🔹 BEGINNER LEVEL (Q1–Q15)
1. What is SQL?
Answer:
SQL (Structured Query Language) is a standard language used to communicate with
relational databases.
It allows you to create, read, update, and delete (CRUD) data.
Example:
SELECT * FROM employees;
Real-Time Application:
Used in banking systems, HR management tools, and web applications for data
management.
2. What are the different types of SQL statements?
Answer:
1. DDL (Data Definition Language) – CREATE, ALTER, DROP
2. DML (Data Manipulation Language) – INSERT, UPDATE, DELETE
3. DCL (Data Control Language) – GRANT, REVOKE
4. TCL (Transaction Control Language) – COMMIT, ROLLBACK
5. DQL (Data Query Language) – SELECT
Example:
CREATE TABLE Students (ID INT, Name VARCHAR(50));
Real-Time Application:
Used in every stage of database development and maintenance.
3. What is a Primary Key?
Answer:
A Primary Key uniquely identifies each record in a table.
It cannot have NULL or duplicate values.
Example:
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(50)
);
Real-Time Application:
Used in customer databases to uniquely identify users or transactions.
4. What is a Foreign Key?
Answer:
A Foreign Key creates a link between two tables, maintaining referential integrity.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
EmpID INT,
FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);
Real-Time Application:
Used to maintain relationships between tables like orders and customers.
5. What is a Candidate Key?
Answer:
All columns that can uniquely identify a record are candidate keys; one of them becomes the
primary key.
Example:
For table Student(RollNo, Email), both could uniquely identify a record.
Real-Time Application:
Used in database design to ensure redundancy-free key selection.
6. What is a Composite Key?
Answer:
A Composite Key is made up of two or more columns that uniquely identify a record.
Example:
PRIMARY KEY (StudentID, CourseID)
Real-Time Application:
Used in many-to-many relationships like students enrolled in multiple courses.
7. What is a Unique Key?
Answer:
A Unique Key ensures all values in a column are unique but allows one NULL value.
Example:
CREATE TABLE User (Email VARCHAR(50) UNIQUE);
Real-Time Application:
Used for unique identification like email addresses or phone numbers.
8. What is a NULL value in SQL?
Answer:
NULL represents missing or undefined data — not zero or empty string.
Example:
SELECT * FROM Employees WHERE ManagerID IS NULL;
Real-Time Application:
Used in data validation and identifying incomplete records.
9. What are Constraints in SQL?
Answer:
Constraints enforce rules on data to ensure accuracy and reliability.
Types:
PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
NOT NULL
DEFAULT
Example:
CREATE TABLE Products (
Price INT CHECK (Price > 0)
);
Real-Time Application:
Used to maintain data consistency and security.
10. What is the difference between DELETE, TRUNCATE, and DROP?
Answer:
Command Action Rollback
DELETE Removes rows Yes
TRUNCATE Removes all rows (faster) No
DROP Removes table structure No
Example:
DELETE FROM students WHERE id = 1;
Real-Time Application:
Used in data cleanup, table resets, and database migrations.
11. What is the difference between WHERE and HAVING?
Answer:
WHERE filters rows before aggregation.
HAVING filters groups after aggregation.
Example:
SELECT Dept, COUNT(*) FROM Employee
GROUP BY Dept
HAVING COUNT(*) > 5;
Real-Time Application:
Used in report generation to filter group results.
12. What is the ORDER BY clause?
Answer:
Used to sort query results in ascending or descending order.
Example:
SELECT * FROM Employee ORDER BY Salary DESC;
Real-Time Application:
Used in leaderboards, reports, or dashboards.
13. What is the difference between CHAR and VARCHAR?
Answer:
Type Description
CHAR(n) Fixed-length string
VARCHAR(n) Variable-length string
Example:
CHAR(10) always uses 10 bytes; VARCHAR(10) uses only as needed.
Real-Time Application:
Used in memory optimization for string storage.
14. What is a View in SQL?
Answer:
A View is a virtual table based on the result of a query.
Example:
CREATE VIEW EmpSalary AS SELECT Name, Salary FROM Employee;
Real-Time Application:
Used for security and simplified access to complex queries.
15. What is Normalization?
Answer:
Normalization is the process of organizing data to eliminate redundancy and improve
integrity.
Normal Forms:
1NF → Atomic values
2NF → No partial dependency
3NF → No transitive dependency
Real-Time Application:
Used in database design to make systems efficient and consistent.
🔸 INTERMEDIATE LEVEL (Q16–Q35)
16. What is a Join in SQL?
Answer:
A JOIN combines rows from two or more tables based on related columns.
Types:
INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN
Example:
SELECT [Link], [Link]
FROM Employee e
INNER JOIN Department d ON [Link] = [Link];
Real-Time Application:
Used to fetch integrated data from multiple tables (e.g., employee + department).
17. What is the difference between INNER JOIN and LEFT JOIN?
Answer:
Type Description
INNER JOIN Returns matching rows only
LEFT JOIN Returns all rows from left table + matched ones from right
Example:
SELECT * FROM Employee e LEFT JOIN Project p ON [Link] = [Link];
Real-Time Application:
Used in reporting incomplete data like employees without assigned projects.
18. What is a Self Join?
Answer:
A Self Join joins a table with itself to compare related data.
Example:
SELECT [Link] AS Employee, [Link] AS Manager
FROM Employee A, Employee B
WHERE [Link] = [Link];
Real-Time Application:
Used to find reporting hierarchies.
19. What is a Subquery?
Answer:
A Subquery is a query nested inside another query.
Example:
SELECT Name FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);
Real-Time Application:
Used in comparisons, filtering, and analytics.
20. What are Aggregate Functions in SQL?
Answer:
Functions that perform calculations on sets of values and return a single result.
Examples: SUM(), AVG(), COUNT(), MAX(), MIN()
Example:
SELECT AVG(Salary) FROM Employee;
Real-Time Application:
Used in reporting dashboards and business analytics.
21. What is a Stored Procedure?
Answer:
A Stored Procedure is a precompiled SQL statement stored in the database.
It increases performance and security.
Example:
CREATE PROCEDURE GetAllEmployees AS SELECT * FROM Employee;
Real-Time Application:
Used in banking, billing, and ERP systems for reusable logic.
22. What is a Trigger in SQL?
Answer:
A Trigger automatically executes a command in response to an event (INSERT, UPDATE,
DELETE).
Example:
CREATE TRIGGER Audit
AFTER INSERT ON Employee
FOR EACH ROW
INSERT INTO AuditLog VALUES ([Link], NOW());
Real-Time Application:
Used for audit logs, monitoring, and automatic rule enforcement.
23. What is the difference between UNION and UNION ALL?
Answer:
Command Description
UNION Removes duplicates
UNION ALL Keeps duplicates
Example:
SELECT Name FROM A
UNION
SELECT Name FROM B;
Real-Time Application:
Used in data merging and result consolidation.
24. What are Transactions in SQL?
Answer:
A Transaction is a group of operations executed as a single unit of work.
It follows ACID properties:
Atomicity
Consistency
Isolation
Durability
Example:
BEGIN;
UPDATE Account SET Balance = Balance - 500 WHERE ID = 1;
UPDATE Account SET Balance = Balance + 500 WHERE ID = 2;
COMMIT;
Real-Time Application:
Used in bank transfers and e-commerce orders.
25. What is an Index in SQL?
Answer:
An Index speeds up data retrieval from a table, similar to an index in a book.
Example:
CREATE INDEX idx_name ON Employee(Name);
Real-Time Application:
Used in search optimization and query performance.
26. What is the difference between Clustered and Non-Clustered Index?
Answer:
Type Description
Clustered Alters table structure; only one per table
Non-Clustered Creates a separate structure
Example:
Clustered Index → Primary Key
Non-Clustered → On columns like Name
Real-Time Application:
Used for faster query lookup and data sorting.
27. What is the difference between DELETE and TRUNCATE in terms of
transaction?
Answer:
DELETE is transaction-safe, TRUNCATE is DDL and cannot be rolled back in some
databases.
Example:
DELETE FROM users; -- Rollback possible
TRUNCATE TABLE users; -- Not always
Real-Time Application:
Used carefully in production database cleanups.
28. What is Denormalization?
Answer:
Denormalization is the process of adding redundancy for faster data retrieval.
Example:
Storing customer city inside the Order table to avoid frequent joins.
Real-Time Application:
Used in data warehousing and OLAP systems.
29. What is the difference between SQL and NoSQL?
Answer:
Type SQL NoSQL
Data Model Relational Document, Key-Value, Graph
Schema Fixed Flexible
Example MySQL MongoDB
Real-Time Application:
SQL → Banking; NoSQL → Big Data & IoT.
30. What is a Cursor in SQL?
Answer:
A Cursor allows row-by-row processing of query results.
It’s slower but useful for complex data manipulation.
Example:
DECLARE cursor_name CURSOR FOR SELECT * FROM Employee;
Real-Time Application:
Used in procedural operations inside stored procedures.
MODULE 5 – SQL (ADVANCED LEVEL:
Q31–Q50)
31. What is a Temporary Table in SQL?
Answer:
A Temporary Table stores data temporarily during a session or transaction. It’s
automatically dropped when the session ends.
Example:
CREATE TEMPORARY TABLE TempOrders AS
SELECT * FROM Orders WHERE OrderDate > '2025-01-01';
Real-Time Application:
Used in report generation or ETL processes where intermediate calculations are needed.
32. What are Common Table Expressions (CTE)?
Answer:
A CTE is a temporary named result set used within a single SQL statement.
Example:
WITH Sales_CTE AS (
SELECT EmpID, SUM(Sales) AS TotalSales FROM Sales GROUP BY EmpID
)
SELECT * FROM Sales_CTE WHERE TotalSales > 10000;
Real-Time Application:
Used in recursive queries, hierarchical data, and complex reports.
33. What is a Recursive Query in SQL?
Answer:
A Recursive Query repeatedly executes itself until it reaches a condition.
Example:
WITH RECURSIVE EmpCTE AS (
SELECT EmpID, ManagerID FROM Employee WHERE ManagerID IS NULL
UNION ALL
SELECT [Link], [Link] FROM Employee e
INNER JOIN EmpCTE c ON [Link] = [Link]
)
SELECT * FROM EmpCTE;
Real-Time Application:
Used for organization hierarchies, tree structures, and parent-child relationships.
34. What is the difference between BETWEEN and IN operators?
Answer:
Operator Usage Example
BETWEEN Range comparison BETWEEN 100 AND 200
IN Specific values IN (100, 200, 300)
Example:
SELECT * FROM Product WHERE Price BETWEEN 100 AND 500;
Real-Time Application:
Used in filtering queries for price ranges, date ranges, or specific IDs.
35. What is the difference between EXISTS and IN?
Answer:
EXISTS: Checks if subquery returns any result (optimized for large data).
IN: Compares a value with a list of values (better for small sets).
Example:
SELECT Name FROM Employee e
WHERE EXISTS (SELECT 1 FROM Department d WHERE [Link] = [Link]);
Real-Time Application:
Used in data validation and relationship checks in enterprise databases.
36. What is a Pivot Table in SQL?
Answer:
Pivot converts row data into columns for summarization and analysis.
Example:
SELECT Dept,
SUM(CASE WHEN Gender='M' THEN 1 ELSE 0 END) AS MaleCount,
SUM(CASE WHEN Gender='F' THEN 1 ELSE 0 END) AS FemaleCount
FROM Employee GROUP BY Dept;
Real-Time Application:
Used in business intelligence, analytics, and dashboards.
37. What is the difference between a Function and a Stored Procedure?
Answer:
Feature Function Stored Procedure
Return Type Must return value Optional
Used In SELECT, WHERE Executed independently
Transaction Cannot commit/rollback Can handle transactions
Example:
CREATE FUNCTION GetSalary(@id INT) RETURNS INT AS
BEGIN RETURN (SELECT Salary FROM Employee WHERE EmpID = @id) END;
Real-Time Application:
Used for reusable calculations and data transformations.
38. What is a Trigger vs Procedure difference?
Answer:
Feature Trigger Procedure
Execution Automatic Manual
Event Data change (INSERT/UPDATE) Called by user
Purpose Auditing/Enforcing Rules Business logic
Example:
Trigger runs automatically after data insertion.
Real-Time Application:
Used in audit logging and enforcing consistency.
39. What is an Execution Plan in SQL?
Answer:
An Execution Plan shows how SQL Server/MySQL/Oracle executes a query. It helps
optimize performance.
Example:
EXPLAIN SELECT * FROM Orders WHERE CustomerID = 5;
Real-Time Application:
Used in query tuning and database performance optimization.
40. What are the different types of relationships in a database?
Answer:
1. One-to-One
2. One-to-Many
3. Many-to-Many
Example:
One employee → one ID card
One department → many employees
Real-Time Application:
Used in data modeling and ER diagrams.
41. What is an ACID property in SQL?
Answer:
ACID ensures reliability in transactions:
A – Atomicity: All or none
C – Consistency: Data must be valid
I – Isolation: Transactions independent
D – Durability: Changes survive system failure
Example:
Bank money transfer ensures both debit and credit succeed or both fail.
Real-Time Application:
Used in financial systems and e-commerce databases.
42. What is SQL Injection and how can it be prevented?
Answer:
SQL Injection is a code injection attack that manipulates queries via user inputs.
Example of attack:
SELECT * FROM users WHERE username = '' OR '1'='1';
Prevention:
Use PreparedStatements
Validate inputs
Limit DB permissions
Real-Time Application:
Used in web security and API input validation.
43. What is a Deadlock in SQL?
Answer:
A Deadlock occurs when two transactions hold locks that the other needs, and neither can
proceed.
Example:
Transaction A locks Table X, waits for Table Y;
Transaction B locks Table Y, waits for Table X.
Real-Time Application:
Resolved using lock timeouts, ordering, and retry logic.
44. What is Query Optimization?
Answer:
Query Optimization improves SQL performance by minimizing execution time.
Techniques:
Use Indexes
Avoid SELECT *
Analyze Execution Plan
Use Joins instead of Subqueries
Example:
SELECT name FROM Employee WHERE id = 5; -- Indexed column
Real-Time Application:
Used by database administrators for tuning large systems.
45. What are Window Functions in SQL?
Answer:
Window Functions perform calculations across sets of rows related to the current row.
Example:
SELECT EmpID, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM
Employee;
Real-Time Application:
Used in ranking systems, analytics, and reporting.
46. What is the difference between RANK(), DENSE_RANK(), and
ROW_NUMBER()?
Answer:
Function Description
RANK() Skips ranks for ties
DENSE_RANK() No gaps in ranking
ROW_NUMBER() Unique sequence number
Example:
Used for leaderboards and reporting dashboards.
47. What is a Materialized View?
Answer:
A Materialized View stores the result of a query physically, unlike normal views which are
virtual.
Example:
CREATE MATERIALIZED VIEW SalesReport AS SELECT * FROM Orders WHERE
Status='Complete';
Real-Time Application:
Used in data warehousing for faster analytics.
48. What is Referential Integrity?
Answer:
It ensures relationships between tables remain valid — foreign key values must exist in the
parent table.
Example:
Cannot insert Order if CustomerID doesn’t exist in Customer table.
Real-Time Application:
Used in relational data consistency and validation.
49. What is the difference between OLTP and OLAP?
Answer:
System Description Example
OLTP Online Transaction Processing Banking System
OLAP Online Analytical Processing Business Analytics
Real-Time Application:
OLTP → real-time transactions,
OLAP → data analytics & forecasting.
50. What is Database Sharding?
Answer:
Sharding divides a large database into smaller, faster, manageable pieces called shards.
Example:
Splitting customer data by region (Asia DB, Europe DB).
Real-Time Application:
Used in scalable distributed systems like Amazon and Netflix.
⚙️ 10 Scenario-Based SQL Interview Questions
1. How would you find the second highest salary from an Employee table?
SELECT MAX(Salary) FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM
Employee);
Concept: Uses nested subquery to find rank-based salary.
Real-Time Application: Payroll or HR analytics.
2. How would you find duplicate records in a table?
SELECT Name, COUNT(*) FROM Employee GROUP BY Name HAVING COUNT(*) > 1;
Application: Data cleanup and validation.
3. How would you delete duplicate rows while keeping one copy?
DELETE FROM Employee
WHERE EmpID NOT IN (
SELECT MIN(EmpID) FROM Employee GROUP BY Name
);
Application: Data quality management.
4. How would you find employees who do not belong to any department?
SELECT Name FROM Employee
WHERE DeptID NOT IN (SELECT DeptID FROM Department);
Application: Identify incomplete data for integrity checks.
5. How would you find the top 3 earning employees in each department?
SELECT * FROM (
SELECT Name, DeptID, RANK() OVER (PARTITION BY DeptID ORDER BY Salary
DESC) AS Rank
FROM Employee
) AS Ranked WHERE Rank <= 3;
Application: Performance and incentive calculations.
6. How would you count total employees hired each year?
SELECT YEAR(HireDate) AS Year, COUNT(*) AS Total
FROM Employee GROUP BY YEAR(HireDate);
Application: HR recruitment analytics.
7. How would you display departments with no employees?
SELECT [Link] FROM Department d
LEFT JOIN Employee e ON [Link] = [Link]
WHERE [Link] IS NULL;
Application: Organizational reporting.
8. How would you retrieve employees earning above the department average?
SELECT Name FROM Employee e
WHERE Salary > (SELECT AVG(Salary) FROM Employee WHERE DeptID = [Link]);
Application: Performance analysis and salary optimization.
9. How would you calculate cumulative salary per department?
SELECT DeptID, Name, Salary,
SUM(Salary) OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS
CumulativeSalary
FROM Employee;
Application: Departmental budget tracking.
10. How would you find customers who have placed more than 5 orders?
SELECT CustomerID, COUNT(*) AS Orders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 5;
Application: Identifying loyal customers for marketing or rewards.