MySQL 11: Latihan Praktikum Data Definision Languange 1

Dari awal diskusi sudah membahas dasar-dasar serta tutorial MySQL. Dari Mengenal tipe data, Install aplikasi MySQL, membuat database, mengaktifkan database, membuat tabel, menghapus database, mengehapus tabel, mengedit tabel, membuat dan memberikan akses user maupun password.

Untuk mengulang kembali apa yang telah dipelajari, sebaiknya teman-teman mengerjakan latihan praktikum data definition language 1 dibawah ini.

Ketika sudah mendapatkan materi serta praktek, agar dapat tertanam dalam ingatan harus di asah terus menerus.

Dibawah ini latihan-latihan praktikum data definition languange untuk memudahkan teman-teman dalam mengingat perintah-perintah pengelolaan database menggunakan user root.

Latihan Praktikum Data Definition Languange 1

Masuk ke dalam mysql melalui command prompt menggunakan user root. Tampilan untuk dapat masuk ke MySQL melalui command prompt dibawah ini.

C:\Users\moderator>cd\

C:\>"xampp/mysql/bin/mysql.exe" -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.38-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Buatlah dua buah database dengan nama masing masing database dengan nama “db_perpuscoba1” dan “db_perpuscoba2“.

MariaDB [(none)]> create database db_perpuscoba1;
Query OK, 1 row affected (0.10 sec)

MariaDB [(none)]> create database db_perpuscoba2;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]>

Tampilkan daftar database yang telah dibuat pada MySQL

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db_bukadiskusi002  |
| db_bukadiskusi1    |
| db_dml_lanjutan    |
| db_latihan_dml     |
| db_perpuscoba1     |
| db_perpuscoba2     |
| information_schema |
| komentar           |
| membuatuser        |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+
13 rows in set (0.18 sec)

MariaDB [(none)]>

Hapus database dengan nama “db_perpuscoba2”

MariaDB [(none)]> drop database db_perpuscoba2;
Query OK, 0 rows affected (0.13 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db_bukadiskusi002  |
| db_bukadiskusi1    |
| db_dml_lanjutan    |
| db_latihan_dml     |
| db_perpuscoba1     |
| information_schema |
| komentar           |
| membuatuser        |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
+--------------------+
12 rows in set (0.00 sec)

MariaDB [(none)]>

Aktifkan database, dengan nama database “db_perpuscoba2”

MariaDB [(none)]> use db_perpuscoba1;
Database changed
MariaDB [db_perpuscoba1]>

Tampilan daftar tabel yang ada di database db_perpuscoba1.

MariaDB [db_perpuscoba1]> show tables;
Empty set (0.00 sec)

MariaDB [db_perpuscoba1]>

Buatlah 3 tabel di db_perpuscoba1 dengan ketentuan sebagai berikut. Beri nama tabel tersebut dengan nama tabel “anggota”

Field Name
TypeSizeKeterangan
id_anggotaChar4Primary Key
namaVarchar20
alamatVarchar30
no_telpVarchar15
tgl_lahirDate
MariaDB [db_perpuscoba1]> create table anggota(
    -> id_anggota char(4),
    -> nama varchar(20),
    -> alamat varchar(30),
    -> no_telp varchar(15),
    -> tgl_lahir date,
    -> primary key (id_anggota));
Query OK, 0 rows affected (0.39 sec)

MariaDB [db_perpuscoba1]> desc anggota;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_anggota | char(4)     | NO   | PRI | NULL    |       |
| nama       | varchar(20) | YES  |     | NULL    |       |
| alamat     | varchar(30) | YES  |     | NULL    |       |
| no_telp    | varchar(15) | YES  |     | NULL    |       |
| tgl_lahir  | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.10 sec)

MariaDB [db_perpuscoba1]>

Tabel ke 2 beri nama tabel “komik”

Field NameTypeSizeKeterangan
id_komikChar5Primary Key
judulVarchar25
pengarang Varchar 30
tahun_terbitYear
jenis_komikVarchar15
MariaDB [db_perpuscoba1]> create table komik(
    -> id_komik char(5),
    -> judul varchar(25),
    -> pengarang varchar(30),
    -> tahun_terbit year,
    -> jenis_komik varchar(15));
