Top 25 - Part 2

1. What is the difference between UNION and UNION ALL?

Both combine the results of two or more SELECT queries. The main difference is how they handle duplicates.

• UNION removes duplicate rows from the combined result.
• UNION ALL keeps all rows, including duplicates.

SELECT city FROM customers
UNION
SELECT city FROM suppliers;

If both queries return "Mumbai":

UNION:
Customers  → Mumbai ─┐
                     ├──> Mumbai   (one copy)
Suppliers  → Mumbai ─┘

UNION ALL:
Customers  → Mumbai ─┐
                     ├──> Mumbai
Suppliers  → Mumbai ─┘     Mumbai

Performance: UNION ALL is generally faster because it doesn’t need to perform duplicate elimination.

Remember: UNION → combine + remove duplicates UNION ALL → combine everything

Use UNION ALL when duplicates are valid or you already know the result sets cannot overlap.

---

2. What is a Stored Procedure vs. a Trigger?

Both are database-side programs, but they are executed differently.

• Stored Procedure — explicitly invoked by a user, application, or another database program.
• Trigger — automatically executed when a defined database event occurs, such as INSERT, UPDATE, or DELETE.

Stored Procedure:

Application
     │
     └── CALL procedure()
              ↓
          Procedure runs


Trigger:

INSERT / UPDATE / DELETE
              ↓
          Trigger fires
              ↓
        Trigger code runs

Example:

A procedure:

CALL GetCustomerOrders(101);

runs only when explicitly called.

A trigger can automatically record an update:

UPDATE employee
       ↓
   Trigger fires
       ↓
   Write audit record

	Stored Procedure	Trigger
Invocation	Explicit	Automatic
Triggered by event	No	Yes
Parameters	Often supported	DBMS-specific
Typical use	Business operations, reusable logic	Auditing, automatic actions

Remember: Procedure → you call it. Trigger → database event calls it.

---

3. What is a Surrogate Key?

A surrogate key is a system-generated identifier that has no meaningful business information.

Common examples include:

• Auto-incrementing integers
• UUIDs

Example:

Customers
┌─────────────┬───────────┐
│ customer_id │ name      │
├─────────────┼───────────┤
│ 1001        │ Ali       │
│ 1002        │ Bob       │
└─────────────┴───────────┘

customer_id is simply an identifier. It doesn’t describe the customer.

A natural key, on the other hand, comes from real-world business data, such as an email address or government-issued identifier, when appropriate.

Natural Key                  Surrogate Key

email = ali@mail.com         customer_id = 1001
      │                             │
Business meaning             No business meaning

Advantages of surrogate keys

• Usually stable even when business information changes.
• Simple and compact for relationships.
• Avoids exposing business meaning in relationships.

However, a surrogate key does not automatically remain stable forever — the application/database design can still change or regenerate identifiers. Also, natural keys may still need UNIQUE constraints.

Remember: Surrogate key → artificial identifier. Natural key → meaningful business attribute.

---

4. What is 1NF, 2NF, and 3NF?

These are stages of database normalization.

1NF — First Normal Form

A table should contain atomic values and should not store repeating groups in a single field.

Bad:

Order
┌─────────┬────────────────────┐
│ OrderID │ Products           │
├─────────┼────────────────────┤
│ 101     │ Phone,Laptop,Mouse │
└─────────┴────────────────────┘

Better:

OrderDetails
┌─────────┬─────────┐
│ OrderID │ Product │
├─────────┼─────────┤
│ 101     │ Phone   │
│ 101     │ Laptop  │
│ 101     │ Mouse   │
└─────────┴─────────┘

2NF — Second Normal Form

A relation must already be in 1NF, and every non-key attribute must depend on the whole candidate key, not just part of a composite key.

Example:

OrderDetails(OrderID, ProductID, ProductName)

If the composite key is:

(OrderID, ProductID)

but:

ProductName → depends only on ProductID

then there is a partial dependency.

Move product information elsewhere:

Products
ProductID → ProductName

OrderDetails
OrderID + ProductID → Quantity

3NF — Third Normal Form

A relation must be in 2NF, and non-key attributes should not depend on other non-key attributes.

Example:

Employee
┌────┬────────────┬───────────────┐
│ ID │ Department │ DeptHead      │
└────┴────────────┴───────────────┘

If:

ID → Department
Department → DeptHead

then:

ID → DeptHead

is a transitive dependency.

Move department information into its own table.

Easy memory trick: 1NF → no repeating/multi-valued groups 2NF → no partial dependency 3NF → no transitive dependency

---

5. What is the difference between SQL and NoSQL?

SQL databases generally use the relational model, where data is organized into tables and relationships are represented using keys.

NoSQL is a broad category of non-relational database systems. It includes several different models:

• Document databases — MongoDB
• Key-value databases — Redis
• Wide-column databases — Cassandra
• Graph databases — Neo4j

	SQL / Relational	NoSQL
