Person Directory Address Join

Easy

You maintain two relational datasets: Person : canonical people records (personId, firstName, lastName) Address : optional location records (addressId, personId, city, state) Build a result set that returns every person from the Person table, along with their city and state when a matching address exists. If a person has no address row, return NULL for city and state . Your output must contain exactly these columns: firstName lastName city state Rows may be returned in any order unless your local runner requires deterministic ordering for comparison. This problem should be solvable in both: SQL (SQLite 3.27.2) Pandas

Example 1

Input

Person:
| personId | lastName | firstName |
|----------|----------|-----------|
| 1        | Carter   | Maya      |
| 2        | Ibrahim  | Noor      |

Address:
| addressId | personId | city      | state |
|-----------|----------|-----------|-------|
| 10        | 2        | Austin    | TX    |
| 11        | 99       | Unused    | ZZ    |

Output

[
  {"firstName":"Maya","lastName":"Carter","city":null,"state":null},
  {"firstName":"Noor","lastName":"Ibrahim","city":"Austin","state":"TX"}
]

Explanation

All people must appear. `personId = 1` has no address, so its location fields are null. The orphan address for `personId = 99` is ignored because it has no matching person.

Example 2

Input

Person:
| personId | lastName | firstName |
|----------|----------|-----------|
| 7        | Silva    | Ana       |

Address:
| addressId | personId | city       | state |
|-----------|----------|------------|-------|
| 70        | 7        | Porto      | PT    |

Output

[
  {"firstName":"Ana","lastName":"Silva","city":"Porto","state":"PT"}
]

Explanation

The person has a matching address, so city and state are populated.

Example 3

Input

Person:
| personId | lastName | firstName |
|----------|----------|-----------|
| 3        | Khan     | Arif      |
| 4        | Khan     | Sara      |

Address:
| addressId | personId | city    | state |
|-----------|----------|---------|-------|
| 40        | 4        | Delhi   | DL    |

Output

[
  {"firstName":"Arif","lastName":"Khan","city":null,"state":null},
  {"firstName":"Sara","lastName":"Khan","city":"Delhi","state":"DL"}
]

Explanation

Matching is performed by `personId`, not by names. Even though both rows share the same last name, only `personId = 4` receives address data.

Constraints

1 <= number of rows in Person <= 10,000
0 <= number of rows in Address <= 10,000
Person.personId is unique
Address.addressId is unique
Address.personId may reference a person not present in Person
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: firstName, lastName, city, state

Preparing your editor. You can read the problem while it loads.

Person Directory Address Join

Easy

You maintain two relational datasets: Person : canonical people records (personId, firstName, lastName) Address : optional location records (addressId, personId, city, state) Build a result set that returns every person from the Person table, along with their city and state when a matching address exists. If a person has no address row, return NULL for city and state . Your output must contain exactly these columns: firstName lastName city state Rows may be returned in any order unless your local runner requires deterministic ordering for comparison. This problem should be solvable in both: SQL (SQLite 3.27.2) Pandas

Example 1

Input

Person:
| personId | lastName | firstName |
|----------|----------|-----------|
| 1        | Carter   | Maya      |
| 2        | Ibrahim  | Noor      |

Address:
| addressId | personId | city      | state |
|-----------|----------|-----------|-------|
| 10        | 2        | Austin    | TX    |
| 11        | 99       | Unused    | ZZ    |

Output

[
  {"firstName":"Maya","lastName":"Carter","city":null,"state":null},
  {"firstName":"Noor","lastName":"Ibrahim","city":"Austin","state":"TX"}
]

Explanation

All people must appear. `personId = 1` has no address, so its location fields are null. The orphan address for `personId = 99` is ignored because it has no matching person.

Example 2

Input

Person:
| personId | lastName | firstName |
|----------|----------|-----------|
| 7        | Silva    | Ana       |

Address:
| addressId | personId | city       | state |
|-----------|----------|------------|-------|
| 70        | 7        | Porto      | PT    |

Output

[
  {"firstName":"Ana","lastName":"Silva","city":"Porto","state":"PT"}
]

Explanation

The person has a matching address, so city and state are populated.

Example 3

Input

Person:
| personId | lastName | firstName |
|----------|----------|-----------|
| 3        | Khan     | Arif      |
| 4        | Khan     | Sara      |

Address:
| addressId | personId | city    | state |
|-----------|----------|---------|-------|
| 40        | 4        | Delhi   | DL    |

Output

[
  {"firstName":"Arif","lastName":"Khan","city":null,"state":null},
  {"firstName":"Sara","lastName":"Khan","city":"Delhi","state":"DL"}
]

Explanation

Matching is performed by `personId`, not by names. Even though both rows share the same last name, only `personId = 4` receives address data.

Constraints

1 <= number of rows in Person <= 10,000
0 <= number of rows in Address <= 10,000
Person.personId is unique
Address.addressId is unique
Address.personId may reference a person not present in Person
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: firstName, lastName, city, state