Database Fundamentals practice questions

A user needs to store customer information including name, address, and order history. Which database type is most appropriate?

Trap 1: Hierarchical database

Hierarchical databases organize data in a tree structure, which is inflexible and not suitable for many-to-many relationships.

Trap 2: Flat-file database

Flat-file databases store data in a single table, leading to redundancy and difficulty in querying related data.

Trap 3: NoSQL database

NoSQL databases are designed for unstructured or semi-structured data, not ideal for structured relational data like customer orders.

A
Hierarchical database
Why wrong: Hierarchical databases organize data in a tree structure, which is inflexible and not suitable for many-to-many relationships.
B
Flat-file database
Why wrong: Flat-file databases store data in a single table, leading to redundancy and difficulty in querying related data.
C
NoSQL database
Why wrong: NoSQL databases are designed for unstructured or semi-structured data, not ideal for structured relational data like customer orders.
D
Relational database
Relational databases use tables, keys, and relationships to efficiently store and query structured data like customer info and orders.

Question 2hardmultiple choice

A database analyst is designing a schema for a library system. Each book can have multiple authors, and each author can write multiple books. Which relationship type should be used?

Trap 1: Many-to-one

Many-to-one is the inverse of one-to-many and does not solve the requirement for multiple authors per book.

Trap 2: One-to-many

One-to-many would allow one author to have many books but not many authors per book.

Trap 3: One-to-one

One-to-one would incorrectly restrict each book to one author and each author to one book.

A
Many-to-one
Why wrong: Many-to-one is the inverse of one-to-many and does not solve the requirement for multiple authors per book.
B
One-to-many
Why wrong: One-to-many would allow one author to have many books but not many authors per book.
C
Many-to-many
Many-to-many is correct because a book can have multiple authors and an author can write multiple books, requiring a junction table.
D
One-to-one
Why wrong: One-to-one would incorrectly restrict each book to one author and each author to one book.

Question 3mediummultiple choice

A database administrator is troubleshooting a slow query on a large table. Which index type would improve performance for an exact match search on a single column?

Trap 1: Clustered index

Clustered indexes sort and store data physically, which may not always improve performance for exact matches.

Trap 2: Bitmap index

Bitmap indexes are efficient for columns with few distinct values, not for high-cardinality columns.

Trap 3: Hash index

Hash indexes are fast for exact matches but do not support range queries.

A
Clustered index
Why wrong: Clustered indexes sort and store data physically, which may not always improve performance for exact matches.
B
B-tree index
B-tree indexes are ideal for exact match and range queries, providing fast lookup.
C
Bitmap index
Why wrong: Bitmap indexes are efficient for columns with few distinct values, not for high-cardinality columns.
D
Hash index
Why wrong: Hash indexes are fast for exact matches but do not support range queries.

Question 4mediummultiple choice

A developer writes a query: SELECT * FROM Employees WHERE Department = 'Sales'. Which statement about this query is true?

Trap 1: It sorts results by Department

There is no ORDER BY clause, so results are not sorted.

Trap 2: It returns only the Department column

SELECT * returns all columns, not just Department.

Trap 3: It returns all rows from the Employees table

The WHERE clause filters only rows where Department is 'Sales', so not all rows are returned.

A
It sorts results by Department
Why wrong: There is no ORDER BY clause, so results are not sorted.
B
It returns all columns for employees in Sales
SELECT * returns all columns, and the WHERE clause filters for Sales department.
C
It returns only the Department column
Why wrong: SELECT * returns all columns, not just Department.
D
It returns all rows from the Employees table
Why wrong: The WHERE clause filters only rows where Department is 'Sales', so not all rows are returned.

Question 5easymultiple choice

A database designer wants to ensure that no two employees have the same email address. Which constraint should be applied to the Email column?

Trap 1: PRIMARY KEY

PRIMARY KEY forces uniqueness and non-null, but is usually applied to the employee ID, not email.

Trap 2: CHECK

CHECK constraint validates data based on a condition, but does not prevent duplicates.

Trap 3: FOREIGN KEY

FOREIGN KEY links tables and enforces referential integrity, not uniqueness.

A
UNIQUE
UNIQUE constraint ensures no duplicate email addresses.
B
PRIMARY KEY
Why wrong: PRIMARY KEY forces uniqueness and non-null, but is usually applied to the employee ID, not email.
C
CHECK
Why wrong: CHECK constraint validates data based on a condition, but does not prevent duplicates.
D
FOREIGN KEY
Why wrong: FOREIGN KEY links tables and enforces referential integrity, not uniqueness.

