1. Why is SQL generally faster than NoSQL databases for complex queries?

SQL databases are designed around relationships between data. They have powerful query optimizers that can choose efficient ways to perform JOINs, filtering, sorting, and aggregation.

NoSQL databases are usually optimized for simple, high-speed access patterns and horizontal scaling. Complex relationships may require multiple queries or application-side processing.

Visual idea

SQL Database
┌───────────┐       JOIN       ┌───────────┐
│ Customers │ ──────────────── │  Orders   │
└───────────┘                  └───────────┘
       │                            │
       └──────── Query Optimizer ───┘
                    │
                    ▼
              Efficient Plan

NoSQL
Application
    │
    ├── Query Collection A
    │
    ├── Query Collection B
    │
    └── Combine/process results

Interview answer: SQL is generally better suited for complex queries involving JOINs, aggregations, and relationships because relational databases have mature query optimizers and execute these operations close to the data.

Note: This does not mean SQL is always faster than NoSQL. Performance depends on the workload, database, indexes, schema, and query.

---

2. Why is a B+ Tree preferred over a Binary Search Tree (BST) for database indexing?

The main reason is fewer disk I/Os.

A BST has only two children per node:

             50
           /    \
         25      75
        /  \    /  \
      10   30  60   90

A B+ Tree has many keys and children per node:

                 [30 | 60]
                /    |    \
               /     |     \
      [10|20]     [40|50]     [70|80|90]

Therefore, a B+ Tree has a much smaller height.

BST                         B+ Tree

     Root                    [30 | 60]
       │                    /    |    \
       ▼                   ▼     ▼     ▼
      ...                 ...   ...   ...
       │
       ▼
Many levels                Few levels

Fewer levels → fewer disk/page accesses → faster searches.

Another advantage: range queries

B+ Tree leaves are linked:

[10 20 30] → [40 50 60] → [70 80 90]

So:

WHERE salary BETWEEN 40000 AND 70000

can efficiently scan the relevant leaf range.

Interview answer: B+ Trees are preferred because they have high fan-out, low height, and linked leaf nodes, resulting in fewer I/O operations and efficient range searches.

---

3. Why is a Hash Index unsuitable for range searches?

A hash index is excellent for exact matches:

id = 42

But poor for:

id BETWEEN 10 AND 20

Why?

Hash Function

10 ──► Bucket 7
11 ──► Bucket 2
12 ──► Bucket 9
13 ──► Bucket 1
14 ──► Bucket 8
...

The values aren’t stored in sorted order.

A B+ Tree maintains ordering:

10 → 11 → 12 → 13 → 14 → ... → 20

So:

Hash Index
Exact lookup ✓
Range lookup ✗

B+ Tree
Exact lookup ✓
Range lookup ✓

Interview answer: Hash indexes are unsuitable for range queries because hashing destroys the ordering of keys. B+ Trees maintain sorted keys and therefore support efficient range searches.

---

4. Why can a database table have only one Clustered Index?

A clustered index determines the physical organization/order of the table’s data.

Imagine arranging books:

Clustered by Author

Adams
Brown
Clark
David
Evans

You cannot simultaneously arrange the same physical books by title:

A Tale...
Database...
Operating Systems...

The physical order can only have one primary organization.

Therefore:

Table
  │
  └── ONE clustered index

But you can have many non-clustered indexes:

Table
 ├── Clustered Index
 ├── Non-Clustered Index: Name
 ├── Non-Clustered Index: Email
 ├── Non-Clustered Index: Salary
 └── Non-Clustered Index: Department

Interview answer: A table can have only one clustered index because the clustered index determines the physical order of the table’s rows.

---

5. What is the primary operational purpose of a Foreign Key?

A foreign key maintains referential integrity.

Customers
┌────┬────────┐
│ ID │ Name   │
├────┼────────┤
│ 1  │ Ali    │
│ 2  │ Bob    │
└────┴────────┘
       ▲
       │ FK
       │
Orders
┌─────────┬─────────────┐
│ OrderID │ CustomerID  │
├─────────┼─────────────┤
│ 101     │ 1           │ ✓
│ 102     │ 2           │ ✓
│ 103     │ 99          │ ✗
└─────────┴─────────────┘

Order 103 cannot reference customer 99 if customer 99 doesn’t exist.

Interview answer: A foreign key ensures that relationships between tables remain valid and prevents orphan records.

---

6. Why does NULL = NULL evaluate to NULL rather than TRUE?

Because SQL treats NULL as unknown, not as an ordinary value.

Think:

NULL = Unknown

Therefore:

Unknown = Unknown
       ↓
    Unknown

SQL uses three-valued logic:

TRUE
FALSE
UNKNOWN

