BACKEND INTERVIEW QUESTIONS & ANSWERS (Level-1)

---

Q1. What are the different types of languages in DBMS and their advanced use cases?

Answer:

In a Database Management System (DBMS), there are four major types of languages that serve specific purposes in database operations. These languages not only help with basic data operations but are also foundational to advanced backend architecture, SQL optimization, and database security:

1. Data Definition Language (DDL):
   Used to define database schema and structure. Common DDL commands include:
   
   • CREATE – Creates a new table or database.
     
   • ALTER – Modifies an existing table (add, remove, or modify columns).
     
   • DROP – Deletes tables or databases.
     
   • Advanced Use Case: Used during schema migrations and in DevOps CI/CD pipelines.
     
2. Data Manipulation Language (DML):
   Used for data retrieval and manipulation. Examples:
   
   • SELECT, INSERT, UPDATE, DELETE.
     
   • Advanced Use Case: Frequently used in transactional scripts and stored procedures for real-time data manipulation.
     
3. Data Control Language (DCL):
   Used for defining access and privileges. Examples:
   
   • GRANT, REVOKE.
     
   • Advanced Use Case: Essential in multi-user systems where role-based access control (RBAC) is enforced.
     
4. Transaction Control Language (TCL):
   Manages database transactions. Examples:
   
   • COMMIT, ROLLBACK, SAVEPOINT.
     
   • Advanced Use Case: Ensures ACID compliance in financial and high-volume transactional systems.
     

---

Q2. Explain ACID Properties in-depth with real-world examples.

Answer:

ACID stands for Atomicity, Consistency, Isolation, and Durability—a set of properties that ensure reliable database transactions. Let’s understand each with a real-world banking example:

• Atomicity: A transaction is treated as a single unit. If one part fails, the entire transaction fails.
  Example: Transferring money between two accounts—either both debit and credit operations succeed, or none does.
  
• Consistency: Ensures the database moves from one valid state to another.
  Example: After a successful transaction, total balance across all accounts remains unchanged, reflecting accurate bookkeeping.
  
• Isolation: Each transaction is isolated from others; it must appear as if it’s the only one in operation.
  Example: Two people booking the same movie ticket cannot both get the last seat—concurrent access is controlled.
  
• Durability: Once a transaction is committed, it remains so even in the event of a system crash.
  Example: After payment confirmation, your order is preserved even if the system reboots.
  

---

Q3. What is Normalization? Explain all Normal Forms with use cases.

Answer:

Normalization is the process of organizing data in a relational database to reduce redundancy and improve data integrity. It involves decomposing tables to remove anomalies (insertion, deletion, and update anomalies).

Normal Forms:

1. 1NF (First Normal Form):
   
   • No repeating groups or arrays.
     
   • Use Case: Flattening user contact numbers into separate rows.
     
2. 2NF (Second Normal Form):
   
   • Must be in 1NF and all non-key attributes fully depend on the primary key.
     
   • Use Case: Splitting product details into separate category and product tables.
     
3. 3NF (Third Normal Form):
   
   • Must be in 2NF and no transitive dependency.
     
   • Use Case: Isolating supplier address data into a separate table.
     
4. BCNF (Boyce-Codd Normal Form):
   
   • More rigorous version of 3NF, ensures candidate key dependency.
     
   • Use Case: Handling composite keys in distributed product tables.
     
5. 4NF (Fourth Normal Form):
   
   • No multi-valued dependencies.
     
   • Use Case: Separate author-book relationships in publishing platforms.
     
6. 5NF (Fifth Normal Form):
   
   • No join dependency or data loss when decomposed.
     
   • Use Case: Complex manufacturing database where parts-vendors-products are interlinked.
     

---

Q4. What is an ER Diagram? Explain its components with examples.

Answer:

An ER Diagram (Entity-Relationship Diagram) is a high-level conceptual data model used to define the structure of a database before physical implementation. It visually represents entities, attributes, and relationships.

Key Components:

