September 10, 2018
Lirik Lagu
Buatlah
databas PEGAWAI dengan tabel sebagai berikut :
mysql> desc infopribadi;
+----------------+----------------+-------+--------+----------------+-------+
| Field
| Type |
Null | Key | Default | Extra |
+----------------+----------------+-------+--------+----------------+-------+
| nip
| varchar(5) |
NO |
PRI | NULL | |
| nama
| varchar(20) |
YES | |
NULL | |
| kota
| varchar(15) |
YES | |
NULL | |
| tgl_lahir |
date | YES | | NULL
| |
| sex
| enum('L','W') |
YES | |
NULL | |
+---------------+-----------------+-------+--------+----------------+--------+
5 rows in set (0.02 sec)
+----------------+----------------+-------+--------+----------------+-------+
| Field
| Type |
Null | Key | Default | Extra |
+----------------+----------------+-------+--------+----------------+-------+
| kode_bag |
char(1) |
NO |
PRI | NULL | |
| nama_bag |
char(15) | NO | unique |
NULL | |
+---------------+-----------------+-------+-------+-----------------+-------+
2 rows in set (0.02 sec)
mysql> desc pekerjaan;
+----------------+----------------+-------+-------+-----------------+-------+
| Field
| Type |
Null | Key | Default | Extra |
+----------------+----------------+-------+-------+-----------------+-------+
| nip
| varchar(5) | NO
| PRI | NULL |
|
| kode_bag |
char(1) | NO | | NULL
| |
| gaji
| int(11) |
NO | |
NULL | |
+----------------+----------------+-------+--------+----------------+-------+
3 rows in set (0.01 sec)
Isi data masing-masing
5 record, seperti contoh berikut :
mysql> select * from biodata;
+----------------+-------------------------+-------------------------+-----------------+-------+
| nip | nama |
kota |
tgl_lhr | sex |
+----------------+-------------------------+-------------------------+-----------------+-------+
| 12345 |
Dita Nurafni | Medan | 1970-07-07 | W |
| 12346 |
Dhani Akbar | Medan | 1980-08-08 | L |
| 12347 |
Tamara Febriani | Jakarta | 1985-05-05 | W |
| 12348 |
Aan Setiawan | Bogor | 1987-07-07 | L |
| 12349 |
Dian Permana | Bandung |
1990-09-09 | L |
+----------------+-------------------------+-------------------------+-----------------+------+
5 rows in set (0.00 sec)
mysql> select * from bagian;
+----------------+----------------+
| kode_bag |
nama_bag |
+----------------+----------------+
| 3
| Akuntansi
|
| 5
| PDE |
| 1
| Pemasaran
|
| 2
|
Produksi |
| 4
| SDM |
+----------------+----------------+
5 rows in set (0.00 sec)
mysql> select * from pekerjaan;
+----------------+----------------+----------------+
| nip | kode_bag | gaji |
+----------------+----------------+----------------+
| 12345 |
2 |
1200000 |
| 12346 |
1 |
1000000 |
| 12347 |
3 |
1200000 |
| 12348 |
4 |
1300000 |
| 12349 |
1 |
1000000 |
+----------------+----------------+----------------+
5 rows in set (0.00 sec)
Ekspresi
pada kolom keluaran :
mysql> select 1/2*gaji from pekerjaan;
+---------------+
|
1/2*gaji |
+---------------+
| 600000.0000 |
| 500000.0000 |
| 600000.0000 |
| 650000.0000 |
| 500000.0000 |
+---------------+
5 rows in set (0.03 sec)
mysql> select format(1/2*gaji,1) as 'Gaji
Baru' from pekerjaan;
+-----------+
| Gaji Baru |
+-----------+
| 600,000.0 |
| 500,000.0 |
| 600,000.0 |
| 650,000.0 |
| 500,000.0 |
+-----------+
5 rows in set (0.00 sec)
mysql> select nip,kode_bag as 'Kode
Bagian',format(1/2*gaji,1) as 'Gaji Baru' from
pekerjaan;
+----------------+----------------+----------------+
| nip | Kode Bagian | Gaji Baru |
+----------------+----------------+----------------+
| 12345 |
2 | 600,000.0 |
| 12346 |
1 | 500,000.0 |
| 12347 |
3 | 600,000.0 |
| 12348 |
4 | 650,000.0 |
| 12349 |
1 | 500,000.0 |
+----------------+----------------+----------------+
5 rows in set (0.02 sec)
Menambah
teks keluaran :
mysql> select nip as 'Gaji Nip','Sebesar',gaji,'Rupiah'
from pekerjaan;
+----------------+----------------+----------------+----------------+
| Gaji Nip |
Sebesar | gaji |
Rupiah |
+----------------+----------------+----------------+----------------+
| 12345
| Sebesar | 1200000 | Rupiah |
| 12346
| Sebesar | 1000000 | Rupiah |
| 12347
| Sebesar | 1200000 | Rupiah |
| 12348
| Sebesar | 1300000 | Rupiah |
| 12349
| Sebesar | 1000000 | Rupiah |
+----------------+----------------+----------------+-----------------+
5 rows in set (0.00 sec)
Ekspresi
berkondisi dengan IF
mysql> select nama, if
(sex='L','Pria','Wanita') from biodata;
+-------------------------+----------------------------------+
| nama |
if (sex='L','Pria','Wanita') |
+-------------------------+----------------------------------+
| Dita Nurafni |
Wanita |
| Dhani Akbar |
Pria |
| Tamara Febriani | Wanita
|
| Aan Setiawan |
Pria |
| Dian Permana |
Pria |
+-------------------------+----------------------------------+
5 rows in set (0.00 sec)
Buatlah query berikut :
0 komentar:
Posting Komentar