DBMS Interview Questions and Answers for Freshers

A DBMS (Database Management System) is software that lets you store, organize, retrieve, and manage data efficiently, while controlling access and keeping the data consistent. Examples include MySQL, PostgreSQL, Oracle, and SQL Server.

An RDBMS (Relational DBMS) stores data in tables (rows and columns) and supports relationships between tables using keys. A plain DBMS may store data in other formats and may not enforce relationships. Most modern databases you will use, like MySQL, are relational.

A column (or set of columns) that uniquely identifies each row in a table. It cannot be null and cannot have duplicate values. Each table has exactly one primary key.

A column in one table that refers to the primary key of another table, creating a link between the two. It enforces referential integrity, meaning you cannot reference a record that does not exist.

Both prevent duplicate values. A table has only one primary key and it cannot be null; a table can have multiple unique keys and a unique column can usually hold one null value. The primary key is the main row identifier.

A candidate key is any column (or combination) that could uniquely identify a row. A table can have several candidate keys; the one you choose as the main identifier becomes the primary key, and the rest remain as alternate keys.

The main categories are DDL (Data Definition, like CREATE, ALTER, DROP), DML (Data Manipulation, like INSERT, UPDATE, DELETE), DQL (Data Query, SELECT), DCL (Data Control, GRANT, REVOKE), and TCL (Transaction Control, COMMIT, ROLLBACK).

DELETE removes specific rows and can be rolled back. TRUNCATE removes all rows quickly and cannot be rolled back easily, but keeps the table structure. DROP removes the entire table including its structure.

WHERE filters rows before grouping. HAVING filters groups after a GROUP BY. For example, WHERE filters individual sales; HAVING filters customers whose total sales exceed a value.

CHAR is fixed-length and pads shorter values with spaces; VARCHAR is variable-length and stores only what is needed. Use CHAR for values of consistent length, VARCHAR for variable text.

Functions that operate on a set of values and return a single result, such as COUNT, SUM, AVG, MIN, and MAX. They are often used with GROUP BY to summarize data.

GROUP BY groups rows that share a value so you can aggregate them. ORDER BY sorts the result rows. They serve different purposes and are often used together.

The process of organizing data to reduce redundancy and improve consistency, by dividing data into related tables and defining relationships between them. It prevents the same data from being stored in multiple places.

It avoids duplicate data, prevents update anomalies (where changing data in one place but not another causes inconsistency), and keeps the database clean and reliable.

1NF: each column holds atomic (single) values, no repeating groups. 2NF: 1NF plus every non-key column depends on the whole primary key. 3NF: 2NF plus no non-key column depends on another non-key column. Most practical databases aim for 3NF.

Deliberately introducing some redundancy back into a normalized database to improve read performance, for example storing a calculated total to avoid recomputing it. It trades some data duplication for speed.

A JOIN combines rows from two or more tables based on a related column, letting you pull connected data together in one result, such as orders shown with customer names.

INNER JOIN (only matching rows from both tables), LEFT JOIN (all rows from the left table plus matches from the right), RIGHT JOIN (all rows from the right plus matches from the left), and FULL JOIN (all rows from both, matched where possible).

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns all rows from the left table, with matching data from the right where it exists and nulls where it does not. Use LEFT JOIN when you want to keep unmatched left-side rows.

One-to-one, one-to-many, and many-to-many. One-to-many is the most common (one customer, many orders). Many-to-many (students and courses) is resolved using a junction table that links the two.

A transaction is a group of database operations treated as a single unit, they all succeed together or all fail together. For example, transferring money debits one account and credits another; both must happen or neither should.

The four guarantees of a reliable transaction: Atomicity (all or nothing), Consistency (the database stays valid), Isolation (concurrent transactions do not interfere), and Durability (committed changes survive failures). A very common interview question.

An index is a data structure that speeds up searches on a column, like an index in a book. It makes reads faster but adds a small cost to writes and uses extra storage, so you index columns you search or join on frequently.

A clustered index determines the physical order of rows in the table, so there can be only one. A non-clustered index is a separate structure pointing to the rows, and a table can have many. (Exact behavior varies by database.)

A view is a saved query that behaves like a virtual table. It does not store data itself but presents data from one or more tables, useful for simplifying complex queries or restricting access to certain columns.

SQL databases are relational, with structured tables and a fixed schema, good for related, consistent data. NoSQL databases are non-relational (documents, key-value, and so on) with flexible schemas, good for large-scale or rapidly changing unstructured data. Each suits different needs.