So:

NULL = NULL

returns:

UNKNOWN

not TRUE.

That’s why this doesn’t work:

WHERE manager_id = NULL

Use:

WHERE manager_id IS NULL

Remember

= NULL       ✗
IS NULL      ✓

<> NULL      ✗
IS NOT NULL  ✓

---

7. Which statement accurately distinguishes DELETE, TRUNCATE, and DROP?

Think of a table as a container:

┌─────────────────────────┐
│        TABLE            │
│                         │
│  Row 1                  │
│  Row 2                  │
│  Row 3                  │
└─────────────────────────┘

DELETE

Removes selected rows:

DELETE
   │
   └── removes rows
       │
       └── table remains

DELETE FROM employees
WHERE department = 'HR';

TRUNCATE

Removes all rows:

TRUNCATE
    │
    ▼
┌─────────────────────────┐
│        TABLE            │
│                         │
│        EMPTY            │
└─────────────────────────┘

The table structure remains.

DROP

Removes the entire object:

DROP
 │
 ▼
┌─────────────────────────┐
│        TABLE            │
└─────────────────────────┘
            ✗
        Gone completely

	DELETE	TRUNCATE	DROP
Removes rows	Yes	Yes, all	Yes
Removes structure	No	No	Yes
WHERE allowed	Yes	No	No
Type	DML	DDL*	DDL

*Classification and rollback behavior can vary somewhat by DBMS.

---

8. How does EXISTS generally compare to IN with a large subquery?

EXISTS asks:

“Does at least one matching row exist?”

Customer
   │
   ▼
Search Orders
   │
   ├── Match found? ──► YES ──► STOP
   │
   └── No ──► continue

Example:

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

Once a matching order is found, the database can logically stop checking for that customer.

Modern optimizers can transform both EXISTS and IN into similar execution plans, so EXISTS is not automatically faster.

Interview answer: EXISTS is often useful for large correlated subqueries because it only needs to establish that a match exists, but modern optimizers may make IN and EXISTS perform similarly.

---

9. Why is UNION ALL generally faster than UNION?

The difference is duplicate removal.

Query A
  ↓
A B C

Query B
  ↓
B C D

UNION

A B C
+
B C D
   ↓
Remove duplicates
   ↓
A B C D

UNION ALL

A B C
+
B C D
   ↓
A B C B C D

So:

UNION
  = combine + deduplicate

UNION ALL
  = combine only

Interview answer: UNION ALL is generally faster because it doesn’t perform duplicate elimination.

---

10. When is CHAR preferred over VARCHAR?

Use CHAR when values have a fixed length.

Examples:

Country code     → IN
Gender code      → M
Status code      → A

Conceptually:

CHAR(2)

IN
US
UK
CA

Whereas:

VARCHAR

Ali
Alexander
Database Engineer
Hello World!

can have different lengths.

Easy rule

Fixed length      → CHAR
Variable length   → VARCHAR

Important: Don’t choose CHAR merely because something is “small.” For most variable-length strings, VARCHAR is the better choice.

---

11. What is a major difference between a Primary Key and a Unique Key?

Both enforce uniqueness:

Primary Key
    │
    ├── Unique
    ├── NOT NULL
    └── One per table

Unique Key
    │
    ├── Unique
    ├── NULL handling depends on DBMS
    └── Multiple allowed

Example:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

Here:

emp_id → identifies the row
email  → prevents duplicate emails

Important correction: The exact treatment of NULL values in a UNIQUE constraint is DBMS-dependent. Don’t memorize “always one NULL.”

---

12. How does a Non-Clustered Index differ from a Clustered Index?

Clustered

The leaf level contains the actual table data:

Clustered Index

[10] → Row
[20] → Row
[30] → Row
[40] → Row

Non-clustered

The index points toward the data:

Non-Clustered Index

[Ali] ──────► Row location
[Bob] ──────► Row location
[John] ─────► Row location

So the lookup can be:

Non-clustered

Index
  │
  ▼
Find key
  │
  ▼
Row locator
  │
  ▼
Table data

A covering index can avoid that final table lookup.

---

13. What is a structural characteristic of a Heap table?

A heap is simply a table without a clustered index.

Heap

┌──────────────────┐
│ Row 7            │
│ Row 2            │
│ Row 9            │
│ Row 1            │
│ Row 5            │
└──────────────────┘

No meaningful ordering

Compared with:

Clustered table

1
2
3
4
5
6
7

A heap can be perfectly valid; it isn’t automatically bad.

Interview answer: A heap is a table without a clustered index, so its rows do not have a clustered physical ordering.

---

14. What is the key difference between Stored Procedures and User-Defined Functions?