Question 6hardmultiple choice

A database administrator notices that a transaction that updates two tables is failing halfway, leaving data inconsistent. Which property of ACID ensures that the database returns to its original state if the transaction fails?

Trap 1: Consistency

Consistency ensures that data is valid before and after a transaction, but the rollback is due to atomicity.

Trap 2: Durability

Durability ensures committed transactions persist, not rollback of uncommitted ones.

Trap 3: Isolation

Isolation ensures concurrent transactions do not affect each other, not rollback.

A
Consistency
Why wrong: Consistency ensures that data is valid before and after a transaction, but the rollback is due to atomicity.
B
Durability
Why wrong: Durability ensures committed transactions persist, not rollback of uncommitted ones.
C
Isolation
Why wrong: Isolation ensures concurrent transactions do not affect each other, not rollback.
D
Atomicity
Atomicity guarantees that all parts of a transaction succeed or none do, allowing rollback on failure.

Question 7mediummulti select

A database administrator is designing a normalized database for an e-commerce site. Which TWO actions represent best practices for normalization?

Trap 1: Creating separate tables for each customer order without linking…

This duplicates customer data and lacks referential integrity.

Trap 2: Storing all product details in a single column as a list

Storing multiple values in one column violates first normal form.

Trap 3: Storing calculated totals in the same table to avoid joins

Calculated values should be derived to avoid update anomalies.

A
Creating separate tables for each customer order without linking them
Why wrong: This duplicates customer data and lacks referential integrity.
B
Storing all product details in a single column as a list
Why wrong: Storing multiple values in one column violates first normal form.
C
Removing duplicate data from tables by creating separate tables for each entity
This reduces redundancy and improves data integrity.
D
Splitting customer information into Customers and Orders tables linked by a foreign key
This reduces redundancy and is a key normalization step.
E
Storing calculated totals in the same table to avoid joins
Why wrong: Calculated values should be derived to avoid update anomalies.

Question 8hardmulti select

A database administrator is setting up user permissions. Which THREE actions follow the principle of least privilege?

Trap 1: Allowing remote access from any IP address for flexibility

Allowing any IP is too permissive; should be restricted to needed addresses.

Trap 2: Granting DBA privileges to all developers for convenience

DBA privileges exceed what developers need, violating least privilege.

A
Granting SELECT only on specific tables to a read-only user
This restricts the user to only read necessary data.
B
Revoking INSERT and DELETE from a user who only needs to view data
Removing unneeded write permissions follows least privilege.
C
Allowing remote access from any IP address for flexibility
Why wrong: Allowing any IP is too permissive; should be restricted to needed addresses.
D
Granting DBA privileges to all developers for convenience
Why wrong: DBA privileges exceed what developers need, violating least privilege.
E
Granting access to a specific database schema rather than the entire server
Limiting access to necessary schemas reduces risk.

Question 9easymultiple choice

Refer to the exhibit. A user executes: SELECT AVG(Salary) FROM Employees; What is the result?

Network Topology

Trap 1: 50000

50000 is the minimum salary.

Trap 2: 165000

165000 is the sum, not the average.

Trap 3: 60000

60000 is the maximum salary.

A
55000
The average is (50000+60000+55000)/3 = 55000.
B
50000
Why wrong: 50000 is the minimum salary.
C
165000
Why wrong: 165000 is the sum, not the average.
D
60000
Why wrong: 60000 is the maximum salary.

Question 10mediummultiple choice

Refer to the exhibit. What is the output of the SELECT statement?

Exhibit

Refer to the exhibit.

```
INSERT INTO Products (ProductID, ProductName, Price) VALUES (1, 'Widget', 10);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (2, 'Gadget', 20);
INSERT INTO Products (ProductID, ProductName, Price) VALUES (3, 'Widget', 15);
SELECT ProductName, COUNT(*) FROM Products GROUP BY ProductName HAVING COUNT(*) > 1;
```

Trap 1: Widget, 2

This is the correct data but the output format in a typical SQL client would show column names; however, the exact output includes both columns. Option D better matches the result set format.

Trap 2: Widget, 2; Gadget, 1

HAVING COUNT(*) > 1 filters out Gadget because its count is 1.

Trap 3: Widget, 2; Gadget, 1; Widget, 1

GROUP BY groups duplicates, so Widget appears once with count 2, and Gadget appears once with count 1, but HAVING filters out Gadget.

