Knowledge + Practice

CCNA Database Fundamentals Questions

72 questions · Database Fundamentals · All types, answers revealed

Practice these questions Domain overview All questions

1

MCQmedium

A small e-commerce website uses a relational database to manage its products and orders. The most common query is retrieving a product by its unique product ID. This query is executed thousands of times per minute. The database currently has no indexes, and the query is slow, causing user-facing delays. The database administrator wants to improve performance with minimal downtime and cost. Which action should be taken first?

A.Partition the table by product category

B.Add an index on the product ID column

C.Increase the server's memory to allow more caching

D.Change the database to a NoSQL system for faster key-value access

AnswerB

An index on the searched column accelerates lookups.

Why this answer

Adding an index on the product ID column creates a B-tree data structure that allows the database to locate rows using a logarithmic search instead of a full table scan. Since the query is executed thousands of times per minute and the table has no indexes, this single-column index directly addresses the bottleneck with minimal downtime and cost, as it requires only a CREATE INDEX statement.

Exam trap

The trap here is that candidates often choose increasing memory (Option C) because they confuse caching with indexing, not realizing that caching only helps after the first access and does not prevent full table scans on cache misses or for new data.

How to eliminate wrong answers

Option A is wrong because partitioning the table by product category would add complexity and overhead without directly speeding up lookups by product ID, and it requires significant schema changes and downtime. Option C is wrong because increasing server memory may improve caching of frequently accessed data, but it does not eliminate the need for a full table scan on a cold cache or first access, and it incurs hardware cost without addressing the root cause. Option D is wrong because migrating to a NoSQL system would require a complete architectural overhaul, significant downtime, and application rewrites, which contradicts the requirement for minimal downtime and cost.

Practice this question →

2

MCQhard

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'?

A.2

B.NULL

C.5

D.7

AnswerD

SUM of both orders (2+5=7).

Why this answer

The LEFT JOIN ensures that Alice's record from the Customers table is retained even if there are no matching rows in Orders. The SUM(o.Quantity) aggregates the Quantity values from the joined Orders rows for Alice. Since Alice has two orders with quantities 2 and 5, the sum is 7, making option D correct.

Exam trap

The trap here is that candidates may mistakenly pick the quantity of a single order (2 or 5) instead of computing the SUM, or incorrectly assume that a LEFT JOIN would produce NULL for Alice when she actually has matching orders.

How to eliminate wrong answers

Option A is wrong because 2 is only the quantity of one of Alice's orders, not the total sum of all her order quantities. Option B is wrong because NULL would only appear if there were no matching Orders rows for Alice, but she has two orders (IDs 101 and 102) with quantities 2 and 5. Option C is wrong because 5 is the quantity of only the second order, not the sum of both orders.

Practice this question →

3

MCQeasy

A database table contains repeating groups of fields for multiple phone numbers per customer. Which normal form is being violated?

A.Second Normal Form

B.Third Normal Form

C.First Normal Form

D.No violation

AnswerC

Repeating groups violate 1NF.

Why this answer

First Normal Form (1NF) requires that each column in a table contains atomic (indivisible) values and that there are no repeating groups of columns. Storing multiple phone numbers per customer in a single row, such as Phone1, Phone2, Phone3, violates 1NF because it creates a repeating group of fields. To comply with 1NF, the phone numbers should be stored in a separate child table with one row per phone number per customer.

Exam trap

CompTIA often tests the misconception that repeating groups are a violation of Second or Third Normal Form, but the trap is that repeating groups are the defining characteristic of a First Normal Form violation, not a higher normal form.

How to eliminate wrong answers

Option A is wrong because Second Normal Form (2NF) addresses partial dependencies on a composite key, not repeating groups; it assumes 1NF is already satisfied. Option B is wrong because Third Normal Form (3NF) deals with transitive dependencies (non-key attributes depending on other non-key attributes), which is a higher-level concern after 1NF and 2NF are met. Option D is wrong because the presence of repeating groups is a clear violation of First Normal Form, so the table is not in 1NF.

Practice this question →

4

MCQmedium

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?

A.Create a single table containing all customer, order, and product information.

B.Create an Order_Items table with foreign keys to Orders and Products, plus a Quantity column.

C.Store a JSON list of product IDs in an OrderProducts column in the Orders table.

D.Add columns Product1, Product2, Product3 to the Orders table.

AnswerB

This correctly implements a junction table for a many-to-many relationship.

Why this answer

Option B is correct because it creates a junction table (Order_Items) that resolves the many-to-many relationship between Orders and Products. The Order_Items table includes foreign keys referencing both the Orders and Products tables, plus a Quantity column to store the number of each product in an order, which is the standard normalized relational database design.

Exam trap

The trap here is that candidates may think storing data in a single table or using a JSON column is simpler and acceptable, but the FC0-U61 exam tests the fundamental principle of normalization and the proper use of junction tables to maintain data integrity and avoid redundancy.

How to eliminate wrong answers

Option A is wrong because it violates database normalization principles by combining all data into a single table, leading to massive data redundancy and update anomalies. Option C is wrong because storing a JSON list of product IDs in a single column breaks first normal form (1NF) by storing multiple values in one column, making it impossible to enforce referential integrity or efficiently query individual products. Option D is wrong because adding fixed columns (Product1, Product2, Product3) cannot handle a variable number of products per order and violates the principle of atomicity, as it imposes an arbitrary limit and wastes space.

Practice this question →

5

MCQmedium

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?

A.Partition the table by row count.

B.Increase the database server's RAM.

C.Create an index on the 'last_name' column.

D.Denormalize the table to reduce joins.

AnswerC

An index on the filtered column enables rapid row retrieval, significantly speeding up queries.

Why this answer

Creating an index on the 'last_name' column allows the database to locate rows matching filter conditions without scanning the entire table. This dramatically reduces I/O and CPU overhead for queries that filter by last_name, directly addressing the performance bottleneck without requiring any changes to application code.

Exam trap

The trap here is that candidates often choose 'Increase the database server's RAM' because they think more memory will speed up all queries, but without an index the database still must read every row from disk or memory, and RAM alone cannot eliminate the need for a full table scan.

How to eliminate wrong answers

Option A is wrong because partitioning by row count does not inherently speed up queries filtered by a specific column; it only splits data into smaller physical segments, which can even increase overhead if the partition key does not align with the filter column. Option B is wrong because increasing RAM may help caching but does not change the fundamental need for a full table scan when no index exists; it is a hardware band-aid that does not optimize query execution paths. Option D is wrong because denormalization reduces joins but does not address the core issue of slow filtering on last_name; it introduces data redundancy and maintenance complexity without improving lookup speed on that column.

Practice this question →

6

MCQhard

Refer to the exhibit. A database contains tables Customers and Orders. Based on the query, what is the purpose of the INNER JOIN clause?

A.To return only customers who have at least one order in New York

B.To create a Cartesian product of customers and orders

C.To combine all rows from both tables regardless of match

D.To include all customers even if they have no orders

AnswerA

The INNER JOIN ensures only matching CustomerIDs are returned, filtered by New York.

Why this answer

INNER JOIN retrieves records where the CustomerID matches in both tables, ensuring only customers with orders from New York are listed (Option C). Option A is wrong because LEFT JOIN would include all customers. Option B is wrong because it returns matching rows, not all rows.

Option D describes a CROSS JOIN.

Practice this question →

7

MCQhard

A large retail chain operates a data warehouse that combines sales data from multiple source databases. The warehouse is designed using a highly normalized snowflake schema. Analysts frequently run complex queries that aggregate sales across many dimensions (e.g., time, product, store). Recently, the queries have become very slow, often taking hours to complete. The data warehouse team suspects the normalization is causing many joins, degrading performance. The business users need faster reporting. The team must decide on a course of action that balances query performance with maintainability. Which technique is most likely to improve reporting speed without significantly compromising data integrity?

A.Denormalize some tables by merging fact and dimension tables

B.Increase the server's CPU and memory resources

C.Replace the relational warehouse with a NoSQL document store

D.Add more indexes on all foreign key columns

AnswerA

Reduces joins, improving read performance for aggregations.

Why this answer

Denormalizing some tables by merging fact and dimension tables reduces the number of joins required for complex analytical queries, directly addressing the performance bottleneck caused by the highly normalized snowflake schema. This technique improves query speed by storing redundant data in a star-like schema, which is a common optimization for data warehouses where read performance is prioritized over write efficiency, while still maintaining data integrity through careful design and ETL processes.

Exam trap

CompTIA often tests the misconception that adding more indexes always improves query performance, but in a highly normalized schema with many joins, the overhead of maintaining and scanning multiple indexes can actually slow down complex aggregations.

How to eliminate wrong answers

Option B is wrong because increasing CPU and memory resources (vertical scaling) only provides a temporary performance boost and does not address the root cause of excessive joins from normalization; it is a costly band-aid that may not scale with growing data volumes. Option C is wrong because replacing the relational warehouse with a NoSQL document store would require a complete architectural overhaul, likely breaking existing BI tools and SQL-based reporting, and NoSQL systems typically lack the ACID guarantees and join capabilities needed for consistent aggregated reporting across dimensions. Option D is wrong because adding more indexes on all foreign key columns can speed up individual join operations but increases write overhead and storage costs, and in a highly normalized snowflake schema with many joins, the cumulative index overhead can actually degrade query performance due to index maintenance and lookup costs.

Practice this question →

8

MCQhard

A table has columns: EmployeeID (PK), DepartmentID, DepartmentName, Salary. Which normal form violation exists?

A.No violation

B.Second Normal Form

C.First Normal Form

D.Third Normal Form

AnswerD

Transitive dependency: DepartmentName depends on DepartmentID, not on EmployeeID.

Why this answer

The table violates Third Normal Form (3NF) because DepartmentName depends on DepartmentID, which is not a candidate key. In 3NF, every non-key column must depend solely on the primary key (EmployeeID), not on another non-key column. This transitive dependency (EmployeeID → DepartmentID → DepartmentName) requires splitting the table into Employees and Departments to achieve 3NF.

Exam trap

The trap here is that candidates often confuse transitive dependencies with partial dependencies, leading them to incorrectly select Second Normal Form (2NF) instead of Third Normal Form (3NF).

How to eliminate wrong answers

Option A is wrong because the table clearly has a transitive dependency, so a normal form violation exists. Option B is wrong because Second Normal Form (2NF) is satisfied: there is no partial dependency since the primary key is a single column (EmployeeID), and all non-key columns are fully functionally dependent on it. Option C is wrong because First Normal Form (1NF) is satisfied: all columns contain atomic values, and there are no repeating groups or arrays.

Practice this question →

9

MCQmedium

