Operator Pembanding dan Operator Logika
Sudah saatnya kita melangkah ke permainan data yang lebih
mengasyikan lagi dengan
menggunakan dua operator, yaitu Operator Pembanding
dan Operator Logika. Kedua jenis
operator ini akan sering digunakan dalam proses
"query" data.
Operator Pembanding
Operator Pembanding
Lebih besar
Lebih kecil
Lebih besar atau sama dengan
Lebih kecil atau sama dengan
Sama dengan
Tidak sama dengan
Operator Logika
Operator Logika
Dan
Atau
Lebih besar atau sama
dengan
Lebih kecil atau sama dengan
Tidak sama dengan
Keterangan
>
<=
>=
<=
=
<>
Keterangan
AND atau &&
OR atau ||
NOT atau !
<=
<>
Berikut ini adalah penerapan dari kedua operator di atas:
Kita tampilkan data karyawan yang tanggal lahirnya sebelum
tanggal 1 Januari 1980, dan
tampilan data diurut berdasarkan nama. Cukup hanya kolom
nama, jenis kelamin dan tanggal lahir
saja yang ditampilkan:
mysql> select nama, jenkel, tgllahir
-> from karyawan
-> where tgllahir
< "1980-01-01"
-> order by nama ;
+--------------+--------+------------+
| nama | jenkel |
tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L |
1977-10-02 |
| Andika | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Banowati | P | 1978-11-12 |
| Beno | L | 1978-08-10 |
| Dadan | L | 1975-10-02 |
| Dadang | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L | 1977-08-10 |
| Mardiatun | P | 1975-07-07 |
| Melia | P | 1979-11-12 |
| Miranti | P | 1975-07-07 |
| Nenny | P | 1972-06-09 |
| Rahmat | L | 1977-03-21 |
| Ratu | P | 1972-11-12 |
| Sobari | L | 1976-10-02 |
| Subur | L | 1977-10-02 |
| Sundariwati | P | 1978-11-12 |
| Susilowati | P | 1973-11-12 |
| The Cute | L | 1977-03-21 |
| Wawan | P | 1971-11-12 |
| Yuliawati | P | 1974-06-09 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
25 rows in set (0.00 sec)
MySQL memiliki kelonggaran dalam penulisan tanggal
selama formatnya mengikuti aturan
"tahun-bulan-tanggal". Misal
"1971-11-12" dapat ditulis 1971-11-12, atau 1971#11#12, atau
19711112, atau 711112.
Kita lihat contohnya dibawah ini dimana tanggal
"1980-01-01" ditulis dengan 19800101
mysql> select nama, jenkel, tgllahir
-> from karyawan
-> where tgllahir
< 19800101
-> and
jenkel="L"
-> order by nama ;
+--------------+--------+------------+
| nama | jenkel |
tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L |
1977-10-02 |
| Andika | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Beno | L | 1978-08-10 |
| Dadan | L | 1975-10-02 |
| Dadang | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L
| 1977-08-10 |
| Rahmat | L | 1977-03-21 |
| Sobari | L | 1976-10-02 |
| Subur | L | 1977-10-02 |
| The Cute | L | 1977-03-21 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
15 rows in set (0.00 sec)
Kita lihat contohnya di bawah ini bila tanggal
"1980-01-01" ditulis dengan cara 800101.
mysql> select nama, jenkel, tgllahir
-> from karyawan
-> where tgllahir
< 800101
-> and
jenkel="L"
-> order by nama;
+--------------+--------+------------+
| nama | jenkel |
tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L |
1977-10-02 |
| Andika | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Beno | L | 1978-08-10 |
| Dadan | L | 1975-10-02 |
| Dadang | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L | 1977-08-10 |
| Rahmat | L | 1977-03-21 |
| Sobari | L | 1976-10-02 |
| Subur | L | 1977-10-02 |
| The Cute | L | 1977-03-21 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
15 rows in set (0.00 sec)
Kita lihat contohnya di bawah ini bila tanggal
"1980-01-01" ditulis dengan cara "1980#01#01".
mysql> select nama, jenkel, tgllahir
-> from karyawan
-> where tgllahir
< "1980#01#01"
-> and
jenkel="L"
-> order by nama ;
+--------------+--------+------------+
| nama | jenkel |
tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L |
1977-10-02 |
| Andika | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Beno | L | 1978-08-10 |
| Dadan | L | 1975-10-02 |
| Dadang | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L
| 1977-08-10 |
| Rahmat | L | 1977-03-21 |
| Sobari | L | 1976-10-02 |
| Subur | L | 1977-10-02 |
| The Cute | L | 1977-03-21 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
15 rows in set (0.00 sec)
Kita lihat contohnya di bawah ini bila tanggal
"1980-01-01" ditulis dengan cara "1980.01.01".
mysql> select nama,jenkel, tgllahir
-> from karyawan
-> where tgllahir
< "1980.01.01"
-> and
jenkel="L"
-> order by nama ;
+--------------+--------+------------+
| nama | jenkel |
tgllahir |
+--------------+--------+------------+
| Ahmad Sobari | L |
1977-10-02 |
| Andika | L | 1978-10-02 |
| Anwar | L | 1972-10-02 |
| Beno | L | 1978-08-10 |
| Dadan | L | 1975-10-02 |
| Dadang | L | 1977-08-10 |
| Donno | L | 1971-10-02 |
| Gunadi | L | 1978-08-10 |
| Maman | L | 1977-08-10 |
| Rahmat | L | 1977-03-21 |
| Sobari | L | 1976-10-02 |
| Subur | L | 1977-10-02 |
| The Cute | L | 1977-03-21 |
| Zamzam | L | 1974-08-10 |
| Zukarman | L | 1978-08-10 |
+--------------+--------+------------+
15 rows in set (0.00 sec)
Perhatikan semua hasil di atas sama walaupun cara penulisan
tanggalnya berbeda-beda (tetapi
formatnya tetap mengikuti "tahun-bulan-tanggal").
Sekarang kita tampilkan data karyawan yang tanggal lahirnya
antara tanggal 1 Januari 1980 dan
31 Desember 1985, dan
tampilan data diurut berdasarkan nama. Cukup hanya kolom nama, jenis
kelamin dan tanggal lahir saja yang ditampilkan:
mysql> select nama, jenkel, tgllahir
-> from karyawan
-> where tgllahir
>= "1980-01-01"
-> and tgllahir <=
"1985-12-31"
-> order by nama ;
+------------+--------+------------+
| nama | jenkel |
tgllahir |
+------------+--------+------------+
| Bambang | L | 1982-03-21 |
| Dian | P | 1980-07-07 |
| Gungun | L | 1981-03-21 |
| Mawar | P | 1985-07-07 |
| Melia | P | 1981-07-07 |
| Miranda | P | 1980-07-07 |
| Rohimat | L | 1980-03-21 |
| Ryan Cakep | L |
1981-03-21 |
| Yanti | P | 1981-06-09 |
| Yenny | P | 1985-06-09 |
| Yossy | P | 1982-06-09 |
| Yuliawati | P | 1982-06-09 |
| Zanda Cute | L |
1980-03-21 |
+------------+--------+------------+
13 rows in set (0.00 sec)
Sekarang kita tampilkan data karyawan yang tanggal lahirnya
antara tanggal 1 Januari 1980 dan
31 Desember 1985, dan
tampilan data diurut berdasarkan nama. Cukup hanya kolom nama, jenis
kelamin dan tanggal lahir saja, serta hanya yang berjenis
kelamin laki-laki yang ditampilkan:
mysql> select nama, jenkel, tgllahir
-> from karyawan
-> where tgllahir
>= "1980-01-01"
-> and tgllahir <=
"1985-12-31"
-> and
jenkel="L"
-> order by nama ;
+------------+--------+------------+
| nama | jenkel |
tgllahir |
+------------+--------+------------+
| Bambang | L | 1982-03-21 |
| Gungun | L | 1981-03-21 |
| Rohimat | L | 1980-03-21 |
| Ryan Cakep | L |
1981-03-21 |
| Zanda Cute | L |
1980-03-21 |
+------------+--------+------------+
5 rows in set (0.00 sec)
Bagaimana, semakin menarik kan? Kita lanjutkan dengan
menampilkan semua data karyawan
dengan usianya pada saat ini. Untuk masalah ini memang
cukup panjang solusinya. Tidak apa-apa,
kita coba saja ya. Disini kita memerlukan bantuan beberapa
fungsi-fungsi yang sudah disediakan
oleh MySQL. Kita lihat dulu ya..:
mysql> select nama, tgllahir,
-> current_date AS
SEKARANG,
->
(year(current_date) - year(tgllahir))
-> -
(right(current_date,5) < right(tgllahir,5))
AS USIA
-> from karyawan ;
+--------------+------------+------------+------+
| nama |
tgllahir | SEKARANG | USIA |
+--------------+------------+------------+------+
| Ahmad Sobari | 1977-10-02 | 2007-08-30 | 29 |
| Sundariwati | 1978-11-12
| 2007-08-30 | 28 |
| Ryan Cakep | 1981-03-21
| 2007-08-30 | 26 |
| Zukarman | 1978-08-10
| 2007-08-30 | 29 |
| Yuliawati | 1982-06-09
| 2007-08-30 | 25 |
| Mawar | 1985-07-07 | 2007-08-30 | 22 |
| Sobari | 1976-10-02
| 2007-08-30 | 30 |
| Melia | 1979-11-12
| 2007-08-30 | 27 |
| Zanda Cute | 1980-03-21
| 2007-08-30 | 27 |
| Maman | 1977-08-10
| 2007-08-30 | 30 |
| Yenny | 1985-06-09 | 2007-08-30 | 22 |
| Rossa | 1987-07-07
| 2007-08-30 | 20 |
| Dadan | 1975-10-02
| 2007-08-30 | 31 |
| Wawan | 1971-11-12
| 2007-08-30 | 35 |
| The Cute | 1977-03-21
| 2007-08-30 | 30 |
| Marpaung | 1988-08-10 | 2007-08-30 | 19 |
| Yono | 1989-06-09
| 2007-08-30 | 18 |
| Dian | 1980-07-07
| 2007-08-30 | 27 |
| Donno | 1971-10-02
| 2007-08-30 | 35 |
| Ratu | 1972-11-12
| 2007-08-30 | 34 |
| Bambang | 1982-03-21
| 2007-08-30 | 25 |
| Dadang | 1977-08-10
| 2007-08-30 | 30 |
| Yuliawati | 1974-06-09
| 2007-08-30 | 33 |
| Miranda | 1980-07-07
| 2007-08-30 | 27 |
| Subur | 1977-10-02
| 2007-08-30 | 29 |
| Banowati | 1978-11-12
| 2007-08-30 | 28 |
| Gungun | 1981-03-21
| 2007-08-30 | 26 |
| Gunadi | 1978-08-10
| 2007-08-30 | 29 |
| Yossy | 1982-06-09
| 2007-08-30 | 25 |
| Melia | 1981-07-07
| 2007-08-30 | 26 |
| Anwar | 1972-10-02 | 2007-08-30 | 34 |
| Susilowati | 1973-11-12
| 2007-08-30 | 33 |
| Rahmat | 1977-03-21
| 2007-08-30 | 30 |
| Zamzam | 1974-08-10
| 2007-08-30 | 33 |
| Nenny | 1972-06-09
| 2007-08-30 | 35 |
| Mardiatun | 1975-07-07 | 2007-08-30 | 32 |
| Andika | 1978-10-02
| 2007-08-30 | 28 |
| Siti | 1988-11-12
| 2007-08-30 | 18 |
| Rohimat | 1980-03-21
| 2007-08-30 | 27 |
| Beno | 1978-08-10
| 2007-08-30 | 29 |
| Yanti | 1981-06-09 | 2007-08-30 | 26 |
| Miranti | 1975-07-07
| 2007-08-30 | 32 |
+--------------+------------+------------+------+
42 rows in set (0.00 sec)
Kita lanjutkan dengan menampilkan data karyawan yang usianya
sama atau dibawah 25 tahun.
Nah bagaimana caranya?:
mysql> select nama, tgllahir,
-> current_date AS
SEKARANG,
->
(year(current_date) - year(tgllahir))
-> -
(right(current_date,5) < right(tgllahir,5))
-> AS USIA
-> from karyawan
-> where
((year(current_date) - year(tgllahir))
-> -
(right(current_date,5) < right(tgllahir,5)))
-> <= 25 ;
+-----------+------------+------------+------+
| nama | tgllahir | SEKARANG
| USIA |
+-----------+------------+------------+------+
| Yuliawati | 1982-06-09 | 2007-08-30 | 25 |
| Mawar | 1985-07-07 |
2007-08-30 | 22 |
| Yenny | 1985-06-09 |
2007-08-30 | 22 |
| Rossa | 1987-07-07 |
2007-08-30 | 20 |
| Marpaung | 1988-08-10 |
2007-08-30 | 19 |
| Yono | 1989-06-09 |
2007-08-30 | 18 |
| Bambang | 1982-03-21 |
2007-08-30 | 25 |
| Yossy | 1982-06-09 |
2007-08-30 | 25 |
| Siti | 1988-11-12 |
2007-08-30 | 18 |
+-----------+------------+------------+------+
9 rows in set (0.00 sec)
Cukup panjang perintahnya ya. Disini kita menggunakan
fungsi CURRENT_DATE yang
mengambil nilai dari tanggal saat ini pada sistem komputer
Anda. YEAR adalah fungsi yang
mengambil nilai tahun. Kemudian AS adalah singkatan
dari AliaS, yang seolah-olah memberikan
nama lain (alias name) pada kolom atau pada hasil
suatu proses. Sedangkan RIGHT adalah fungsi
yang mengambil nilai dari sekian karakter dari sisi kanan
sebuah target. Misal RIGHT("TOMAT",
3) maka akan menghasilkan karakter "MAT".
Baik kita lanjutkan tutorial ini dengan perintah-perintah
lainnya. Mari...
Kita akan menampilkan karyawan yang kota kelahirannya di
"Bandung":
mysql> select * from karyawan
-> where
kota="Bandung" ;
+------+--------------+--------+---------+---------+------------+---------+
| noid | nama |
jenkel | kota | kodepos |
tgllahir | gaji |
+------+--------------+--------+---------+---------+------------+---------+
| 1 | Ahmad Sobari |
L | Bandung | 41011 | 1977-10-02 | 1000000 |
| 2 | Sundariwati | P
| Bandung | 40123 | 1978-11-12 |
1250000 |
| 8 | Melia | P
| Bandung | 40123 | 1979-11-12 |
1200000 |
| 13 | Dadan | L
| Bandung | 41011 | 1975-10-02 |
1450000 |
| 19 | Donno | L
| Bandung | 41011 | 1971-10-02 |
1850000 |
| 25 | Subur | L
| Bandung | 41011 | 1977-10-02 |
2150000 |
| 32 | Susilowati | P
| Bandung | 40123 | 1973-11-12 |
1125000 |
| 37 | Andika | L
| Bandung | 41011 | 1978-10-02 |
1725000 |
+------+--------------+--------+---------+---------+------------+---------+
8 rows in set (0.03 sec)
Kita tampilkan karyawan yang kota kelahirannya bukan
di Bandung:
mysql> select * from karyawan
-> where kota !=
"bandung" ;
+------+------------+--------+------------+---------+------------+---------+
| noid | nama | jenkel
| kota | kodepos | tgllahir | gaji
|
+------+------------+--------+------------+---------+------------+---------+
| 3 | Ryan Cakep | L | Jakarta | 12111
| 1981-03-21 | 1500000 |
| 4 | Zukarman | L
| Bekasi | 17211 | 1978-08-10 | 1750000 |
| 5 | Yuliawati | P
| Bogor | 00000 | 1982-06-09 | 2000000 |
| 6 | Mawar | P
| Bogor | 12345 | 1985-07-07 | 2250000 |
| 7 | Sobari | L
| Jakarta | 41011 | 1976-10-02 | 1100000 |
| 9 | Zanda Cute | L | Jakarta | 12111
| 1980-03-21 | 1300000 |
| 10 | Maman | L
| Bekasi | 17211 | 1977-08-10 | 1400000 |
| 11 | Yenny | P
| Bogor | 00000 | 1985-06-09 | 1150000 |
| 12 | Rossa | P
| Jakarta | 12345 | 1987-07-07 | 1350000 |
| 14 | Wawan | P
| Semarang | 40123 | 1971-11-12 | 1600000 |
| 15 | The Cute | L
| Jakarta | 12111 | 1977-03-21 | 1700000 |
| 16 | Marpaung | L
| Surabaya | 17211 | 1988-08-10 | 1800000 |
| 17 | Yono | P
| Bogor | 00000 | 1989-06-09 | 1900000 |
| 18 | Dian | P
| Jakarta | 12345 | 1980-07-07 | 1650000 |
| 20 | Ratu | P
| Yogyakarta | 40123 |
1972-11-12 | 1950000 |
| 21 | Bambang | L
| Jakarta | 12111 | 1982-03-21 | 2100000 |
| 22 | Dadang | L
| Surabaya | 17211 | 1977-08-10 | 2200000 |
| 23 | Yuliawati | P
| Bogor | 00000 | 1974-06-09 | 2300000 |
| 24 | Miranda | P
| Bogor | 12345 | 1980-07-07 | 2400000 |
| 26 | Banowati | P
| Malang | 40123 | 1978-11-12 | 2350000 |
| 27 | Gungun | L
| Jakarta | 12111 | 1981-03-21 | 2450000 |
| 28 | Gunadi | L
| Bekasi | 17211 | 1978-08-10 | 2125000 |
| 29 | Yossy | P
| Bogor | 00000 | 1982-06-09 | 2225000 |
| 30 | Melia | P
| Malang | 12345 | 1981-07-07 | 2325000 |
| 31 | Anwar | L
| Purwakarta | 41011 |
1972-10-02 | 2425000 |
| 33 | Rahmat | L
| Jakarta | 12111 | 1977-03-21 | 1225000 |
| 34 | Zamzam | L
| Bekasi | 17211 | 1974-08-10 | 1325000 |
| 35 | Nenny | P
| Medan | 00000 | 1972-06-09 | 1425000 |
| 36 | Mardiatun | P
| Bogor | 12345 | 1975-07-07 | 1625000 |
| 38 | Siti | P
| Medan | 40123 | 1988-11-12 | 1825000 |
| 39 | Rohimat | L
| Jakarta | 12111 | 1980-03-21 | 1925000 |
| 40 | Beno | L
| Bekasi | 17211 | 1978-08-10 | 1175000 |
| 41 | Yanti | P
| Jakarta | 00000 | 1981-06-09 | 1275000 |
| 42 | Miranti | P
| Medan | 12345 | 1975-07-07 | 1375000 |
+------+------------+--------+------------+---------+------------+---------+
34 rows in set (0.00 sec)
Perintah di atas dapat juga menggunakan tanda "<>',
dan hasilnya tetap sama dengan di atas:
mysql> select * from karyawan
-> where kota
<> "bandung" ;
+------+------------+--------+------------+---------+------------+---------+
| noid | nama | jenkel
| kota | kodepos | tgllahir | gaji
|
+------+------------+--------+------------+---------+------------+---------+
| 3 | Ryan Cakep | L | Jakarta | 12111
| 1981-03-21 | 1500000 |
| 4 | Zukarman | L
| Bekasi | 17211 | 1978-08-10 | 1750000 |
| 5 | Yuliawati | P
| Bogor | 00000 | 1982-06-09 | 2000000 |
| 6 | Mawar | P
| Bogor | 12345 | 1985-07-07 | 2250000 |
| 7 | Sobari | L
| Jakarta | 41011 | 1976-10-02 | 1100000 |
| 9 | Zanda Cute | L | Jakarta | 12111
| 1980-03-21 | 1300000 |
| 10 | Maman | L
| Bekasi | 17211 | 1977-08-10 | 1400000 |
| 11 | Yenny | P
| Bogor | 00000 | 1985-06-09 | 1150000 |
| 12 | Rossa | P
| Jakarta | 12345 | 1987-07-07 | 1350000 |
| 14 | Wawan | P
| Semarang | 40123 | 1971-11-12 | 1600000 |
| 15 | The Cute | L
| Jakarta | 12111 | 1977-03-21 | 1700000 |
| 16 | Marpaung | L
| Surabaya | 17211 | 1988-08-10 | 1800000 |
| 17 | Yono | P
| Bogor | 00000 | 1989-06-09 | 1900000 |
| 18 | Dian | P
| Jakarta | 12345 | 1980-07-07 | 1650000 |
| 20 | Ratu | P
| Yogyakarta | 40123 |
1972-11-12 | 1950000 |
| 21 | Bambang | L
| Jakarta | 12111 | 1982-03-21 | 2100000 |
| 22 | Dadang | L
| Surabaya | 17211 | 1977-08-10 | 2200000 |
| 23 | Yuliawati | P
| Bogor | 00000 | 1974-06-09 | 2300000 |
| 24 | Miranda | P
| Bogor | 12345 | 1980-07-07 | 2400000 |
| 26 | Banowati |
P | Malang | 40123
| 1978-11-12 | 2350000 |
| 27 | Gungun | L
| Jakarta | 12111 | 1981-03-21 | 2450000 |
| 28 | Gunadi | L
| Bekasi | 17211 | 1978-08-10 | 2125000 |
| 29 | Yossy | P
| Bogor | 00000
| 1982-06-09 | 2225000 |
| 30 | Melia | P
| Malang | 12345 | 1981-07-07 | 2325000 |
| 31 | Anwar | L
| Purwakarta | 41011 |
1972-10-02 | 2425000 |
| 33 | Rahmat | L
| Jakarta | 12111 | 1977-03-21 | 1225000 |
| 34 | Zamzam | L
| Bekasi | 17211 | 1974-08-10 | 1325000 |
| 35 | Nenny | P
| Medan | 00000 | 1972-06-09 | 1425000 |
| 36 | Mardiatun | P
| Bogor | 12345 | 1975-07-07 | 1625000 |
| 38 | Siti | P
| Medan | 40123 | 1988-11-12 | 1825000 |
| 39 | Rohimat | L
| Jakarta | 12111 | 1980-03-21 | 1925000 |
| 40 | Beno | L
| Bekasi | 17211 | 1978-08-10 | 1175000 |
| 41 | Yanti | P
| Jakarta | 00000 | 1981-06-09 | 1275000 |
| 42 | Miranti | P
| Medan | 12345 | 1975-07-07 | 1375000 |
+------+------------+--------+------------+---------+------------+---------+
34 rows in set (0.00 sec)
Sekarang kita tampilkan karyawan dengan kota kelahiran
bukan di Bandung, Jakarta dan Bekasi.
Tampilan data diurut berdasarkan nama kota. Bagaimana
bentuk perintahnya?
mysql> select * from karyawan
-> where kota
<> "bandung"
-> and kota <>
"Jakarta"
-> and kota <>
"Bekasi"
-> order by kota ;
+------+-----------+--------+------------+---------+------------+---------+
| noid | nama | jenkel
| kota | kodepos | tgllahir | gaji
|
+------+-----------+--------+------------+---------+------------+---------+
| 5 | Yuliawati | P | Bogor | 00000
| 1982-06-09 | 2000000 |
| 36 | Mardiatun | P | Bogor | 12345
| 1975-07-07 | 1625000 |
| 29 | Yossy | P
| Bogor | 00000 | 1982-06-09 | 2225000 |
| 24 | Miranda | P
| Bogor | 12345 | 1980-07-07 | 2400000 |
| 23 | Yuliawati | P | Bogor | 00000
| 1974-06-09 | 2300000 |
| 17 | Yono | P
| Bogor | 00000 | 1989-06-09 | 1900000 |
| 6 | Mawar | P
| Bogor | 12345 | 1985-07-07 | 2250000 |
| 11 | Yenny | P
| Bogor | 00000 | 1985-06-09 | 1150000 |
| 30 | Melia | P
| Malang | 12345 | 1981-07-07 | 2325000 |
| 26 | Banowati | P
| Malang | 40123 | 1978-11-12 | 2350000 |
| 38 | Siti | P
| Medan | 40123 | 1988-11-12 | 1825000 |
| 35 | Nenny | P
| Medan | 00000 | 1972-06-09 | 1425000 |
| 42 | Miranti | P
| Medan | 12345 | 1975-07-07 | 1375000 |
| 31 | Anwar | L
| Purwakarta | 41011 |
1972-10-02 | 2425000 |
| 14 | Wawan | P
| Semarang | 40123 | 1971-11-12 | 1600000 |
| 16 | Marpaung | L
| Surabaya | 17211 | 1988-08-10 | 1800000 |
| 22 | Dadang | L
| Surabaya | 17211 | 1977-08-10 | 2200000 |
| 20 | Ratu | P
| Yogyakarta | 40123 |
1972-11-12 | 1950000 |
+------+-----------+--------+------------+---------+------------+---------+
18 rows in set (0.00 sec)
Hampir mirip dengan perintah di atas, tetapi selain diurut
berdasarkan kota, nama karyawan pun
ikut diurut. Kita coba dengan perintah dibawah:
mysql> select * from karyawan
-> where kota
<> "bandung"
-> and kota <>
"Jakarta"
-> and kota <>
"Bekasi"
-> order by kota and
nama ;
+------+-----------+--------+------------+---------+------------+---------+
| noid | nama | jenkel
| kota | kodepos | tgllahir | gaji
|
+------+-----------+--------+------------+---------+------------+---------+
| 5 | Yuliawati | P | Bogor | 00000
| 1982-06-09 | 2000000 |
| 38 | Siti | P
| Medan | 40123 | 1988-11-12 | 1825000 |
| 36 | Mardiatun | P | Bogor | 12345
| 1975-07-07 | 1625000 |
| 35 | Nenny | P
| Medan | 00000 | 1972-06-09 | 1425000 |
| 31 | Anwar | L
| Purwakarta | 41011 |
1972-10-02 | 2425000 |
| 30 | Melia | P
| Malang | 12345 | 1981-07-07 | 2325000 |
| 29 | Yossy | P
| Bogor | 00000 | 1982-06-09 | 2225000 |
| 26 | Banowati | P
| Malang | 40123 | 1978-11-12 | 2350000 |
| 24 | Miranda | P
| Bogor | 12345 | 1980-07-07 | 2400000 |
| 23 | Yuliawati | P | Bogor | 00000
| 1974-06-09 | 2300000 |
| 22 | Dadang | L
| Surabaya | 17211 | 1977-08-10 | 2200000 |
| 20 | Ratu | P
| Yogyakarta | 40123 |
1972-11-12 | 1950000 |
| 17 | Yono | P
| Bogor | 00000 | 1989-06-09 | 1900000 |
| 16 | Marpaung | L
| Surabaya | 17211 | 1988-08-10 | 1800000 |
| 14 | Wawan | P
| Semarang | 40123 | 1971-11-12 | 1600000 |
| 11 | Yenny | P
| Bogor | 00000 | 1985-06-09 | 1150000 |
| 6 | Mawar | P
| Bogor | 12345 | 1985-07-07 | 2250000 |
| 42 | Miranti | P
| Medan | 12345 | 1975-07-07 | 1375000 |
+------+-----------+--------+------------+---------+------------+---------+
18 rows in set (0.00 sec)
Coba perhatikan hasilnya. Apakah ini hasil yang kita
inginkan? Keliatannya ada yang tidak beres...
Kita coba lagi dengan menambah tanda kurung ( dan )
pada bagian perintah "ORDER BY", siapa
tahu berhasil:
mysql> select * from karyawan
-> where kota
<> "bandung"
-> and kota <>
"Jakarta"
-> and kota <>
"Bekasi"
-> order by (kota and
nama) ;
+------+-----------+--------+------------+---------+------------+---------+
| noid | nama | jenkel
| kota | kodepos | tgllahir | gaji
|
+------+-----------+--------+------------+---------+------------+---------+
| 5 | Yuliawati | P | Bogor | 00000
| 1982-06-09 | 2000000 |
| 38 | Siti | P
| Medan | 40123 | 1988-11-12 | 1825000 |
| 36 | Mardiatun | P | Bogor | 12345
| 1975-07-07 | 1625000 |
| 35 | Nenny | P
| Medan | 00000 | 1972-06-09 | 1425000 |
| 31 | Anwar | L
| Purwakarta | 41011 |
1972-10-02 | 2425000 |
| 30 | Melia | P
| Malang | 12345 | 1981-07-07 | 2325000 |
| 29 | Yossy |
P | Bogor | 00000
| 1982-06-09 | 2225000 |
| 26 | Banowati | P
| Malang | 40123 | 1978-11-12 | 2350000 |
| 24 | Miranda | P
| Bogor | 12345 | 1980-07-07 | 2400000 |
| 23 | Yuliawati | P | Bogor | 00000
| 1974-06-09 | 2300000 |
| 22 | Dadang | L
| Surabaya | 17211 | 1977-08-10 | 2200000 |
| 20 | Ratu | P
| Yogyakarta | 40123 |
1972-11-12 | 1950000 |
| 17 | Yono | P
| Bogor | 00000 | 1989-06-09 | 1900000 |
| 16 | Marpaung | L
| Surabaya | 17211 | 1988-08-10 | 1800000 |
| 14 | Wawan | P
| Semarang | 40123 | 1971-11-12 | 1600000 |
| 11 | Yenny | P
| Bogor | 00000 | 1985-06-09 | 1150000 |
| 6 | Mawar |
P | Bogor | 12345
| 1985-07-07 | 2250000 |
| 42 | Miranti | P
| Medan | 12345 | 1975-07-07 | 1375000 |
+------+-----------+--------+------------+---------+------------+---------+
18 rows in set (0.00 sec)
Hm, masih belum tepat juga. Kita coba lagi:
mysql> select * from karyawan
-> where kota
<> "bandung"
-> and kota <>
"Jakarta"
-> and kota <>
"Bekasi"
-> order by kota,
nama ;
+------+-----------+--------+------------+---------+------------+---------+
| noid | nama | jenkel
| kota | kodepos | tgllahir | gaji
|
+------+-----------+--------+------------+---------+------------+---------+
| 36 | Mardiatun | P | Bogor | 12345
| 1975-07-07 | 1625000 |
| 6 | Mawar | P
| Bogor | 12345 | 1985-07-07 | 2250000 |
| 24 | Miranda | P
| Bogor | 12345 | 1980-07-07 | 2400000 |
| 11 | Yenny | P
| Bogor | 00000 | 1985-06-09 | 1150000 |
| 17 | Yono | P
| Bogor | 00000 | 1989-06-09 | 1900000 |
| 29 | Yossy | P
| Bogor | 00000 | 1982-06-09 | 2225000 |
| 5 | Yuliawati | P | Bogor | 00000
| 1982-06-09 | 2000000 |
| 23 | Yuliawati | P | Bogor | 00000
| 1974-06-09 | 2300000 |
| 26 | Banowati | P
| Malang | 40123 | 1978-11-12 | 2350000 |
| 30 | Melia | P
| Malang | 12345 | 1981-07-07 | 2325000 |
| 42 | Miranti | P
| Medan | 12345 | 1975-07-07 | 1375000 |
| 35 | Nenny | P
| Medan | 00000 | 1972-06-09 | 1425000 |
| 38 | Siti | P
| Medan | 40123 | 1988-11-12 | 1825000 |
| 31 | Anwar | L
| Purwakarta | 41011 |
1972-10-02 | 2425000 |
| 14 | Wawan | P
| Semarang | 40123 | 1971-11-12 | 1600000 |
| 22 | Dadang | L
| Surabaya | 17211 | 1977-08-10 | 2200000 |
| 16 | Marpaung | L
| Surabaya | 17211 | 1988-08-10 | 1800000 |
| 20 | Ratu | P
| Yogyakarta | 40123 |
1972-11-12 | 1950000 |
+------+-----------+--------+------------+---------+------------+---------+
18 rows in set (0.00 sec)
Nah, ternyata sekarang baru berhasil. Coba sekali lagi
perhatikan permintaannya: "tampilkan
karyawan dengan kota kelahiran bukan di Bandung, Jakarta dan Bekasi.
Tampilan data diurut
berdasarkan nama kota dan
juga nama karyawan." Walaupun ada kata "dan" di sini,
tetapi tidak
semata-mata kita bisa menggunakan operator logika AND.
Memang diperlukan kejelian dan coba-
coba dalam permainan logika ini.
Tidak ada komentar:
Posting Komentar