nerdexam
CompTIA

LX0-104 · Question #119

Which of the following SQL queries counts the number of occurrences for each value of the field order_type in the table orders?

The correct answer is B. SELECT order_type,COUNT(*) FROM orders GROUP BY order_type. To count occurrences for each unique value of a field, an SQL query must use the GROUP BY clause with the desired field and COUNT(*) in the SELECT statement.

Shells, Scripting and Data Management

Question

Which of the following SQL queries counts the number of occurrences for each value of the field order_type in the table orders?

Options

  • ASELECT order_type,COUNT(*) FROM orders WHERE order_type=order_type;
  • BSELECT order_type,COUNT(*) FROM orders GROUP BY order_type;
  • CCOUNT(SELECT order_type FROM orders);
  • DSELECT COUNT(*) FROM orders ORDER BY order_type;
  • ESELECT AUTO_COUNT FROM orders COUNT order_type;

How the community answered

(24 responses)
  • A
    4% (1)
  • B
    79% (19)
  • D
    13% (3)
  • E
    4% (1)

Why each option

To count occurrences for each unique value of a field, an SQL query must use the GROUP BY clause with the desired field and COUNT(*) in the SELECT statement.

ASELECT order_type,COUNT(*) FROM orders WHERE order_type=order_type;

WHERE order_type=order_type is always true and does not perform grouping; it would return the total count of all rows with the order_type column, not counts per unique type.

BSELECT order_type,COUNT(*) FROM orders GROUP BY order_type;Correct

The GROUP BY order_type clause partitions the rows of the orders table into groups based on identical values in the order_type column. The SELECT order_type, COUNT(*) then returns each unique order_type and the total number of rows (occurrences) within that specific group.

CCOUNT(SELECT order_type FROM orders);

COUNT(SELECT order_type FROM orders) is syntactically incorrect SQL; COUNT is an aggregate function that operates on a result set, not a subquery in this manner.

DSELECT COUNT(*) FROM orders ORDER BY order_type;

SELECT COUNT(*) FROM orders ORDER BY order_type would count all rows in the table as a single total count and then attempt to order a single result by order_type, which is not meaningful for individual occurrences.

ESELECT AUTO_COUNT FROM orders COUNT order_type;

SELECT AUTO_COUNT FROM orders COUNT order_type uses invalid SQL syntax for counting occurrences.

Concept tested: SQL GROUP BY clause for aggregate functions

Source: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

Topics

#SQL#GROUP BY#aggregate functions#database querying

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full LX0-104 Practice