Refer to the exhibit. A user runs the query but gets no results, even though there are employees in the Sales department. Which of the following is the most likely cause?

A.The column name is misspelled.

B.The database is offline.

C.The Department values contain trailing spaces.

D.The table name is incorrect.

AnswerC

Trailing spaces would make 'Sales' not match 'Sales '.

Why this answer

The query likely uses an exact match (e.g., WHERE Department = 'Sales'), but if the Department column contains trailing spaces (e.g., 'Sales '), the string comparison fails because SQL treats trailing spaces as significant in many databases (or the data was padded with spaces). This is a common data integrity issue where imported or manually entered data includes invisible whitespace, causing legitimate rows to be missed.

Exam trap

CompTIA often tests the subtlety that trailing whitespace in data can cause exact-match queries to fail, leading candidates to overlook data quality issues and instead suspect syntax or connectivity problems.

How to eliminate wrong answers

Option A is wrong because if the column name were misspelled, the query would typically return a syntax or invalid column name error, not an empty result set. Option B is wrong because if the database were offline, the query would fail with a connection error, not silently return zero rows. Option D is wrong because an incorrect table name would produce a 'table not found' error, not an empty result set.

Practice this question →

10

MCQhard

A report requires data from two tables: Customers and Orders. Which SQL clause is used to combine rows from both tables based on a related column?

A.UNION

B.SUBQUERY

C.JOIN

D.INTERSECT

AnswerC

JOIN merges rows from tables based on a condition, typically a foreign key.

Why this answer

The JOIN clause (specifically an INNER JOIN) is used to combine rows from two or more tables based on a related column between them, such as a foreign key. In this scenario, the Customers and Orders tables would typically be linked by a CustomerID column, and a JOIN allows you to retrieve data like customer names alongside their order details in a single result set.

Exam trap

Cisco often tests the distinction between set operations (UNION, INTERSECT) and join operations, so the trap here is that candidates confuse UNION (which stacks rows) with JOIN (which combines columns from related tables).

How to eliminate wrong answers

Option A is wrong because UNION combines rows from two or more SELECT statements into a single result set by stacking them vertically, not by matching related columns horizontally. Option B is wrong because a SUBQUERY is a nested query used to return a value or set of values for use in an outer query's WHERE, FROM, or SELECT clause, not to combine entire rows from two tables based on a related column. Option D is wrong because INTERSECT returns only the rows that appear in the result sets of both SELECT statements, which is used for set operations on identical row structures, not for joining distinct tables on a related column.

Practice this question →

11

Multi-Selectmedium

A database administrator is designing a normalized database. Which TWO are benefits of normalization?

Select 2 answers

A.Reduces data redundancy

B.Eliminates update anomalies

C.Improves query performance

D.Increases data redundancy

E.Requires fewer tables

AnswersA, B

One of the main goals of normalization.

Why this answer

Normalization reduces data redundancy (Option B) and eliminates update anomalies (Option D). Option A is false (increases complexity), C is false (more joins may reduce performance), E is false (requires more tables).

Practice this question →

12

Multi-Selectmedium

Which TWO of the following are valid SQL functions used to aggregate data?

Select 2 answers

A.COUNT

B.SORT

C.SUM

D.LENGTH

E.APPEND

AnswersA, C

COUNT returns the number of rows in a result set.

Why this answer

COUNT and SUM are both aggregate functions in SQL. COUNT returns the number of rows in a result set or the number of non-NULL values in a column, while SUM calculates the total sum of a numeric column. These functions operate on a set of rows and return a single summary value, which is the defining characteristic of aggregate functions in SQL.

Exam trap

The trap here is that candidates often confuse SQL clauses (like ORDER BY for sorting) or scalar functions (like LENGTH) with aggregate functions, because they see them used in queries but fail to recognize the fundamental difference between row-level operations and set-level summarization.

Practice this question →

13

MCQhard

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?

A.Use a stored procedure that updates all products in one statement without error handling

B.Increase the frequency of the script to run every hour with smaller batches

C.Remove the transaction and use individual UPDATE statements with error logging

D.Wrap all UPDATE statements in a single transaction with READ COMMITTED isolation level and add error handling to roll back on failure

AnswerD

This ensures atomicity (all or nothing) and prevents interference from other transactions.

Why this answer

Option D is correct because wrapping all UPDATE statements in a single transaction with READ COMMITTED isolation level ensures atomicity: either all updates commit or none do, preventing partial updates. Adding error handling with a rollback on failure guarantees that if the script fails partway through, the entire transaction is undone, leaving the database consistent. READ COMMITTED isolation prevents dirty reads and minimizes locking, reducing interference with daytime operations.

Exam trap

The trap here is that candidates may think error logging (Option C) or batching (Option B) is sufficient to maintain consistency, but they overlook that without a transaction with rollback, partial updates are still committed and cannot be undone.

How to eliminate wrong answers

Option A is wrong because a stored procedure that updates all products in one statement without error handling does not provide atomicity or rollback capability; if the single statement fails partway (e.g., due to a constraint violation), some rows may still be updated depending on the statement type, and without error handling, partial updates can occur. Option B is wrong because increasing the frequency of the script to run every hour with smaller batches does not solve the atomicity problem; each batch would still be vulnerable to partial failure, and more frequent runs increase contention with daytime operations. Option C is wrong because removing the transaction and using individual UPDATE statements with error logging does not provide atomicity; even with error logging, if the script fails after some updates, those updates persist, leaving inconsistent data, and error logging alone cannot roll back already committed changes.

Practice this question →

14

MCQhard

A developer writes an UPDATE statement to change the price of a product but accidentally omits the WHERE clause. What is the most likely outcome?

A.All rows in the table are updated.

B.Only the first row is updated.

C.The database returns a syntax error.

D.No rows are updated because the statement is invalid.

AnswerA

Without a condition, the update applies to every row.

Why this answer

In SQL, an UPDATE statement without a WHERE clause applies the change to every row in the specified table. The database engine processes the statement as a set operation, iterating over all rows and setting the specified column(s) to the new value. This is not an error; it is a valid SQL command that results in a mass update.

Exam trap

CompTIA often tests the misconception that an UPDATE without a WHERE clause will cause an error or only affect the first row, but the correct understanding is that it updates all rows in the table.

How to eliminate wrong answers

Option B is wrong because SQL does not limit UPDATE to only the first row; without a WHERE clause, the operation targets all rows, not a single row. Option C is wrong because omitting the WHERE clause does not produce a syntax error; the UPDATE statement is syntactically complete and valid. Option D is wrong because the statement is not invalid; it executes successfully and updates every row in the table.

Practice this question →

15

MCQhard

A database transaction that updates two accounts fails halfway due to a power outage. Which ACID property ensures that partial changes are undone?

A.Durability

B.Isolation

C.Consistency

D.Atomicity

AnswerD

Atomicity ensures all or nothing; partial changes are rolled back.

Why this answer

Atomicity ensures that a transaction is treated as a single, indivisible unit of work. If a power outage interrupts the transaction after updating one account but before updating the second, the database management system (DBMS) must roll back any partial changes to restore the original state. This 'all-or-nothing' property prevents incomplete transactions from leaving the database in an inconsistent state.

Exam trap

CompTIA often tests the distinction between atomicity (rollback of a failed transaction) and durability (persistence of committed data), so candidates mistakenly choose durability because they associate 'failure' with 'data loss' rather than 'partial update rollback'.

How to eliminate wrong answers

Option A is wrong because durability guarantees that committed transactions persist permanently, even after a system failure; it does not handle undoing uncommitted partial changes. Option B is wrong because isolation controls how concurrent transactions interact to prevent dirty reads or lost updates, but it does not address rollback of a single failed transaction. Option C is wrong because consistency ensures that a transaction transforms the database from one valid state to another, but it relies on atomicity to undo partial changes when a transaction fails; consistency itself does not perform the rollback.

Practice this question →

16

MCQmedium

A database administrator notices that queries on a large table are taking too long to execute. Which action would most likely improve performance?

A.Add more columns to the table.

B.Denormalize the table.

C.Perform further normalization.

D.Create an index on frequently queried columns.

AnswerD

Indexes allow faster data access by reducing full table scans.

Why this answer

Creating an index on frequently queried columns allows the database to locate rows using a B-tree or hash structure, reducing the need for full table scans. This directly addresses slow query performance on large tables by minimizing disk I/O and CPU overhead during SELECT operations.

Exam trap

The trap here is that candidates may confuse normalization with performance optimization, but normalization is designed to reduce redundancy and maintain data integrity, not to speed up queries on large tables.

How to eliminate wrong answers

Option A is wrong because adding more columns increases the row width, which can degrade I/O performance and does not speed up queries. Option B is wrong because denormalization introduces data redundancy and can improve read performance only in specific warehousing scenarios, but it is not the most direct or standard fix for slow queries on a large table; it often complicates writes and maintenance. Option C is wrong because further normalization typically increases the number of joins required, which can slow down queries rather than improve performance on a large table.

Practice this question →

17

Multi-Selecthard

Which THREE operations can be performed using Data Manipulation Language (DML) statements?

Select 3 answers

A.CREATE

B.UPDATE

C.INSERT

D.DROP

E.SELECT

AnswersB, C, E

UPDATE is DML.

Why this answer

UPDATE is a Data Manipulation Language (DML) statement because it modifies existing data within a table without altering the table's structure. DML focuses on managing data stored in database objects, and UPDATE directly changes row values.

Exam trap

CompTIA often tests the distinction between DML and DDL, trapping candidates who confuse structural commands (CREATE, DROP) with data manipulation commands (INSERT, UPDATE, SELECT).

Practice this question →

18

MCQeasy

Refer to the exhibit. The following SQL statement is executed. The result message is: '0 rows affected'. What is the most likely reason?

A.The table 'Employees' does not exist

B.The UPDATE privilege has been revoked

C.The table 'Employees' is empty

D.The statement lacks a WHERE clause

AnswerC

No rows to update.

Why this answer

The SQL statement executed successfully (no syntax error) but affected zero rows because the target table 'Employees' is empty. An UPDATE statement modifies existing rows; if no rows exist, zero rows are affected regardless of the SET or WHERE clause. The message '0 rows affected' indicates the statement parsed and executed without error, but no data matched the condition (or there was no data to update).

Exam trap

CompTIA often tests the distinction between a successful but zero-row operation and an error condition, trapping candidates who assume '0 rows affected' implies a missing WHERE clause or a nonexistent table.

How to eliminate wrong answers

Option A is wrong because if the table 'Employees' did not exist, the database would return an error like 'Table does not exist' or 'Invalid object name', not a success message with '0 rows affected'. Option B is wrong because if the UPDATE privilege had been revoked, the database would return a permission denied error, not a successful execution message. Option D is wrong because an UPDATE statement without a WHERE clause updates all rows in the table; if the table were not empty, it would affect all rows, not zero.

