Skip to content

Interview Questions & Answers

Curated collection of technical interview questions

Answer

📁 Data Files - The Core Data

Data files contain the actual customer orders, products, and user accounts organized in tables. Each table row is a record (e.g., Order #12345), each column is an attribute (order_date, total_amount). Without data files, you have no database.

📊 Index Files - Fast Search

Index files store references to data locations based on specific columns. Example: An index on 'email' column lets you find user 'john@email.com' instantly instead of scanning all 1 million rows. They speed up queries but can be rebuilt from data files.

📝 Transaction Log Files - The Recovery Key

Transaction logs record every change: 'User #123 placed order at 1:30 AM', 'Payment processed at 1:31 AM'. To restore to 1 hour ago, you'd load the last full backup and replay transaction logs up to that time. This is how point-in-time recovery works.

🗂️ System Catalog/Metadata Files

These store database structure: table definitions, column types, constraints. Example: 'orders table has columns: order_id (INT), user_id (INT), total (DECIMAL)'. The database needs this to understand its own structure.

💾 Backup Files

Full database snapshots taken periodically (e.g., daily at midnight). For your 2 AM crash, you'd restore the midnight backup, then replay transaction logs from midnight to 1 AM.

🔧 Temporary Files

Used during query processing for sorting/joining large datasets. Example: When running 'SELECT * FROM orders ORDER BY date' on 10 million rows, temp files help. These are automatically cleaned up.

Practice Question

Which file type is MOST critical for point-in-time database recovery?

Answer

🗄️ DBMS (Database Management System) - General Term

DBMS is the broad category - any system that manages data. It can store data in files, hierarchical structures, or any format. Examples: File-based systems, XML databases. DBMS doesn't enforce how data is organized.

🔗 RDBMS (Relational DBMS) - Structured Tables

RDBMS organizes data into structured tables with relationships. Example: 'users' table links to 'accounts' table via user_id. Uses SQL for queries. Enforces ACID properties (Atomicity, Consistency, Isolation, Durability) - perfect for banking where data consistency is critical.

💰 Why RDBMS for Banking

Banking requires ACID guarantees. When transferring $1000: deduct from Account A AND add to Account B - both must succeed or both fail (atomicity). RDBMS ensures this. Relationships between tables (users, accounts, transactions) are naturally modeled in relational schema.

📱 NoSQL - Flexible, Scalable

NoSQL databases (MongoDB, Redis, Cassandra) handle unstructured/semi-structured data without rigid schemas. Perfect for social feeds where each post might have different fields: some have images, some videos, some polls. Scales horizontally easily.

🚀 Why NoSQL for Social Feed

Social feeds need to scale to millions of users quickly. Post structure varies widely. NoSQL allows flexible documents, scales by adding servers, and prioritizes performance over strict consistency (eventual consistency is ok - a comment appearing 1 second late doesn't matter).

RDBMS example - Banking schema with relationships sql
-- RDBMS: Structured tables with relationships
CREATE TABLE users (
  user_id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  user_id INT,
  balance DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

-- Enforces relationships: account must belong to valid user
-- ACID transaction: Transfer $1000
BEGIN TRANSACTION;
  UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
  UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;
COMMIT; -- Both succeed or both fail

-- NoSQL example (MongoDB) - Flexible document
{
  "post_id": "abc123",
  "user": "john_doe",
  "content": "Great day!",
  "media": {"type": "image", "url": "..."}, // Optional field
  "likes": 42,
  "comments": [...] // Nested data
}
// No fixed schema - each document can have different fields

Practice Question

Which scenario is BEST suited for RDBMS over NoSQL?

Answer

🎯 What is SQL?

SQL stands for Structured Query Language. It's a standardized programming language designed specifically for managing and manipulating data in relational databases. Think of it as the universal language for talking to databases.

📊 SQL vs Excel - Scalability

Excel is great for 1,000-10,000 rows. But your e-commerce site has 10 million customer records. Excel crashes. SQL databases (MySQL, PostgreSQL) handle billions of rows effortlessly. They're built for scale.

🔍 SQL Capabilities

SQL lets you: - Query: SELECT * FROM customers WHERE city = 'Mumbai' - Insert: INSERT INTO orders VALUES (order_id, customer_id, amount) - Update: UPDATE products SET price = 499 WHERE product_id = 123 - Delete: DELETE FROM cart WHERE user_id = 456 - Create schemas: CREATE TABLE, ALTER TABLE, etc. - Control access: GRANT/REVOKE permissions

⚡ Performance & Concurrency

Excel allows one user at a time. SQL databases handle thousands of simultaneous users querying/updating data. When 1000 customers place orders simultaneously, SQL handles it. Excel can't.

🔒 Data Integrity & Security

SQL enforces rules: 'email must be unique', 'price cannot be negative', 'every order must have a valid customer'. Excel has no such enforcement. SQL also provides user permissions, audit logs, and encryption - critical for business data.

Common SQL operations for business data sql
-- Query: Find all customers from Mumbai who spent > ₹10,000
SELECT customer_name, email, total_spent 
FROM customers 
WHERE city = 'Mumbai' AND total_spent > 10000
ORDER BY total_spent DESC;

-- Insert: Add new customer
INSERT INTO customers (name, email, city) 
VALUES ('Priya Sharma', 'priya@email.com', 'Delhi');

-- Update: Apply 10% discount to electronics
UPDATE products 
SET price = price * 0.9 
WHERE category = 'Electronics';

-- Delete: Remove cancelled orders
DELETE FROM orders 
WHERE status = 'Cancelled' AND order_date < '2024-01-01';

-- Complex query: Join customers and orders
SELECT c.name, COUNT(o.order_id) as total_orders
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name
HAVING total_orders > 5;

Practice Question

What does SQL stand for?

Answer

📱 What is SQLite?

SQLite is a self-contained, serverless, embedded relational database. Unlike MySQL/PostgreSQL which run as separate server processes, SQLite is a library that runs inside your application. The entire database is a single file on disk.

🚫 Serverless Architecture

Traditional databases: App → Network → MySQL Server → Database SQLite: App → SQLite Library → Local File No server setup, no network dependency. Perfect for mobile apps where users might be offline.

💾 Single File Database

The entire expense tracker database (tables, indexes, data) is stored in one file: 'expenses.db' on the phone. Easy to back up, transfer, or delete. The app can query this file directly using SQL.

⚡ Lightweight & Fast

SQLite is tiny (600 KB) with zero configuration. It's embedded in iOS, Android, Chrome, Firefox. Reads are extremely fast since there's no network latency. Perfect for mobile where resources are limited.

✅ Perfect for Mobile Offline-First Apps

Users log expenses offline. Data is stored in local SQLite database. When internet is available, app syncs SQLite data to remote server. WhatsApp uses SQLite to store messages locally on your phone.

⚠️ Limitations

SQLite doesn't support high concurrency (many simultaneous writers). It's not for web servers handling thousands of users. But for single-user mobile apps, it's ideal.

SQLite example - Expense tracker schema sql
-- Create expense tracker database (stored in 'expenses.db' file)
CREATE TABLE expenses (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  category TEXT NOT NULL,
  amount REAL NOT NULL,
  description TEXT,
  date TEXT DEFAULT CURRENT_DATE,
  synced INTEGER DEFAULT 0  -- 0 = not synced, 1 = synced to server
);

-- User logs expense offline
INSERT INTO expenses (category, amount, description) 
VALUES ('Food', 450.00, 'Lunch at restaurant');

-- Query monthly spending
SELECT category, SUM(amount) as total
FROM expenses
WHERE date LIKE '2024-11-%'
GROUP BY category;

-- Mark as synced after uploading to server
UPDATE expenses SET synced = 1 WHERE synced = 0;

-- All of this works WITHOUT internet connection!

Practice Question

Which characteristic makes SQLite ideal for mobile apps?

Answer

🔗 Relational Databases (SQL)

Store data in structured tables with predefined schemas. Examples: MySQL, PostgreSQL, Oracle. Each row is a record, columns are attributes. Tables relate via foreign keys. Uses SQL for queries. Enforces ACID properties (data consistency guaranteed).

✅ Relational DB - Best For

- Structured data with clear relationships (users → orders → products) - When data consistency is critical (banking, authentication) - Complex queries with JOINs across tables - When data structure is stable and won't change frequently Example: User authentication - users have fixed fields (id, email, password_hash)

📄 Non-Relational Databases (NoSQL)

Store data in flexible formats: documents (MongoDB), key-value (Redis), wide-column (Cassandra), graph (Neo4j). No fixed schema - each record can have different fields. Prioritizes scalability and performance over strict consistency.

🚀 NoSQL - Best For

- Unstructured/semi-structured data (JSON documents, logs) - Rapidly changing schema (new fields added frequently) - Massive scale, horizontal scaling (millions of users) - When eventual consistency is acceptable (social media, chat) Example: Chat messages - flexible structure, high volume, needs horizontal scaling

🛍️ Product Catalog Decision

Recommendation: MongoDB (NoSQL) Reason: Products have varying attributes: - Books: {title, author, ISBN, pages} - Clothes: {title, brand, size, color} - Electronics: {title, brand, warranty, specs} NoSQL allows flexible schemas without NULL columns. Easy to add new product types without altering table structure.

Relational vs NoSQL data models sql
-- Relational (PostgreSQL) - Fixed schema
CREATE TABLE products (
  id INT PRIMARY KEY,
  title VARCHAR(200),
  price DECIMAL(10,2),
  category VARCHAR(50),
  -- Problem: Book-specific fields are NULL for clothes
  isbn VARCHAR(13),      -- Only for books
  author VARCHAR(100),   -- Only for books
  size VARCHAR(10),      -- Only for clothes
  color VARCHAR(50)      -- Only for clothes
);
-- Many NULL values, hard to add new product types

-- NoSQL (MongoDB) - Flexible documents
// Book document
{
  "_id": "book_001",
  "title": "Clean Code",
  "price": 599,
  "category": "Books",
  "isbn": "978-0132350884",
  "author": "Robert Martin",
  "pages": 464
}

// Clothing document - different structure
{
  "_id": "cloth_001",
  "title": "Cotton T-Shirt",
  "price": 299,
  "category": "Clothing",
  "size": "L",
  "color": "Blue",
  "material": "100% Cotton"
}
// No NULL fields, easy to add new product types

Practice Question

Which database type is BEST for storing user profiles where every user has the same fields (id, name, email, created_at)?

Someone from India subscribed to Premium recently

845 online
Feedback