Why databases over files in SQL - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why choose databases instead of plain files?

Which of the following is a key advantage of using a database over simple file storage?

A Databases allow multiple users to access and modify data safely at the same time.

B Files automatically organize data into tables without extra setup.

C Files provide built-in data validation and indexing by default.

D Databases store data only as plain text without structure.

💡 Hint

Think about what happens when many people want to use the data at once.

Databases support concurrent access with safety mechanisms, unlike plain files which can get corrupted if multiple users write at the same time.

🧠 Conceptual

intermediate

Data retrieval speed: databases vs files

Why are databases generally faster than files when searching for specific data?

A Databases use indexes to quickly find data without scanning everything.

B Files store data in memory for instant access.

C Files automatically sort data to speed up searches.

D Databases store data as plain text making it faster to read.

💡 Hint

Think about how you find a word in a dictionary quickly.

Databases create indexes like a book's index, so they can jump directly to the needed data instead of reading everything.

❓ query_result

advanced

What is the output of this SQL query on a file-based table?

Assume a table employees stored in a database and also exported as a CSV file. The SQL query below is run on the database:

SELECT department, COUNT(*) AS count FROM employees GROUP BY department;

What is the expected output?

SQL

SELECT department, COUNT(*) AS count FROM employees GROUP BY department;

A [{"department": "Sales"}, {"department": "HR"}]

B [{"department": "Sales", "count": "5"}, {"department": "HR", "count": "3"}]

C [{"department": "Sales", "count": 5}, {"department": "HR", "count": 3}]

D SyntaxError: GROUP BY clause missing

💡 Hint

COUNT(*) returns a number, not a string. GROUP BY groups rows by department.

The query counts employees per department and returns numeric counts, not strings. Option C matches this output.

❓ schema

advanced

Which schema design supports data integrity better?

Given two options for storing customer orders, which schema better ensures data integrity?

Option 1: Store all orders in a single file with no structure.
Option 2: Use a database with separate tables for customers and orders linked by customer ID.

A Option 1 because files are simpler and less prone to errors.

B Option 2 because relational tables enforce relationships and prevent invalid data.

C Option 1 because files can be edited easily with any text editor.

D Option 2 because it stores all data in one big table without relations.

💡 Hint

Think about how to avoid mistakes like orders without customers.

Relational databases enforce foreign keys and constraints, preventing invalid data like orders linked to non-existent customers.

❓ optimization

expert

Which SQL query optimizes data retrieval best for large datasets?

Consider a large sales table with millions of rows. Which query will run fastest to find total sales per region?

A) SELECT region, SUM(amount) FROM sales GROUP BY region;

B) SELECT region, SUM(amount) FROM sales WHERE amount > 0 GROUP BY region;

C) SELECT region, SUM(amount) FROM sales GROUP BY region ORDER BY region;

D) SELECT region, SUM(amount) FROM sales;

A Runs fastest because it sums all amounts without grouping.

B Runs fastest because it groups all rows by region without extra filtering or sorting.

C Runs fastest because ordering helps the database optimize grouping.

D Runs fastest because it filters out zero amounts before grouping.

💡 Hint

Filtering data before grouping can reduce the amount of data processed.

Filtering out zero amounts reduces rows to group, speeding up aggregation. Option D uses a WHERE clause to filter before grouping.