The absence of a WHERE clause does not cause zero rows affected unless the table itself is empty.

Practice this question →

19

MCQmedium

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?

A.Clustered index

B.B-tree index

C.Bitmap index

D.Hash index

AnswerB

B-tree indexes are ideal for exact match and range queries, providing fast lookup.

Why this answer

A B-tree index is the correct choice for an exact match search on a single column because it organizes data in a balanced tree structure that allows O(log n) lookups, making it highly efficient for equality searches. In a large table, the B-tree index reduces the number of disk I/O operations by quickly navigating to the leaf node containing the exact key value.

Exam trap

The trap here is that candidates often confuse clustered indexes with performance gains for all query types, not realizing that a clustered index primarily optimizes data retrieval order and can slow down writes, while a B-tree index is the standard choice for exact match searches in most relational databases.

How to eliminate wrong answers

Option A is wrong because a clustered index physically reorders the table data based on the index key, which is beneficial for range queries but does not inherently improve exact match performance beyond what a B-tree index provides, and it can cause overhead during inserts and updates. Option C is wrong because a bitmap index is optimized for columns with low cardinality (few distinct values) and is typically used in data warehousing for complex queries involving multiple conditions, not for exact match searches on a single column in a transactional database. Option D is wrong because a hash index is designed for exact match lookups using a hash function, but it does not support ordered retrieval and is not the default or most common index type in relational databases like MySQL or SQL Server; B-tree indexes are the standard for general-purpose exact match queries.

Practice this question →

20

Multi-Selecthard

Which TWO of the following are common causes of errors when executing a SELECT query? (Choose two.)

Select 2 answers

A.Table is too large

B.Incorrect column name

C.Index is missing

D.Missing table name

E.Network is slow

AnswersB, D

A misspelled or invalid column name causes an error.

Why this answer

Option B is correct because a SELECT query references specific columns in the WHERE clause or SELECT list; if the column name is misspelled or does not exist in the table schema, the database returns an error (e.g., 'Unknown column' in MySQL or 'Invalid column name' in SQL Server). This is a fundamental syntax error that prevents query execution.

Exam trap

The trap here is that candidates confuse performance issues (large table, missing index, slow network) with actual query execution errors, but only syntax or schema mismatches (incorrect column name, missing table name) cause the query to fail.

Practice this question →

21

MCQeasy

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

A.55000

B.50000

C.165000

D.60000

AnswerA

The average is (50000+60000+55000)/3 = 55000.

Why this answer

The AVG function calculates the arithmetic mean of the Salary column across all rows in the Employees table. Given the salaries 50000, 60000, and 55000, the average is (50000 + 60000 + 55000) / 3 = 55000. Therefore, option A is correct.

Exam trap

The trap here is that candidates often confuse AVG with SUM or MIN/MAX, leading them to pick the sum (165000) or an extreme value (50000 or 60000) instead of correctly computing the mean.

How to eliminate wrong answers

Option B (50000) is wrong because it represents the minimum salary, not the average. Option C (165000) is wrong because it is the sum of all salaries (50000 + 60000 + 55000), not the average. Option D (60000) is wrong because it represents the maximum salary, not the average.

Practice this question →

22

MCQmedium

A database must be restored to the exact state at 11:00 AM. The last full backup was at 10:00 PM previous day, and transaction log backups were taken every hour. What is the minimum to restore?

A.Full backup plus all transaction log backups from after full backup

B.Transaction log backup only

C.Full backup only

D.Full backup plus differential backup

AnswerA

Enables restoration to any point in time.

Why this answer

To restore a database to a specific point in time (11:00 AM) when only full and transaction log backups exist, you must restore the most recent full backup (from 10:00 PM previous day) and then apply all subsequent transaction log backups in sequence. Transaction log backups contain all committed transactions up to the time of the backup, so applying them in order after the full backup brings the database to the exact state at the time of the last log backup (which would be 11:00 AM if the hourly log backup was taken at that time). Option A is correct because it includes the full backup and all transaction log backups taken after it, which is the minimum required for point-in-time recovery.

Exam trap

The trap here is that candidates often think a differential backup can achieve point-in-time recovery, but differentials only capture changes up to a point, not the granular transaction-level detail needed to restore to an exact time like 11:00 AM.

How to eliminate wrong answers

Option B is wrong because a transaction log backup alone cannot restore a database without a prior full backup to provide the base data structure and pages. Option C is wrong because a full backup alone only restores the database to the state at the time of that backup (10:00 PM previous day), not to the later point-in-time of 11:00 AM. Option D is wrong because a differential backup captures changes since the last full backup but does not provide point-in-time granularity to a specific hour; transaction log backups are required for that precision.

Practice this question →

23

MCQeasy

A company needs to store customer orders, where each order can contain multiple items. Which database design is most appropriate?

A.Three tables: Orders, Customers, and Items, with no direct link.

B.Two tables: Orders and OrderItems, linked by an OrderID foreign key.

C.A single table storing all data in a JSON column.

D.A single table with columns for order details and repeated item information.

AnswerB

This normalized design efficiently handles one-to-many relationships.

Why this answer

Option B is correct because it uses a normalized relational design with two tables: Orders (holding order-level data) and OrderItems (holding individual item details per order). The OrderID foreign key in OrderItems creates a one-to-many relationship, allowing each order to contain multiple items without data redundancy or update anomalies. This design ensures referential integrity and efficient querying, which is the standard approach for transactional order systems.

Exam trap

The trap here is that candidates often choose Option D (single table with repeated columns) because it seems simpler, but they overlook the violation of first normal form and the practical limitations of fixed-width schemas for variable-length data.

How to eliminate wrong answers

Option A is wrong because three tables with no direct link fail to establish any relationship between orders, customers, and items, making it impossible to associate a customer with their orders or items with specific orders. Option C is wrong because storing all data in a single JSON column violates first normal form (1NF), prevents efficient indexing and querying of individual fields, and makes it difficult to enforce data integrity or perform relational operations like joins. Option D is wrong because a single table with repeated item information (e.g., multiple columns for item1, item2) violates atomicity and leads to a fixed maximum number of items per order, causing data redundancy and update anomalies when item details change.

Practice this question →

24

MCQmedium

A database designer is creating a table to store employee information. To ensure that each employee can be uniquely identified, which constraint should be applied?

A.UNIQUE

B.FOREIGN KEY

C.PRIMARY KEY

D.NOT NULL

AnswerC

A primary key uniquely identifies each row and cannot contain NULL.

Why this answer

A PRIMARY KEY constraint uniquely identifies each record in a database table. It enforces both uniqueness and non-nullability, ensuring that no two employees can have the same primary key value and that the value is never NULL. This makes it the correct choice for guaranteeing each employee can be uniquely identified.

Exam trap

The trap here is that candidates often confuse UNIQUE with PRIMARY KEY, not realizing that UNIQUE allows a NULL value (which cannot serve as a guaranteed identifier) and that PRIMARY KEY inherently includes both uniqueness and non-nullability.

How to eliminate wrong answers

Option A is wrong because a UNIQUE constraint ensures all values in a column are distinct but allows a single NULL value, which would break the requirement for a guaranteed unique identifier for every employee. Option B is wrong because a FOREIGN KEY constraint enforces referential integrity between two tables and does not provide unique identification within the table itself. Option D is wrong because a NOT NULL constraint only prevents NULL values in a column but does not enforce uniqueness, so duplicate employee identifiers would still be allowed.

Practice this question →

25

MCQmedium

A database administrator adds an index on the 'Email' column of a 'Users' table. What is a likely consequence?

A.Reduced storage space

B.Improved query performance for all queries

C.Slower update operations on the table

D.Faster inserts on the table

AnswerC

Index maintenance adds overhead to INSERT/UPDATE/DELETE.

Why this answer

Adding an index on the 'Email' column improves the speed of SELECT queries that filter or sort by that column, but it introduces overhead on write operations. Every time a row is inserted, updated, or deleted, the database must also update the index structure (e.g., a B-tree). This additional work makes UPDATE operations slower, which is why option C is correct.

Exam trap

The trap here is that candidates assume indexes universally speed up all database operations, ignoring the write-performance penalty that occurs because the index must be maintained on every INSERT, UPDATE, and DELETE.

How to eliminate wrong answers

Option A is wrong because an index consumes additional storage space, not less; it stores a separate data structure (e.g., a B-tree) that maps index keys to row locations. Option B is wrong because an index only improves query performance for queries that use the indexed column in WHERE, JOIN, or ORDER BY clauses; queries that do not reference the 'Email' column or that perform full-table scans may see no benefit or even degraded performance. Option D is wrong because inserts become slower, not faster, due to the need to maintain the index by inserting a new entry into the index structure for each new row.

Practice this question →

26

MCQhard

A query uses a LEFT JOIN between Customers and Orders. The result set includes customers with no orders. What does the Orders columns show for those customers?

A.NULL

B.The same as the customer ID

C.Zero

D.Empty string

AnswerA

LEFT JOIN produces NULL when no match.

Why this answer

In a LEFT JOIN, all rows from the left table (Customers) are retained, and for rows with no matching rows in the right table (Orders), the columns from the right table are filled with NULL. This is the standard SQL behavior defined by the SQL standard (ISO/IEC 9075). Therefore, for customers with no orders, the Orders columns show NULL, not zero, an empty string, or any other default value.

Exam trap

The trap here is that candidates often confuse NULL with zero or an empty string, thinking a missing record should show a default value like 0 or '', but SQL strictly uses NULL to represent the absence of a matching row in a LEFT JOIN.

How to eliminate wrong answers

Option B is wrong because the Orders columns do not replicate the customer ID; they show NULL to indicate the absence of a matching order. Option C is wrong because zero is a numeric value that would imply an order exists with a zero value, not the absence of an order; SQL uses NULL for missing data. Option D is wrong because an empty string is a valid string value that would indicate an order with an empty field, not the lack of an order; NULL is the correct indicator for no matching row.

Practice this question →

27

MCQeasy

A small business wants to store customer contact information and order history. The data is structured and relationships between customers and orders must be enforced. Which type of database is most appropriate?

A.Flat-file database

B.Relational database

C.NoSQL database

D.Hierarchical database

AnswerB

Correct because it enforces relationships and supports structured data.

Why this answer

A relational database (Option A) is best for structured data with relationships. NoSQL (B) is for unstructured data, flat file (C) lacks relationships, and hierarchical (D) is less flexible.

Practice this question →

28

MCQeasy

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

A.UNIQUE

B.PRIMARY KEY

C.CHECK

