124. Zaxira qiymatlar zanjiri
QiyinSubquery'larmy.gov.uz
Har bir fuqaro uchun bitta aloqa ma'lumotini tanlang. Ustuvorlik tartibi:
- mobil telefon (
kind = 'MOBILE') - uy telefoni (
kind = 'HOME') - elektron pochta (
kind = 'EMAIL') - hech biri yo'q bo'lsa —
no contactmatni
Har bir bosqichni alohida skalyar ichki so'rov bilan oling va ularni
COALESCE zanjiriga joylang. Bo'sh ichki so'rov NULL qaytaradi —
COALESCE shuning uchun keyingisiga o'tadi.
Bir turdan bir nechta yozuv bo'lsa, id eng kichigi olinsin.
Ustunlar: id, name, contact.
Saralash: id o'sish.
citizens: id INT PK, name VARCHAR
contacts: id INT PK, citizen_id INT, kind VARCHAR, value VARCHAR
Jadval: citizens
| Ustun | Turi |
|---|---|
| id | INT PK |
| name | VARCHAR(60) |
Jadval: contacts
| Ustun | Turi |
|---|---|
| id | INT PK |
| citizen_id | INT |
| kind | VARCHAR(10) |
| value | VARCHAR(60) |
Namuna ma'lumotlar
sample
citizens
| idINT | nameVARCHAR(60) |
|---|---|
| 1 | Aziz Karimov |
| 3 | Bekzod Rahimov |
| 5 | Jasur Abdullayev |
contacts
| idINT | citizen_idINT | kindVARCHAR(10) | valueVARCHAR(60) |
|---|---|---|---|
| 1 | 1 | MOBILE | +998901234567 |
| 5 | 3 | bekzod@example.com |
Kutilgan natija
| id | name | contact |
|---|---|---|
| 1 | Aziz Karimov | +998901234567 |
| 3 | Bekzod Rahimov | bekzod@example.com |
| 5 | Jasur Abdullayev | no contact |
sample-full
citizens
| idINT | nameVARCHAR(60) |
|---|---|
| 1 | Aziz Karimov |
| 2 | Malika Yusupova |
| 3 | Bekzod Rahimov |
| 4 | Nilufar Karimova |
| 5 | Jasur Abdullayev |
| 6 | Dilnoza Toshpulatova |
| 7 | Sardor Rahimov |
| 8 | Zilola Ergasheva |
contacts
| idINT | citizen_idINT | kindVARCHAR(10) | valueVARCHAR(60) |
|---|---|---|---|
| 1 | 1 | MOBILE | +998901234567 |
| 2 | 1 | aziz@example.com | |
| 3 | 2 | HOME | +998712345678 |
| 4 | 2 | malika@example.com | |
| 5 | 3 | bekzod@example.com | |
| 6 | 4 | MOBILE | +998933456789 |
| 7 | 4 | HOME | +998712223344 |
| 8 | 4 | MOBILE | +998944567890 |
| 9 | 6 | HOME | +998715556677 |
| 10 | 7 | sardor@example.com | |
| 11 | 7 | s.rahimov@example.com | |
| 12 | 8 | MOBILE | +998977890123 |
Kutilgan natija
| id | name | contact |
|---|---|---|
| 1 | Aziz Karimov | +998901234567 |
| 2 | Malika Yusupova | +998712345678 |
| 3 | Bekzod Rahimov | bekzod@example.com |
| 4 | Nilufar Karimova | +998933456789 |
| 5 | Jasur Abdullayev | no contact |
| 6 | Dilnoza Toshpulatova | +998715556677 |
| 7 | Sardor Rahimov | sardor@example.com |
| 8 | Zilola Ergasheva | +998977890123 |
sample-extra
citizens
| idINT | nameVARCHAR(60) |
|---|---|
| 2 | Diyor Ismoilov |
| 1 | Islom Abdullayev |
contacts
| idINT | citizen_idINT | kindVARCHAR(10) | valueVARCHAR(60) |
|---|---|---|---|
| 2 | 1 | MOBILE | +998977890123 |
| 1 | 1 | aziz@example.com |
Kutilgan natija
| id | name | contact |
|---|---|---|
| 1 | Islom Abdullayev | +998977890123 |
| 2 | Diyor Ismoilov | no contact |
O'xshash masalalar
Konsol
Natijani ko'rish uchun so'rovni ishga tushiring.