DATABRICKS-DATA-ANALYST-ASSOCIATE practice question 10 of 15

A data analyst wants the following output: customer_name number_of_orders John Doe 388 Zhang San 234 Which statement will produce this output?

Answer options

A: SELECT customer_name, count(order_id) AS number_of_orders FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customer_name; Correct answer

B: SELECT customer_name, count(order_id) number_of_orders FROM customers JOIN orders ON customers.customer_id = orders.customer_id USE customer_name;

C: SELECT customerjiame, (order_id) number_of_orders FROM customers JOIN orders ON customers.customer_id = orders.customer_id;

D: SELECT customerjiame, count(order_id) FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customerjiame;

Correct Answer:

A. SELECT customer_name, count(order_id) AS number_of_orders FROM customers JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customer_name;

Explanation

This query correctly produces the desired output by performing three essential SQL operations. First, it uses an INNER JOIN to combine rows from the customers and orders tables based on their shared customerid. Second, it applies the COUNT(orderid) aggregate function to count the number of orders for each customer. Third, the GROUP BY customername clause is used to group the rows so that the COUNT function calculates the total orders for each unique customer name. Finally, AS numberoforders renames the aggregated column to match the required output.

Why Incorrect

B: This statement is syntactically incorrect. It uses a non-existent USE clause and is missing the required GROUP BY clause for aggregation. C: This query is missing the COUNT() aggregate function to count the orders and the necessary GROUP BY clause. It also contains a typo (customerjiame). D: This query fails to alias the count column as numberoforders, which is required by the problem description. It also contains a typo (customerjiame).

References

1. GROUP BY Clause: The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is essential when using aggregate functions like COUNT().

Databricks SQL Language Reference

GROUP BY clause. (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-groupby.html)

2. JOIN Clause: The JOIN clause combines records from two or more tables. The ON subclause specifies the join condition

matching customerid in both tables.

Databricks SQL Language Reference

JOIN clause. (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-join.html)

3. count Aggregate Function: The count function returns the number of rows or non-NULL values within a group. COUNT(orderid) correctly counts the total orders.

Databricks SQL Language Reference

count aggregate function. (https://docs.databricks.com/en/sql/language-manual/functions/count.html)

4. SELECT Clause and Aliases: The SELECT clause specifies the columns to be returned. An alias (using AS) can be used to provide a temporary

more descriptive name for a column in the result set.

Databricks SQL Language Reference

SELECT clause. (https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select.html)

Keep practicing

Take the free DATABRICKS-DATA-ANALYST-ASSOCIATE practice test

Ten exam-style questions with answers and explanations, plus the exam facts and study guides.

Start the free practice test