• Entity: Object or thing in the real world (e.g., Student, Order).
  
• Attribute: Characteristics or properties of entities (e.g., Student Name, Order Date).
  
• Relationship: How entities interact with each other (e.g., Student ENROLLS in Course).
  

Example: In a hospital management system:

• Entities: Patient, Doctor
  
• Relationship: Patient – Visits – Doctor
  
• Attributes: Patient Name, Doctor Specialization
  

---

Q5. Explain all types of SQL Joins with sample outputs and when to use them.

Answer:

SQL JOINs combine rows from two or more tables based on related columns. Below are all JOIN types with their advanced use cases:

1. INNER JOIN:
   
   • Returns only matching records.
     
   • Best for: Fetching data where relationship exists.
     
   • Output: Employees and their valid departments.
     
2. LEFT JOIN (LEFT OUTER JOIN):
   
   • Returns all records from the left table with matching data from the right table.
     
   • Best for: Getting all employees, even if they don’t belong to any department.
     
3. RIGHT JOIN (RIGHT OUTER JOIN):
   
   • Returns all records from the right table with matching data from the left.
     
   • Best for: Listing all departments, even if no employee belongs to them.
     
4. FULL OUTER JOIN:
   
   • Returns all records with matches from both tables.
     
   • Best for: Auditing mismatches between two related tables.
     
5. SELF JOIN:
   
   • A table joined with itself.
     
   • Best for: Hierarchical data like manager-employee relationships.
     

---

Q6. What is Statelessness in RESTful Architecture?

Answer:

Statelessness is a key principle of REST API architecture where each HTTP request must contain all necessary information for the server to process it. The server does not store session information between requests.

Benefits:

• Scalability: Easily distribute load across servers.
  
• Testability: Each request is independent and repeatable.
  
• Maintenance: No session tracking, leading to simpler backend logic.
  

Real-World Use Case: A mobile app calling a REST API with authentication token on each request without relying on server sessions.

---

Q7. What are Idempotent Methods in REST API? Explain with HTTP verbs.

Answer:

An operation is idempotent if performing it multiple times yields the same result as doing it once. In REST, this is crucial to ensure reliability and fault tolerance.

Idempotent HTTP Methods:

• GET: Retrieve data (Safe + Idempotent)
  
• PUT: Replace or update a resource
  
• DELETE: Removes a resource, repeated calls have no additional effect
  
• HEAD / OPTIONS / TRACE: Do not modify server state
  

Example:
Calling DELETE /user/23 twice – the first deletes the user, the second returns a 204 (No Content), with no side effects.

---

Q8. Explain CAP Theorem with real-time database examples.

Answer:

CAP Theorem (Consistency, Availability, Partition Tolerance) states that a distributed system can guarantee only two out of three:

• Consistency (C): All nodes see the same data at the same time.
  
• Availability (A): System responds to every request (even if outdated).
  
• Partition Tolerance (P): System continues to function despite network failures.
  

Trade-Off Categories:

• CP (Consistency + Partition Tolerance): Ex: MongoDB, HBase
  
• AP (Availability + Partition Tolerance): Ex: Cassandra, CouchDB
  
• CA (Consistency + Availability): Ex: Traditional RDBMS like MySQL, Oracle (not truly distributed)
  

Use Case: In financial systems, CP is preferred. In real-time analytics, AP systems are better.

---

Q9. What is SQL Injection and how can it be prevented?

Answer:

SQL Injection is a serious security vulnerability where attackers manipulate SQL queries by injecting malicious inputs into forms or URLs, aiming to extract, alter, or delete data.

Example:

sql

SELECT * FROM users WHERE username = ‘admin’ — ‘ AND password = ‘123’

The attacker comments out the password check.

Prevention Techniques:

• Use Prepared Statements / Parameterized Queries
  
• Input Validation & Sanitization
  
• Use ORM frameworks (e.g., Hibernate)
  
• Least Privilege Database Access
  
• Regular Security Audits

👉The Next 7 Questions-2: BACKEND