Mysql - Gaji Pegawai

mysql> create database RDBMS;
Query OK, 1 row affected (0.05 sec)

mysql> use RDBMS;
Database changed

mysql> create table pegawai (nip char(2) primary key,
-> nama char(20),gol int(1),jml_absen int(2));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into pegawai values
-> ('01','Hani',1,24),
-> ('02','Bany',2,22),
-> ('03','Sweety',3,24),
-> ('10','Boneng',1,23),
-> ('17','Geblek',2,20),
-> ('14','Selamanya',1,15);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0

mysql> create table gaji (gol int(1) primary key, gapok bigint(10),
-> tunjangan bigint(10),transpor bigint(10));
Query OK, 0 rows affected (0.13 sec)

mysql> insert into gaji values
-> (1,1000000,200000,10000),
-> (2,1500000,300000,15000),
-> (3,3000000,500000,20000);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

Untuk menampilkan seluruh isi table

 

mysql> select * from pegawai;
+-----+-----------+------+-----------+
| nip | nama | gol | jml_absen |
+-----+-----------+------+-----------+
| 01 |Hani | 1 | 24 |
| 02 |Bany | 2 | 22 |
| 03 |Sweety | 3 | 24 |
| 10 |Boneng | 1 | 23 |
| 17 |Geblek | 2 | 20 |
| 14 |Selamanya | 1 | 15 |
+-----+-----------+------+-----------+
6 rows in set (0.00 sec)

mysql> select * from gaji;
+-----+---------+-----------+----------+
| gol | gapok | tunjangan | transpor |
+-----+---------+-----------+----------+
| 1 | 1000000 | 200000 | 10000 |
| 2 | 1500000 | 300000 | 15000 |
| 3 | 3000000 | 500000 | 20000 |
+-----+---------+-----------+----------+
3 rows in set (0.00 sec)

Untuk menampilkan jumlah pegawai per-golongan

mysql> select gol, count(nip) as jumlah_pegawai from pegawai group by gol;
+------+----------------+
| gol | jumlah_pegawai |
+------+----------------+
| 1 | 3 |
| 2 | 2 |
| 3 | 1 |
+------+----------------+
3 rows in set (0.02 sec)

Untuk menampilkan nip,nama dan total gaji bersih

mysql> select nip,nama,gapok+tunjangan+(jml_absen*transpor) as gaji_bersih
-> from pegawai inner join gaji on pegawai.gol=gaji.gol;
+-----+-----------+-------------+
| nip | nama | gaji_bersih |
+-----+-----------+-------------+
| 01 | Hani | 1440000 |
| 02 | Bany | 2130000 |
| 03 | Sweety | 3980000 |
| 10 | Boneng | 1430000 |
| 17 | Geblek | 2100000 |
| 14 | Selamanya | 1350000 |
+-----+-----------+-------------+
6 rows in set (0.00 sec)

Untuk menampilkan total gaji bersih tertinggi

mysql> select max(gapok+tunjangan+(jml_absen*transpor)) as gaji_tertinggi-> from pegawai inner join gaji on pegawai.gol=gaji.gol;
+----------------+
| gaji_tertinggi |
+----------------+
| 3980000 |
+----------------+
1 row in set (0.00 sec)

mysql> select * from gaji;
+-----+---------+-----------+----------+
| gol | gapok | tunjangan | transpor |
+-----+---------+-----------+----------+
| 1 | 1000000 | 200000 | 10000 |
| 2 | 1500000 | 300000 | 15000 |
| 3 | 3000000 | 500000 | 20000 |
+-----+---------+-----------+----------+
3 rows in set (0.00 sec)

Untuk menghitung gaji pokok setelah tunjangan dinaikkan sebesar 40% bagi yang bergolongan 1
mysql> update gaji set gapok=gapok+(tunjangan*0.4) where gol=1;
Query OK, 1 row affected (0.42 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from gaji;
+-----+---------+-----------+----------+
| gol | gapok | tunjangan | transpor |
+-----+---------+-----------+----------+
| 1 | 1080000 | 200000 | 10000 |
| 2 | 1500000 | 300000 | 15000 |
| 3 | 3000000 | 500000 | 20000 |
+-----+---------+-----------+----------+
3 rows in set (0.00 sec)

semoga bermanfaat.^^