Query yang berkaitan dengan kependudukan

Query pada MySQL yang berkaitan dengan kependudukan

1. Mencari Nomor KTP yang sama

select * from penduduk where no_ktp = (select no_ktp from penduduk having(count(no_ktp)>1));
+----------+---------------+--------------+
| no_ktp | nama | alamat |
+----------+---------------+--------------+
| 64710008 | Casmadi S.Kom | Cirebon |
| 64710008 | Joko Prastiyo | Gunung Kidul |
+----------+---------------+--------------+
2 rows in set (0.00 sec)

2. Mendapatkan umur

"SELECT nama, tgl_lahir, CURDATE(),(YEAR(CURDATE())-YEAR(tgl_lahir)) - (RIGHT(CURDATE(),5)< >+------------------+------------+------------+----+
| nama | tgl_lahir | CURDATE() |umur|
+------------------+------------+------------+----+
| Casmadi S.Kom | 1995-10-16 | 2008-04-15 | 12 |
| Joko Prastiyo | 1983-01-11 | 2008-04-15 | 25 |
| Hadi Triwaskito | 1980-12-01 | 2008-04-15 | 27 |
+------------------+------------+------------+----+
3 rows in set (0.00 sec)

3. Mendapatkan Umur dibawah umur 17 (Tanggal Acuan Tgl Sekarang/saat ini)

" SELECT nama,tgl_lahir,CURDATE(),(YEAR(CURDATE())-YEAR(tgl_lahir)) - (RIGHT(CURDATE(),5)<>+---------+------------+------------+----+
| nama | tgl_lahir | CURDATE() |umur|
+---------+------------+------------+----+
| Casmadi | 1995-10-16 | 2008-04-15 | 12 |
+---------+------------+------------+----+
1 row in set (0.00 sec)

4. Mendapatkan umur dibawah 17 (tanggal yang telah ditetapkan, Contoh tgl 25 Oktober 2008)

"SELECT nama, tgl_lahir,
(YEAR( '2008-10-25' ) - YEAR( tgl_lahir )) - ( RIGHT( '2008-10-25', 5 ) <>
+---------+------------+------+
| nama | tgl_lahir | umur |
+---------+------------+------+
| Casmadi | 1995-10-16 | 13 |
+---------+------------+------+
1 row in set (0.00 sec)