Introduction
The retailer can now map files to tables, but a readable table is not automatically ready for reporting. Some status values have extra spaces, a customer identifier is missing, another is empty text, one price is unknown, and one quantity is zero. A query that ignores these differences can select the wrong rows or display misleading values.
You will build a small review queue: positive-quantity paid rows with known prices, normalized status text, and a visible default for a missing customer identifier. Missing prices will be excluded, not replaced with zero. Displaying an unknown customer label does not create a real customer record or change the source data.
This lab uses a separate seven-row data-quality sample, not the clean sales fixture from earlier labs. You will first combine conditions, then examine missing values and simple functions, add labels with CASE, and change one condition to check your understanding. You already know SELECT, WHERE, ORDER BY, and SQL files. All services and table fixtures are prepared independently; use the Terminal.
Combine Row Conditions Deliberately
In this step, you will combine a quantity test with status alternatives and understand why parentheses matter.
Start in your project:
cd /home/labex/project
Read the prepared CSV before querying:
cat /home/labex/project/data/quality-orders.csv
There are seven business rows after the header. Notice the spaces around paid in r1, the \N markers in r2 and r3, the empty customer field in r6, and the zero quantity in r7. The prepared table interprets \N as SQL NULL; an empty text field remains an empty string. You will examine those distinctions shortly.
SQL comparisons such as quantity > 0 test each row. AND requires both conditions to be true; OR accepts either alternative. Parentheses group the status alternatives so a pending row with quantity zero does not bypass the quantity test.
Save a first filter using the SQL-file pattern you already practiced:
cat > /home/labex/project/queries/conditions.sql <<'SQL'
USE retail_filters;
SELECT order_id, quantity, order_status
FROM quality_orders
WHERE quantity > 0
AND (order_status = 'paid' OR order_status = 'pending')
ORDER BY order_id;
SQL
Run the file with the known Beeline connection and tab-separated output options:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/conditions.sql > /home/labex/project/output/conditions.txt
Read the result:
cat /home/labex/project/output/conditions.txt
The order rows should be:
r2 1 paid
r3 1 paid
r4 1 pending
r6 1 paid
r7 is excluded by its quantity, even though its status is pending. r1 is excluded because its stored text contains spaces and is not exactly 'paid'. The missing price in r3 has not been addressed by this filter yet. Each condition solves a specific problem; the next steps add the missing pieces.
Distinguish Missing Values from Empty Text
In this step, you will test NULL correctly and normalize display values without changing the stored rows.
NULL represents a missing or unknown value. It is not the text 'NULL' and is not an empty string. Use IS NULL or IS NOT NULL to test it, rather than = NULL.
Ask for missing and empty customer identifiers separately. The first statement selects r2; the second selects r6:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e "SELECT order_id FROM retail_filters.quality_orders WHERE customer_id IS NULL; SELECT order_id FROM retail_filters.quality_orders WHERE customer_id = '';"
Two small functions help display these records clearly. TRIM(text) removes spaces at the ends of a string. COALESCE(a,b) returns the first non-NULL value, so it can display 'unknown' when the identifier is NULL. It does not replace empty text, and it does not modify the underlying table.
Save an inspection query that applies those functions to all seven rows:
cat > /home/labex/project/queries/normalized-values.sql <<'SQL'
USE retail_filters;
SELECT order_id,
COALESCE(customer_id, 'unknown') AS customer_label,
TRIM(order_status) AS clean_status,
unit_price
FROM quality_orders
ORDER BY order_id;
SQL
Run it:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/normalized-values.sql > /home/labex/project/output/normalized-values.txt
Read the transcript:
cat /home/labex/project/output/normalized-values.txt
Find three observations: r1 now displays paid without padding, r2 displays customer label unknown, and r6 still has an empty customer field. r3 has a NULL price. A display default is appropriate for the missing customer label in this review queue, but inventing a zero price would misrepresent the order. The final filter will exclude unknown prices instead.
Label and Select a Review Queue
In this step, you will add a readable payment-status label and combine the row-quality checks into one query.
A SQL CASE expression chooses a value from conditions. It evaluates WHEN branches in order, uses the matching THEN value, and falls back to ELSE when no branch matches. You will label paid rows Payment received, pending rows Awaiting payment, and other statuses Other status.
Preview the labels over all rows before adding the review-queue filter:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex -e "SELECT order_id, CASE WHEN TRIM(order_status) = 'paid' THEN 'Payment received' WHEN TRIM(order_status) = 'pending' THEN 'Awaiting payment' ELSE 'Other status' END AS payment_label FROM retail_filters.quality_orders ORDER BY order_id;"
Find r5, whose refunded status takes the ELSE branch. r4 and r7 are labeled as awaiting payment. A status label describes the status; it does not by itself validate quantity or price.
Now build the queue's complete rule: positive quantity, known price, and normalized paid status. The source customer identifier remains unchanged. In the query result, display a customer label with COALESCE. The displayed result does not include a separate copy of the original identifier. Save the query:
cat > /home/labex/project/queries/filtered-orders.sql <<'SQL'
USE retail_filters;
SELECT order_id,
COALESCE(customer_id, 'unknown') AS customer_label,
TRIM(order_status) AS clean_status,
CASE
WHEN TRIM(order_status) = 'paid' THEN 'Payment received'
WHEN TRIM(order_status) = 'pending' THEN 'Awaiting payment'
ELSE 'Other status'
END AS payment_label
FROM quality_orders
WHERE quantity > 0
AND unit_price IS NOT NULL
AND TRIM(order_status) = 'paid'
ORDER BY order_id;
SQL
Execute it independently:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/filtered-orders.sql > /home/labex/project/output/filtered-orders.txt
Read the result:
cat /home/labex/project/output/filtered-orders.txt
Expected order rows:
r1 c1 paid Payment received
r2 unknown paid Payment received
r6 paid Payment received
The blank customer display field on r6 is intentional: it is empty text rather than NULL. r1 is included after trimming its status. r3 is excluded because its price is unknown, while pending, refunded, and zero-quantity rows are outside this queue. You have defined the intended rows without pretending that missing prices are zero.
Change One Condition and Predict the Result
In this step, you will reuse the same display expressions and quality rules for a pending-order queue.
Before running anything, predict the result if only the final status condition changes to pending. r4 has a positive quantity and known price, while r7 has quantity zero. Only r4 should remain, and its CASE label should be Awaiting payment.
Keep the first query intact and save a second file. Its selected expressions and quality checks are unchanged; only the final WHERE status value differs:
cat > /home/labex/project/queries/pending-review.sql <<'SQL'
USE retail_filters;
SELECT order_id,
COALESCE(customer_id, 'unknown') AS customer_label,
TRIM(order_status) AS clean_status,
CASE
WHEN TRIM(order_status) = 'paid' THEN 'Payment received'
WHEN TRIM(order_status) = 'pending' THEN 'Awaiting payment'
ELSE 'Other status'
END AS payment_label
FROM quality_orders
WHERE quantity > 0
AND unit_price IS NOT NULL
AND TRIM(order_status) = 'pending'
ORDER BY order_id;
SQL
Run it:
beeline -u 'jdbc:hive2://localhost:10000/default' -n labex --silent=true --showHeader=false --outputformat=tsv2 -f /home/labex/project/queries/pending-review.sql > /home/labex/project/output/pending-review.txt
Read the output:
cat /home/labex/project/output/pending-review.txt
Expected order row:
r4 c1 pending Awaiting payment
The table has not changed; the query selected a different set of rows and the existing CASE expression chose the corresponding label. In the next lab, you will build totals from correctly selected rows rather than combining data-quality rules and first-time aggregation in one jump.
Summary
You combined conditions with AND, OR, and parentheses, distinguished NULL from empty text, and used TRIM and COALESCE for explicit display behavior. You added CASE labels and defined a review queue without inventing monetary values or changing the source data.
A one-condition change produced the predicted pending queue while preserving the quality checks. Next, you will aggregate clean, deliberately selected order rows into sales measures.
