DBMS Fresher Interview Questions and Answers 2026

Last updated: Jan 16, 2026

Author :

ExamAdda Team

1. What is a Database Management System (DBMS)?

A DBMS is software that allows users to create, manage, retrieve, and manipulate data in a database efficiently.

Key Points:

Acts as an interface between users/applications and the database
Ensures data consistency, security, and integrity
Supports multiple users accessing data simultaneously

Examples:

MySQL
Oracle Database
MongoDB

2. What is a Database?

A database is an organized collection of data stored electronically for easy access, management, and updating.

Key Characteristics:

Structured (tables, rows, columns)
Persistent storage
Easily retrievable

Example:

A student database may include:

Name
Roll number
Marks
Address

3. Difference Between Database and DBMS

Feature	Database	DBMS
Definition	Collection of data	Software to manage data
Function	Stores data	Manages and manipulates data
Interaction	Cannot interact directly	Provides interface
Example	Student records	MySQL, Oracle

Simple Analogy:

Database = Library books
DBMS = Librarian

4. Advantages of Using a DBMS

1. Data Independence

Changes in structure don’t affect applications

2. Data Security

Role-based access control

3. Data Integrity

Ensures accuracy using constraints

4. Reduced Data Redundancy

Avoids duplicate data

5. Backup & Recovery

Protects data from failures

6. Multi-user Access

Supports concurrent users

5. Disadvantages of DBMS

1. High Cost

Licensing, hardware, maintenance

2. Complexity

Requires skilled professionals

3. Performance Overhead

Extra layer may slow simple operations

4. Large Size

Requires significant storage

5. Single Point of Failure

DBMS failure can affect entire system

6. Main Functions of a DBMS

1. Data Definition

Defines schema using DDL

2. Data Manipulation

Insert, update, delete (DML)

3. Data Security

Authentication & authorization

4. Data Integrity

Enforces rules (constraints)

5. Transaction Management

Ensures ACID properties

6. Concurrency Control

Handles multiple users safely

7. Backup & Recovery

Restores data after failure

7. Different Types of DBMS

1. Hierarchical DBMS

Tree-like structure
Example: IBM IMS

2. Network DBMS

Graph structure (many-to-many relationships)

3. Relational DBMS (RDBMS)

Data stored in tables

4. Object-Oriented DBMS

Stores objects instead of tables

5. NoSQL DBMS

Flexible schema (JSON, key-value, etc.)
Example: MongoDB

8. What is RDBMS?

An RDBMS (Relational Database Management System) stores data in tables (relations) consisting of rows and columns.

Key Features:

Uses Structured Query Language (SQL)
Supports relationships via keys
Ensures ACID properties

Examples:

MySQL
PostgreSQL
Oracle Database

9. Difference Between DBMS and RDBMS

Feature	DBMS	RDBMS
Structure	File-based / simple	Table-based
Relationships	Limited	Strong (via keys)
Normalization	Not supported	Supported
Security	Less	More
Users	Single-user	Multi-user
Examples	Basic DBMS	MySQL, PostgreSQL

Key Insight:

RDBMS is a type of DBMS, but more advanced

10. Different Database Models

1. Hierarchical Model

Parent-child relationship
One-to-many

2. Network Model

Many-to-many relationships
Flexible than hierarchical

3. Relational Model

Tables (most widely used)
Based on mathematical relations

4. Object-Oriented Model

Stores data as objects (like OOP)

5. Entity-Relationship (ER) Model

Conceptual design model
Uses entities, attributes, relationships

6. NoSQL Model

Types include:

Key-Value
Document (e.g., MongoDB)
Column-family
Graph databases

11. What is a Table in DBMS?

A table is the fundamental structure in a database where data is stored in rows and columns.

Key Characteristics:

Represents a real-world entity (e.g., Student, Employee)
Contains fields (columns) and records (rows)
Each table has a unique name

Example:

ID	Name	Age
1	John	20
2	Sam	22

Think of a table as a spreadsheet-like structure

12. What is a Relation in DBMS?

A relation is a mathematical term used in relational databases that corresponds to a table.

Key Points:

Relation = Table (in RDBMS terminology)
Based on relational model (set theory)
Contains tuples (rows) and attributes (columns)

Important Insight:

Table → practical term
Relation → theoretical term

13. What are Rows and Columns in a Table?

Rows (Tuples):

Represent a single record
Example: One student’s data

Columns (Attributes):

Represent a property of data
Example: Name, Age, ID

Example:

ID (Column)	Name (Column)	Age (Column)
1 (Row)	John	20

14. What is a Database Schema?

A schema is the blueprint or structure of a database.

Defines:

Tables
Columns and data types
Relationships
Constraints

Example:

Schema = Design of database (not actual data)

15. What is an Instance of a Database?