D.FOREIGN KEY

AnswerA

UNIQUE constraint ensures no duplicate email addresses.

Why this answer

The UNIQUE constraint ensures that all values in the Email column are distinct, preventing any two employees from having the same email address. Unlike PRIMARY KEY, UNIQUE allows NULL values (though typically email columns are set to NOT NULL), and it can be applied to non-key columns. This directly enforces the business rule of unique email addresses without requiring the column to be the table's primary identifier.

Exam trap

The trap here is that candidates often confuse UNIQUE with PRIMARY KEY, assuming uniqueness can only be enforced by a primary key, but PRIMARY KEY imposes additional non-null and single-per-table restrictions that are not required for simply ensuring unique email addresses.

How to eliminate wrong answers

Option B (PRIMARY KEY) is wrong because while it also enforces uniqueness, it additionally requires the column to be non-null and uniquely identify each row, which is not necessary for just ensuring unique emails; the Email column may not be the natural primary key. Option C (CHECK) is wrong because CHECK constraints validate data against a Boolean expression (e.g., ensuring email format contains '@'), but they cannot enforce uniqueness across rows. Option D (FOREIGN KEY) is wrong because it enforces referential integrity between tables by requiring values in the Email column to match values in another table's primary key or unique column, which does not prevent duplicate emails within the same table.

Practice this question →

29

Multi-Selecteasy

Which TWO of the following are advantages of using a relational database over a NoSQL database? (Choose two.)

Select 2 answers

A.Schema flexibility

B.Low latency for large volumes

C.ACID compliance

D.Standard query language (SQL)

E.Horizontal scalability

AnswersC, D

Relational databases enforce ACID properties, ensuring transaction reliability.

Why this answer

Relational databases offer ACID compliance and a standard query language (SQL), which are key advantages.

Practice this question →

30

Matchingmedium

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

Why these pairings

Network classification by scope.

Practice this question →

31

MCQmedium

A user reports that a SELECT query on a large table with millions of records is very slow. The table has no indexes. Which index type would most likely improve the performance of equality searches (e.g., WHERE id = 100)?

A.Full-text index on the column

B.Nonclustered index on the column

C.Clustered index on the column

D.Unique index on the column

AnswerC

A clustered index sorts and stores the data rows in order, making equality lookups very fast via binary search.

Why this answer

A clustered index on the column used in equality searches (e.g., WHERE id = 100) is the most effective because it physically reorders the table data in the same order as the index key. This allows the database engine to perform a single seek operation directly to the exact row, rather than scanning millions of records. For a large table with no indexes, a clustered index provides the fastest possible access for equality lookups on the indexed column.

Exam trap

The trap here is that candidates often confuse 'nonclustered index' as the default or best choice for all searches, but for equality searches on a large table, a clustered index eliminates the extra lookup step and is the most performant option.

How to eliminate wrong answers

Option A is wrong because a full-text index is designed for linguistic searches on large text fields (e.g., searching for words or phrases within documents), not for exact equality matches on a single value like an ID. Option B is wrong because a nonclustered index on the column would improve performance but requires an additional lookup step (key lookup or bookmark lookup) to retrieve the actual data row, making it slower than a clustered index for direct equality searches. Option D is wrong because a unique index enforces uniqueness of values but does not inherently improve performance more than a non-unique index; its primary purpose is constraint enforcement, not query speed optimization.

Practice this question →

32

Multi-Selecthard

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

Select 3 answers

A.The database uses a hierarchical model.

B.Queries are written using SQL.

C.The database is schema-less and flexible.

D.Data is stored as JSON documents.

E.Data is stored in tables with rows and columns.

.ACID transactions ensure data integrity.

AnswersB, E

SQL is the standard language for relational databases.

Why this answer

Option B is correct because SQL (Structured Query Language) is the standard language used to query and manipulate data in relational databases. It allows users to perform operations such as SELECT, INSERT, UPDATE, and DELETE on structured data stored in tables.

Exam trap

The trap here is that candidates often confuse the flexible, schema-less nature of NoSQL databases with the rigid, table-based structure of relational databases, leading them to select options like C or D.

Practice this question →

33

MCQhard

A university database includes a table named 'Students' and a table named 'Enrollments'. The 'Enrollments' table has a foreign key column 'StudentID' that references the 'StudentID' primary key in the 'Students' table. The university policy requires that when a student withdraws (i.e., their record is deleted from the 'Students' table), all of their associated enrollment records must be automatically removed to maintain data consistency. The database administrator wants to enforce this rule at the database level. Which referential action should be applied when creating the foreign key constraint on the 'Enrollments' table?

A.ON DELETE CASCADE

B.ON DELETE SET NULL

C.ON DELETE NO ACTION

D.ON DELETE RESTRICT

AnswerA

CASCADE automatically deletes child records when parent is deleted.

Why this answer

Option A (ON DELETE CASCADE) is correct because it automatically deletes all child rows in the 'Enrollments' table when the corresponding parent row in the 'Students' table is deleted. This enforces the university policy of removing associated enrollment records upon student withdrawal, maintaining referential integrity at the database level without manual intervention.

Exam trap

The trap here is that candidates often confuse ON DELETE CASCADE with ON DELETE SET NULL or ON DELETE RESTRICT, mistakenly thinking that preventing deletion or setting NULL satisfies the automatic removal requirement, when only CASCADE actually deletes the child records automatically.

How to eliminate wrong answers

Option B (ON DELETE SET NULL) is wrong because it would set the 'StudentID' foreign key column in 'Enrollments' to NULL when the parent student record is deleted, which does not remove the enrollment records and may violate NOT NULL constraints. Option C (ON DELETE NO ACTION) is wrong because it prevents the deletion of a student record if any referencing enrollment rows exist, requiring manual deletion of enrollments first, which contradicts the automatic removal policy. Option D (ON DELETE RESTRICT) is wrong because it also blocks the deletion of a student record if referencing enrollments exist, similar to NO ACTION, and does not automatically remove associated records.

Practice this question →

34

MCQhard

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?

A.Many-to-one

B.One-to-many

C.Many-to-many

D.One-to-one

AnswerC

Many-to-many is correct because a book can have multiple authors and an author can write multiple books, requiring a junction table.

Why this answer

The correct relationship is many-to-many because a book can have multiple authors and an author can write multiple books. In database schema design, this requires a junction table (also known as a linking or associative table) to resolve the many-to-many relationship into two one-to-many relationships, ensuring referential integrity and avoiding data redundancy.

Exam trap

The trap here is that candidates often confuse one-to-many with many-to-many when the problem statement mentions 'multiple' on one side only, but the key is that both entities can have multiple related records, which requires a junction table.

How to eliminate wrong answers

Option A is wrong because many-to-one is the inverse of one-to-many and implies that many books could be associated with a single author, but it fails to capture the scenario where a book has multiple authors. Option B is wrong because one-to-many would mean one book can have many authors, but it does not allow an author to write many books, which violates the bidirectional requirement. Option D is wrong because one-to-one would mean each book has exactly one author and each author writes exactly one book, which directly contradicts the given requirement that both sides can have multiple instances.

Practice this question →

35

MCQmedium

A database administrator needs to modify the 'Phone' column in the 'Employees' table to allow up to 15 characters. Which SQL statement should the administrator use?

A.CREATE TABLE Employees (Phone VARCHAR(15));

B.ALTER TABLE Employees ALTER COLUMN Phone VARCHAR(15);

C.UPDATE Employees SET Phone = VARCHAR(15);

D.INSERT INTO Employees (Phone) VALUES ('123456789012345');

AnswerB

This changes the column's data type to allow up to 15 characters.

Why this answer

The ALTER TABLE statement is used to modify an existing table's structure, and the ALTER COLUMN clause specifically changes a column's data type or size. Option B correctly uses ALTER TABLE Employees ALTER COLUMN Phone VARCHAR(15) to change the Phone column to allow up to 15 characters, which is the standard SQL syntax for this operation.

Exam trap

CompTIA often tests the distinction between DDL (ALTER TABLE) and DML (UPDATE, INSERT) commands, trapping candidates who confuse modifying table structure with modifying data.

How to eliminate wrong answers

Option A is wrong because CREATE TABLE is used to create a new table, not to modify an existing one, and it would attempt to create a duplicate table or fail if the table already exists. Option C is wrong because UPDATE is a Data Manipulation Language (DML) statement used to modify existing row data, not to change the column's schema or data type; VARCHAR(15) is not a valid value for an UPDATE statement. Option D is wrong because INSERT INTO adds a new row of data to the table, not to alter the column definition, and it would simply insert a specific phone number without changing the column's maximum length.

Practice this question →

36

MCQmedium

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

A.It sorts results by Department

B.It returns all columns for employees in Sales

C.It returns only the Department column

D.It returns all rows from the Employees table

AnswerB

SELECT * returns all columns, and the WHERE clause filters for Sales department.

Why this answer

The SELECT * clause retrieves all columns from the specified table, and the WHERE Department = 'Sales' filter restricts the result set to only those rows where the Department column has the value 'Sales'. Therefore, the query returns every column for employees in the Sales department, making option B correct.

Exam trap

The trap here is that candidates confuse the SELECT clause with the WHERE clause, mistakenly thinking SELECT * returns all rows (option D) or only a specific column (option C), when in fact SELECT * returns all columns and WHERE filters rows.

How to eliminate wrong answers

Option A is wrong because the query contains no ORDER BY clause, so results are not sorted by Department or any other column; sorting requires an explicit ORDER BY. Option C is wrong because SELECT * returns all columns, not just the Department column; to return only the Department column, the query would need to specify SELECT Department. Option D is wrong because the WHERE clause filters rows to only those where Department = 'Sales', so it does not return all rows from the Employees table.

Practice this question →

37

MCQhard

An e-commerce application experiences a data anomaly where an order is partially recorded: the order header is saved but some order line items are missing due to a system crash. Which property of database transactions is violated?

A.Consistency

B.Isolation

C.Atomicity

D.Durability

AnswerC

Atomicity requires all operations in a transaction to complete or none; a partial commit breaks this.

Why this answer

Atomicity ensures that a transaction is treated as a single, indivisible unit of work: either all operations (e.g., saving the order header and all line items) complete successfully, or none are applied. In this scenario, the system crash caused a partial write, leaving the order header committed while line items were lost, which directly violates atomicity. The database should have rolled back the entire transaction upon crash recovery, preventing the partial state.

Exam trap

The trap here is that candidates confuse 'consistency' (which relates to data integrity rules) with the all-or-nothing requirement of atomicity, especially when the partial data appears to violate business logic rather than the transaction boundary.

How to eliminate wrong answers

