DBMS and SQL - Complete Topic-wise Explanation
1. Introduction to DBMS
A Database Management System (DBMS) is software that allows users to define, create, maintain, and
control access to databases. It ensures data consistency and security.
Importance in Interview:
Frequently asked as an opening question.
Example:
"MySQL, Oracle, and PostgreSQL are examples of DBMS."
2. Types of DBMS
1. Hierarchical DBMS
2. Network DBMS
3. Relational DBMS (RDBMS) - Most popular
4. Object-oriented DBMS
Interview Relevance:
Candidates should know RDBMS, as it's used in most applications.
Example:
RDBMS stores data in tables with rows and columns.
3. Database Models
Defines how data is structured.
- Hierarchical: Parent-child relationship.
- Relational: Tables with foreign keys.
- Object-Oriented: Uses classes and objects.
Interview Focus: Explain Relational Model with Table Example.
Example:
DBMS and SQL - Complete Topic-wise Explanation
Student Table with attributes: ID, Name, Course.
4. SQL Overview
SQL (Structured Query Language) is used for accessing and manipulating databases.
Importance: Most technical interviews have at least 2-3 SQL query questions.
Types of SQL Commands:
- DDL: CREATE, DROP
- DML: INSERT, UPDATE, DELETE
- DQL: SELECT
- DCL: GRANT, REVOKE
- TCL: COMMIT, ROLLBACK
5. Keys in DBMS
Keys are attributes used to uniquely identify data.
Types:
- Primary Key: Unique & Not Null
- Foreign Key: Refers to Primary Key in another table
- Unique Key: Ensures uniqueness
- Composite Key: Multiple columns as primary
Example:
CREATE TABLE Student(ID INT PRIMARY KEY, Name VARCHAR(20));
6. Constraints in SQL
Constraints restrict data to maintain accuracy.
Types:
- NOT NULL
- UNIQUE
DBMS and SQL - Complete Topic-wise Explanation
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
Example:
CREATE TABLE Emp(ID INT PRIMARY KEY, Age INT CHECK (Age > 18));
7. Normalization
Process of organizing data to reduce redundancy.
Forms:
1NF - Atomic columns
2NF - Full functional dependency
3NF - No transitive dependency
Interview Importance:
Asked in DBMS rounds for understanding schema design.
Example:
Splitting Employee table to Employee & Department tables.
8. Joins in SQL
Used to combine rows from two or more tables.
Types:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- SELF JOIN
Example:
DBMS and SQL - Complete Topic-wise Explanation
SELECT E.Name, D.DeptName FROM Employee E
JOIN Department D ON E.DeptID = D.ID;
Asked frequently to test multi-table queries.
9. Aggregate Functions
Functions that return a single value from a set.
Functions:
- COUNT()
- SUM()
- AVG()
- MIN()
- MAX()
Example:
SELECT AVG(Salary) FROM Employee;
Common in interview when testing analytical thinking.
10. Subqueries and Nested Queries
A query inside another query.
Example:
SELECT Name FROM Employee WHERE Salary =
(SELECT MAX(Salary) FROM Employee);
Interview Focus: Used in optimization questions.
11. GROUP BY and HAVING
Used to group rows and apply conditions on aggregated data.
DBMS and SQL - Complete Topic-wise Explanation
Example:
SELECT DeptID, COUNT(*) FROM Employee
GROUP BY DeptID HAVING COUNT(*) > 5;
Importance:
Good to know when grouping-based questions are asked.
12. Indexes in SQL
Indexes speed up data retrieval.
Example:
CREATE INDEX idx_name ON Employee(Name);
Interview Importance:
Performance-based SQL questions.
13. Views
A virtual table based on SQL query.
Example:
CREATE VIEW HighSalary AS
SELECT * FROM Employee WHERE Salary > 50000;
Good concept to discuss in DB-heavy roles.
14. Transactions & ACID
ACID: Atomicity, Consistency, Isolation, Durability.
Used in bank-like scenarios for reliable transactions.
Example:
BEGIN;
DBMS and SQL - Complete Topic-wise Explanation
UPDATE Account SET Balance = Balance - 100 WHERE ID=1;
UPDATE Account SET Balance = Balance + 100 WHERE ID=2;
COMMIT;
Interview Importance: System design questions.
15. Difference between DELETE, TRUNCATE, DROP
DELETE - Deletes selected rows (can use WHERE), can be rolled back.
TRUNCATE - Deletes all rows, faster, can't be rolled back.
DROP - Deletes entire table structure.
Example:
DELETE FROM Student WHERE ID = 5;
Interview Relevance:
Always asked to test understanding of DML/DDL.