A database instance is the actual data stored in the database at a specific moment.

Key Points:

Changes frequently
Snapshot of database at a given time

Example:

Schema = Structure of Student table
Instance = Current records inside the table

16. What is Data Redundancy?

Data redundancy means duplicate copies of the same data stored in multiple places.

Example:

Same customer data stored in multiple tables

Problems:

Wasted storage
Inconsistency
Increased maintenance

17. How does DBMS Reduce Data Redundancy?

1. Normalization

Organizes data into multiple related tables

2. Use of Relationships

Foreign keys avoid duplication

3. Centralized Control

Single source of truth

Example:

Instead of repeating customer info:

Customer Table
Orders Table (with customer_id)

Reduces duplication significantly

18. What is Data Independence?

Data independence is the ability to change database structure without affecting applications.

Types:

1. Logical Data Independence

Change logical structure without affecting programs
Example: Adding a column

2. Physical Data Independence

Change storage without affecting schema
Example: Indexing, file organization

19. Different Levels of Data Abstraction

DBMS uses 3 levels of abstraction:

1. Physical Level (Lowest)

How data is stored physically
Files, indexes

2. Logical Level

What data is stored
Tables, relationships

3. View Level (Highest)

What users see
Different views for different users

Diagram (conceptual):


View Level (User View)
       ↑
Logical Level (Schema)
       ↑
Physical Level (Storage)

20. What is an ER Diagram?

An ER (Entity-Relationship) Diagram is a visual representation of database design.

Components:

1. Entity

Real-world object (Student, Employee)

2. Attributes

Properties (Name, Age)

3. Relationships

Connections between entities

Symbols:

Rectangle → Entity
Oval → Attribute
Diamond → Relationship

21. What is an Entity?

An entity is a real-world object or concept that can be uniquely identified and stored in a database.

Key Characteristics

Has independent existence
Can be physical (e.g., Student, Car) or abstract (e.g., Course, Account)
Contains attributes that describe it

Example

Student entity → represents a student
Attributes: ID, Name, Age

Types

Strong Entity: Has its own primary key
Weak Entity: Depends on another entity (no primary key)

22. What is an Attribute?

An attribute is a property or characteristic that describes an entity.

Types of Attributes

Simple Attribute – Cannot be divided (e.g., Age)
Composite Attribute – Can be divided (e.g., Address → Street, City)
Derived Attribute – Calculated (e.g., Age from DOB)
Multivalued Attribute – Multiple values (e.g., Phone Numbers)

Example

Student:

Name, Age, Email → attributes

23. What is an Entity Set?

An entity set is a collection of similar entities.

Key Points

Represents a table in DBMS
Each row = entity instance
Each column = attribute

Example

Student Entity Set:

ID	Name	Age
1	John	20
2	Ravi	22

24. What is a Relationship in DBMS?

A relationship defines how two or more entities are associated with each other.

Purpose

Shows interaction between entities
Helps in designing database structure

Example

Student enrolls in Course
→ "enrolls" is the relationship

25. What are the Different Types of Relationships?

1. One-to-One (1:1)

One entity relates to only one entity
Example: Person ↔ Passport

2. One-to-Many (1:N)

One entity relates to multiple entities
Example: Teacher → Students

3. Many-to-Many (M:N)

Multiple entities relate to multiple entities
Example: Students ↔ Courses

4. Recursive Relationship

Entity relates to itself
Example: Employee manages Employee

26. What is a Primary Key?

A primary key is an attribute (or set of attributes) that uniquely identifies each record in a table.

Properties

Must be unique
Cannot be NULL
Only one primary key per table

Example

Student Table:

ID → Primary Key

27. What is a Foreign Key?

A foreign key is an attribute in one table that refers to the primary key of another table.

Purpose

Maintains referential integrity
Establishes relationship between tables

Example

Student table → Course_ID (Foreign Key)
Course table → Course_ID (Primary Key)

28. What is a Candidate Key?

A candidate key is any attribute (or set of attributes) that can uniquely identify a record.

Key Points

A table can have multiple candidate keys
One is selected as primary key

Example

Student:

ID, Email → both can uniquely identify → candidate keys

29. What is a Super Key?

A super key is a set of one or more attributes that uniquely identifies a record.

Important Insight

May contain extra attributes
All candidate keys are super keys, but not vice versa

Example

{ID}
{ID, Name} → still unique → super key

30. What is a Composite Key?

A composite key is a primary key made up of two or more attributes.

When Used

When a single attribute cannot uniquely identify a record

Example

Enrollment Table:

Student_ID + Course_ID → Composite Key

31. What is a Surrogate Key?

A surrogate key is an artificially generated unique identifier for a table, not derived from application data.

Key Characteristics

System-generated (e.g., AUTO_INCREMENT, UUID)
Has no business meaning
Always unique and stable

