Amazon-style Company ChallengeBeginnerVerified answerSQLite live

Employee Warehouse Assignment

Which employees are assigned to a warehouse, and what is the warehouse location for each of them?

  • Joins

Challenge brief

Understand the request

Fulfillment Operations is reviewing warehouse staffing and needs to know where each warehouse employee is currently based.

Show warehouse-assigned employees with their current warehouse location.

Return

  • first_name
  • last_name
  • warehouse_location

Constraints

  • Only include employees who have a warehouse assignment
  • Exclude office staff and executives (they have no warehouse)

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • warehouse_idINTEGER

warehouses

  • warehouse_idINTEGER
  • locationVARCHAR(100)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

The employee names you need are in the employees table. The warehouse location you need is in the warehouses table — not in employees. The link between them is the warehouse_id column that appears in both tables.

Hint 2

Use INNER JOIN to connect employees to warehouses on the shared warehouse_id. INNER JOIN automatically excludes employees who have no warehouse (their warehouse_id is NULL). Alias w.location as warehouse_location.

Hint 3

Scaffold: SELECT the two employee name fields and aliased warehouse location FROM employees e JOIN warehouses w ON the shared warehouse key.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
SELECT e.first_name, e.last_name, w.location AS warehouse_location FROM employees e INNER JOIN warehouses w ON e.warehouse_id = w.warehouse_id;

Why this works

INNER JOIN keeps only rows where both sides have a matching key. Since 5 employees have a warehouse_id and 5 do not (office staff), the join naturally returns 5 rows. No WHERE clause is needed — the INNER JOIN acts as the filter.

Success check

5 rows — one per warehouse-assigned employee

Expected result

Use this output to verify values, aliases, ordering, and row count.

first_namelast_namewarehouse_location
SarahConnorSeattle, WA
EmilyDavisSeattle, WA
MichaelBrownLos Angeles, CA
RachelGreenLos Angeles, CA
TomWilsonSeattle, WA

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.