The easiest distinction is how they are invoked.

Function

Can appear inside expressions:

SELECT
    name,
    CalculateBonus(salary)
FROM employees;

SQL Statement
      │
      ▼
   Function
      │
      ▼
   Return value

Procedure

Normally called separately:

CALL UpdateSalary(101, 5000);

Application
     │
     ▼
Procedure
     │
     ├── INSERT
     ├── UPDATE
     └── DELETE

Exact restrictions vary by DBMS.

Interview answer: A function generally returns a value and can be used in SQL expressions, while a stored procedure is invoked as a separate executable routine and is commonly used for multi-step operations.

---

15. What distinguishes a Trigger from a Stored Procedure?

The key word is automatic.

Stored Procedure

Application
     │
     │ CALL
     ▼
Procedure

But:

Trigger

INSERT / UPDATE / DELETE
           │
           ▼
       TRIGGER
           │
           ▼
      Automatic action

Example:

INSERT INTO orders VALUES (...);
              │
              ▼
        Audit Trigger
              │
              ▼
       audit_log updated

Easy memory trick:

Procedure = "You call it"
Trigger   = "Database calls it"

---

16. When would a Temp Table be preferred over a CTE?

A CTE normally exists for one SQL statement:

WITH temp AS (...)
SELECT ...

Its lifetime is roughly:

Start query
    │
    ▼
  CTE exists
    │
    ▼
Query finishes
    │
    ▼
  CTE gone

A temporary table can survive across multiple statements in its supported scope:

CREATE TEMP TABLE temp_data
        │
        ▼
     Query 1
        │
        ▼
     Query 2
        │
        ▼
     Query 3
        │
        ▼
   DROP / session ends

Temp tables can also often have indexes.

Easy rule

One query
   → CTE

Multiple steps / reusable intermediate data
   → Temp Table

---

17. What is the key difference between a Standard View and a Materialized View?

Think of them as two different windows.

Standard View

User
 │
 ▼
VIEW
 │
 ▼
Base Tables
 │
 ▼
Query executes

The view normally stores the query definition, not the result.

Materialized View

Base Tables
     │
     ▼
Compute Query
     │
     ▼
┌─────────────────┐
│ Stored Result   │
└─────────────────┘
     ▲
     │
     User reads

So:

View
= always calculates from source

Materialized View
= stores calculated result

	View	Materialized View
Stores result	No	Yes
Freshness	Usually current	Depends on refresh
Read speed	Depends on query	Often faster
Storage	Minimal	Additional storage

---

18. Which design pattern is typical of an OLAP system?

OLAP systems commonly use star schemas or snowflake schemas.

Star Schema

             Dimension
                 │
                 │
Dimension ── Fact Table ── Dimension
                 │
                 │
             Dimension

Example:

              Date
               │
               ▼
Product ──► SALES ◄── Customer
               ▲
               │
             Store

The central fact table contains measurable events:

Sales
Revenue
Quantity
Profit

Dimensions describe them:

Customer
Product
Date
Store

OLTP:

Many small transactions
        ↓
Highly normalized

OLAP:

Large analytical queries
        ↓
Often denormalized

---

19. What is the main structural difference between Star and Snowflake Schema?

Star Schema

Dimensions are relatively denormalized:

             Date
              │
              ▼
Product ──► FACT ◄── Customer
              ▲
              │
            Store

Snowflake Schema

Dimensions are normalized further:

                    Country
                       │
                    Region
                       │
                    Customer
                       │
                       ▼
Product ───────────► FACT

Think:

STAR

      D
      │
D ─── FACT ─── D
      │
      D


SNOWFLAKE

       D
       │
      D2
       │
D ─── FACT
       │
      D2

Memory trick

Star      → simpler → fewer JOINs
Snowflake → normalized → more JOINs

---

20. How does Sharding differ from Table Partitioning?

Both divide data, but the key difference is where the data goes.

Partitioning

One database/server:

             Database Server
                   │
              Users Table
                   │
       ┌───────────┼───────────┐
       ▼           ▼           ▼
   Partition 1  Partition 2  Partition 3

Sharding

Multiple database servers:

                 Application
                      │
          ┌───────────┼───────────┐
          ▼           ▼           ▼
       Server 1    Server 2    Server 3
       Shard A     Shard B     Shard C

Easy memory

Partitioning = split within a database

Sharding     = split across databases/servers

---

21. Which isolation level provides complete protection against dirty, non-repeatable, and phantom reads?

Serializable provides the strongest standard SQL isolation guarantee.

Read Uncommitted
      │
      ▼
Read Committed
      │
      ▼
Repeatable Read
      │
      ▼
Serializable