Why Use It?

Avoids complexity of natural keys
Prevents issues when business data changes
Improves performance in joins

Example

ID → surrogate key

32. What is a Unique Key?

A unique key ensures that all values in a column (or set of columns) are distinct across rows.

Key Properties

Ensures uniqueness
Can allow NULL values (DBMS-dependent, often one NULL allowed)
A table can have multiple unique keys

Difference from Primary Key

Feature	Primary Key	Unique Key
NULL allowed	❌ No	✅ Yes
Count	One	Multiple

Example

Email in a user table

33. What are Database Constraints?

Constraints are rules enforced on data in a database to maintain accuracy, consistency, and integrity.

Purpose

Prevent invalid data entry
Enforce business rules
Maintain data reliability

Example

Age must be > 0
Email must be unique

34. What are the Different Types of Constraints in DBMS?

1. NOT NULL

Column cannot have NULL values

2. UNIQUE

Ensures all values are distinct

3. PRIMARY KEY

Unique + NOT NULL

4. FOREIGN KEY

Maintains relationship between tables

5. CHECK

Enforces a condition

6. DEFAULT

Assigns default value if none provided

35. What is Normalization?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

Goal

Eliminate duplicate data
Ensure logical data storage

Normal Forms

1NF – Atomic values (no repeating groups)
2NF – No partial dependency
3NF – No transitive dependency
BCNF – Advanced version of 3NF

36. Why is Normalization Important?

Key Benefits

1. Reduces Data Redundancy

Avoid duplicate storage

2. Improves Data Integrity

Consistent and accurate data

3. Avoids Anomalies

Insert anomaly
Update anomaly
Delete anomaly

4. Efficient Storage

Saves memory and improves structure

37. What is Denormalization?

Denormalization is the process of combining tables to improve read performance.

Key Idea

Introduce controlled redundancy

Why Use It?

Faster queries (fewer joins)
Useful in analytics and reporting

Trade-offs

Advantage	Disadvantage
Faster reads	Data redundancy
Simpler queries	Risk of inconsistency

38. What is SQL?

SQL (Structured Query Language) is a standard language used to interact with relational databases.

Uses

Create and manage databases
Query data
Insert, update, delete records

Example

39. What are the Different Types of SQL Commands?

1. DDL (Data Definition Language)

Defines database structure
Commands: CREATE, ALTER, DROP

2. DML (Data Manipulation Language)

Works with data
Commands: INSERT, UPDATE, DELETE

3. DQL (Data Query Language)

Fetch data
Command: SELECT

4. DCL (Data Control Language)

Controls access
Commands: GRANT, REVOKE

5. TCL (Transaction Control Language)

Manages transactions
Commands: COMMIT, ROLLBACK, SAVEPOINT

40. What is DDL (Data Definition Language)?

DDL is a subset of SQL used to define and modify the structure of database objects.

Key Commands

1. CREATE

Create tables/databases

2. ALTER

Modify existing structure

3. DROP

Delete table/database

4. TRUNCATE

Remove all records quickly

41. What is DML (Data Manipulation Language)?

DML is a subset of SQL used to manipulate data stored in database tables.

Key Operations

Insert new data
Update existing data
Delete data

Common Commands

INSERT – Add records
UPDATE – Modify records
DELETE – Remove records

Example

Key Insight

Works on data, not structure (unlike DDL)

42. What is DCL (Data Control Language)?

DCL is used to control access and permissions in a database.

Purpose

Ensure security
Define who can access or modify data

Commands

GRANT – Give permissions
REVOKE – Remove permissions

Example

Use Case

Restrict sensitive data access (e.g., salary info)

43. What is TCL (Transaction Control Language)?

TCL is used to manage transactions in a database.

What is a Transaction?

A transaction is a group of operations executed as a single unit.

Commands

COMMIT – Save changes
ROLLBACK – Undo changes
SAVEPOINT – Set rollback point

Example

Key Insight

Ensures data consistency in case of failure

44. What is a View?

A view is a virtual table based on the result of a SQL query.

Key Characteristics

Does not store data physically
Stores only the query
Always reflects latest data

Example

Cases

Simplify complex queries
Restrict data access
Improve security

45. Difference Between View and Table

Feature	Table	View
Storage	Physical	Virtual
Data	Stored	Derived from query
Update	Direct	Limited
Performance	Faster (direct access)	Depends on query

Key Insight

Table = actual data
View = logical representation

46. What is Indexing?

Indexing is a technique used to speed up data retrieval in a database.

Concept

Similar to a book index
Avoids full table scan

Why Important?

Improves query performance
Reduces search time

Trade-off

Uses extra storage
Slows down write operations

47. What is an Index?

An index is a data structure that improves the speed of data retrieval.

Types of Indexes

