When to use SQL vs NoSQL in HLD - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Choosing the right database for structured data

You have a project that requires storing customer information with fixed fields like name, email, and phone number. Which type of database is best suited for this?

A Use a NoSQL database because it handles fixed fields better.

B Use a SQL database because it is designed for structured data with fixed schemas.

C Use a NoSQL database because it is always faster for any data.

D Use a file system instead of a database for structured data.

💡 Hint

Think about which database type enforces a fixed structure for data.

SQL databases are designed to handle structured data with fixed schemas, making them ideal for customer information with defined fields.

🧠 Conceptual

intermediate

Handling flexible data formats

Your application needs to store user-generated content that can vary in format and fields, such as comments, images, and tags. Which database type fits best?

A SQL database because it handles flexible data well.

B Flat files because they are easier to manage than databases.

C In-memory cache because it stores data permanently.

D NoSQL database because it supports flexible and changing data structures.

💡 Hint

Consider which database type allows storing data without a fixed schema.

NoSQL databases are designed to store flexible and varying data formats, making them suitable for user-generated content with different fields.

❓ query_result

advanced

Querying relational data with SQL

Given a SQL table Orders with columns OrderID, CustomerID, and Amount, what is the result of this query?

SELECT CustomerID, SUM(Amount) AS Total FROM Orders GROUP BY CustomerID;

HLD

SELECT CustomerID, SUM(Amount) AS Total FROM Orders GROUP BY CustomerID;

A A list of customers with the total amount of their orders summed up.

B A list of orders with individual amounts.

C An error because GROUP BY is missing columns.

D A list of customers with the average amount of their orders.

💡 Hint

GROUP BY groups rows by customer and SUM adds amounts per group.

The query groups orders by customer and sums the amounts, returning total spent per customer.

🧠 Conceptual

advanced

Scaling databases for high traffic

Your app expects millions of users and needs to scale horizontally across many servers. Which database type is generally easier to scale horizontally?

A Flat files because they can be copied to many servers easily.

B SQL databases because they are designed for horizontal scaling by default.

C NoSQL databases because they are built to scale horizontally across many servers.

D In-memory databases because they store data on disk.

💡 Hint

Think about which database type is designed for easy distribution across servers.

NoSQL databases are generally designed for horizontal scaling, allowing data to be spread across many servers easily.

🧠 Conceptual

expert

Choosing database type for complex transactions

You need to build a banking system that requires complex transactions with strict consistency and rollback capabilities. Which database type is most suitable?

A SQL database because it supports ACID transactions ensuring consistency.

B NoSQL database because it handles large volumes of data quickly.

C Flat files because they are simple to manage for transactions.

D In-memory cache because it is fast for transactions.

💡 Hint

Consider which database type guarantees transaction safety and consistency.

SQL databases support ACID properties, which are essential for complex transactions requiring consistency and rollback.

Practice

1. Which type of database is best suited for data with a fixed schema and strong relationships between tables?

easy

A. Flat file storage

B. NoSQL database

C. SQL database

D. In-memory cache

Solution

1. Step 1: Understand data structure needs

   Data with fixed schema and strong relationships fits well with structured tables.

2. Step 2: Match database type to structure

   SQL databases use tables and enforce schema, ideal for such data.

3. Final Answer:

   SQL database -> Option C

4. Quick Check:

   Structured data = SQL database [OK]

Hint: Fixed schema and relations? Choose SQL [OK]

Common Mistakes:

• Choosing NoSQL for strictly structured data
• Confusing flat files with databases
• Assuming in-memory cache stores persistent data

2. Which of the following is the correct way to describe NoSQL databases?

easy

A. They always use tables with fixed columns

B. They are designed for flexible, schema-less data storage

C. They require strict ACID transactions for all operations

D. They cannot handle large volumes of data

Solution

1. Step 1: Identify NoSQL characteristics

   NoSQL databases allow flexible, schema-less data models.

2. Step 2: Eliminate incorrect options

   They do not require fixed tables or strict ACID for all operations and can handle large data.

3. Final Answer:

   They are designed for flexible, schema-less data storage -> Option B

4. Quick Check:

   NoSQL = flexible schema [OK]

Hint: No fixed schema? NoSQL fits best [OK]

Common Mistakes:

• Thinking NoSQL always uses tables
• Assuming NoSQL can't scale
• Confusing ACID requirements with SQL only

3. Consider a social media app storing user posts that can have varying fields like text, images, or videos. Which database type will likely handle this data best?

medium

A. SQL database with fixed tables

B. CSV files for storage

C. Relational database with strict schema

D. NoSQL document database

Solution

1. Step 1: Analyze data variability

   User posts have varying fields, so schema flexibility is needed.

2. Step 2: Choose database type matching flexibility

   NoSQL document databases store flexible, nested data well.

3. Final Answer:

   NoSQL document database -> Option D

4. Quick Check:

   Variable fields = NoSQL document DB [OK]

Hint: Varying fields? Use NoSQL document DB [OK]

Common Mistakes:

• Choosing fixed-schema SQL for flexible data
• Using CSV which lacks query power
• Confusing relational DB with NoSQL

4. A developer tries to store rapidly changing sensor data with a fixed schema in a SQL database but faces performance issues. What is the likely cause and best fix?

medium

A. Fixed schema causes overhead; use a NoSQL database for flexibility

B. SQL databases are slow; switch to CSV files

C. Sensor data is small; use in-memory cache only

D. NoSQL databases cannot handle sensor data

Solution

1. Step 1: Identify problem with fixed schema and rapid changes

   Fixed schema in SQL causes overhead with frequent schema updates or inserts.

2. Step 2: Recommend flexible schema database

   NoSQL databases handle rapid, flexible data better, improving performance.

3. Final Answer:

   Fixed schema causes overhead; use a NoSQL database for flexibility -> Option A

4. Quick Check:

   Rapid changes + fixed schema = use NoSQL [OK]

Hint: Rapid, flexible data? NoSQL is better [OK]

Common Mistakes:

• Switching to CSV which lacks performance
• Assuming NoSQL can't handle sensor data
• Ignoring schema flexibility importance

5. You are designing a global e-commerce platform that needs to store user profiles, product catalogs, and handle millions of transactions per day. Which database strategy is best?

hard

A. Use SQL for transactions and NoSQL for product catalogs and user profiles

B. Use only SQL for all data to keep consistency

C. Use only NoSQL for all data to maximize flexibility

D. Use flat files for product catalogs and SQL for transactions

Solution

1. Step 1: Analyze data types and needs

   Transactions require strong consistency (SQL), catalogs and profiles need flexibility and scale (NoSQL).

2. Step 2: Combine database types for best fit

   Using SQL for transactions ensures ACID compliance; NoSQL handles flexible, large-scale data efficiently.

3. Final Answer:

   Use SQL for transactions and NoSQL for product catalogs and user profiles -> Option A

4. Quick Check:

   Mixed data needs = hybrid SQL + NoSQL [OK]

Hint: Mix SQL for transactions, NoSQL for flexible data [OK]

Common Mistakes:

• Using only SQL limits flexibility and scale
• Using only NoSQL risks transaction consistency
• Using flat files lacks query and scale support