Query OK, 0 rows affected (10.40 sec)

MariaDB [db_perpuscoba1]> desc komik;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id_komik     | char(5)     | YES  |     | NULL    |       |
| judul        | varchar(25) | YES  |     | NULL    |       |
| pengarang    | varchar(30) | YES  |     | NULL    |       |
| tahun_terbit | year(4)     | YES  |     | NULL    |       |
| jenis_komik  | varchar(15) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]>

Tabel ke 3 beri nama dengan nama tabel “pinjam”

Field Name TypeSizeKeterangan
no_pinjamInt 4
id_anggotaChar4
id_komikChar5
jumlahInt2
MariaDB [db_perpuscoba1]> create table pinjam(
    -> no_pinjam int(4),
    -> id_anggota char(4),
    -> id_komik char(5),
    -> jumlah int(2));
Query OK, 0 rows affected (0.71 sec)

MariaDB [db_perpuscoba1]> desc pinjam;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| no_pinjam  | int(4)  | YES  |     | NULL    |       |
| id_anggota | char(4) | YES  |     | NULL    |       |
| id_komik   | char(5) | YES  |     | NULL    |       |
| jumlah     | int(2)  | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
4 rows in set (0.07 sec)

MariaDB [db_perpuscoba1]>

Tampilkan daftar table yang ada di database db_perpuscoba1.

MariaDB [db_perpuscoba1]> show tables;
+--------------------------+
| Tables_in_db_perpuscoba1 |
+--------------------------+
| anggota                  |
| komik                    |
| pinjam                   |
+--------------------------+
3 rows in set (0.00 sec)

MariaDB [db_perpuscoba1]>

Buka struktur dari masing-masing dari daftar tabel tersebut.

MariaDB [db_perpuscoba1]> desc anggota;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_anggota | char(4)     | NO   | PRI | NULL    |       |
| nama       | varchar(20) | YES  |     | NULL    |       |
| alamat     | varchar(30) | YES  |     | NULL    |       |
| no_telp    | varchar(15) | YES  |     | NULL    |       |
| tgl_lahir  | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]> desc komik;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id_komik     | char(5)     | YES  |     | NULL    |       |
| judul        | varchar(25) | YES  |     | NULL    |       |
| pengarang    | varchar(30) | YES  |     | NULL    |       |
| tahun_terbit | year(4)     | YES  |     | NULL    |       |
| jenis_komik  | varchar(15) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]> desc pinjam;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| no_pinjam  | int(4)  | YES  |     | NULL    |       |
| id_anggota | char(4) | YES  |     | NULL    |       |
| id_komik   | char(5) | YES  |     | NULL    |       |
| jumlah     | int(2)  | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]>

Masukkan field baru pada tabel anggota yaitu dengan nama field “tgl_daftar” dengan tipe data “date”

MariaDB [db_perpuscoba1]> alter table anggota
    -> add tgl_daftar date;
Query OK, 0 rows affected (0.54 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db_perpuscoba1]> desc anggota;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id_anggota | char(4)     | NO   | PRI | NULL    |       |
| nama       | varchar(20) | YES  |     | NULL    |       |
| alamat     | varchar(30) | YES  |     | NULL    |       |
| no_telp    | varchar(15) | YES  |     | NULL    |       |
| tgl_lahir  | date        | YES  |     | NULL    |       |
| tgl_daftar | date        | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]>

Rubahlah nama tabel yang awalnya bernama table “anggota” menjadi “anggota_perpus”

MariaDB [db_perpuscoba1]> rename table anggota to anggota_perpus;
Query OK, 0 rows affected (0.37 sec)

MariaDB [db_perpuscoba1]> show tables;
+--------------------------+
| Tables_in_db_perpuscoba1 |
+--------------------------+
| anggota_perpus           |
| komik                    |
| pinjam                   |
+--------------------------+
3 rows in set (0.00 sec)

MariaDB [db_perpuscoba1]>