Option A is wrong because consistency ensures that a transaction transforms the database from one valid state to another, respecting all defined rules (e.g., constraints, triggers); a partially recorded order could still be consistent if the missing line items are allowed by schema, but the core issue is the incomplete unit of work. Option B is wrong because isolation deals with concurrent transaction visibility (e.g., preventing dirty reads or lost updates via locking or MVCC), not with the all-or-nothing completion of a single transaction. Option D is wrong because durability guarantees that once a transaction is committed, its changes survive system failures (e.g., via write-ahead logging); here the header was saved but the line items were not, so the transaction was never fully committed, and durability is not violated.

Practice this question →

38

MCQeasy

A user is unable to insert a new record into a database table because a required field is missing. Which database concept enforces that a field must have a value?

A.NOT NULL constraint

B.Primary key

C.Index

D.Foreign key

AnswerA

A NOT NULL constraint prevents NULL values in a column, making it required.

Why this answer

A NOT NULL constraint is a database rule that prevents a field from containing a NULL value, ensuring that a value must be provided for that column when inserting or updating a record. In this scenario, the user cannot insert a new record because the required field is missing, which directly violates the NOT NULL constraint. This constraint is enforced at the database engine level, typically during the INSERT or UPDATE operation, and will raise an error if the field is omitted or set to NULL.

Exam trap

The trap here is that candidates often confuse the NOT NULL constraint with the primary key, thinking that a primary key's uniqueness property is what enforces a required value, but the primary key's NOT NULL behavior is a side effect, not its defining purpose.

How to eliminate wrong answers

Option B (Primary key) is wrong because a primary key uniquely identifies each row in a table and implicitly enforces NOT NULL and uniqueness, but the question specifically asks about a constraint that enforces a field must have a value—a primary key's primary role is uniqueness, not just requiring a value. Option C (Index) is wrong because an index is a data structure that improves query performance by allowing faster data retrieval; it does not enforce any requirement for a field to have a value. Option D (Foreign key) is wrong because a foreign key enforces referential integrity between two tables, ensuring that a value in one table matches a value in another table's primary key; it does not require that a field itself must have a value.

Practice this question →

39

MCQeasy

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

A.Hierarchical database

B.Flat-file database

C.NoSQL database

D.Relational database

AnswerD

Relational databases use tables, keys, and relationships to efficiently store and query structured data like customer info and orders.

Why this answer

A relational database is the most appropriate choice because it organizes data into tables with rows and columns, allowing efficient storage and retrieval of structured customer information (name, address) while supporting relationships to order history via foreign keys. This structure enables ACID compliance and complex queries using SQL, which is ideal for transactional data with clear schema requirements.

Exam trap

The trap here is that candidates often choose NoSQL databases (Option C) because they assume 'order history' implies large-scale or flexible data, but the question specifies structured customer information with clear relationships, making relational databases the correct fit for ACID-compliant transactional integrity.

How to eliminate wrong answers

Option A is wrong because hierarchical databases organize data in a tree-like structure with parent-child relationships, which is inflexible for modeling many-to-many relationships between customers and orders without redundancy. Option B is wrong because flat-file databases store all data in a single table or file, leading to data duplication and difficulty managing related entities like order history without manual normalization. Option C is wrong because NoSQL databases are designed for unstructured or semi-structured data and horizontal scaling, but they lack the strict schema enforcement and relational integrity needed for consistent customer-order associations.

Practice this question →

40

Multi-Selecteasy

Which TWO of the following are examples of database management systems (DBMS)?

Select 2 answers

A.Microsoft SQL Server

B.Python

C.MySQL

D.HTML

E.Linux

AnswersA, C

Microsoft SQL Server is a relational DBMS.

Why this answer

Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It uses Structured Query Language (SQL) to store, retrieve, and manage data in a structured format, making it a core example of a DBMS.

Exam trap

The trap here is that candidates may confuse programming languages or operating systems with database management systems because they are often used together in development stacks, but only software specifically designed to manage databases qualifies as a DBMS.

Practice this question →

41

Multi-Selecthard

Which THREE of the following are best practices for database security?

Select 3 answers

A.Use strong, complex passwords

B.Grant all database privileges to all users

C.Disable audit logs to save storage

D.Encrypt sensitive data at rest

E.Implement least privilege access

AnswersA, D, E

Strong passwords resist brute-force attacks.

Why this answer

Using strong, complex passwords is a fundamental security practice that reduces the risk of brute-force or dictionary attacks against database authentication. Passwords should meet length and complexity requirements (e.g., at least 12 characters with mixed case, numbers, and special characters) and be stored using salted, cryptographically strong hashes like bcrypt or PBKDF2. This prevents unauthorized access even if the password hash is compromised.

Exam trap

CompTIA often tests the misconception that disabling security features like audit logs is acceptable for performance or storage savings, when in fact it eliminates critical accountability and detection capabilities required for secure database operations.

Practice this question →

42

Multi-Selectmedium

Which TWO of the following are advantages of using a relational database management system (RDBMS) over a file-based system?

Select 2 answers

A.Enforcement of data integrity

B.Faster data access for all queries

C.Reduced data redundancy

D.No need for a database administrator

E.Simpler setup and maintenance

AnswersA, C

Constraints enforce accuracy and consistency.

Why this answer

A relational database management system (RDBMS) enforces data integrity through constraints such as primary keys, foreign keys, unique constraints, and check constraints. These rules ensure that data entered into the database adheres to defined business rules and referential integrity, preventing orphaned records and invalid data entries. In contrast, a file-based system relies on application code to enforce such rules, which is error-prone and inconsistent.

Exam trap

The trap here is that candidates often assume 'faster data access' is always true for RDBMS due to indexing, but they overlook the overhead of SQL processing and the fact that file-based systems can be faster for simple, non-relational lookups.

Practice this question →

43

MCQeasy

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

A.2

B.3

C.0

D.1

AnswerA

Employees with department_id 10 are John Doe and Bob Johnson.

Why this answer

The query `SELECT COUNT(*) FROM employees WHERE department_id = 10;` counts all rows in the `employees` table where the `department_id` column equals 10. According to the exhibit (not shown here but implied), there are exactly two employees in department 10, so the result is 2.

Exam trap

Cisco often tests the difference between `COUNT(*)`, `COUNT(column)`, and `COUNT(DISTINCT column)`, and the trap here is that candidates might miscount the rows in the exhibit or confuse the filter condition with a different department ID.

How to eliminate wrong answers

Option B (3) is wrong because it would imply three employees in department 10, but the data shows only two. Option C (0) is wrong because it would mean no employees in department 10, but the data clearly contains two. Option D (1) is wrong because it would indicate a single employee, but the query counts two matching rows.

Practice this question →

44

MCQhard

Refer to the exhibit. The database administrator sees this error in the log. Which action should the administrator take to resolve the deadlock?

A.Increase the transaction isolation level to Serializable to prevent deadlocks.

B.Remove all indexes from the involved tables to reduce lock contention.

C.Manually kill one of the processes to break the deadlock.

D.The database will automatically roll back one of the transactions; no immediate action is needed.

AnswerD

Most DBMSs detect deadlocks and automatically choose a victim transaction to roll back.

Why this answer

When a deadlock occurs in a database, the database management system (DBMS) automatically detects the circular dependency and selects one of the transactions as a victim, rolling it back to break the deadlock. This is a built-in mechanism in most relational databases, such as SQL Server, Oracle, or MySQL, and does not require manual intervention. Therefore, no immediate action is needed from the administrator, as the database will resolve the deadlock itself.

Exam trap

The trap here is that candidates may think manual intervention (killing a process) is required, but the exam tests the understanding that modern databases have automatic deadlock resolution built in, making option D the correct answer.

How to eliminate wrong answers

Option A is wrong because increasing the transaction isolation level to Serializable increases locking and contention, which actually makes deadlocks more likely, not less. Option B is wrong because removing indexes increases the likelihood of table scans and lock escalation, which can worsen lock contention and deadlock frequency. Option C is wrong because manually killing one of the processes is unnecessary and disruptive; the database's deadlock detection mechanism automatically selects and rolls back a victim transaction without administrator intervention.

Practice this question →

45

MCQeasy

A database designer wants to avoid duplicate data across tables and ensure each table stores information about a single entity. Which process should the designer apply?

A.Indexing

B.Transaction isolation

C.Normalization

D.Backup and recovery

AnswerC

Normalization minimizes duplication by splitting data into related tables.

Why this answer

Normalization is the process of organizing data in a database to reduce redundancy and dependency by dividing large tables into smaller, related tables and defining relationships between them. This ensures each table stores information about a single entity and avoids duplicate data across tables, aligning with the designer's goal.

Exam trap

The trap here is that candidates confuse normalization with indexing, assuming both reduce redundancy, but indexing only optimizes query speed without addressing data duplication or entity isolation.

How to eliminate wrong answers

Option A is wrong because indexing is used to speed up data retrieval by creating data structures (e.g., B-trees) that improve query performance, not to eliminate duplicate data or enforce entity-level table design. Option B is wrong because transaction isolation is a property of database transactions (defined by SQL standards like ACID) that controls concurrency and visibility of changes, not a method for structuring tables to avoid redundancy. Option D is wrong because backup and recovery involves creating copies of data and restoring it after failure (e.g., using full, differential, or transaction log backups), which does not address data duplication or table design.

Practice this question →

46

MCQhard

A database designer is modeling a system where each book can have multiple authors and each author can write multiple books. Which type of relationship should be used between the Book and Author entities?

A.One-to-one

B.Hierarchical

C.One-to-many

D.Many-to-many

AnswerD

Many-to-many allows a book to have multiple authors and an author to have multiple books, typically implemented via a junction table.

Why this answer

The scenario describes a many-to-many relationship because each book can have multiple authors and each author can write multiple books. In relational database design, this is modeled using a junction (or associative) table that contains foreign keys referencing both the Book and Author primary keys, enabling the bidirectional association without data redundancy.

Exam trap

The trap here is that candidates often confuse a one-to-many relationship with many-to-many, mistakenly thinking that adding multiple author fields to the Book table (violating first normal form) is acceptable, rather than recognizing the need for a junction table.

How to eliminate wrong answers

Option A is wrong because a one-to-one relationship would require each book to have exactly one author and each author to write exactly one book, which contradicts the given scenario. Option B is wrong because hierarchical is not a standard relational database relationship type; it typically refers to a data model (like in LDAP or XML) where records have parent-child relationships, not the associative many-to-many needed here. Option C is wrong because a one-to-many relationship would allow one book to have many authors but would force each author to write only one book (or vice versa), which does not satisfy the bidirectional multiplicity described.

Practice this question →

47

MCQmedium

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

A.Widget, 2

B.Widget, 2

