# LEAKED MERNIS DATABASE

## Hazirlik ve Kurulum

Yeni kullanici ve database olusturulmasi:

```psql
USER mernis WITH PASSWORD 'password';
CREATE DATABASE leak;
GRANT ALL PRIVILEGES ON DATABASE leak to mernis;
```

Indirilen text dump dosyasinin yuklenemsi:

Not: 'ALTER TABLE public.citizen OWNER TO postgres;' satiri nedeniyle hata alinabilir, ya owner'i ya da
dosyayi degistirerek bu hatayi cozebilirsiniz (ve ya gercek bir bilgisayari gibi hicbir sey yapmayin
calismaya devam edecektir). Bu islem biraz uzun surebilir.

```
tar -zxvf mernis.tar.gz
psql --username mernis --dbname leak < data_dump.sql
```

## Database'e Baglanma ve Veri Kontrolu

```
psql --username mernis --dbname leak
```

Column ve Type'larini kontrol etmek icin:

```
leak=> \d citizen
```

```
...
 national_identifier      | text                 | not null
 first                    | text                 | not null
 last                     | text                 | not null
 mother_first             | text                 | not null
 father_first             | text                 | not null
 gender                   | character varying(1) | not null
 birth_city               | text                 | not null
 date_of_birth            | text                 | not null
 id_registration_city     | text                 | not null
 id_registration_district | text                 | not null
 address_city             | text                 | not null
 address_district         | text                 | not null
 address_neighborhood     | text                 | not null
 street_address           | text                 | not null
 door_or_entrance_number  | text                 | not null
 misc                     | text                 | not null
 ...
```
## Ornek Query'ler

### Istanbul'da yasayip, kutugu Istanbul'da olmayan insan sayisi (tahmini goc rakami):

select count(id_registration_city) as number from citizen where address_city = 'ISTANBUL' and id_registration_city != 'ISTANBUL';

```7151289```

### Istanbul'a en cok goc veren ilk 10 il:

select id_registration_city, count(id_registration_city) as number from citizen where address_city = 'ISTANBUL' and id_registration_city != 'ISTANBUL' group by id_registration_city order by number desc limit 10;

```
 id_registration_city | number
----------------------+--------
 SIVAS                | 482309
 KASTAMONU            | 363139
 GIRESUN              | 318214
 ORDU                 | 310016
 TOKAT                | 269422
 SAMSUN               | 254651
 TRABZON              | 250997
 MALATYA              | 240064
 SINOP                | 235073
 ERZINCAN             | 210607
```

### Istanbul'daki en yaygin ilk 10 erkek ismi:

select first, count(first) as number from citizen where address_city = 'ISTANBUL' and gender = 'E' group by first order by number desc limit 10;

```
  first  | number
---------+--------
 MEHMET  | 140602
 MUSTAFA | 125941
 AHMET   |  99865
 MURAT   |  87946
 ALI     |  86085
 HUSEYIN |  80489
 HASAN   |  74948
 ISMAIL  |  55221
 IBRAHIM |  54221
 OSMAN   |  38662
```

### Istanbul'daki en yaygin ilk 10 kadin ismi:

select first, count(first) as number from citizen where address_city = 'ISTANBUL' and gender = 'K' group by first order by number desc limit 10;

```
  first  | number
---------+--------
 FATMA   | 154597
 AYSE    | 114662
 EMINE   | 103470
 HATICE  |  84866
 ZEYNEP  |  52833
 ELIF    |  35430
 HULYA   |  34558
 OZLEM   |  33932
 SEVIM   |  32574
 YASEMIN |  31844
```

### Nufusu en az olan ilk 10 il:

select address_city, count(address_city) as number from citizen group by address_city order by number limit 10;

```
 address_city | number
--------------+--------
 BAYBURT      |  49675
 ARDAHAN      |  71240
 KILIS        |  73608
 GUMUSHANE    |  88319
 IGDIR        | 103716
 TUNCELI      | 110808
 HAKKARI      | 119775
 ARTVIN       | 122749
 YALOVA       | 136767
 BARTIN       | 138982
```

### Kutuge kayitli insan sayisi en az olan ilk 10 il:

select id_registration_city, count(id_registration_city) as number from citizen group by id_registration_city order by number limit 10;

```
 id_registration_city | number
----------------------+--------
 YALOVA               |  90362
 HAKKARI              | 137177
 KILIS                | 160155
 BAYBURT              | 163876
 IGDIR                | 172665
 BILECIK              | 198426
 KARAMAN              | 218307
 BARTIN               | 221850
 KARABUK              | 233251
 TUNCELI              | 234608
```

### Turkiye genelinde en sik dogum gerceklesen ilk 10 gun:

select date_of_birth, count(date_of_birth) as number from citizen group by date_of_birth order by number limit 10;

```
 date_of_birth | number
---------------+--------
 1/1/1966      | 180577
 1/1/1956      | 143843
 1/1/1965      | 143244
 1/1/1960      | 128328
 1/1/1964      | 109443
 1/1/1974      | 103824
 1/1/1950      |  98385
 1/1/1963      |  95640
 1/1/1970      |  94237
 1/1/1955      |  90063
```

### Turkiye genelinde 1990 yilinda en sik dogum gerceklesen ilk 10 gun:

select date_of_birth, count(date_of_birth) as number from citizen where date_of_birth like '%/1990' group by date_of_birth order by number desc limit 10;

```
 date_of_birth | number
---------------+--------
 1/1/1990      |  67926
 1/10/1990     |  11421
 1/3/1990      |  11263
 1/9/1990      |  11215
 1/5/1990      |  11084
 10/10/1990    |  10994
 1/7/1990      |  10937
 1/2/1990      |  10717
 1/8/1990      |  10260
 1/6/1990      |   9802
```

- Buyuk ihtimalle verideki dogum gunleri yanlis...

### En cok dogumun gerceklestigi ilk 10 yil:

```
  yil  | number
-------+--------
 1981  | 773149
 1980  | 725932
 1984  | 705406
 1982  | 705105
 1989  | 701716
 1986  | 698923
 1985  | 695638
 1990  | 694213
 1983  | 692594
 1979  | 685918
```