Microsoft Certified: Fabric Analytics Engineer AssociateExplore and analyze data (15-20%)Medium
A data analyst is querying a large Delta table `orders` in Microsoft Fabric. They need to retrieve all orders placed in the year 2023 by customers whose `customer_id` is an even number. Which SQL query correctly combines these conditions?
- ASELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2023 OR customer_id % 2 = 0;
- BSELECT * FROM orders WHERE order_date LIKE '2023%' AND MOD(customer_id, 2) = 0;
- CSELECT * FROM orders WHERE YEAR(order_date) = 2023 AND customer_id % 2 = 0;
- DSELECT * FROM orders WHERE YEAR(order_date) = 2023 HAVING customer_id % 2 = 0;
Show answer & explanationAnswer & explanation
Correct answer: C. SELECT * FROM orders WHERE YEAR(order_date) = 2023 AND customer_id % 2 = 0;
The `WHERE` clause is used to filter individual rows based on conditions. Combining `YEAR(order_date) = 2023` to filter by year and `customer_id % 2 = 0` (using the modulo operator) to filter for even customer IDs with `AND` correctly implements both requirements.
Why the other options are wrong
- A. Using `OR` would include orders from other years or odd customer IDs, which is incorrect. `EXTRACT()` is also a valid function for year, but `OR` is the critical error.
- B. `LIKE '2023%'` for date filtering is string-based and less robust than `YEAR()`. `MOD()` is a valid alternative to `%` but the `LIKE` is less ideal.
- D. The `HAVING` clause is used to filter *groups* after aggregation, not individual rows. The `customer_id % 2 = 0` condition applies to individual rows, so it must be in the `WHERE` clause.
SQL WHERE Clause with AND/OR
The SQL WHERE clause is used to filter records based on specified conditions, allowing multiple conditions to be combined using logical operators like AND, OR, and NOT.
- Filters individual rows before any grouping or aggregation.
- `AND` requires all combined conditions to be true.
- `OR` requires at least one combined condition to be true.
- Commonly used with comparison operators, date functions, and mathematical operators.
Memory trick: Where All Conditions Meet, And Or Not, Filters the Rows Neatly.