C.Widget, 2; Gadget, 1

D.Widget, 2; Gadget, 1; Widget, 1

AnswerB

The GROUP BY groups by ProductName, and HAVING filters groups with count > 1, so only Widget (count 2) is returned.

Why this answer

The SELECT statement uses COUNT(*) with GROUP BY Category, which counts the number of rows per unique category value. The table has two rows with 'Widget' and one row with 'Gadget', so the output is two rows: Widget with count 2, and Gadget with count 1. Option B correctly lists both rows, matching the query's result.

Exam trap

The trap here is that candidates may forget that GROUP BY produces one row per unique group value, causing them to either omit groups with fewer rows or incorrectly include duplicate rows with partial counts.

How to eliminate wrong answers

Option A is wrong because it only shows one row (Widget, 2), omitting the Gadget row entirely, which would only happen if a WHERE clause filtered out Gadget or if the query used DISTINCT on Category without COUNT. Option C is wrong because it lists 'Widget, 2; Gadget, 1; Widget, 1', which incorrectly includes a third row with Widget count 1, likely confusing COUNT(*) with a row count per individual occurrence or misapplying a HAVING clause. Option D is wrong because it lists 'Widget, 2; Gadget, 1; Widget, 1' (same as C), which duplicates Widget with an incorrect count and adds an extra row not produced by the GROUP BY.

Practice this question →

48

MCQhard

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?

A.Consistency

B.Durability

C.Isolation

D.Atomicity

AnswerD

Atomicity guarantees that all parts of a transaction succeed or none do, allowing rollback on failure.

Why this answer

Atomicity (option D) ensures that a transaction is treated as a single, indivisible unit of work. If any part of the transaction fails (e.g., updating the second table), the entire transaction is rolled back, and the database returns to its original state before the transaction began. This prevents partial updates that would leave data inconsistent.

Exam trap

CompTIA often tests the distinction between Atomicity and Consistency, where candidates mistakenly think Consistency handles rollback, but Atomicity is the property that ensures the entire transaction is undone on failure.

How to eliminate wrong answers

Option A is wrong because Consistency ensures that a transaction brings the database from one valid state to another, but it does not handle rollback on failure—that is the role of Atomicity. Option B is wrong because Durability guarantees that once a transaction is committed, its changes persist even after a system crash, but it does not undo a failed transaction. Option C is wrong because Isolation ensures that concurrent transactions do not interfere with each other, but it does not provide rollback mechanisms for a single failing transaction.

Practice this question →

49

MCQmedium

A database table contains redundant employee data, leading to update anomalies. Which database design process should be applied to resolve this?

A.Normalization

B.Indexing

C.Denormalization

D.Query optimization

AnswerA

Normalization eliminates redundant data and reduces update anomalies.

Why this answer

Normalization is the database design process that eliminates redundant data and ensures data dependencies make sense, thereby resolving update anomalies. By decomposing a table into smaller, related tables and defining relationships through foreign keys, normalization reduces data duplication and maintains consistency during insert, update, and delete operations.

Exam trap

The trap here is that candidates often confuse normalization with indexing or query optimization because all three improve database performance, but only normalization addresses structural redundancy and update anomalies at the schema level.

How to eliminate wrong answers

Option B (Indexing) is wrong because indexing improves query performance by creating data structures for faster data retrieval, but it does not address data redundancy or update anomalies. Option C (Denormalization) is wrong because it intentionally adds redundant data to improve read performance, which would worsen update anomalies rather than resolve them. Option D (Query optimization) is wrong because it focuses on improving the efficiency of SQL query execution plans, not on restructuring the database schema to eliminate redundancy.

Practice this question →

50

MCQhard

An inventory database contains Products (ProductID int PK, ProductName varchar(50), Quantity int, Price decimal) and Sales (SaleID int PK, ProductID int FK, SaleDate date, QuantitySold int). A report is needed: for each product, show product ID, name, current quantity, and total quantity sold. An employee writes: SELECT p.ProductID, p.ProductName, p.Quantity, SUM(s.QuantitySold) AS TotalSold FROM Products p LEFT JOIN Sales s ON p.ProductID = s.ProductID ORDER BY p.ProductID; The query executes without error but the results are incorrect. For products with no sales, TotalSold displays the same value as p.Quantity. For products with sales, TotalSold shows the correct sum. Which action should the employee take to fix the query?

A.Replace LEFT JOIN with INNER JOIN

B.Add 'GROUP BY p.ProductID, p.ProductName, p.Quantity'

C.Use SUM(COALESCE(s.QuantitySold,0))

D.Remove the ORDER BY clause

AnswerB

Corrects aggregation by grouping per product.

Why this answer

The query uses an aggregate function (SUM) without a GROUP BY clause, which causes SQL to treat the non-aggregated columns (p.ProductID, p.ProductName, p.Quantity) as part of a single group. For products with no sales, the LEFT JOIN produces NULL for s.QuantitySold, and SUM(NULL) returns NULL, but the database engine incorrectly displays p.Quantity in the TotalSold column because the ungrouped query collapses rows unpredictably. Adding GROUP BY p.ProductID, p.ProductName, p.Quantity ensures SUM(s.QuantitySold) is calculated per product, returning NULL (or 0 if COALESCE is used) for products with no sales.

Exam trap

CompTIA often tests the requirement for GROUP BY when mixing aggregate and non-aggregate columns, exploiting the misconception that adding COALESCE or changing join types alone resolves the issue.

How to eliminate wrong answers

Option A is wrong because replacing LEFT JOIN with INNER JOIN would exclude products with no sales entirely, which does not fix the incorrect TotalSold display and loses required data. Option C is wrong because SUM(COALESCE(s.QuantitySold,0)) would convert NULL to 0, but without a GROUP BY, the aggregate still operates on an ungrouped result set, so the incorrect display of p.Quantity for TotalSold persists. Option D is wrong because removing the ORDER BY clause only affects row ordering, not the aggregation logic; the fundamental issue of missing GROUP BY remains.

Practice this question →

51

MCQmedium

A banking transaction transfers money from savings to checking. The system updates the savings balance, then the power fails before the checking balance is updated. Which ACID property is violated?

A.Atomicity

B.Isolation

C.Durability

D.Consistency

AnswerA

Atomicity requires all or nothing; partial update violates it.

Why this answer

Atomicity ensures that a transaction is treated as a single, indivisible unit of work. In this scenario, the transfer operation consists of two steps: deducting from savings and crediting to checking. Because the power failure occurs after the savings balance is updated but before the checking balance is updated, the transaction is left partially completed, violating atomicity.

The system must either complete both steps or roll back the first step to maintain atomicity.

Exam trap

The trap here is that candidates often confuse atomicity with consistency, thinking a partial update violates consistency first, but the core issue is that the transaction did not complete as an atomic unit, which is the direct violation of atomicity.

How to eliminate wrong answers

Option B (Isolation) is wrong because isolation concerns the visibility of intermediate transaction states to other concurrent transactions, not the completion of a single transaction. Option C (Durability) is wrong because durability guarantees that committed changes persist, but here the transaction was never committed due to the failure. Option D (Consistency) is wrong because consistency ensures that a transaction leaves the database in a valid state; while a partial update could lead to inconsistency, the primary violated property is atomicity, as the transaction failed to execute as an all-or-nothing unit.

Practice this question →

52

MCQeasy

A company wants to store product catalogs that vary in structure (different attributes per product). Which type of database is best suited?

A.Relational database

B.Network database

C.Hierarchical database

D.Document database

AnswerD

Document databases store JSON-like documents, allowing each product to have different attributes.

Why this answer

A document database (NoSQL) is ideal for storing product catalogs with varying structures because it stores data in flexible, schema-less documents (e.g., JSON or BSON). Each product can have different attributes without requiring predefined columns or schema migrations, unlike relational databases. This allows the company to handle heterogeneous product data efficiently.

Exam trap

The trap here is that candidates often default to relational databases for all structured data, failing to recognize that 'varying structure' is a key indicator for a NoSQL document store, not a relational or legacy database model.

How to eliminate wrong answers

Option A is wrong because relational databases require a fixed schema with predefined columns, making it difficult to store products with varying attributes without extensive null values or table redesigns. Option B is wrong because network databases use a graph-like structure with records and sets, which is rigid and not designed for schema-less, document-oriented data. Option C is wrong because hierarchical databases organize data in a strict parent-child tree structure, which cannot accommodate products with different attributes without forcing a uniform schema across all records.

Practice this question →

53

MCQhard

A user runs the query: SELECT * FROM Employees WHERE Department = 'Sales' AND Salary > 50000; but no rows are returned. The Department column contains 'Sales ' with a trailing space. What is the most likely cause?

A.The data has trailing spaces causing no match

B.The AND operator is incorrect

C.The column name is misspelled

D.Salary is stored as text

AnswerA

Trailing spaces mean 'Sales' does not equal 'Sales '.

Why this answer

The query uses an exact string comparison with 'Sales', but the stored data contains 'Sales ' (with a trailing space). In SQL, string comparisons are character-by-character, so the trailing space causes the equality check to fail, resulting in zero rows returned. This is a common data quality issue where invisible whitespace prevents matches.

Exam trap

The trap here is that candidates assume trailing spaces are automatically ignored in SQL string comparisons, but the FC0-U61 exam tests awareness that exact matches require identical strings, including whitespace.

How to eliminate wrong answers

Option B is wrong because the AND operator is syntactically and logically correct for combining two conditions; it is not the cause of the mismatch. Option C is wrong because the column name 'Department' is correctly spelled in the query and matches the table schema; a misspelling would cause an error, not an empty result. Option D is wrong because even if Salary were stored as text, the comparison 'Salary > 50000' would still work in most SQL databases via implicit conversion, and the issue is specifically with the Department string, not the salary comparison.

Practice this question →

54

MCQmedium

Refer to the exhibit. The query returns two rows. If the database had an index on the Department column only, how would the query execution be affected?

A.The query would run faster because the index reduces the number of rows to scan for Department.

B.The query would return fewer rows because the index excludes the Salary condition.

C.The query would return more rows because the index includes both columns.

D.The query would fail because the index conflicts with the SELECT *.

AnswerA

The index narrows down to IT rows quickly, then filters on Salary, improving performance.

Why this answer

Option A is correct because an index on the Department column allows the database engine to quickly locate rows matching the Department condition without scanning the entire table. This reduces the number of rows that need to be evaluated for the Salary condition, speeding up the query. The index does not affect the result set—it only improves access path efficiency.

Exam trap

CompTIA often tests the misconception that an index changes the query result set (rows returned) rather than just the execution speed, leading candidates to incorrectly choose options B or C.

How to eliminate wrong answers

