hardMultiple ChoiceObjective-mapped
FC0-U71 Practice Question: An inventory database contains Products…
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?
⚠ Common 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.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Add 'GROUP BY p.ProductID, p.ProductName, p.Quantity'
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.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Replace LEFT JOIN with INNER JOIN
Why it's wrong here
Would exclude products with no sales.
- ✓
Add 'GROUP BY p.ProductID, p.ProductName, p.Quantity'
Why this is correct
Corrects aggregation by grouping per product.
- ✗
Use SUM(COALESCE(s.QuantitySold,0))
Why it's wrong here
Does not fix the missing GROUP BY.
- ✗
Remove the ORDER BY clause
Why it's wrong here
ORDER BY is not the cause.
Go deeper
Related to this question
About these practice questions
Courseiva writes every FC0-U71 question from scratch — 988 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This FC0-U71 practice question is part of Courseiva's free CompTIA certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the FC0-U71 exam.