Fix a Query to Achieve the Desired Results. DATA-ANALYST-ASSOCIATE Practice Questions (Page 3)

Questions 11–15

1. 11application · medium

   An analyst wants to display the top 3 products by total sales revenue. The following query returns the bottom 3 products. What is the error? SELECT product_name, SUM(revenue) AS total_revenue FROM sales GROUP BY product_name ORDER BY total_revenue ASC LIMIT 3;

   • AThe SELECT should use COUNT instead of SUM.
   • BThe LIMIT should be 10 instead of 3.
   • CThe ORDER BY should use DESC instead of ASC.
   • DThe GROUP BY should include revenue.
   Reveal Solution Discussion Select an answer first
2. 12expert · medium

   A financial analyst runs the following query to compute the total revenue per region for the year 2023. The result shows NULL for some regions that had sales. The analyst expects 0 for regions with no sales, but regions with sales should show the correct total. What is the most likely cause and fix? SELECT region, SUM(revenue) AS total_revenue FROM sales WHERE year = 2023 GROUP BY region;

   • AThe SUM function should be replaced with COUNT to count sales instead of summing revenue.
   • BThe revenue column contains NULL values for some sales, and SUM ignores NULLs, so the total is NULL if all revenues are NULL.
   • CThe WHERE clause filters out regions with no sales, so they do not appear in the result.
   • DThe GROUP BY clause should include year to avoid aggregation across years.
   Reveal Solution Discussion Select an answer first
4. 13foundation · easy

   A query is intended to show the total quantity sold for each product. The current query returns one row per product but the quantity column shows the quantity of a single order. Which change to the GROUP BY or aggregation would fix this?

   • AAdd SUM(quantity) to the SELECT clause and GROUP BY product_id
   • BGroup by order_id instead of product_id
   • CRemove GROUP BY and use SELECT DISTINCT
   • DAdd COUNT(quantity) to the SELECT clause
   Reveal Solution Discussion Select an answer first
5. 14foundation · easy

   A query is meant to count the number of orders placed in January. The query returns a count of all orders. Which logical error is most likely the cause?

   • AThe ORDER BY clause sorts by date
   • BThe WHERE clause is missing the month filter
   • CThe GROUP BY clause groups by month
   • DThe SELECT clause uses SUM instead of COUNT
   Reveal Solution Discussion Select an answer first
6. 15expert · medium

   An analyst wants to find all products that have never been ordered. The following query returns no rows, but the analyst knows such products exist. What is the most likely logical error? SELECT p.product_name FROM products p WHERE p.product_id NOT IN ( SELECT o.product_id FROM orders o );

   • AThe subquery should use SELECT DISTINCT to avoid duplicates.
   • BThe NOT IN should be changed to NOT EXISTS.
   • CThe WHERE clause should be placed after GROUP BY.
   • DThe subquery returns NULL values, causing NOT IN to evaluate to UNKNOWN for all rows.
   Reveal Solution Discussion Select an answer first