Option B is wrong because an index does not exclude rows from the result; it only speeds up access to rows that satisfy the query conditions. The query still returns all rows matching both Department and Salary conditions. Option C is wrong because an index on only the Department column does not include the Salary column, so it cannot cause the query to return more rows; the result set is determined by the WHERE clause, not the index.

Option D is wrong because SELECT * is fully compatible with indexes; an index never causes a query to fail—it is an optional performance structure.

Practice this question →

55

MCQhard

Refer to the exhibit. A database administrator runs the command shown, but user1 still cannot select data from the Customers table. What is the most likely reason?

A.The user does not have permission to access the database.

B.The GRANT command requires a COMMIT.

C.The user also needs UPDATE privilege.

D.The table name is misspelled in the GRANT command.

AnswerA

The user may need a CONNECT grant or access to the schema.

Why this answer

The GRANT command shown grants SELECT on the Customers table to user1, but if user1 does not have the CONNECT privilege or has not been granted access to the database itself, the SELECT will still fail. In most SQL databases (e.g., MySQL, PostgreSQL, SQL Server), a user must first have the ability to connect to the database (e.g., CONNECT privilege in PostgreSQL or a login in SQL Server) before any object-level permissions take effect. Without that foundational access, the GRANT SELECT is effectively useless.

Exam trap

CompTIA often tests the misconception that granting an object-level privilege (like SELECT) is sufficient, when in reality the user must also have database-level access (e.g., CONNECT or a login) to even reach the table.

How to eliminate wrong answers

Option B is wrong because in most SQL databases (including MySQL, PostgreSQL, and SQL Server), GRANT statements are auto-committed as DDL commands and do not require an explicit COMMIT. Option C is wrong because SELECT privilege alone is sufficient to read data; UPDATE is a separate operation and is not required for SELECT to work. Option D is wrong because if the table name were misspelled, the GRANT command would typically return an error (e.g., 'Table or view not found'), not silently succeed while still blocking the SELECT.

Practice this question →

56

Multi-Selectmedium

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

Select 2 answers

A.Creating separate tables for each customer order without linking them

B.Storing all product details in a single column as a list

C.Removing duplicate data from tables by creating separate tables for each entity

D.Splitting customer information into Customers and Orders tables linked by a foreign key

E.Storing calculated totals in the same table to avoid joins

AnswersC, D

This reduces redundancy and improves data integrity.

Why this answer

Option C is correct because normalization aims to reduce data redundancy and improve data integrity by removing duplicate data and organizing it into separate, related tables. By creating separate tables for each distinct entity (e.g., Customers, Orders, Products), you eliminate repeating groups and ensure each fact is stored only once, which is a core principle of database normalization.

Exam trap

The trap here is that candidates often confuse normalization with simply splitting data arbitrarily (Option A) or think that storing pre-calculated values (Option E) improves performance, when in fact normalization prioritizes data integrity over denormalization for performance.

Practice this question →

57

Multi-Selecthard

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

Select 3 answers

A.Granting SELECT only on specific tables to a read-only user

B.Revoking INSERT and DELETE from a user who only needs to view data

C.Allowing remote access from any IP address for flexibility

D.Granting DBA privileges to all developers for convenience

E.Granting access to a specific database schema rather than the entire server

AnswersA, B, E

This restricts the user to only read necessary data.

Why this answer

Granting SELECT only on specific tables to a read-only user adheres to the principle of least privilege by limiting data access to exactly what the user needs to perform their job. This prevents unnecessary exposure of sensitive data and reduces the risk of accidental or malicious modification. In SQL databases, this is implemented via GRANT SELECT ON specific_table TO user, rather than granting broader permissions like SELECT on all tables or higher-level privileges.

Exam trap

The trap here is that candidates may confuse 'flexibility' (Option C) or 'convenience' (Option D) with good security practice, failing to recognize that least privilege requires minimizing access, not maximizing it.

Practice this question →

58

MCQeasy

Refer to the exhibit. A database administrator executes the DELETE statement, and it reports success. However, a subsequent SELECT still shows the row. Which is the most likely explanation?

A.The table is locked by another user.

B.The WHERE clause used the wrong value.

C.The command was not committed.

D.The user lacks DELETE privilege.

AnswerC

Without COMMIT, the deletion is not finalized and can be rolled back.

Why this answer

In SQL databases, a DELETE statement removes rows from a table only when the transaction is committed. If the database is using implicit transactions or the user has not issued a COMMIT command, the deletion is held in a pending transaction and remains visible to other sessions (or even the same session if read consistency is applied). The reported 'success' indicates the statement executed without error, but without a commit, the changes are not made permanent, so a subsequent SELECT still shows the row.

Exam trap

CompTIA often tests the distinction between statement execution success and transaction permanence, trapping candidates who assume 'success' means the data is immediately visible to all queries.

How to eliminate wrong answers

Option A is wrong because a table lock by another user would typically cause the DELETE to wait or fail, not report success while still showing the row; locks block conflicting operations, not hide committed changes. Option B is wrong because if the WHERE clause used the wrong value, the DELETE would still report success (0 rows affected) and the original row would remain, but the question states the DELETE 'reports success' and the row is still there—this could happen, but the most likely explanation in a database context is a missing commit, not a WHERE clause error, as the latter is a logic mistake not a transaction issue. Option D is wrong because lacking DELETE privilege would cause the statement to fail with an error (e.g., 'permission denied'), not report success.

Practice this question →

59

MCQeasy

A help desk technician needs to retrieve the names and email addresses of all customers who live in 'Chicago'. Which SQL statement should the technician use?

A.UPDATE customers SET city = 'Chicago'

B.SELECT name, email FROM customers WHERE city = 'Chicago'

C.DELETE FROM customers WHERE city = 'Chicago'

D.INSERT INTO customers (name, email) VALUES (...)

AnswerB

This retrieves the desired columns filtered by city.

Why this answer

The SELECT statement is used to query data from a database. Option B correctly uses SELECT to retrieve the 'name' and 'email' columns from the 'customers' table, filtered by the WHERE clause to only include rows where the 'city' column equals 'Chicago'. This matches the technician's requirement to retrieve specific data without modifying or deleting it.

Exam trap

The trap here is that candidates may confuse data retrieval (SELECT) with data manipulation (UPDATE, DELETE, INSERT), especially when the question asks for 'retrieving' data but includes familiar keywords like 'city' and 'Chicago' in the wrong commands.

How to eliminate wrong answers

Option A is wrong because UPDATE modifies existing records by setting the city to 'Chicago' for all rows, which does not retrieve any data and would incorrectly change all customers' cities. Option C is wrong because DELETE removes all rows where city is 'Chicago', which destroys data instead of retrieving it. Option D is wrong because INSERT adds new rows to the table, which does not retrieve existing data and would require specifying values, not a condition.

Practice this question →

60

Multi-Selecteasy

A user wants to retrieve all records from a table named 'Products' where the price is greater than 100. Which TWO SQL statements will work?

Select 2 answers

A.SELECT * FROM Products WHERE Price > 100 GROUP BY Category;

B.SELECT * FROM Products WHERE Price > 100;

C.SELECT ALL FROM Products WHERE Price > 100;

D.SELECT * FROM Products HAVING Price > 100;

E.SELECT * FROM Products WHERE Price > 100 ORDER BY Name;

AnswersB, E

Correct syntax and logic.

Why this answer

Option B is correct because it uses the standard SQL SELECT statement with a WHERE clause to filter records where the Price column is greater than 100, retrieving all columns with the asterisk (*). This is the fundamental and correct syntax for filtering rows in SQL.

Exam trap

The trap here is that candidates confuse WHERE with HAVING, thinking HAVING can replace WHERE for row-level filtering, or they mistakenly add GROUP BY without understanding its purpose for aggregation.

Practice this question →

61

Drag & Dropmedium

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

Why this order

Task Manager is used to monitor and manage running processes.

Practice this question →

62

MCQhard

A database administrator needs to remove all rows from the 'Log' table but keep the table structure for future use. The administrator also wants the operation to be irreversible (cannot be rolled back). Which SQL statement should be used?

A.DELETE FROM Log;

B.ALTER TABLE Log REMOVE ROWS;

C.TRUNCATE TABLE Log;

D.DROP TABLE Log;

AnswerC

TRUNCATE is DDL, removes all rows, and typically cannot be rolled back.

Why this answer

TRUNCATE TABLE Log; is correct because it removes all rows from the table while preserving the table structure (columns, constraints, indexes) for future use. Unlike DELETE, TRUNCATE is a DDL operation that does not generate individual row deletion logs, making it irreversible and cannot be rolled back in most database systems.

Exam trap

The trap here is that candidates often confuse TRUNCATE with DELETE, thinking both are reversible, or they mistakenly believe DROP TABLE preserves the structure, when in fact TRUNCATE is the only option that removes all rows irreversibly while keeping the table schema intact.

How to eliminate wrong answers

Option A is wrong because DELETE FROM Log; is a DML operation that removes rows one by one, logging each deletion, and can be rolled back within a transaction, so it is not irreversible. Option B is wrong because ALTER TABLE Log REMOVE ROWS; is not a valid SQL statement; ALTER TABLE is used to modify table structure (e.g., add/drop columns), not to remove rows. Option D is wrong because DROP TABLE Log; removes the entire table structure along with all data, which does not preserve the table for future use.

Practice this question →

63

MCQeasy

A small independent bookstore has been operating for several years using a single Microsoft Excel spreadsheet to manage its inventory, sales, and customer data. The spreadsheet contains over 10,000 rows and is shared among five employees. Recently, employees have noticed recurring data quality issues: the same book appears multiple times in the inventory list with different prices, some sales records reference book titles that do not exist in the inventory, and customer contact information is duplicated across rows. Because there are no constraints, employees can enter any value into any cell. The owner wants to implement a more robust system to eliminate data redundancy and ensure data integrity. The bookstore has a budget for new software and some technical support. The owner is not very technical but is willing to hire a consultant. Which action should be taken to best address these problems?

A.Use a single flat file for all data to simplify data entry

B.Implement a NoSQL database for flexibility

C.Migrate to a relational database with normalized tables and foreign key constraints

D.Continue using the spreadsheet but add data validation rules to restrict input

AnswerC

Normalization reduces redundancy, and foreign keys enforce referential integrity.

Why this answer

Option C is correct because migrating to a relational database with normalized tables and foreign key constraints directly addresses the data redundancy and integrity issues. Normalization splits data into separate tables (e.g., Books, Sales, Customers) to eliminate duplicate entries, while foreign key constraints ensure that every sales record references a valid book and that customer data is stored once. This approach enforces referential integrity and prevents the anomalies seen in the flat spreadsheet.

Exam trap

