Membuat Database Multi_finance

Membuatdatabase multi_finance

mysql> use multi_finance;

mysql> use multi_finance;

Database changed

Membuat tabel nasabah

mysql> create table nasabah(no_kontrak int(10), nama varchar(30), alamat varchar(30));

Input record tabel nasabah

mysql> insert into nasabah values(‘2009001’,’Feri’,’Depok’);

mysql> insert into nasabah values(‘2009002’,’Suhendra’,’Jakarta’);

mysql> insert into nasabah values(‘2010001’,’Doni’,’Jakarta’);

mysql> insert into nasabah values(‘2011001’,’Zulkifli’,’Bogor’);

mysql> insert into nasabah values(‘2009002’,’Sebastian’,’Depok’);

Tampilkan tabel nasabah

mysql> select * from nasabah;

+------------+-----------+---------+

| no_kontrak | nama      | alamat  |

+------------+-----------+---------+

|    2009001 | Feri      | Depok   |

|    2009002 | Suhendra  | Jakarta |

|    2010001 | Doni      | Jakarta |

|    2011001 | Zulkifli  | Bogor   |

|    2011002 | Sebastian | Depok   |

+------------+-----------+---------+

5 rows in set (0.00 sec)

Membuat tabel angsuran

mysql> create table angsuran(nopol varchar(10), no_kontrak int(10), nama varchar(30), angsuran int(20));

Input record tabel angsuran

mysql>insert into angsuran values(‘B6001EGZ’, ‘2009001’, ’Feri’, ’675000’);

mysql>insert into angsuran values(‘B5678UL’,‘2009002’, ’Suhendra’, ’715000’);

mysql>insert into angsuran values(‘B6254UL’, ‘2010001’, ’Doni’, ’415000’);

mysql>insert into angsuran values(‘B5580EGB’, ‘2011001’, ’Zulkifli’, ’513000’);

mysql>insert into angsuran values(‘B6500EFX’, ‘2011002’, ’Sebastian’, ’607000’);

Tampilkan tabel angsuran

mysql> select * from angsuran;

+----------+------------+-----------+----------+

| nopol    | no_kontrak | nama      | angsuran |

+----------+------------+-----------+----------+

| B6001EGZ |    2009001 | Feri      |   675000 |

| B5678UL  |    2009002 | Suhendra  |   715000 |

| B6254UL  |    2010001 | Doni      |   415000 |

| F5580EGB |    2011001 | Zulkifli  |   513000 |

| B6500EFX |    2011002 | Sebastian |   607000 |

+----------+------------+-----------+----------+

5 rows in set (0.00 sec)

Membuat tabel kendaraan

mysql> create table kendaraan(nopol varchar(10), Merk varchar(20), type varchar(20));

Input record tabel kendaraan

mysql>insert into kendaraan values(‘B6001EGZ’, ‘Yamaha’,’VegaZR’);

mysql>insert into kendaraan values(‘B5678UL’,’Yamaha’,’JupiterZ’);

mysql>insert into kendaraan values(‘B6254UL’,’Honda’,’Supra X’);

mysql>insert into kendaraan values(‘B5580EGB’,’Honda’,’Vario’);

mysql>insert into kendaraan values(‘B6500EFX’,’Yamaha’,’Mio CW’);

Tampilkan tabel kendaraan

mysql> select * from kendaraan;

+----------+--------+-----------+

| nopol    | Merk   | type      |

+----------+--------+-----------+

| B6001EGZ | Yamaha | VegaZR    |

| B5678UL  | Yamaha | JupiterZ  |

| B6254UL  | Honda  | Supra X   |

| F5580EGB | Honda  | Vario     |

| B6500EFX | Yamaha | Mio CW    |

+----------+--------+-----------+

5 rows in set (0.03 sec)

Membuat tabel Asuransi

mysql> create table asuransi(nopol varchar(10), nama varchar(30), premi int(20));

Input record tabel Asuransi

mysql>insert into asuransi values(‘B6001EGZ’,’Feri’,’125000’);

mysql>insert into asuransi values(‘B5678UL’,’Suhendra’,’112000’);

mysql>insert into asuransi values(‘B6254UL’,’Doni’,’87000’);

mysql>insert into asuransi values(‘B5580EGB’,’Zulkifli’,’98000’);

mysql>insert into asuransi values(‘B6500EFX’,’Sebastian’,’99000’);

Tampilkan tabel Asuransi

mysql> select * from asuransi;

+----------+-----------+--------+

| nopol    | nama      | premi  |

+----------+-----------+--------+