Isolation	Dirty	Non-repeatable	Phantom
Read Uncommitted	✓	✓	✓
Read Committed	✗	✓	✓
Repeatable Read	✗	✗	DBMS-dependent*
Serializable	✗	✗	✗

• The exact behavior of Repeatable Read varies between DBMS implementations.

Memory trick:

Dirty read       → Read Uncommitted
Non-repeatable   → Read Committed+
Phantom          → Serializable

---

22. How does a database engine typically resolve a deadlock?

Imagine:

T1 holds Lock A
       │
       ▼
   wants Lock B
       ▲
       │
T2 holds Lock B
       │
       ▼
   wants Lock A

This creates a cycle:

T1 ─────► T2
▲         │
│         ▼
└─────────┘

The database detects the cycle using mechanisms such as a wait-for graph.

Then:

Detect cycle
     │
     ▼
Choose victim
     │
     ▼
Rollback victim
     │
     ▼
Release locks
     │
     ▼
Other transaction continues

Interview answer: The DBMS detects the circular wait, chooses a transaction as the victim, rolls it back, releases its locks, and allows the remaining transaction to continue.

---

23. What is the primary objective of a cost-based Query Optimizer?

The optimizer tries to find the lowest-cost execution plan.

For example:

SELECT *
FROM orders
WHERE customer_id = 42;

The database may consider:

Plan A
Full Table Scan
     ↓
10 million rows

Plan B
Index Seek
     ↓
100 rows

It uses statistics to estimate costs:

SQL Query
    │
    ▼
Query Optimizer
    │
    ├── Plan A: Table Scan
    ├── Plan B: Index Seek
    ├── Plan C: Different Join
    │
    ▼
Choose cheapest estimated plan

It may consider:

• Index scans/seeks
• Table scans
• Join order
• Nested-loop joins
• Hash joins
• Merge joins
• Sorting
• Aggregation

Important: “cheapest” means lowest estimated cost, not necessarily guaranteed fastest in reality.

---

24. When would an architect deliberately choose denormalization?

Usually when read performance is more important than eliminating redundancy.

Normalized

Orders ──► Customers
   │
   └── JOIN required

Denormalized

Orders
┌────────┬──────────────┬──────────────┐
│ ID     │ CustomerID   │ CustomerName │
└────────┴──────────────┴──────────────┘

Now the query can avoid a JOIN.

Normalization
     │
     ▼
Less redundancy
     │
     ▼
More JOINs


Denormalization
     │
     ▼
More redundancy
     │
     ▼
Potentially faster reads

Interview answer: Denormalization is deliberately used when reducing JOINs and improving read performance outweighs the costs of duplicated data and more complicated updates.

---

25. What makes an index a Covering Index?

A covering index contains everything needed by a particular query.

Suppose:

SELECT name, salary
FROM employees
WHERE name = 'Ali';

An index containing:

(name, salary)

can cover the query.

             Query
               │
               ▼
      ┌─────────────────┐
      │ Covering Index  │
      │                 │
      │ name            │
      │ salary          │
      └─────────────────┘
               │
               ▼
          Return result

No additional table lookup is needed.

Without a covering index:

Index
  │
  ▼
Find row
  │
  ▼
Go to table
  │
  ▼
Fetch remaining columns

With a covering index:

Index
  │
  ▼
Everything needed
  │
  ▼
Return result

Interview answer: A covering index contains all columns required by a query, allowing the database to answer it directly from the index without accessing the base table.

---

26. How do Window Functions differ from GROUP BY?

This is one of the most important differences to remember.

GROUP BY

It collapses rows:

Employees

Ali   Sales    5000
Bob   Sales    6000
Cam   Sales    7000
Dan   IT       8000

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Result:

Sales → 6000
IT    → 8000

The individual employee rows disappear.

3 Sales rows
     │
     ▼
   GROUP BY
     │
     ▼
1 Sales row

---

Window Function

Keeps the original rows:

SELECT
    name,
    department,
    salary,
    RANK() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS rank
FROM employees;

Result:

Name   Dept    Salary    Rank
─────────────────────────────
Cam    Sales   7000       1
Bob    Sales   6000       2
Ali    Sales   5000       3
Dan    IT      8000       1

Every employee remains.

3 Sales rows
     │
     ▼
Window Function
     │
     ▼
3 Sales rows + calculated rank

The easiest way to remember

GROUP BY
   ↓
Reduces rows
   ↓
"Give me one result per group"


WINDOW FUNCTION
   ↓
Keeps rows
   ↓
"Calculate something across related rows"

Common window-function uses:

RANK()
ROW_NUMBER()
DENSE_RANK()
SUM() OVER (...)
AVG() OVER (...)
LAG()
LEAD()

---