The trap here is that candidates often choose data validation (Option D) thinking it can enforce integrity, but they overlook that spreadsheets lack relational constraints and cannot prevent duplicate rows or cross-table inconsistencies across multiple users.

How to eliminate wrong answers

Option A is wrong because using a single flat file for all data would perpetuate the same redundancy and integrity problems—it offers no mechanism to enforce unique entries or relationships between data. Option B is wrong because a NoSQL database, while flexible, typically lacks built-in support for ACID transactions and foreign key constraints, making it less suitable for enforcing strict referential integrity in a small bookstore scenario with structured relational data. Option D is wrong because adding data validation rules to a spreadsheet cannot prevent duplicate rows or enforce cross-table relationships; spreadsheets lack the relational integrity features needed to eliminate redundancy and ensure consistency across 10,000+ rows shared by multiple users.

Practice this question →

64

Multi-Selectmedium

A database designer wants to enforce that a 'CustomerID' value in an 'Orders' table must exist in the 'Customers' table. Which TWO methods can achieve this?

Select 2 answers

A.Create a unique constraint on Orders.CustomerID

B.Create an index on Orders.CustomerID

C.Use a trigger to check existence

D.Create a primary key on Customers.CustomerID

E.Create a foreign key from Orders.CustomerID to Customers.CustomerID

AnswersC, E

A trigger can validate the CustomerID.

Why this answer

Option C is correct because a trigger can be programmed to check, before an INSERT or UPDATE on the Orders table, whether the new CustomerID exists in the Customers table. If the value is not found, the trigger can roll back the transaction, enforcing referential integrity without using a formal foreign key constraint. This is a valid method in database systems that support procedural logic within triggers.

Exam trap

The trap here is that candidates often confuse a primary key or unique constraint with referential integrity, thinking that ensuring uniqueness in the parent table automatically enforces existence in the child table, but neither does so without an explicit foreign key or trigger.

Practice this question →

65

MCQeasy

A web application constructs SQL queries by concatenating user input directly. What is the primary security risk?

A.SQL injection

B.Denial of service

C.Cross-site scripting

D.Buffer overflow

AnswerA

Input concatenation allows malicious SQL execution.

Why this answer

When a web application constructs SQL queries by concatenating user input directly, an attacker can inject malicious SQL code into the query. This allows the attacker to manipulate the database, such as by bypassing authentication, retrieving unauthorized data, or executing arbitrary commands. This is the classic definition of SQL injection, which exploits the lack of input sanitization or parameterized queries.

Exam trap

The trap here is that candidates may confuse SQL injection with cross-site scripting (XSS) because both involve user input, but XSS targets the browser with scripts, while SQL injection targets the database server directly.

How to eliminate wrong answers

Option B is wrong because a denial of service attack aims to overwhelm resources or crash the service, not to manipulate database queries via input; SQL injection can sometimes lead to DoS, but it is not the primary risk. Option C is wrong because cross-site scripting involves injecting client-side scripts into web pages viewed by other users, not directly into SQL queries on the server. Option D is wrong because a buffer overflow exploits memory boundaries in compiled code (e.g., C/C++), not in SQL query construction within a web application.

Practice this question →

66

Multi-Selecthard

Which THREE of the following are valid database integrity constraints?

Select 3 answers

A.UNIQUE

B.INDEX

C.FOREIGN KEY

D.CONDITION

E.PRIMARY KEY

AnswersA, C, E

Ensures all values in a column are distinct.

Why this answer

A UNIQUE constraint ensures that all values in a column or a set of columns are distinct from one another, preventing duplicate entries. This is a standard SQL integrity constraint that enforces data uniqueness at the table level, and it is correctly listed as a valid database integrity constraint.

Exam trap

The trap here is that candidates often confuse database objects like indexes with integrity constraints, or they invent terms like 'CONDITION' because it sounds similar to the valid CHECK constraint, leading them to select incorrect options.

Practice this question →

67

MCQeasy

A company needs to store an organizational chart showing reporting relationships. Which database model is most appropriate?

A.Relational

B.Document

C.Graph

D.Hierarchical

AnswerD

Hierarchical databases are optimized for parent-child relationships.

Why this answer

A hierarchical database model is most appropriate for storing an organizational chart because it naturally represents parent-child relationships, such as manager-subordinate reporting lines, in a tree-like structure. This model allows each record to have a single parent and multiple children, directly mirroring the one-to-many relationships in a reporting hierarchy.

Exam trap

The trap here is that candidates often choose 'Relational' because it is the most common model, but they overlook that hierarchical databases are specifically designed for strict parent-child structures like org charts, whereas relational databases require cumbersome recursive queries.

How to eliminate wrong answers

Option A is wrong because a relational database uses tables with foreign keys to represent relationships, which can model hierarchies but requires complex self-joins and is less efficient for recursive queries like traversing an org chart. Option B is wrong because a document database stores semi-structured data (e.g., JSON) and lacks native support for efficient traversal of parent-child relationships, often requiring application-level logic to reconstruct the hierarchy. Option C is wrong because a graph database excels at modeling complex many-to-many relationships (e.g., social networks) and is overkill for a simple tree structure, though it could technically work; the hierarchical model is more directly suited for strict parent-child reporting lines.

Practice this question →

68

MCQeasy

Refer to the exhibit. The Products table has been created and two rows have been inserted. What will be the result of the following query? SELECT ProductName FROM Products WHERE Price > 100;

A.Mouse

B.Laptop, Mouse

C.Laptop

D.An empty result set

AnswerC

Laptop meets the condition Price > 100.

Why this answer

The query selects ProductName from the Products table where the Price column is greater than 100. According to the exhibit (not shown but implied), only the row with ProductName 'Laptop' has a Price value exceeding 100, so only 'Laptop' is returned. This demonstrates the basic SQL WHERE clause filtering on a numeric column.

Exam trap

CompTIA often tests the candidate's ability to apply a simple numeric comparison in a WHERE clause, and the trap here is assuming that all rows are returned or misreading the exhibit's data, leading to selecting 'Mouse' or both products.

How to eliminate wrong answers

Option A is wrong because 'Mouse' would only be returned if its Price were greater than 100, but it is not (likely 50 or less). Option B is wrong because 'Laptop, Mouse' implies both rows satisfy the condition, but only 'Laptop' has Price > 100. Option D is wrong because the result set is not empty; at least one row (Laptop) meets the condition.

Practice this question →

69

MCQeasy

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

A.DISTINCT

B.GROUP BY

C.WHERE

D.ORDER BY

AnswerA

DISTINCT ensures the result set contains only unique rows.

Why this answer

The DISTINCT keyword in SQL is used within a SELECT statement to remove duplicate rows from the result set. When a query returns multiple identical rows, DISTINCT filters them out so that only unique combinations of column values are displayed. This directly addresses the user's requirement to eliminate duplicate rows.

Exam trap

CompTIA often tests the misconception that GROUP BY can eliminate duplicates on its own, but candidates must remember that GROUP BY is for aggregation and can still return duplicate rows if no aggregate function is applied to non-grouped columns.

How to eliminate wrong answers

Option B (GROUP BY) is wrong because it is used to group rows that have the same values in specified columns into summary rows, often with aggregate functions (e.g., COUNT, SUM), but it does not inherently eliminate duplicates; it can actually produce duplicate-like rows if not combined with aggregation. Option C (WHERE) is wrong because it filters rows based on a condition but does not remove duplicates; it only restricts which rows are included in the result set. Option D (ORDER BY) is wrong because it sorts the result set by one or more columns but has no effect on duplicate removal; it only changes the order of rows.

Practice this question →

70

MCQmedium

A company's customer database contains a table named 'Orders' with columns: OrderID, CustomerID, ProductID, Quantity, OrderDate. The company wants to enforce that every CustomerID in 'Orders' must exist in the 'Customers' table. Which database constraint should be added to the 'Orders' table?

A.Check constraint on CustomerID

B.Primary key on CustomerID

C.Foreign key on CustomerID referencing Customers

D.Unique constraint on CustomerID

AnswerC

A foreign key ensures that CustomerID values in Orders match a primary key value in Customers.

Why this answer

A foreign key constraint on CustomerID referencing the Customers table ensures referential integrity. Option A is incorrect because a primary key is for the table's own unique identifier. Option B is incorrect because a unique constraint prevents duplicates but does not reference another table.

Option D is incorrect because a check constraint validates data within the same row, not across tables.

Practice this question →

71

Multi-Selectmedium

Which THREE of the following are best practices for database normalization? (Choose three.)

Select 3 answers

A.Use composite primary keys excessively

B.Eliminate redundant data

C.Ensure data dependencies make sense

D.Reduce update anomalies

E.Create separate tables for each attribute

AnswersB, C, D

Redundancy causes anomalies; normalization removes it.

Why this answer

Eliminating redundant data is a core goal of database normalization because it reduces storage requirements and prevents data inconsistencies. By ensuring each piece of data is stored in only one place, you avoid update anomalies that occur when the same fact is recorded in multiple rows or tables. This practice directly supports the second normal form (2NF) and third normal form (3NF) objectives of removing partial and transitive dependencies.

Exam trap

Cisco often tests the misconception that normalization means creating many small tables (one per attribute), when in fact it requires grouping attributes by their functional dependencies to eliminate redundancy without over-fragmentation.

Practice this question →

72

MCQmedium

Refer to the exhibit. A user attempts to insert a row into Orders with a CustomerID that does not exist in the Customers table. What is the expected outcome?

A.The row is inserted but with a warning

B.The row is inserted successfully

C.The Customers table automatically creates a new customer

D.The INSERT statement fails with a constraint violation

AnswerD

Foreign key constraint error.

Why this answer

The INSERT statement fails with a constraint violation because the CustomerID column in the Orders table is defined as a foreign key referencing the CustomerID primary key in the Customers table. When a user attempts to insert a row with a CustomerID that does not exist in the Customers table, the relational database management system (RDBMS) enforces referential integrity and rejects the operation, raising a foreign key constraint violation error.

Exam trap

The trap here is that candidates may assume the database will silently accept the row or create a placeholder customer, failing to recognize that foreign key constraints enforce strict referential integrity and cause the statement to fail with an error.

How to eliminate wrong answers

Option A is wrong because a foreign key violation does not produce a warning; it is a hard error that prevents the insert entirely, and the row is not inserted. Option B is wrong because the row cannot be inserted successfully due to the foreign key constraint; the database enforces referential integrity and blocks the operation. Option C is wrong because foreign key constraints do not trigger automatic creation of missing parent records; the Customers table remains unchanged, and the insert fails.

Practice this question →

Ready to test yourself?

Try a timed practice session using only Database Fundamentals questions.

Start 20-question session