Data model	Tables and relationships	Documents, key-value, wide-column, graph, etc.
Schema	Often structured/schema-defined	Often more flexible, depending on system
JOINs	Strong relational support	Varies significantly
Transactions	Strong transaction support	Varies by database
Scaling	Can scale horizontally and vertically	Many are designed for horizontal scaling
Typical use	Complex relational queries, OLTP	Large-scale distributed workloads, flexible data models

SQL                         NoSQL

Table                       Document
┌────┬──────┐               {
│ ID │ Name │                 "id": 1,
├────┼──────┤                 "name": "Ali"
│ 1  │ Ali  │               }
└────┴──────┘

                            Key → Value
                            user:101 → Ali

There is no universal “better” choice.

Remember: Choose the database model based on the workload, consistency requirements, query patterns, and scaling needs.

---

6. What is a Correlated Subquery?

A correlated subquery is a subquery that references a column from the outer query.

Example:

SELECT e.name
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

The inner query uses:

e.department_id

from the outer query.

Conceptually:

Employee 1
   ↓
Find average of Employee 1's department
   ↓
Compare salary

Employee 2
   ↓
Find average of Employee 2's department
   ↓
Compare salary

Employee 3
   ↓
...

That’s why it is called correlated — the inner query depends on the current row of the outer query.

A correlated subquery may be less efficient than an equivalent JOIN or window-function solution, but modern database optimizers can sometimes transform or optimize it. So don’t assume it literally executes once per row in every DBMS.

Remember: Correlated subquery → inner query depends on the outer query.

---

7. What is Database Sharding?

Sharding splits the rows of a large database across multiple independent database servers. It is a form of horizontal partitioning.

Example:

              Users
        100 million rows
               │
       ┌───────┼───────┐
       ↓       ↓       ↓
    Shard A Shard B Shard C
    1–33M   34–66M   67–100M

Each shard stores only part of the data.

Why shard?

One server
   │
   ├── CPU limit
   ├── Memory limit
   └── Storage / I/O limit
          ↓
      Sharding
          ↓
Multiple servers

Sharding can distribute:

• Read workload
• Write workload
• Storage
• Processing

The downside

Cross-shard operations become more complicated.

Query
  │
  ├──> Shard A
  ├──> Shard B
  └──> Shard C
          ↓
     Merge results

Cross-shard JOINs, transactions, rebalancing, and choosing a good shard key can be challenging.

Remember: Sharding = split rows across multiple servers.

---

8. What are the three levels of Data Abstraction?

Database systems commonly describe three levels of data abstraction:

1. Physical / Internal Level

Describes how data is physically stored.

Disk
 ↓
Files
 ↓
Pages / Blocks
 ↓
Indexes

Users normally don’t need to know these implementation details.

2. Logical / Conceptual Level

Describes what data exists and how it is related.

Students
   │
   ├── student_id
   ├── name
   └── course_id

Database designers mainly work at this level.

3. View / External Level

Describes what a particular user or application sees.

Database
    │
    ├── Student View
    │      └── name, course
    │
    └── Admin View
           └── name, salary, department

This allows different users to see different parts of the same database.

Remember: Physical → How is it stored? Logical → What is stored? View → What does the user see?

---

9. What is a Self-Join?

A self-join is when a table is joined with itself.

The table is given different aliases so that its rows can be compared with one another.

A classic example is an employee-manager relationship:

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id;

The same table is being used twice:

              employees
              /       \
             /         \
          e (employee)  m (manager)
             │             │
             └── manager_id
                    ↓
               employee_id

Example:

Employees
┌────┬──────┬────────────┐
│ ID │ Name │ Manager_ID │
├────┼──────┼────────────┤
│ 1  │ Ali  │ NULL       │
│ 2  │ Bob  │ 1          │
│ 3  │ Cam  │ 1          │
└────┴──────┴────────────┘

Result:

Bob → Ali
Cam → Ali

Self-joins are useful for hierarchical or row-to-row relationships such as employees/managers and category hierarchies.

---

10. What is a Database Schema vs. Instance?

• Schema — the structure or blueprint of the database.
• Instance — the actual data stored in the database at a particular point in time.

SCHEMA                         INSTANCE

Tables                         Current rows
Columns                        Current values
Constraints                    Current data
Relationships                  Current records
   │                              │
   └────── blueprint ────────────┘

For example:

Schema:
Students(id, name, age)

Instance today:
101, Ali, 21
102, Bob, 22

Instance tomorrow:
101, Ali, 21
102, Bob, 23
103, Cam, 20

The schema may remain unchanged while the instance changes constantly.

Remember: Schema = structure. Instance = data at a particular time.

---

11. What is Denormalization?

Denormalization is the intentional introduction of controlled redundancy into a database to improve performance or simplify certain queries.

Suppose normalized tables require a JOIN:

Orders ──JOIN──> Customers
   │                 │
   └─────────────────┘

A denormalized design might store the customer’s name directly in the order:

Orders
┌─────────┬─────────────┬───────────────┐
│ OrderID │ Customer_ID │ Customer_Name │
└─────────┴─────────────┴───────────────┘

Now some queries can avoid a JOIN.

Trade-off

Normalization
     │
     ├── Less redundancy
     ├── Better consistency
     └── More JOINs may be required


Denormalization
     │
     ├── Faster/simpler reads in some workloads
     ├── More redundancy
     └── More effort to keep data consistent

Denormalization should be a deliberate performance decision, usually based on measured workload requirements.

Remember: Denormalization = controlled redundancy for a reason.

---

12. What is a Trigger?

A trigger is database-side code that automatically executes when a specified event occurs.

Typical events include:

• INSERT
• UPDATE
• DELETE

Depending on the DBMS, triggers can execute BEFORE, AFTER, or sometimes INSTEAD OF an operation.

Example:

CREATE TRIGGER log_salary_changes
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO audit_log
    (employee_id, old_salary, new_salary)
VALUES
    (OLD.id, OLD.salary, NEW.salary);

Conceptually:

UPDATE employees
       ↓
Trigger fires automatically
       ↓
Read OLD / NEW values
       ↓
Insert audit record

Triggers are useful for:

• Auditing
• Maintaining derived information
• Enforcing certain database-side rules

However, overusing triggers can make application behavior harder to understand and debug.

Remember: Trigger → automatic database action caused by an event.

---

13. What is Data Integrity?

Data integrity means maintaining the accuracy, consistency, validity, and reliability of data throughout its lifetime.

Important forms include:

Entity Integrity

Every row should have a valid unique identifier.

Primary Key
     ↓
Unique + NOT NULL
     ↓
Identifies each row

Referential Integrity

A foreign key should reference a valid related row, according to the defined constraints.

Customers
ID = 101
   ▲
   │
   │ Foreign Key
   │
Orders
Customer_ID = 101

This prevents invalid references such as an order pointing to a nonexistent customer.

Domain Integrity

Values must satisfy their allowed type, format, range, or constraints.

Age
 ├── 25 ✓
 ├── 40 ✓
 └── -5 ✗

User-defined / Business Integrity

Rules specific to the application or organization.

Examples:

salary >= minimum_salary
quantity > 0
start_date <= end_date

Databases can enforce these rules using:

• PRIMARY KEY
• FOREIGN KEY
• NOT NULL
• UNIQUE
• CHECK
• Appropriate data types and other constraints

Remember: Data integrity = keeping data correct, valid, and consistent.

---

14. What is a Cursor?

A cursor is a database mechanism that allows a result set to be processed row by row.

Normally, SQL is set-oriented:

1000 rows
   ↓
One SQL operation
   ↓
Result

With a cursor:

1000 rows
   ↓
FETCH row 1 → process
FETCH row 2 → process
FETCH row 3 → process
...
FETCH row 1000 → process

Typical cursor lifecycle:

DECLARE
   ↓
OPEN
   ↓
FETCH
   ↓
Process row
   ↓
FETCH next row
   ↓
...
   ↓
CLOSE
   ↓
DEALLOCATE (where applicable)

Cursors can be useful when genuinely row-by-row processing is required, but they often have more overhead than set-based SQL.

Rule of thumb: If a problem can be solved efficiently with a set-based SQL operation, prefer that over a cursor.

---

15. What is the CAP Theorem?

The CAP theorem describes a trade-off in distributed data systems involving three properties:

• Consistency (C) — after a successful operation, reads observe a consistent/latest state according to the system’s consistency model.
• Availability (A) — every request to a non-failing node receives a response, even when the system cannot guarantee the latest data.
• Partition Tolerance (P) — the system continues operating despite network communication failures between nodes.

             CAP
              │
       ┌──────┼──────┐
       ↓      ↓      ↓
 Consistency Availability Partition
                     Tolerance

The important point is that network partitions can happen in a distributed system. When a partition occurs, a system has to make a trade-off between maintaining consistency and maintaining availability.

Normal operation
       ↓
Network partition
       ↓
┌───────────────┐
│ Choose trade- │
│ off between C │
│ and A         │
└───────────────┘

CP

A CP-oriented system prefers consistency during a partition and may reject or delay some requests.

Partition
   ↓
Cannot safely guarantee consistency
   ↓
Reject / wait for some requests

AP

An AP-oriented system prioritizes availability and may return data that is temporarily stale or inconsistent.

Partition
   ↓
Continue serving requests
   ↓
Data may temporarily diverge

The CAP theorem is specifically about behavior when a network partition occurs. It does not simply mean that every distributed system permanently chooses exactly two properties.

Remember: During a partition, you generally have to trade Consistency against Availability.