Quiz MySql Storing Procedure (jawaban)

Studi kasus pada quiz ini adalah menghitung jumlah SKS yang diambil mahasiswa menggunakan procedure.

Disediakan tiga buah tabel yaitu : mahasiswa, matakuliah dan mhs_mk

mysql> desc mahasiswa;

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

| Field    | Type        | Null | Key | Default | Extra |

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

| nim      | varchar(10) | NO   | PRI | NULL    |       |

| nama_mhs | varchar(30) | YES  |     | NULL    |       |

| alamat   | varchar(50) | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

mysql> desc matakuliah;

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

| Field   | Type        | Null | Key | Default | Extra |

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

| kode_mk | varchar(10) | NO   | PRI | NULL    |       |

| nama_mk | varchar(50) | YES  |     | NULL    |       |

| sks     | int(3)      | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

mysql> desc mhs_mk;

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

| Field   | Type        | Null | Key | Default | Extra |

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

| nim     | varchar(10) | NO   | PRI |         |       |

| kode_mk | varchar(10) | NO   | PRI |         |       |

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

2 rows in set (0.01 sec)

Jawaban

Query untuk membuat DB dan Tabel

mysql> CREATE DATABASE krs;

Query OK, 1 row affected (0.00 sec)

mysql> USE krs;

Database changed

mysql> CREATE TABLE mahasiswa

    -> (nim varchar (10) primary key,

    -> nama_mhs varchar (30),

    -> alamat varchar (50)

    -> );

Query OK, 0 rows affected (0.36 sec)

mysql> CREATE TABLE matakuliah (

    -> kode_mk varchar (10) primary key,

    -> nama_mk varchar (50),

    -> sks int (3)

    -> );

Query OK, 0 rows affected (0.36 sec)

mysql> CREATE TABLE mhs_mk 

    -> ( nim varchar (10),

    -> kode_mk varchar (10),

    -> PRIMARY KEY (nim,kode_mk),

    -> FOREIGN KEY (nim) REFERENCES mahasiswa(nim),

    -> FOREIGN KEY (kode_mk) REFERENCES matakuliah(kode_mk)

    -> );

Query OK, 0 rows affected (0.08 sec)

ISI DATA PADA TABEL

mysql> SELECT*FROM mahasiswa;

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

| nim     | nama_mhs | alamat  |

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

| TE17001 | Budi     | Mataram |

| TE17002 | Rusli    | Ampenan |

| TE17003 | Wati     | Gerung  |

| TE17004 | Dewi     | Praya   |

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

mysql> SELECT*FROM matakuliah;

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

| kode_mk | nama_mk                  | sks  |

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

| MK001   | Basis Data               |    3 |

| MK002   | Jaringan Komputer        |    3 |

| MK003   | Sistem Operasi           |    2 |

| MK004   | Rekayasa Perangkat Lunak |    3 |

| MK005   | Keamanan Informasi       |    2 |

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

mysql> SELECT*FROM mhs_mk;

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

| nim     | kode_mk |

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

| TE17001 | MK001   |

| TE17002 | MK001   |

| TE17003 | MK001   |

| TE17001 | MK002   |

| TE17003 | MK002   |

| TE17001 | MK003   |

| TE17002 | MK003   |

| TE17004 | MK003   |

| TE17004 | MK004   |

| TE17003 | MK005   |

| TE17004 | MK005   |

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

jika diperhatikan dari ketiga tabel di atas akan cukup kesulitan untuk membuat procedure, sehingga perlu dibuat tabel ke empat yang tidak merusak struktur 3 tabel yang telah ternormalisasi.

Tabel yang dibuat berdasarkan hasil query dari klausa JOIN.

mysql> CREATE TABLE join_mk_mhs AS

    -> SELECT mhs_mk.nim, mhs_mk.kode_mk, matakuliah.sks

    -> FROM mhs_mk

    -> INNER JOIN matakuliah ON mhs_mk.kode_mk = matakuliah.kode_mk

    -> ;

Query OK, 11 rows affected (0.09 sec)

mysql> SELECT*FROM join_mk_mhs;

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

| nim     | kode_mk | sks  |

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

| TE17001 | MK001   |    3 |

| TE17002 | MK001   |    3 |

| TE17003 | MK001   |    3 |

| TE17001 | MK002   |    3 |

| TE17003 | MK002   |    3 |

| TE17001 | MK003   |    2 |

| TE17002 | MK003   |    2 |

| TE17004 | MK003   |    2 |

| TE17004 | MK004   |    3 |

| TE17003 | MK005   |    2 |

| TE17004 | MK005   |    2 |

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

11 rows in set (0.72 sec)

Jika besar SKS suatu matakuliah pada tabel matakuliah berubah, maka secara otomatis besar SKS pada tabel join_mk_mhs juga berubah, karena tabel terbentuk berdasarkan hasil query dari klausa JOIN.

BUAT TABEL UNTUK MENAMPUNG HASIL DARI STORING PROCEDURE

mysql> CREATE TABLE sks_total AS 

    -> SELECT nim,

    -> 0 AS jumlah_sks 

    -> FROM mahasiswa;

Query OK, 4 rows affected (0.40 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT*FROM sks_total;

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

| nim     | jumlah_sks |

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

| TE17001 |          0 |

| TE17002 |          0 |

| TE17003 |          0 |

| TE17004 |          0 |

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

4 rows in set (0.00 sec)

BUAT PROCEDURE BERDASARKAN TABEL join_mhs_mk

mysql> delimiter //

mysql> CREATE PROCEDURE hitungtotalsks 

-> (IN nimmhs varchar(10)) 

-> BEGIN UPDATE sks_total SET jumlah_sks = 

-> (SELECT SUM(sks) FROM join_mk_mhs WHERE nim=nimmhs) 

-> WHERE nim = nimmhs;

-> end//

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call hitungtotalsks('TE17001');                               Query OK, 1 row affected (0.01 sec)

mysql> call hitungtotalsks('TE17002');

Query OK, 0 rows affected (0.00 sec)

mysql> call hitungtotalsks('TE17003');

Query OK, 1 row affected (0.00 sec)

mysql> call hitungtotalsks('TE17004');

Query OK, 1 row affected (0.00 sec)

mysql> select*from sks_total;

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

| nim     | jumlah_sks |

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

| TE17001 |          8 |

| TE17002 |          5 |

| TE17003 |          8 |

| TE17004 |          7 |

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

4 rows in set (0.00 sec)