| B6001EGZ | Feri      | 125000 |

| B5678UL  | Suhendra  | 112000 |

| B6254UL  | Doni      |  87000 |

| F5580EGB | Zulkifli  |  98000 |

| B6500EFX | Sebastian |  99000 |

+----------+-----------+--------+

5 rows in set (0.01 sec)

Relasi 2 TABEL menampilkan no_kontrak, nama, nopol, angsuran

mysql> select nasabah.no_kontrak, nasabah.nama,angsuran.nopol, angsuran.angsuran from nasabah, angsuran where nasabah.no_kontrak = angsuran.no_kontrak;

+------------+-----------+----------+----------+

| no_kontrak | nama      | nopol    | angsuran |

+------------+-----------+----------+----------+

|    2009001 | Feri      | B6001EGZ |   675000 |

|    2009002 | Suhendra  | B5678UL  |   715000 |

|    2010001 | Doni      | B6254UL  |   415000 |

|    2011001 | Zulkifli  | F5580EGB |   513000 |

|    2011002 | Sebastian | B6500EFX |   607000 |

+------------+-----------+----------+----------+

5 rows in set (0.03 sec)

Relasi 3 TABEL menampilkan no_kontrak, nama, nopol, angsuran, merk

mysql> select nasabah.no_kontrak, nasabah.nama, angsuran.nopol ,angsuran.angsuran,kendaraan.Merk, kendaraan.type from nasabah, angsuran, kendaraan where nasabah.no_kontrak = angsuran.no_kontrak AND angsuran.nopol = kendaraan.nopol;

+------------+-----------+----------+----------+--------+-----------+

| no_kontrak | nama      | nopol    | angsuran | Merk   | type      |

+------------+-----------+----------+----------+--------+-----------+

|    2009001 | Feri      | B6001EGZ |   675000 | Yamaha | VegaZR    |

|    2009002 | Suhendra  | B5678UL  |   715000 | Yamaha | Jupiter Z |

|    2010001 | Doni      | B6254UL  |   415000 | Honda  | Supra X   |

|    2011001 | Zulkifli  | F5580EGB |   513000 | Honda  | Vario     |

|    2011002 | Sebastian | B6500EFX |   607000 | Yamaha | Mio CW    |

+------------+-----------+----------+----------+--------+-----------+

5 rows in set (0.00 sec)

Relasi 4 TABEL  menampilkan no_kontrak, nama, nopol, merk, angsuran, premi

mysql> select nasabah.no_kontrak, nasabah.nama, angsuran.nopol, kendaraan.Merk, asuransi.premi from nasabah, angsuran, kendaraan, asuransi where nasabah.no_kontrak = angsuran.no_kontrak AND angsuran.nopol = kendaraan.nopol AND kendaraan.nopol = asuransi.nopol;

+------------+-----------+----------+--------+--------+

| no_kontrak | nama      | nopol    | Merk   | premi  |

+------------+-----------+----------+--------+--------+

|    2009001 | Feri      | B6001EGZ | Yamaha | 125000 |

|    2009002 | Suhendra  | B5678UL  | Yamaha | 112000 |

|    2010001 | Doni      | B6254UL  | Honda  |  87000 |

|    2011001 | Zulkifli  | F5580EGB | Honda  |  98000 |

|    2011002 | Sebastian | B6500EFX | Yamaha | 137000 |

+------------+-----------+----------+--------+--------+

5 rows in set (0.00 sec)

Relasi 4 TABEL menampilkan no_kontrak, nama, nopol, merk, premi yang nilai angsurannya lebih besar dari 600000 dan nilai preminya lebih besar dari 100000

mysql> select nasabah.no_kontrak, nasabah.nama, angsuran.nopol, kendaraan.Merk, asuransi.premi from nasabah, angsuran, kendaraan, asuransi where nasabah.no_kontrak = angsuran.no_kontrak AND angsuran.nopol = kendaraan.nopol AND kendaraan.nopol = asuransi.nopol AND angsuran.angsuran > 600000 AND asuransi.premi > 100000;

+------------+-----------+----------+--------+--------+

| no_kontrak | nama      | nopol    | Merk   | premi  |

+------------+-----------+----------+--------+--------+

|    2009001 | Feri      | B6001EGZ | Yamaha | 125000 |

|    2009002 | Suhendra  | B5678UL  | Yamaha | 112000 |

|    2011002 | Sebastian | B6500EFX | Yamaha | 137000 |

+------------+-----------+----------+--------+--------+

3 rows in set (0.00 sec)
http://pribadiferi.blogspot.com