News World

.

Tuesday, December 11, 2012

LATIHAN SOAL MYSQL

       Berikut ini TUTORIAL Latihan Soal untuk MySQL :
=======================================================
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
NULL
KODE_BRG
CHAR(4)
PRIMARY KEY
NAMA_BRG
VARCHAR(20)
NOT NULL
HARGA_BRG
INT(6)
NOT NULL
JUMLAH
INT(3)
NOT NULL
mysql> 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
JUMLAH
B001
SANDAL
35000
5
B002
BAJU KOTAK-KOTAK
100000
20
B003
CELANA JEANS
165000
15
B004
KAOS
40000
8
B005
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) 
===================================================================
Untuk lebih jelasnya Unduh Filenya Disini

1 comments:

trima kasih,setidaknya sdh mencoba

Post a Comment

Share

Twitter Delicious Facebook Digg Stumbleupon Favorites