SnowPro Associate: Platform Question 172
Single answer● Run basic SQL commandsA data analyst needs to review recent orders from a Snowflake table named SALES_DB.PUBLIC.ORDERS. The analyst only wants rows where the ORDER_STATUS is 'SHIPPED' and the ORDER_DATE is on or after January 1, 2024. The result should show CUSTOMER_ID, ORDER_ID, and ORDER_DATE, sorted by ORDER_DATE from newest to oldest. Which SQL statement correctly returns the required data?
- A
SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE FROM SALES_DB.PUBLIC.ORDERS WHERE ORDER_STATUS = 'SHIPPED' AND ORDER_DATE >= '2024-01-01' ORDER BY ORDER_DATE DESC;
- B
SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE FROM SALES_DB.PUBLIC.ORDERS ORDER BY ORDER_DATE DESC WHERE ORDER_STATUS = 'SHIPPED' AND ORDER_DATE >= '2024-01-01';
- C
SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE FROM SALES_DB.PUBLIC.ORDERS WHERE ORDER_STATUS = 'SHIPPED' OR ORDER_DATE >= '2024-01-01' ORDER BY ORDER_DATE DESC;
- D
SELECT CUSTOMER_ID, ORDER_ID, ORDER_DATE FROM SALES_DB.PUBLIC.ORDERS WHERE ORDER_STATUS IN 'SHIPPED' AND ORDER_DATE AFTER '2024-01-01' SORT BY ORDER_DATE DESC;
Show answer and explanation
Correct answer: A
Explanation
The correct answer is the statement that follows standard Snowflake SQL query structure: SELECT, FROM, WHERE, and then ORDER BY. To meet the scenario requirements, the query must return only the requested columns, apply both filters together using AND, and sort descending by ORDER_DATE so the newest orders appear first. This tests basic SQL command execution in Snowflake, including filtering and sorting result sets. According to Snowflake SQL syntax and documentation, ORDER BY is used for sorting query results, and predicates in the WHERE clause use operators such as =, >=, AND, and OR. Using the correct clause order and logical operator is essential to produce the intended result.
- A. Correct.
Correct. This statement uses valid SQL syntax for Snowflake: SELECT to choose the required columns, WHERE to filter rows, AND to require both conditions, and ORDER BY ... DESC to sort from newest to oldest. Comparing ORDER_DATE to the string literal '2024-01-01' is valid because Snowflake can interpret it as a date value in this context.
- B. Incorrect.
Incorrect. In SQL, the WHERE clause must appear before ORDER BY. This option has the clauses in the wrong order, so it is not valid SQL syntax.
- C. Incorrect.
Incorrect. This query uses OR instead of AND. That would return rows that are either shipped or on/after January 1, 2024, which is broader than the requirement. For example, it could include non-shipped orders placed after that date or shipped orders from before that date.
- D. Incorrect.
Incorrect. This option contains multiple SQL syntax problems. IN requires parentheses, such as IN ('SHIPPED'), although using = is simpler here. Snowflake SQL does not use AFTER as a comparison operator, and SORT BY is not the correct clause; the correct syntax is ORDER BY.