Primary Index – Based on primary key
Secondary Index – On non-key attributes
Clustered Index – Data stored in sorted order
Non-Clustered Index – Separate structure

Example

48. What is Data Integrity?

Data integrity ensures that data is accurate, consistent, and reliable over time.

Types

Entity Integrity – Primary key must be unique and not null
Referential Integrity – Foreign key validity
Domain Integrity – Valid data types and ranges

Example

Age cannot be negative
ID must be unique

49. What is Referential Integrity?

Referential integrity ensures that relationships between tables remain consistent.

Key Rule

Foreign key must match a primary key or be NULL

Example

Cannot insert a student with a non-existing Course_ID

Enforced By

Foreign key constraints

50. What is ACID in DBMS?

ACID represents properties that ensure reliable and consistent transactions.

A – Atomicity

Transaction is all or nothing
If one operation fails → entire transaction fails

C – Consistency

Database remains in a valid state before and after transaction

I – Isolation

Transactions execute independently
No interference between concurrent transactions

D – Durability

Once committed → data is permanently saved
Survives system failure

Example

Bank Transfer:

Deduct from A
Add to B
If failure occurs → rollback

51. Are NULL values same as zero or blank?

NULL represents the absence of a value (unknown or missing), not zero or empty.

Key Differences

Value Type	Meaning
NULL	No value / unknown
0	Numeric value
'' (blank)	Empty string

Important Points

NULL ≠ 0
NULL ≠ ''
Cannot use = to compare NULL → use IS NULL

Example

52. What is DELETE command?

DELETE removes specific rows from a table.

Key Features

Can use WHERE clause
Row-by-row deletion
Can be rolled back (transaction-safe)

Example

53. What is TRUNCATE command?

TRUNCATE removes all rows from a table quickly.

Key Features

No WHERE clause
Faster than DELETE
Cannot be rolled back (in most DBMS)

Example

54. What is DROP command?

DROP removes the entire table structure and data from the database.

Key Features

Deletes table permanently
Frees memory
Cannot be recovered easily

Example

55. Difference: DELETE vs TRUNCATE vs DROP

Feature	DELETE	TRUNCATE	DROP
Type	DML	DDL	DDL
Removes	Selected rows	All rows	Entire table
WHERE clause	✅ Yes	❌ No	❌ No
Rollback	✅ Yes	❌ Usually No	❌ No
Speed	Slow	Fast	Very Fast

Key Insight

DELETE → selective
TRUNCATE → fast clean
DROP → complete removal

56. What is a JOIN in SQL?

Definition

A JOIN combines rows from two or more tables based on a related column.

Purpose

Retrieve data from multiple tables
Establish relationships

Example

57. What are the Different Types of JOINs?

1. INNER JOIN

Returns matching rows only

2. LEFT JOIN (LEFT OUTER JOIN)

All rows from left table + matches

3. RIGHT JOIN (RIGHT OUTER JOIN)

All rows from right table + matches

4. FULL JOIN

All rows from both tables

5. CROSS JOIN

Cartesian product (all combinations)

58. What is INNER JOIN?

Returns only rows that have matching values in both tables.

Example

Use Case

When only related data is required

59. What is OUTER JOIN?

Definition

Returns matched + unmatched rows from one or both tables.

Types

LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN

Example (LEFT JOIN)

Key Insight

Includes NULLs for missing matches

60. What is GROUP BY Clause?

GROUP BY groups rows with the same values into summary rows.

Used With

Aggregate functions

Example

61. What is HAVING Clause?

HAVING filters grouped data after GROUP BY.

Difference from WHERE

WHERE	HAVING
Before grouping	After grouping

Example

62. What are Aggregate Functions?

Functions that perform calculations on multiple rows and return a single value.

Common Functions

COUNT() – number of rows
SUM() – total value
AVG() – average
MIN() – smallest value
MAX() – largest value

Example

63. What is a Subquery?

A subquery is a query nested inside another SQL query.

Types

Single-row subquery
Multi-row subquery
Correlated subquery

Example

64. What is a Database Cursor?

A cursor is a database object used to retrieve and manipulate data row by row.

Why Use It?

When row-by-row processing is required

Types

Implicit cursor
Explicit cursor

Use Case

Complex logic not possible with normal queries

65. What is a Data Dictionary?

A data dictionary is a metadata repository that stores information about database structure.

Contains

Table names
Column names
Data types
Constraints

Purpose

Helps understand database design
Used by DBMS internally

66. What is a Database Administrator (DBA)?

A DBA (Database Administrator) is responsible for managing and maintaining databases.

Key Responsibilities

1. Database Design

Schema creation and optimization

2. Security Management

User access control

3. Backup & Recovery

Prevent data loss

4. Performance Tuning

Optimize queries and indexing

5. Monitoring

Ensure uptime and reliability