A
Widget, 2
Why wrong: This is the correct data but the output format in a typical SQL client would show column names; however, the exact output includes both columns. Option D better matches the result set format.
B
Widget, 2
The GROUP BY groups by ProductName, and HAVING filters groups with count > 1, so only Widget (count 2) is returned.
C
Widget, 2; Gadget, 1
Why wrong: HAVING COUNT(*) > 1 filters out Gadget because its count is 1.
D
Widget, 2; Gadget, 1; Widget, 1
Why wrong: GROUP BY groups duplicates, so Widget appears once with count 2, and Gadget appears once with count 1, but HAVING filters out Gadget.

Question 11hardmultiple choice

A small business uses a MySQL database to manage inventory and sales. The database has two tables: Products (ProductID, ProductName, QuantityInStock) and Sales (SaleID, ProductID, QuantitySold, SaleDate). The business runs a nightly script that updates QuantityInStock by subtracting QuantitySold from the Products table based on the day's sales. Recently, the inventory levels have become inaccurate. For example, a product shows negative stock even though no sales occurred that day. The database administrator suspects the issue is related to how transactions are handled. The nightly script runs multiple UPDATE statements in a loop. If the script fails partway through, some products' stock is updated while others are not, leaving inconsistent data. The administrator wants to ensure that either all updates succeed or none do, and that the script does not interfere with daytime operations. Which action should the administrator take?

Trap 1: Use a stored procedure that updates all products in one statement…

Without error handling, a failure could still leave partial updates; a stored procedure alone does not guarantee atomicity without transaction control.

Trap 2: Increase the frequency of the script to run every hour with smaller…

Running more frequently does not solve the partial update problem; it may increase contention.

Trap 3: Remove the transaction and use individual UPDATE statements with…

Without a transaction, partial updates can still occur, leading to inconsistency.

A
Use a stored procedure that updates all products in one statement without error handling
Why wrong: Without error handling, a failure could still leave partial updates; a stored procedure alone does not guarantee atomicity without transaction control.
B
Increase the frequency of the script to run every hour with smaller batches
Why wrong: Running more frequently does not solve the partial update problem; it may increase contention.
C
Remove the transaction and use individual UPDATE statements with error logging
Why wrong: Without a transaction, partial updates can still occur, leading to inconsistency.
D
Wrap all UPDATE statements in a single transaction with READ COMMITTED isolation level and add error handling to roll back on failure
This ensures atomicity (all or nothing) and prevents interference from other transactions.

Question 12mediummultiple choice

A company needs to store customer orders with items and quantities. The database currently has a table 'Customers' and a table 'Products'. Which of the following is the best way to represent the many-to-many relationship between orders and products?

Trap 1: Create a single table containing all customer, order, and product…

This leads to massive redundancy and anomalies.

Trap 2: Store a JSON list of product IDs in an OrderProducts column in the…

While possible, this is not a normalized relational design and complicates queries.

Trap 3: Add columns Product1, Product2, Product3 to the Orders table.

This violates 1NF and limits the number of products per order.

A
Create a single table containing all customer, order, and product information.
Why wrong: This leads to massive redundancy and anomalies.
B
Create an Order_Items table with foreign keys to Orders and Products, plus a Quantity column.
This correctly implements a junction table for a many-to-many relationship.
C
Store a JSON list of product IDs in an OrderProducts column in the Orders table.
Why wrong: While possible, this is not a normalized relational design and complicates queries.
D
Add columns Product1, Product2, Product3 to the Orders table.
Why wrong: This violates 1NF and limits the number of products per order.

Question 13hardmulti select

Which THREE of the following are valid database integrity constraints?

Trap 1: INDEX

Indexes are not constraints; they improve query performance.

Trap 2: CONDITION

This is not a standard SQL constraint name; the correct term is CHECK.

A
UNIQUE
Ensures all values in a column are distinct.
B
INDEX
Why wrong: Indexes are not constraints; they improve query performance.
C
FOREIGN KEY
Ensures referential integrity between tables.
D
CONDITION
Why wrong: This is not a standard SQL constraint name; the correct term is CHECK.
E
PRIMARY KEY
Ensures uniqueness and non-null for a column or set of columns.

Question 14hardmultiple choice

Refer to the exhibit. A database administrator runs the following query:

SELECT c.Name, SUM(o.Quantity) AS TotalItems FROM Customers c LEFT JOIN Orders o ON c.ID = o.CustomerID GROUP BY c.Name;

What is the result for 'Alice'?

Network Topology

Trap 1: 2

This is the quantity of only the Laptop order.

Trap 2: NULL

NULL would appear only if Alice had no orders, but she has two.

