101
0/304
Loading content...
You maintain two relational datasets:
personId, firstName, lastName)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:
firstNamelastNamecitystateRows may be returned in any order unless your local runner requires deterministic ordering for comparison.
This problem should be solvable in both:
Person:
| personId | lastName | firstName |
|----------|----------|-----------|
| 1 | Carter | Maya |
| 2 | Ibrahim | Noor |
Address:
| addressId | personId | city | state |
|-----------|----------|-----------|-------|
| 10 | 2 | Austin | TX |
| 11 | 99 | Unused | ZZ |[
{"firstName":"Maya","lastName":"Carter","city":null,"state":null},
{"firstName":"Noor","lastName":"Ibrahim","city":"Austin","state":"TX"}
]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.
Person:
| personId | lastName | firstName |
|----------|----------|-----------|
| 7 | Silva | Ana |
Address:
| addressId | personId | city | state |
|-----------|----------|------------|-------|
| 70 | 7 | Porto | PT |[
{"firstName":"Ana","lastName":"Silva","city":"Porto","state":"PT"}
]The person has a matching address, so city and state are populated.
Person:
| personId | lastName | firstName |
|----------|----------|-----------|
| 3 | Khan | Arif |
| 4 | Khan | Sara |
Address:
| addressId | personId | city | state |
|-----------|----------|---------|-------|
| 40 | 4 | Delhi | DL |[
{"firstName":"Arif","lastName":"Khan","city":null,"state":null},
{"firstName":"Sara","lastName":"Khan","city":"Delhi","state":"DL"}
]Matching is performed by personId, not by names. Even though both rows share the same last name, only personId = 4 receives address data.
Constraints