Masukkan field tambahan pada tabel komik yaitu “status” dengan type “varchar” dan size “10”

MariaDB [db_perpuscoba1]> alter table komik
    -> add status varchar(10);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db_perpuscoba1]> desc komik;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id_komik     | char(5)     | YES  |     | NULL    |       |
| judul        | varchar(25) | YES  |     | NULL    |       |
| pengarang    | varchar(30) | YES  |     | NULL    |       |
| tahun_terbit | year(4)     | YES  |     | NULL    |       |
| jenis_komik  | varchar(15) | YES  |     | NULL    |       |
| status       | varchar(10) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]>

Lihat Struktrr pada tabel pinjam

MariaDB [db_perpuscoba1]> desc pinjam;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| no_pinjam  | int(4)  | YES  |     | NULL    |       |
| id_anggota | char(4) | YES  |     | NULL    |       |
| id_komik   | char(5) | YES  |     | NULL    |       |
| jumlah     | int(2)  | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]> alter table pinjam
    -> add primary key (no_pinjam);
Query OK, 0 rows affected (0.68 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db_perpuscoba1]>

Lihat kembali struktur table pinjam yang telah ditambahkan primary key.

MariaDB [db_perpuscoba1]> desc pinjam;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| no_pinjam  | int(4)  | NO   | PRI | NULL    |       |
| id_anggota | char(4) | YES  |     | NULL    |       |
| id_komik   | char(5) | YES  |     | NULL    |       |
| jumlah     | int(2)  | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]>

Rubahlah nama tabel “pinjam” menjadi “transaksi”

MariaDB [db_perpuscoba1]> rename table pinjam to transaksi;
Query OK, 0 rows affected (0.17 sec)

MariaDB [db_perpuscoba1]> show tables;
+--------------------------+
| Tables_in_db_perpuscoba1 |
+--------------------------+
| anggota_perpus           |
| komik                    |
| transaksi                |
+--------------------------+
3 rows in set (0.00 sec)

MariaDB [db_perpuscoba1]>

Hapus field pada tabel komik dengan nama field “komik”

MariaDB [db_perpuscoba1]> desc komik;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id_komik     | char(5)     | YES  |     | NULL    |       |
| judul        | varchar(25) | YES  |     | NULL    |       |
| pengarang    | varchar(30) | YES  |     | NULL    |       |
| tahun_terbit | year(4)     | YES  |     | NULL    |       |
| jenis_komik  | varchar(15) | YES  |     | NULL    |       |
| status       | varchar(10) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]> alter table komik
    -> drop jenis_komik;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db_perpuscoba1]> desc komik;
+--------------+-------------+------+-----+---------+-------+
| Field        | Type        | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id_komik     | char(5)     | YES  |     | NULL    |       |
| judul        | varchar(25) | YES  |     | NULL    |       |
| pengarang    | varchar(30) | YES  |     | NULL    |       |
| tahun_terbit | year(4)     | YES  |     | NULL    |       |
| status       | varchar(10) | YES  |     | NULL    |       |
+--------------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]>

Ganti nama field “jumlah” pada tabel transaksi menjadi “jml_komik” type “int” size “4”

MariaDB [db_perpuscoba1]> alter table transaksi
    -> change jumlah jml_komik int(4);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [db_perpuscoba1]> desc transaksi
    -> ;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| no_pinjam  | int(4)  | NO   | PRI | NULL    |       |
| id_anggota | char(4) | YES  |     | NULL    |       |
| id_komik   | char(5) | YES  |     | NULL    |       |
| jml_komik  | int(4)  | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+
4 rows in set (0.01 sec)

MariaDB [db_perpuscoba1]>

Pada latihan praktikum data definision language 1 diatas, teman-teman dapat mengingat setiap perintah yang ada pada MySQL.

Apabila peirntah-perintah tersebut sudah diingat diluar kepala, tentu akan lebih mudah dalam penggabungan ke program lainnya. Semoga pembahsan latihan diatas dapat memberikan manfaat.

Add a Comment

Your email address will not be published. Required fields are marked *