lat. soal mysql
DESCRIPTION
sdfsdfTRANSCRIPT
1. BUATLAH DATABASE DENGAN NAMA “GUDANG”
mysql> create database GUDANG;
Query OK, 1 row affected (0.11 sec)
2. DENGAN TABEL “BARANG”, SEBAGAI BERIKUT:
FIELD TYPE NULLKODE_BRG CHAR(4) PRIMARY KEY NAMA_BRG VARCHAR(20) NOT NULLHARGA_BRG INT(6) NOT NULLJUMLAH INT(3) NOT NULLmysql> use GUDANG;
Database changed
mysql> create table BARANG
-> (KODE_BRG CHAR(4) PRIMARY KEY,
-> NAMA_BRG VARCHAR(20) NOT NULL,
-> HARGA_BRG INT(6) NOT NULL,
-> JUMLAH INT(3) NOT NULL);
Query OK, 0 rows affected (0.66 sec)
mysql> DESC BARANG;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| KODE_BRG | char(4) | NO | PRI | NULL | |
| NAMA_BRG | varchar(20) | NO | | NULL | |
| HARGA_BRG | int(6) | NO | | NULL | |
| JUMLAH | int(3) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.06 sec)
KODE_BRG NAMA_BRG HARGA_BRG JUMLAHB001 SANDAL 35000 5B002 BAJU KOTAK-KOTAK 100000 20B003 CELANA JEANS 165000 15B004 KAOS 40000 8B005 TOPI 25000 3
mysql> INSERT INTO BARANG
-> VALUES
-> ("B001","SANDAL",35000,5),
-> ("B002","BAJU KOTAK-KOTAK",100000,20),
-> ("B003","CELANA JEANS",165000,15),
-> ("B004","KAOS",40000,8),
-> ("B005","TOPI",25000,3);
Query OK, 5 rows affected (0.23 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM BARANG;
+----------+------------------+-----------+--------+
| KODE_BRG | NAMA_BRG | HARGA_BRG | JUMLAH |
+----------+------------------+-----------+--------+
| B001 | SANDAL | 35000 | 5 |
| B002 | BAJU KOTAK-KOTAK | 100000 | 20 |
| B003 | CELANA JEANS | 165000 | 15 |
| B004 | KAOS | 40000 | 8 |
| B005 | TOPI | 25000 | 3 |
+----------+------------------+-----------+--------+
5 rows in set (0.06 sec)
3. GUNAKAN PERINTAH UPDATE UNTUK MERUBAH NILAI HARGA_BRG BAJU KOTAK-KOTAK MENJADI 99000.
mysql> UPDATE BARANG
-> SET HARGA_BRG=99000
-> WHERE KODE_BRG="B002";
Query OK, 1 row affected (0.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM BARANG;
+----------+------------------+-----------+--------+
| KODE_BRG | NAMA_BRG | HARGA_BRG | JUMLAH |
+----------+------------------+-----------+--------+
| B001 | SANDAL | 35000 | 5 |
| B002 | BAJU KOTAK-KOTAK | 99000 | 20 |
| B003 | CELANA JEANS | 165000 | 15 |
| B004 | KAOS | 40000 | 8 |
| B005 | TOPI | 25000 | 3 |
+----------+------------------+-----------+--------+
5 rows in set (0.00 sec)
4. GANTILAH NAMA FIELD “JUMLAH” MENJADI “STOK” MENGGUNAKAN PERINTAH CHANGE
mysql> ALTER TABLE BARANG
-> CHANGE JUMLAH STOK INT(3) NOT NULL;
Query OK, 5 rows affected (0.83 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from barang;
+----------+------------------+-----------+------+
| KODE_BRG | NAMA_BRG | HARGA_BRG | STOK |
+----------+------------------+-----------+------+
| B001 | SANDAL | 35000 | 5 |
| B002 | BAJU KOTAK-KOTAK | 99000 | 20 |
| B003 | CELANA JEANS | 165000 | 15 |
| B004 | KAOS | 40000 | 8 |
| B005 | TOPI | 25000 | 3 |
+----------+------------------+-----------+------+
5 rows in set (0.00 sec)
5. GUNAKAN PERINTAH MODIFY UNTUK MERUBAH TYPE DATA HARGA_BRG DARI INT(6) MENJADI INT(7)
mysql> ALTER TABLE BARANG
-> MODIFY HARGA_BRG INT(7) NOT NULL;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC BARANG;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| KODE_BRG | char(4) | NO | PRI | NULL | |
| NAMA_BRG | varchar(20) | NO | | NULL | |
| HARGA_BRG | int(7) | NO | | NULL | |
| STOK | int(3) | NO | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.06 sec)
6. TAMPILKAN ISI DATA 3 FIELD, KECUALI FIELD STOK
mysql> SELECT KODE_BRG, NAMA_BRG, HARGA_BRG FROM BARANG;
+----------+------------------+-----------+
| KODE_BRG | NAMA_BRG | HARGA_BRG |
+----------+------------------+-----------+
| B001 | SANDAL | 35000 |
| B002 | BAJU KOTAK-KOTAK | 99000 |
| B003 | CELANA JEANS | 165000 |
| B004 | KAOS | 40000 |
| B005 | TOPI | 25000 |
+----------+------------------+-----------+
5 rows in set (0.00 sec)
7. TAMPILKAN 3 FIELD, (KECUALI FIELD STOK) DAN TAMPILKAN DATA YANG BERNAMA = “KAOS” (LAINYA TIDAK DITAMPILKAN)
mysql> SELECT KODE_BRG, NAMA_BRG, HARGA_BRG FROM BARANG
-> WHERE NAMA_BRG="KAOS";
+----------+----------+-----------+
| KODE_BRG | NAMA_BRG | HARGA_BRG |
+----------+----------+-----------+
| B004 | KAOS | 40000 |
+----------+----------+-----------+
1 row in set (0.02 sec)
8. GUNAKAN PERINTAH ORDER UNTUK MENGURUTKAN FIELD NAMA_BRG SECARA ASCENDING
mysql> select * from barang
-> ORDER BY NAMA_BRG;
+----------+------------------+-----------+------+
| KODE_BRG | NAMA_BRG | HARGA_BRG | STOK |
+----------+------------------+-----------+------+
| B002 | BAJU KOTAK-KOTAK | 99000 | 20 |
| B003 | CELANA JEANS | 165000 | 15 |
| B004 | KAOS | 40000 | 8 |
| B001 | SANDAL | 35000 | 5 |
| B005 | TOPI | 25000 | 3 |
+----------+------------------+-----------+------+
5 rows in set (0.00 sec)
9. GUNAKAN PERINTAH LIKE UNTUK MENAMPILKAN NAMA _BRG YANG BERAWALAN HURUF “C” (CONTOH: LIKE”C%”)
mysql> select * from barang
-> WHERE NAMA_BRG LIKE "C%";
+----------+--------------+-----------+------+
| KODE_BRG | NAMA_BRG | HARGA_BRG | STOK |
+----------+--------------+-----------+------+
| B003 | CELANA JEANS | 165000 | 15 |
+----------+--------------+-----------+------+
1 row in set (0.00 sec)