Counting Customers Who Are Not There
A small stationery shop stores orders in a MySQL table orders(order_id, cust_name, cust_phone, amount). The owner runs SELECT COUNT(*) FROM orders; and gets 40. SELECT COUNT(cust_phone) FROM orders; returns 31. Nine orders are walk-in sales with NULL phone numbers. The owner then runs SELECT * FROM orders WHERE cust_phone <> '9876543210'; and gets 12 rows, although that phone number belongs to only one customer and appears on 19 orders. A student intern suggests making cust_phone NOT NULL and writing cust_phone = NULL to find missing numbers. The shop has 12 distinct customers, but cust_name is typed inconsistently, so the same person appears as 'R. Rao' and 'Rao'. Two orders also share the same order_id because of a manual entry. The owner wants a reliable list of customers with phone numbers, a count of orders per customer, and a safe way to add a primary key without losing the duplicate order. The database is live, and the owner cannot shut the shop for a day.
1. Explain why COUNT(*) returns 40 while COUNT(cust_phone) returns 31, and why the <> query returns 12 rather than 21. Show the arithmetic.
Read the worked answer
COUNT(*) counts rows regardless of content, so it returns 40. COUNT(cust_phone) counts only rows where cust_phone is not NULL; the 9 walk-in orders have NULL phone, so it returns 40 - 9 = 31. For the <> query, SQL uses three-valued logic: NULL <> '9876543210' is UNKNOWN, not TRUE, so those 9 rows are filtered out. Of the 31 non-NULL rows, 19 have the phone '9876543210'; the remaining 31 - 19 = 12 are returned. The owner may have expected 40 - 19 = 21, but that would require treating NULL as 'some other phone', which SQL does not do. The 12 is therefore correct for the query as written, but it is not a count of all other orders.
2. Write queries for (a) each distinct non-null phone with order count, and (b) count of orders with missing phone. Explain why cust_phone = NULL fails.
Read the worked answer
SELECT cust_phone, COUNT(*) AS order_count
FROM orders
WHERE cust_phone IS NOT NULL
GROUP BY cust_phone;
SELECT COUNT(*) AS missing_phone_orders
FROM orders
WHERE cust_phone IS NULL;
cust_phone = NULL fails because NULL is not a value; it marks an unknown. Any comparison with NULL yields UNKNOWN, and a WHERE clause keeps only rows for which the condition is TRUE, so = NULL returns no rows. The same applies to <> NULL. Use IS NULL or IS NOT NULL. For a reliable customer list, grouping by phone is safer than grouping by cust_name, because the names are inconsistent; if two different customers can share a phone, a separate customer ID is needed.
3. The duplicate order_id blocks a primary key. Describe a safe cleanup sequence and how to choose which duplicate row to keep. Include how to avoid losing the live data.
Read the worked answer
First export or back up the table, because the shop cannot stop and a delete is irreversible. Find duplicates with SELECT order_id, COUNT(*) FROM orders GROUP BY order_id HAVING COUNT(*) > 1; and inspect both rows. Choose the row to keep using a stated rule: the one with a non-NULL phone, the larger or more plausible amount, or the earlier entry if a timestamp exists. In a transaction, either delete the rejected duplicate or assign it a new order_id if the order is real and merely misnumbered; then recheck the count. Only after the duplicate is gone should you run ALTER TABLE orders ADD PRIMARY KEY (order_id);. If the true business key is not order_id, add a surrogate auto-increment key instead and keep order_id as a normal column. A live system may need a short lock or a replica, but the backup and the keep-rule are the parts that prevent data loss.