TUGAS 1 PRAKTIKUM SMBD
PRAKTIKAN :
Nama :
EKA YANI ( 131051092 )
Kelompok : SN2
ASISTEN
LAB :
1. Ebedia
Hilda A.M
2. Ice
Okalia Sari
3. Adhika
Putra S
4. Apolinarius
Gusala
Senin,
20 Oktober 2014
INSTITUT
SAINS & TEKNOLOGI AKPRIND YOGYAKARTA
TAHUN AJARAN 2014-2015
TUGAS 1
1. Buatlah database baru dengan nama <nama_namakel>
contoh : eka_sn2
2. Buatlah tabel sesuai dengan ketentuan berikut ini :
a.
t_dosen ( tabel induk )
+------------+-------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
|
id_dosen | varchar(8) | NO
| PRI | NULL | |
|
nama_dosen | varchar(50) | YES | | NULL
| |
|
alamat | varchar(50) | YES | |
NULL |
|
+------------+-------------+------+-----+---------+-------+
b.
t_matkul ( tabel anak )
+-------------+-------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
|
id_matkul | varchar(8) | NO
| PRI | NULL | |
|
nama_matkul | varchar(20) | YES | | NULL
| |
|
id_dosen | varchar(8) | YES
| MUL | NULL | |
|
kelas | varchar(4) | YES
| | NULL |
|
+-------------+-------------+------+-----+---------+-------+
3. Isilah kedua tabel, masing – masing tabel minimal 5
baris record
4. Gantilah nama kolom kelas yang ada di tabel t_matkul menjadi
kls
5. Gantilah type nama_dosen varchar(50) yang ada di tabel
t_dosen
menjadi varchar(30)
6. Lakukan update dan delete record pada record yang
telah diisikan
7. Buatlah tabel baru dengan nama t_dosen2 dan isi tabel
t_dosen2
dengan mengcopy
seluruh isi dari tabel t_dosen
8. Tampilkan kolom nama_dosen dan alamat
9. Tampilkan kolom nama_dosen secara desc
10. Hapus database yang anda buat sekarang.
JAWAB :
1.
mysql> create database eka_SN2;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
|eka_SN2 |
+--------------------+
4 rows in set (0.00 sec)
mysql> use eka_SN2;
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| eka_SN2 |
+--------------------+
4
rows in set (0.00 sec)
2.
a. t_dosen
mysql> create
table t_dosen (id_dosen varchar (8),nama_dosen varchar (50),alamat varchar
(50));
Query OK, 0 rows
affected (0.08 sec)
mysql> desc
t_dosen;
+------------+-------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
|
id_dosen | varchar(8) | NO
| PRI | NULL | |
|
nama_dosen | varchar(50) | YES | | NULL
| |
|
alamat | varchar(50) | YES | |
NULL | |
+------------+-------------+------+-----+---------+-------+
4
rows in set (0.02 sec)
b. t_matkul
mysql> create
table t_matkul (id_matkul varchar (8),nama_matkul varchar (20), id_dosen
varchar (8),kelas varchar (4));
Query OK, 0 rows
affected (0.08 sec)
mysql> desc
t_matkul;
+-------------+-------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
|
id_matkul | varchar(8) | NO
| PRI | NULL | |
| nama_matkul
| varchar(20) | YES | | NULL
| |
|
id_dosen | varchar(8) | YES
| MUL | NULL | |
|
kelas | varchar(4) | YES
| | NULL |
|
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
3.
a. t_dosen
mysql> load
data local infile 'D://PRAKTIKUM SMBD/SN2_131051092/eka.txt' into table
t_dosen;
Query OK, 4 rows
affected (0.03 sec)
Records: 4 Deleted: 0
Skipped: 0 Warnings: 0
mysql>
select*from t_dosen;
+-----------+-------------+-------------+
| id_dosen | nama_dosen
| alamat |
+-----------+-------------+-------------+
|1051092 | eka yani | Palembang |
|105109 | uning | Jogja |
|1051070 | Nur widayat | Mulyo Asih |
|1051066 | Putri | Karya Maju |
|1051033 | desi ambar | tegal mulyo |
+-----------+-------------+-------------+
5 rows in set (0.00 sec)
b. t_matkul
mysql> load data local infile
'D://PRAKTIKUM SMBD/SN2_131051092/data.txt' into table t_matkul;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Deleted: 0
Skipped: 0 Warnings: 0
mysql> select*from t_matkul;
+------------+-------------+------------+------------+
| id_matkul | nama_matkul | id_dosen |
kelas |
+------------+-------------+-------------+-----------+
|P011 | MD | 105192 | A |
|FT81 | IMK | 105109 | B |
|P221 | PBO | 1051070 | A |
|P123 | SMBD | 1051066 | B |
|P543 | SO | 1051033 | A |
+-----------+-------------+-------------+------------+
5 rows in set (0.00 sec)
4.
mengganti kolom
mysql>
alter table t_matkul change kelas kls varchar(4);
Query
OK, 2 rows affected (0.14 sec)
Records:
2 Duplicates: 0 Warnings: 0
mysql> desc
t_matkul;
+-------------+-------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
|
id_matkul | varchar(8) | NO
| PRI | NULL | |
|
nama_matkul | varchar(20) | YES | | NULL
| |
|
id_dosen | varchar(8) | YES
| MUL | NULL | |
| kls |
varchar(4) | YES | |
NULL | |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
5.
mengganti nama type data
mysql> alter table t_dosen modify
nama_dosen varchar (50), modify series varchar (30);
Query OK, 2 rows affected (0.13 sec)
Records: 2 Duplicates: 0
Warnings: 0
mysql> desc t_dosen;
+------------+-------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
|
id_dosen | varchar(8) | NO
| PRI | NULL | |
|
nama_dosen | varchar(30) | YES | |
NULL | |
|
alamat | varchar(50) | YES | |
NULL | |
+------------+-------------+------+-----+---------+-------+
5
rows in set (0.01 sec)
6. melakukan update
dan delete record pada record
mysql>
delete from t_dosen where id_dosen='1051092';
Query
OK, 1 row affected (0.02 sec)
mysql> select*from t_dosen;
+-----------+-------------+-------------+
| id_dosen | nama_dosen
| alamat |
+-----------+-------------+-------------+
|105109 | uning | Jogja |
|1051070 | Nur widayat | Mulyo Asih |
|1051066 | Putri | Karya Maju |
|1051033 | desi ambar | tegal mulyo |
+-----------+-------------+-------------+
6
rows in set (0.00 sec)
7. membuat tabel
baru dengan nama t_dosen2 dan isi tabel t_dosen2 dengan mengcopy seluruh isi
dari tabel t_dosen
mysql> create
table t_dosen2(id_dosen varchar (8),nama_dosen varchar (50),alamat varchar
(50));
Query OK, 0 rows
affected (0.09 sec)
mysql> desc
t_dosen2;
+------------+-------------+------+-----+---------+-------+
|
Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
|
id_dosen | varchar(8) | NO
| PRI | NULL | |
|
nama_dosen | varchar(50) | YES | | NULL
| |
|
alamat | varchar(50) | YES | |
NULL | |
+------------+-------------+------+-----+---------+-------+
4
rows in set (0.02 sec)
mysql> load
data local infile 'D://PRAKTIKUM SMBD/SN2_131051092/eka.txt' into table
t_dosen;
Query OK, 4 rows
affected (0.03 sec)
Records: 4 Deleted: 0
Skipped: 0 Warnings: 0
mysql>
select*from t_dosen;
+-----------+-------------+-------------+
| id_dosen | nama_dosen
| alamat |
+-----------+-------------+-------------+
|1051092 | eka yani | Palembang |
|105109 | uning | Jogja |
|1051070 | Nur widayat | Mulyo Asih |
|1051066 | Putri | Karya Maju |
|1051033 | desi ambar | tegal mulyo |
+-----------+-------------+-------------+
5 rows in set (0.00 sec)
8.
menampilkan kolom nama_dosen dan alamat
mysql> select
nama_dosen ,alamat from t_dosen;
+-------------+-------------+
| nama_dosen | alamat
|
+-------------+-------------+
| eka yani | Palembang |
| uning | Jogja |
| Nur widayat | Mulyo Asih |
| Putri | Karya Maju |
| desi ambar | tegal mulyo |
+-------------+-------------+
5 rows in set (0.00 sec)
9.
menampilkan kolom nama_dosen secara desc
mysql> select*from t_dosen ORDER BY nama_dosen
DESC;
+-----------+-------------+-------------+
| id_dosen | nama_dosen
| alamat |
+-----------+-------------+-------------+
|1051033 | desi ambar
| tegal mulyo |
|105192 | eka yani | Palembang |
|1051070 | Nur widayat | Mulyo Asih |
|1051066 | Putri | Karya Maju |
|105109
| uning
| Jogja |
+-----------+-------------+-------------+
5 rows in set (0.00 sec)
10.
menghapus database yang di buat
mysql> drop database eka_SN2;
Query
OK, 0 rows affected (0.02 sec)
mysql>
show databases;
+--------------------+
| Database |
+--------------------+
|
information_schema |
| mysql |
|
performance_schema |
+--------------------+
4 rows in set (0.00 sec)
Komentar