Query menggunakan parameter kondisi WHERE
mysql> select*from pegawai1 where jabatan="Staff junior";
+----------+----------------+------------+--------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+----------------+------------+--------------+---------+
| PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 |
| PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 |
+----------+----------------+------------+--------------+---------+
4 rows in set (0.02 sec)
Perintah diatas menunjukkan data pegawai yang difilter berdasarkan jabatan yang berisi staff junior
mysql> select nip,nama_peg from pegawai1 where jabatan="Staff junior";
+----------+----------------+
| nip | nama_peg |
+----------+----------------+
| PEG-1010 | Rano Karno |
| PEG-1011 | Rahmadi Sholeh |
| PEG-1012 | Ilham Ungara |
| PEG-1013 | Endang melati |
+----------+----------------+
4 rows in set (0.00 sec)
Untuk menampilkan tidak semua data atau hanya data pada kolom-kolom tertentu yang telah dituliskan dan tetap berdasarkan jabatan = junior staff
Query menggunakan beberapa parameter kondisional
mysql> select*from pegawai1 where alamat_peg="Yogyakarta" and gaji <4000000;
+----------+----------------+------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+----------------+------------+----------------+---------+
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+----------------+------------+----------------+---------+
3 rows in set (0.00 sec)
Perintah diatas menunjukkan pemfilteran dengan 2 kondisi yaitu berdasarkan alamat_peg dan gaji yang kurang dari 4 juta.
mysql> select nip,nama_peg from pegawai1 where alamat_peg="Yogyakarta" or alamat_peg="Jakarta";
+----------+-----------------------+
| nip | nama_peg |
+----------+-----------------------+
| PEG-1001 | Soeharto Mangundirejo |
| PEG-1003 | Olga syahputra |
| PEG-1008 | Ely Oktafiani |
| PEG-1011 | Rahmadi Sholeh |
| PEG-1012 | Ilham Ungara |
| PEG-1015 | Paijem |
+----------+-----------------------+
6 rows in set (0.00 sec)
Menunjukkan data yang mempunyai alamat di Yogyakarta atau di Jakarta.
Memberikan alias hasil query pada SELECT
mysql> select nama_peg as nama_pegawai, alamat_peg as asal from pegawai1 where alamat_peg="Jakarta";
+----------------+---------+
| nama_pegawai | asal |
+----------------+---------+
| Olga syahputra | Jakarta |
| Ilham Ungara | Jakarta |
+----------------+---------+
2 rows in set (0.00 sec)
Saat menampilkan hasil, nama kolom akan diganti dengan perintah as
Query data bertipe teks dengan pattern matching
mysql> select nama_peg from pegawai1 where jabatan like "M______";
+---------------+
| nama_peg |
+---------------+
| Felix Nababan |
+---------------+
1 row in set (0.00 sec)
Menunjukkan bahwa data yang diminta adalah data yang memuat Jabatan berawalan M dan berjumlah 7 huruf.
mysql> select nama_peg from pegawai1 where jabatan like "M%";
+---------------+
| nama_peg |
+---------------+
| Felix Nababan |
+---------------+
1 row in set (0.00 sec)
Menunjukkan bahwa data yang diminta adalah data nama pegawai yang jabatannya berawalan huruf ‘M’
mysql> select nama_peg from pegawai1 where alamat_peg like "%A"
+-----------------------+
| nama_peg |
+-----------------------+
| Soeharto Mangundirejo |
| Olga syahputra |
| Ely Oktafiani |
| Rahmadi Sholeh |
| Ilham Ungara |
| Paijem |
+-----------------------+
6 rows in set (0.00 sec)
Menunjukkan data yang diminta adalah data nama pegawai yang alamatnya berakhiran huruf “A”
Query unik milik DISTINCT
mysql> select distinct alamat_peg from pegawai1;
+-------------+
| alamat_peg |
+-------------+
| Yogyakarta |
| Medan |
| Jakarta |
| Bandung |
| Jawa Tengah |
| Malang |
| Kudus |
| Magelang |
| Solo |
| Madiun |
| Makasar |
+-------------+
11 rows in set (0.00 sec)
Membatasi hasil query dengan LIMIT
mysql> select*from pegawai1 LIMIT 5;
+----------+-----------------------+-------------+-------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+-------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
+----------+-----------------------+-------------+-------------+---------+
5 rows in set (0.00 sec)
Mengelompokkan hasil query dengan GROUP BY
mysql> select*from pegawai1 group by gaji;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
+----------+-----------------------+-------------+----------------+---------+
8 rows in set (0.00 sec)
Menunjukkan pengelompokkan data berdasarkan gaji
Mendapatkan jumlah anggota setiap kelompok COUNT ()
mysql> select gaji, count(*) from pegawai1 group by gaji;
+---------+----------+
| gaji | count(*) |
+---------+----------+
| 500000 | 1 |
| 1000000 | 1 |
| 2000000 | 4 |
| 3000000 | 3 |
| 4500000 | 2 |
| 6000000 | 2 |
| 8000000 | 1 |
| 9000000 | 1 |
+---------+----------+
8 rows in set (0.00 sec)
Parameter kondisional HAVING
mysql> select*from pegawai1 having gaji>6000000;
+----------+-----------------------+------------+---------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+------------+---------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
+----------+-----------------------+------------+---------+---------+
2 rows in set (0.00 sec)
Mengurutkan hasil query menggunakan ORDER BY
mysql> select*from pegawai1 order by nama_peg;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
+----------+-----------------------+-------------+----------------+---------+
15 rows in set (0.00 sec)
mysql> select*from pegawai1 order by nama_peg desc;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
+----------+-----------------------+-------------+----------------+---------+
15 rows in set (0.00 sec)
Membuat tabel baru dengan nama Pegawai3
mysql> create table pegawai3(nama varchar(20), alamat varchar(20));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into pegawai3 value('Zeze','Yogyakarta');
Query OK, 1 row affected (0.03 sec)
mysql> insert into pegawai3 value('Zeze','Yogyakarta'),('Zeze','Jakarta'),('Riza','Bandung'),('Riza','Aceh'),('Amir','Demak'),('Amir','Cilacap');
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> select*from pegawai3;
+------+------------+
| nama | alamat |
+------+------------+
| Riza | Bandung |
| Riza | Aceh |
| Amir | Demak |
| Amir | Cilacap |
| Zeze | Yogyakarta |
+------+------------+
5 rows in set (0.00 sec)
Mengurutkan berdasarkan nama dan alamat
mysql> select nama, alamat from pegawai3 order by nama, alamat;
+------+------------+
| nama | alamat |
+------+------------+
| Amir | Cilacap |
| Amir | Demak |
| Riza | Aceh |
| Riza | Bandung |
| Zeze | Jakarta |
| Zeze | Yogyakarta |
+------+------------+
6 rows in set (0.00 sec)
Kombinasi ORDER BY dengan LIMIT
mysql> select*from pegawai1 order by gaji limit 5;
+----------+----------------+------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+----------------+------------+----------------+---------+
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 |
+----------+----------------+------------+----------------+---------+
5 rows in set (0.00 sec)
Operator BETWEEN
mysql> select*from pegawai1 where gaji between 4000000 and 9000000;
+----------+-----------------------+-------------+-------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+-------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
+----------+-----------------------+-------------+-------------+---------+
6 rows in set (0.00 sec)
Perintah diatas menampilkan gaji diantara 4000000 dan 9000000
**note : input range diawali dengan urutan yang lebih kecil, contoh 4jt – 9jt
mysql> select nama_peg, gaji from pegawai1 where nama_peg not between"Ely Oktafiani" and "Olga syahputra";
+-----------------------+---------+
| nama_peg | gaji |
+-----------------------+---------+
| Soeharto Mangundirejo | 9000000 |
| Chelsea Olivia | 6000000 |
| Tuti Wardani | 4500000 |
| Budi Drajat | 4500000 |
| Bambang Pamungkas | 3000000 |
| Rani Wijaya | 3000000 |
| Rano Karno | 2000000 |
| Rahmadi Sholeh | 2000000 |
| Donny Damara | 1000000 |
| Paijem | 500000 |
+-----------------------+---------+
10 rows in set (0.00 sec)
MAX, MIN, AVERAGE data dari tabel
mysql> select MIN(gaji) from pegawai1;
+-----------+
| MIN(gaji) |
+-----------+
| 500000 |
+-----------+
1 row in set (0.00 sec)
mysql> select max(gaji) from pegawai1;
+-----------+
| max(gaji) |
+-----------+
| 9000000 |
+-----------+
1 row in set (0.00 sec)
mysql> select avg(gaji) from pegawai1;
+--------------+
| avg(gaji) |
+--------------+
| 3766666.6667 |
+--------------+
1 row in set (0.00 sec)
mysql> select sum(gaji) from pegawai1;
+-----------+
| sum(gaji) |
+-----------+
| 56500000 |
+-----------+
1 row in set (0.00 sec)
SUB QUERY
mysql> select nama_peg, gaji from pegawai1 where gaji=(select max(gaji) from pegawai1);
+-----------------------+---------+
| nama_peg | gaji |
+-----------------------+---------+
| Soeharto Mangundirejo | 9000000 |
+-----------------------+---------+
1 row in set (0.00 sec)
Perintah ini hanya bias menerima satu buah hasil dari sub query, jika hasil dari sub query ada lebih dari satu maka akan terjadi error.
mysql> select nama_peg, gaji from pegawai1 where alamat_peg=(select alamat_peg from pegawai1 where alamat_peg like 'J%');
ERROR 1242 (21000): Subquery returns more than 1 row
Error karena hasil dari sub query diatas lebih dari satu
mysql> select nama_peg, gaji from pegawai1 where gaji < all(select gaji from pegawai1 where gaji > '2500000');
+----------------+---------+
| nama_peg | gaji |
+----------------+---------+
| Rano Karno | 2000000 |
| Rahmadi Sholeh | 2000000 |
| Ilham Ungara | 2000000 |
| Endang melati | 2000000 |
| Donny Damara | 1000000 |
| Paijem | 500000 |
+----------------+---------+
6 rows in set (0.00 sec)
Menunjukkan penampilan atas nama pegawai beserta gajinya yang memiliki gaji kurang dari 2,5 juta.
mysql> select nama_peg, gaji from pegawai1 where gaji < any(select gaji from pegawai1 where gaji > '2000000');
+-------------------+---------+
| nama_peg | gaji |
+-------------------+---------+
| Felix Nababan | 8000000 |
| Olga syahputra | 6000000 |
| Chelsea Olivia | 6000000 |
| Tuti Wardani | 4500000 |
| Budi Drajat | 4500000 |
| Bambang Pamungkas | 3000000 |
| Ely Oktafiani | 3000000 |
| Rani Wijaya | 3000000 |
| Rano Karno | 2000000 |
| Rahmadi Sholeh | 2000000 |
| Ilham Ungara | 2000000 |
| Endang melati | 2000000 |
| Donny Damara | 1000000 |
| Paijem | 500000 |
+-------------------+---------+
14 rows in set (0.00 sec)
Sub Query dengan EXISTS
mysql> select*from pegawai1 where exists (select*from pegawai1 where alamat_peg="Yogyakarta");
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 |
| PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
15 rows in set (0.00 sec)
mysql> select*from pegawai1 where exists (select*from pegawai1 where alamat_peg="Kebumen");
Empty set (0.00 sec)
mysql> select*from pegawai1 where not exists (select*from pegawai1 where alamat_peg="Kebumen");
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 |
| PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
15 rows in set (0.00 sec)
Sub query dengan IN
mysql> create table job(id int(2) primary key, job varchar(20));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into job value('1','Supervisor'),('2','Staff Senior'),('3','Staff Junior'),('4','Tenaga Kontrak');
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select*from job;
+----+----------------+
| id | job |
+----+----------------+
| 1 | Supervisor |
| 2 | Staff Senior |
| 3 | Staff Junior |
| 4 | Tenaga Kontrak |
+----+----------------+
4 rows in set (0.00 sec)
mysql> select nip, nama_peg, jabatan from pegawai1 where jabatan IN (select job from job);
+----------+-------------------+----------------+
| nip | nama_peg | jabatan |
+----------+-------------------+----------------+
| PEG-1005 | Tuti Wardani | Supervisor |
| PEG-1006 | Budi Drajat | Supervisor |
| PEG-1007 | Bambang Pamungkas | Staff Senior |
| PEG-1008 | Ely Oktafiani | Staff Senior |
| PEG-1009 | Rani Wijaya | Staff Senior |
| PEG-1010 | Rano Karno | Staff junior |
| PEG-1011 | Rahmadi Sholeh | Staff junior |
| PEG-1012 | Ilham Ungara | Staff junior |
| PEG-1013 | Endang melati | Staff junior |
| PEG-1014 | Donny Damara | Tenaga Kontrak |
| PEG-1015 | Paijem | Tenaga Kontrak |
+----------+-------------------+----------------+
11 rows in set (0.00 sec)
Table Join
Membuat table jabatan:
mysql> desc jabatan;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_jabatan | int(2) | NO | PRI | | |
| nm_jabatan | varchar(20) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into jabatan value('1','C.E.O'),('2','Manager'),('3','Kepala Unit'),('4','Supervisor'),('5','Staff Senior'),('6','Staff Junior'),('7','Tenaga Kontrak’);
Query OK, 7 rows affected (0.05 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> select*from jabatan;
+------------+----------------+
| id_jabatan | nm_jabatan |
+------------+----------------+
| 1 | C.E.O |
| 2 | Manager |
| 3 | Kepala Unit |
| 4 | Supervisor |
| 5 | Staff Senior |
| 6 | Staff Junior |
| 7 | Tenaga Kontrak |
+------------+----------------+
7 rows in set (0.00 sec)
mysql> create table pegawai_join as (select*from pegawai1);
Query OK, 15 rows affected (0.09 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql> select*from pegawai_join;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 |
| PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
15 rows in set (0.00 sec)
Untuk merelasikan table pegawai_join dan tabel jabatan kita tambahkan kolom id_jabatan pada tabel pegawai_join untuk mereferensi kedua tabel.
mysql> alter table pegawai_join add id_jabatan int(2) after alamat_peg;
Query OK, 15 rows affected (0.17 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql> desc pegawai_join;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| nip | varchar(8) | NO | | | |
| nama_peg | varchar(50) | YES | | NULL | |
| alamat_peg | varchar(50) | YES | | NULL | |
| id_jabatan | int(2) | YES | | NULL | |
| jabatan | varchar(20) | YES | | NULL | |
| gaji | int(7) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> update pegawai_join, jabatan set pegawai_join.id_jabatan=jabatan.id_jabatan where pegawai_join.jabatan=jabatan.nm_jabatan;
Query OK, 15 rows affected (0.03 sec)
Rows matched: 15 Changed: 15 Warnings: 0
mysql> select*from pegawai_join;
+----------+-----------------------+-------------+------------+----------------+---------+
| nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji |
+----------+-----------------------+-------------+------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |
| PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 |
| PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+------------+----------------+---------+
15 rows in set (0.00 sec)
Cross Join
mysql> select*from pegawai_join, jabatan;
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
| nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji | id_jabatan | nm_jabatan |
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 1 | C.E.O |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 2 | Manager |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 3 | Kepala Unit |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 4 | Supervisor |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 5 | Staff Senior |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 6 | Staff Junior |
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 7 | Tenaga Kontrak |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 1 | C.E.O |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 2 | Manager |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 3 | Kepala Unit |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 4 | Supervisor |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 5 | Staff Senior |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 6 | Staff Junior |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 7 | Tenaga Kontrak |
| PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 | 1 | C.E.O |
| PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 | 2 | Manager |
| PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 | 3 | Kepala Unit |
| PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 | 4 | Supervisor |
| PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 | 5 | Staff Senior |
| PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 | 6 | Staff Junior |
| PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 | 7 | Tenaga Kontrak |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 | 1 | C.E.O |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 | 2 | Manager |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 | 3 | Kepala Unit |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 | 4 | Supervisor |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 | 5 | Staff Senior |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 | 6 | Staff Junior |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 | 7 | Tenaga Kontrak |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 1 | C.E.O |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 2 | Manager |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 3 | Kepala Unit |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 4 | Supervisor |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 5 | Staff Senior |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 6 | Staff Junior |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 7 | Tenaga Kontrak |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 1 | C.E.O |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 2 | Manager |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 3 | Kepala Unit |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 4 | Supervisor |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 5 | Staff Senior |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 6 | Staff Junior |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 7 | Tenaga Kontrak |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 1 | C.E.O |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 2 | Manager |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 3 | Kepala Unit |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 4 | Supervisor |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 6 | Staff Junior |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 7 | Tenaga Kontrak |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 1 | C.E.O |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 2 | Manager |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 3 | Kepala Unit |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 4 | Supervisor |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 6 | Staff Junior |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 7 | Tenaga Kontrak |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 1 | C.E.O |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 2 | Manager |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 3 | Kepala Unit |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 4 | Supervisor |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 6 | Staff Junior |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 7 | Tenaga Kontrak |
| PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 | 1 | C.E.O |
| PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 | 2 | Manager |
| PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 | 3 | Kepala Unit |
| PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 | 4 | Supervisor |
| PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 | 5 | Staff Senior |
| PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 | 7 | Tenaga Kontrak |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 | 1 | C.E.O |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 | 2 | Manager |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 | 3 | Kepala Unit |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 | 4 | Supervisor |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 | 5 | Staff Senior |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 | 7 | Tenaga Kontrak |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 | 1 | C.E.O |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 | 2 | Manager |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 | 3 | Kepala Unit |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 | 4 | Supervisor |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 | 5 | Staff Senior |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 | 7 | Tenaga Kontrak |
| PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 | 1 | C.E.O |
| PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 | 2 | Manager |
| PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 | 3 | Kepala Unit |
| PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 | 4 | Supervisor |
| PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 | 5 | Staff Senior |
| PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 | 7 | Tenaga Kontrak |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 1 | C.E.O |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 2 | Manager |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 3 | Kepala Unit |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 4 | Supervisor |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 5 | Staff Senior |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 6 | Staff Junior |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 7 | Tenaga Kontrak |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 1 | C.E.O |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 2 | Manager |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 3 | Kepala Unit |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 4 | Supervisor |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 5 | Staff Senior |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 6 | Staff Junior |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 7 | Tenaga Kontrak |
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
105 rows in set (0.00 sec)
Equi-Join atau Inner Join
mysql> select*from pegawai1, jabatan where pegawai1.jabatan=jabatan.nm_jabatan;
+----------+-----------------------+-------------+----------------+---------+------------+----------------+
| nip | nama_peg | alamat_peg | jabatan | gaji | id_jabatan | nm_jabatan |
+----------+-----------------------+-------------+----------------+---------+------------+----------------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | 1 | C.E.O |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | 2 | Manager |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 | 3 | Kepala Unit |
| PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 | 3 | Kepala Unit |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | 4 | Supervisor |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 | 4 | Supervisor |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | 7 | Tenaga Kontrak |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | 7 | Tenaga Kontrak |
+----------+-----------------------+-------------+----------------+---------+------------+----------------+
15 rows in set (0.00 sec)
mysql> select*from pegawai_join inner join jabatan using (id_jabatan);
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
| id_jabatan | nip | nama_peg | alamat_peg | jabatan | gaji | nm_jabatan |
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
| 1 | PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | C.E.O |
| 2 | PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | Manager |
| 3 | PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 | Kepala Unit |
| 3 | PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 | Kepala Unit |
| 4 | PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | Supervisor |
| 4 | PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 | Supervisor |
| 5 | PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | Staff Senior |
| 5 | PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | Staff Senior |
| 5 | PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | Staff Senior |
| 6 | PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 | Staff Junior |
| 6 | PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 | Staff Junior |
| 6 | PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 | Staff Junior |
| 6 | PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 | Staff Junior |
| 7 | PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | Tenaga Kontrak |
| 7 | PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | Tenaga Kontrak |
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
15 rows in set (0.00 sec)
Natural Join
mysql> select*from pegawai_join natural join jabatan;
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
| id_jabatan | nip | nama_peg | alamat_peg | jabatan | gaji | nm_jabatan |
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
| 1 | PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 | C.E.O |
| 2 | PEG-1002 | Felix Nababan | Medan | Manager | 8000000 | Manager |
| 3 | PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 | Kepala Unit |
| 3 | PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 | Kepala Unit |
| 4 | PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 | Supervisor |
| 4 | PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 | Supervisor |
| 5 | PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 | Staff Senior |
| 5 | PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 | Staff Senior |
| 5 | PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 | Staff Senior |
| 6 | PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 | Staff Junior |
| 6 | PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 | Staff Junior |
| 6 | PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 | Staff Junior |
| 6 | PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 | Staff Junior |
| 7 | PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 | Tenaga Kontrak |
| 7 | PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 | Tenaga Kontrak |
+------------+----------+-----------------------+-------------+----------------+---------+----------------+
15 rows in set (0.00 sec)
Left Join & Right Join
mysql> insert into jabatan values(8,'Tenaga Magang');
Query OK, 1 row affected (0.05 sec)
mysql> select*from jabatan t1 left join pegawai_join t2 on t1.id_jabatan=t2.id_jabatan;
+------------+----------------+----------+-----------------------+-------------+------------+----------------+---------+
| id_jabatan | nm_jabatan | nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji |
+------------+----------------+----------+-----------------------+-------------+------------+----------------+---------+
| 1 | C.E.O | PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 |
| 2 | Manager | PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 |
| 3 | Kepala Unit | PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 |
| 3 | Kepala Unit | PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 |
| 4 | Supervisor | PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 |
| 4 | Supervisor | PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 |
| 5 | Staff Senior | PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 |
| 5 | Staff Senior | PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 |
| 5 | Staff Senior | PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 |
| 6 | Staff Junior | PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 |
| 6 | Staff Junior | PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 |
| 6 | Staff Junior | PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 |
| 6 | Staff Junior | PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 |
| 7 | Tenaga Kontrak | PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 |
| 7 | Tenaga Kontrak | PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 |
| 8 | Tenaga Magang | NULL | NULL | NULL | NULL | NULL | NULL |
+------------+----------------+----------+-----------------------+-------------+------------+----------------+---------+
16 rows in set (0.00 sec)
mysql> select*from pegawai_join t1 right join jabatan t2 on t1.id_jabatan=t2.id_jabatan;
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
| nip | nama_peg | alamat_peg | id_jabatan | jabatan | gaji | id_jabatan | nm_jabatan |
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | 1 | C.E.O | 9000000 | 1 | C.E.O |
| PEG-1002 | Felix Nababan | Medan | 2 | Manager | 8000000 | 2 | Manager |
| PEG-1003 | Olga syahputra | Jakarta | 3 | kepala unit | 6000000 | 3 | Kepala Unit |
| PEG-1004 | Chelsea Olivia | Bandung | 3 | kepala unit | 6000000 | 3 | Kepala Unit |
| PEG-1005 | Tuti Wardani | Jawa Tengah | 4 | Supervisor | 4500000 | 4 | Supervisor |
| PEG-1006 | Budi Drajat | Malang | 4 | Supervisor | 4500000 | 4 | Supervisor |
| PEG-1007 | Bambang Pamungkas | Kudus | 5 | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1008 | Ely Oktafiani | Yogyakarta | 5 | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1009 | Rani Wijaya | Magelang | 5 | Staff Senior | 3000000 | 5 | Staff Senior |
| PEG-1010 | Rano Karno | Solo | 6 | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | 6 | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1012 | Ilham Ungara | Jakarta | 6 | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1013 | Endang melati | Madiun | 6 | Staff junior | 2000000 | 6 | Staff Junior |
| PEG-1014 | Donny Damara | Makasar | 7 | Tenaga Kontrak | 1000000 | 7 | Tenaga Kontrak |
| PEG-1015 | Paijem | Yogyakarta | 7 | Tenaga Kontrak | 500000 | 7 | Tenaga Kontrak |
| NULL | NULL | NULL | NULL | NULL | NULL | 8 | Tenaga Magang |
+----------+-----------------------+-------------+------------+----------------+---------+------------+----------------+
16 rows in set (0.00 sec)
Update menggunakan Join Tabel
mysql> update pegawai_join, jabatan set nama_peg='Soeharto Mangantelo' where pegawai_join.id_jabatan=jabatan.id_jabatan and jabatan.nm_jabatan='C.E.O';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select nip,nama_peg,jabatan from pegawai_join inner join jabatan using (id_jabatan) limit 3;
+----------+---------------------+-------------+
| nip | nama_peg | jabatan |
+----------+---------------------+-------------+
| PEG-1001 | Soeharto Mangantelo | C.E.O |
| PEG-1002 | Felix Nababan | Manager |
| PEG-1003 | Olga syahputra | kepala unit |
+----------+---------------------+-------------+
3 rows in set (0.00 sec)
View
mysql> create view view_1 as select*from pegawai1;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from view_1;
+----------+-----------------------+-------------+----------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+-------------+----------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
| PEG-1004 | Chelsea Olivia | Bandung | kepala unit | 6000000 |
| PEG-1005 | Tuti Wardani | Jawa Tengah | Supervisor | 4500000 |
| PEG-1006 | Budi Drajat | Malang | Supervisor | 4500000 |
| PEG-1007 | Bambang Pamungkas | Kudus | Staff Senior | 3000000 |
| PEG-1008 | Ely Oktafiani | Yogyakarta | Staff Senior | 3000000 |
| PEG-1009 | Rani Wijaya | Magelang | Staff Senior | 3000000 |
| PEG-1010 | Rano Karno | Solo | Staff junior | 2000000 |
| PEG-1011 | Rahmadi Sholeh | Yogyakarta | Staff junior | 2000000 |
| PEG-1012 | Ilham Ungara | Jakarta | Staff junior | 2000000 |
| PEG-1013 | Endang melati | Madiun | Staff junior | 2000000 |
| PEG-1014 | Donny Damara | Makasar | Tenaga Kontrak | 1000000 |
| PEG-1015 | Paijem | Yogyakarta | Tenaga Kontrak | 500000 |
+----------+-----------------------+-------------+----------------+---------+
15 rows in set (0.00 sec)
mysql> select*from view_1 limit 3;
+----------+-----------------------+------------+-------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+-----------------------+------------+-------------+---------+
| PEG-1001 | Soeharto Mangundirejo | Yogyakarta | C.E.O | 9000000 |
| PEG-1002 | Felix Nababan | Medan | Manager | 8000000 |
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
+----------+-----------------------+------------+-------------+---------+
3 rows in set (0.00 sec)
mysql> select*from view_1 where nama_peg='Olga Syahputra';
+----------+----------------+------------+-------------+---------+
| nip | nama_peg | alamat_peg | jabatan | gaji |
+----------+----------------+------------+-------------+---------+
| PEG-1003 | Olga syahputra | Jakarta | kepala unit | 6000000 |
+----------+----------------+------------+-------------+---------+
1 row in set (0.00 sec)
mysql> create view view_2 as select nip, nama_peg, gaji from pegawai1 where alamat_peg='Yogyakarta';
Query OK, 0 rows affected (0.00 sec)
mysql> select*from view_2;
+----------+-----------------------+---------+
| nip | nama_peg | gaji |
+----------+-----------------------+---------+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1008 | Ely Oktafiani | 3000000 |
| PEG-1011 | Rahmadi Sholeh | 2000000 |
| PEG-1015 | Paijem | 500000 |
+----------+-----------------------+---------+
4 rows in set (0.00 sec)
Algorithm
mysql> create algorithm=merge view v_merge (nomor_induk_pegawai,nama_pegawai,gaji) as select nip,nama_peg,gaji from pegawai1 where gaji>2000000;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from v_merge;
+---------------------+-----------------------+---------+
| nomor_induk_pegawai | nama_pegawai | gaji |
+---------------------+-----------------------+---------+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1002 | Felix Nababan | 8000000 |
| PEG-1003 | Olga syahputra | 6000000 |
| PEG-1004 | Chelsea Olivia | 6000000 |
| PEG-1005 | Tuti Wardani | 4500000 |
| PEG-1006 | Budi Drajat | 4500000 |
| PEG-1007 | Bambang Pamungkas | 3000000 |
| PEG-1008 | Ely Oktafiani | 3000000 |
| PEG-1009 | Rani Wijaya | 3000000 |
+---------------------+-----------------------+---------+
9 rows in set (0.00 sec)
mysql> create algorithm=merge view view_4(nomor_induk_pegawai, nama_pegawai, gaji) as select nip,nama_peg,gaji from pegawai1 where gaji>2000000 limit 5
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------+
| Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) |
+---------+------+-------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> create algorithm=temptable view v_temptable(nomor_induk_pegawai,nama_pegawai,gaji) as select nip,nama_peg,gaji from pegawai1 where gaji>2000000 limit 5;
Query OK, 0 rows affected (0.00 sec)
mysql> select*from v_temptable;
+---------------------+-----------------------+---------+
| nomor_induk_pegawai | nama_pegawai | gaji |
+---------------------+-----------------------+---------+
| PEG-1001 | Soeharto Mangundirejo | 9000000 |
| PEG-1002 | Felix Nababan | 8000000 |
| PEG-1003 | Olga syahputra | 6000000 |
| PEG-1004 | Chelsea Olivia | 6000000 |
| PEG-1005 | Tuti Wardani | 4500000 |
+---------------------+-----------------------+---------+
5 rows in set (0.00 sec)
Tidak ada komentar:
Posting Komentar