Trap 3: 5

This is the quantity of only the Mouse order.

A
2
Why wrong: This is the quantity of only the Laptop order.
B
NULL
Why wrong: NULL would appear only if Alice had no orders, but she has two.
C
5
Why wrong: This is the quantity of only the Mouse order.
D
7
SUM of both orders (2+5=7).

Question 15mediummultiple choice

A company's database administrator notices that queries against a large customer table are running slowly. The table has millions of rows and is frequently filtered by the 'last_name' column. Which of the following is the BEST way to improve query performance without changing the application code?

Trap 1: Partition the table by row count.

Partitioning organizes data into smaller segments but does not inherently accelerate searches on a specific column.

Trap 2: Increase the database server's RAM.

More RAM may improve caching but is not a targeted solution for column-based filtering; it is less effective than indexing.

Trap 3: Denormalize the table to reduce joins.

Denormalization introduces data redundancy and does not directly speed up filtering on 'last_name'.

A
Partition the table by row count.
Why wrong: Partitioning organizes data into smaller segments but does not inherently accelerate searches on a specific column.
B
Increase the database server's RAM.
Why wrong: More RAM may improve caching but is not a targeted solution for column-based filtering; it is less effective than indexing.
C
Create an index on the 'last_name' column.
An index on the filtered column enables rapid row retrieval, significantly speeding up queries.
D
Denormalize the table to reduce joins.
Why wrong: Denormalization introduces data redundancy and does not directly speed up filtering on 'last_name'.

Question 16hardmulti select

Which THREE of the following are characteristics of a relational database? (Choose THREE.)

Trap 1: The database uses a hierarchical model.

Hierarchical model is an older model, not relational.

Trap 2: The database is schema-less and flexible.

Schema-less is characteristic of NoSQL, not relational databases.

Trap 3: Data is stored as JSON documents.

JSON documents are used in document-oriented NoSQL databases.

A
The database uses a hierarchical model.
Why wrong: Hierarchical model is an older model, not relational.
B
Queries are written using SQL.
SQL is the standard language for relational databases.
C
The database is schema-less and flexible.
Why wrong: Schema-less is characteristic of NoSQL, not relational databases.
D
Data is stored as JSON documents.
Why wrong: JSON documents are used in document-oriented NoSQL databases.
E
Data is stored in tables with rows and columns.
This is the fundamental structure of a relational database.
F
ACID transactions ensure data integrity.
ACID is a key property of relational databases.

Question 17easymultiple choice

Refer to the exhibit. A database administrator executes the following query: SELECT COUNT(*) FROM employees WHERE department_id = 10; What is the result?

Exhibit

Refer to the exhibit.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    hire_date DATE
);

INSERT INTO employees VALUES (1, 'John', 'Doe', 10, '2021-01-15');
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 20, '2020-06-01');
INSERT INTO employees VALUES (3, 'Bob', 'Johnson', 10, '2022-03-22');

Trap 1: 3

3 is the total number of employees, not just those in department 10.

Trap 2: 0

There are employees with department_id 10.

Trap 3: 1

Only one employee in department 10? Actually there are two.

A
2
Employees with department_id 10 are John Doe and Bob Johnson.
B
3
Why wrong: 3 is the total number of employees, not just those in department 10.
C
0
Why wrong: There are employees with department_id 10.
D
1
Why wrong: Only one employee in department 10? Actually there are two.

Question 18mediumdrag order

Drag and drop the steps to open the Task Manager in Windows 10 into the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

5Step 5

Question 19mediummatching

Read the full NAT/PAT explanation →

Match each network type to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Small geographic area

Large geographic area

Personal devices around a person

City-sized network

Question 20easymultiple choice

A user runs a query on a database table and notices that the results contain duplicate rows. Which SQL keyword would eliminate these duplicates?

Trap 1: GROUP BY

GROUP BY groups rows, but without aggregate functions it may not eliminate duplicates in the way DISTINCT does.

Trap 2: WHERE

WHERE filters rows based on conditions but does not eliminate duplicates.

Trap 3: ORDER BY

ORDER BY sorts the result set but does not remove duplicates.

A
DISTINCT
DISTINCT ensures the result set contains only unique rows.
B
GROUP BY
Why wrong: GROUP BY groups rows, but without aggregate functions it may not eliminate duplicates in the way DISTINCT does.
C
WHERE
Why wrong: WHERE filters rows based on conditions but does not eliminate duplicates.
D
ORDER BY
Why wrong: ORDER BY sorts the result set but does not remove duplicates.