Thursday, December 21, 2017

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)


Wednesday, December 20, 2017

Storing Procedure pada MySql

Sebelumnya download contoh database yang akan dijadikan studi kasus : sql

Parameter Storing Procedure

terdapat 3 parameter pada storing procedure

  • IN- adalah parameter default. Saat menggunakan parameter IN, maka program pemanggil harus memberikan argumen pada storing procedure.
  • OUT- nilai pada parameter out dapat diganti pada storing procedure dan nilai yang baru diberikan kembali pada program pemanggil.
  • INOUT- adalah kombinasi IN dan OUT, artinya program pemanggil melewatkan argumen dan storing procedure dapat merubah INOUT parameter dan memberikan nilai yang baru pada program pemanggil.
PARAMETER IN
mysql> DELIMITER //

mysql> CREATE PROCEDURE listofficeByCountry (IN namaCountry VARCHAR(200))
    -> BEGIN
    -> SELECT* FROM offices
    -> WHERE country = namaCountry;
    -> end//
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> call listofficeByCountry('USA');






mysql> call listofficeByCountry('France');
-+-------+-----------------+--------------------------+--------------+-------+---------+
| city | phone | addressLine1 | addressLine2 | state | country |
-+-------+-----------------+--------------------------+--------------+-------+---------+
| Paris | +33 14 723 4404 | 43 Rue Jouffroy D'abbans | NULL | NULL | France |
-+-------+-----------------+--------------------------+--------------+-------+---------+
1 row in set (0.04 sec)
Query OK, 0 rows affected (0.04 sec)

PARAMETER  IN dan OUT
mysql> DELIMITER //
mysql> CREATE PROCEDURE HitungStatusOrder(
    -> IN statusOrder VARCHAR(255),
    -> OUT total INT)
    -> BEGIN
    -> SELECT count(orderNumber)
    -> INTO total
    -> FROM orders
    -> WHERE status = statusOrder;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> call HitungStatusOrder('Shipped',@total);
Query OK, 1 row affected (0.03 sec)
mysql> SELECT @total;
+--------+
| @total |
+--------+
|    303 |
+--------+
1 row in set (0.00 sec)

mysql> call HitungStatusOrder('in process',@total);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @total AS total_in_process;
+------------------+
| total_in_process |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)
mysql> 




Monday, December 4, 2017

python scapy


scapy adalah sebuah tool box yang dibuat menggunakan bahasa python, dipergunakan untuk memanipulasi packet data dalam jaringan. Scapy dapat disertakan pada program yang dibuat untuk melakukan scanning, tracerouting, probing, unit tests, attacks dan network discovery.

install scapy pada ubuntu

sudo apt-get update
sudo apt-ge install python-scapy