Roll Call

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Problem

Support needs a clean roster of everyone in the customer table. List each customer's first name, last name, and country, ordered alphabetically by first name, then by last name, and finally by country so identical names still fall in a fixed sequence.

Every name on the books, in order.

Tables

customers

Columns: customer_id INTEGER, first_name TEXT, last_name TEXT, country TEXT

orders

Columns: order_id INTEGER, status TEXT, region TEXT, profit REAL

Expected output

Worked solution and explanation

What this is really asking

Strip the costume and this is a single-table projection wearing a two-table schema. You are handed customers and orders and an easy-sounding ask, so the reflex is to reach for a join. But look at the keys: orders holds order_id, status, region, and profit, and there is no customer_id anywhere in it. There is nothing to join on. The skill being probed is discipline: read exactly what is asked, pull it from the one table that has it, and leave the distractor alone. Reach for orders and you either get an all-null column from a key that does not mean anything, or you fan every customer across every order.

Building it

Step 1: Start from the table that actually holds the data

All three requested fields (first_name, last_name, country) sit in customers. That single fact decides the FROM clause. No join, no subquery, no CTE.

Step 2: Project only the three columns asked for

Name the columns explicitly instead of SELECT star. The roster is a clean deliverable for support, so extra columns like customer_id are noise. Listing the columns also documents the exact contract of the output.

Step 3: Sort on two keys, in order

Sort by first_name first, then last_name. The second key is not decoration: when two people share a first name, it is the only thing that gives a stable, predictable order. Drop it and those rows come back in whatever order the engine happens to scan them.

SELECT first_name, last_name, country
FROM customers
ORDER BY first_name, last_name, country;

The reflex (wrong)

SELECT c.first_name, c.last_name, c.country, o.status FROM customers c LEFT JOIN orders o ON c.customer_id = o.order_id. The join key pairs a customer id against an order id, two unrelated number spaces, so status comes back null for every row and you have added a column nobody asked for.

The answer

SELECT first_name, last_name, country FROM customers ORDER BY first_name, last_name. Reads from the only table that carries the requested fields and returns exactly the roster that was asked for.

Common follow-up questions

• If orders did carry a `customer_id`, how would you attach each customer's most recent order status while still listing customers who have never ordered? (Tests whether they reach for a LEFT JOIN plus a per-customer latest-row pick, and understand why an inner join would silently drop order-less customers.)
• Suppose country can be null and you want those customers listed last. How does the sort change? (Tests awareness of null ordering semantics and how to force nulls to the end explicitly.)