TUGAS2

SISTEM MANAGEMEN BASIS DATA

DENGAN My SQL

MODUL 1

Menggunakan  Insert

Insert menggunakan load data

MODUL 2

Tabel dan Manipulasi Data
Membuat tabel dengan primary key
Primary key adalah nilai yang menjadi pembeda antara satu record data dengan record yang lain. Membuat tabel dengan primary key ditunjukkan sebagai berikut:

Pada contoh di atas, kolom 'id' diberikan 'AUTO_INCREMENT', artinya kolom ini akan diberikan nilai increment secara automatis. Kolom 'id' diset sebagai PRIMARY KEY.
Memberikan nilai UNIQUE
UNIQUE memberikan batasan bahwa nilai dalam sebuah kolom harus distinct (atau tidak ada data yang nilainya sama). Error akan terjadi jika kita menambahkan nilai yang sama dengan data yang sudah ada untuk kolom tersebut.

 

 

Parameter IF NOT EXISTS
Parameter IF NOT EXISTS digunakan untuk mengecek apakah sebuah tabel dengan nama yang sama sudah ada atau belum sebelum membuat tabel. Penggunaannya dapat dilihat sebagai berikut:

Pada contoh di atas, mencoba membuat tabel dengan nama CARS yang sudah ada. Mysql akan memberikan satu buat warning. namun ketika, kita membuat tabel yang belum ada, maka tabel tersebut akan terbentuk.
Menyalin tabel dengan statement SELECT
Kita dapat membuat tabel dari tabel lain menggunakan statement SELECT. Caranya ditunjukkan sebagai berikut. Pertama, kita membuat terlebih dahulu tabel sumber dengan nama 'employee' seperti sebagai berikut:

Selanjutnya, mulai menyalin tabel. Pada contoh di bawah, data yang akan disalin ke tabel yang baru adalah data employee yang mulai bekerja antara 1 Januari 1970 sampai 31 Desember 1990.

Membuat tabel dengan foreign key
MySQL memiliki beberapa engine database. Salah satunya adalah engine InnoDB yang memiliki dukungan terhadap ACID. Pembuatan tabel menggunakan engine InnoDB dapat membantu memberikan referential integrity. Berikut adalah contoh membuat tabel yang memiliki foreign key dengan engine InnoDB:

Perintah sql di atas, membuat dua buah tabel dengan nama models dan orders. Tabel orders memiliki foreign key modelid yang bereferensi dengan primary key tabel models, yaitu modelid.

Pada perintah sql di atas, memasukkan data ke tabel models dan orders. Data yang dimasukkan pada tabel orders memiliki referensi terhadap primary key pada tabel models, yaitu modelid yang nilainya adalah 1.

Perintah sql di atas, menghapus data di tabel models yang memiliki modelid = 1. Secara automatis, data pada tabel orders yang memiliki modelid = 1 juga ikut terhapus untuk memastikan referential integrity.

Menambah kolom pada tabel

Memodifikasi nama

Memodifikasi definisi tabel

Menambahkan primary key

Menghapus kolom

Mengganti nama tabel
Mengganti nama tabel yang sudah dibuat dapat dilakukan dengan menggunakan perintah ALTER TABLE RENAME. Berikut adalah contoh penggunaannya. Disini merubah nama employee_copy menjadi senior_employee.

Menghapus semua data dalam tabel
Untuk menghapus semua data di dalam tabel digunakan command TRUNCATE sebagai berikut:

Menghapus Tabel
Untuk menghapus tabel dari database, digunakan command DROP TABLE.

Memasukkan data dengan INSERT
Memasukkan data ke dalam tabel dilakukan menggunakan command INSERT. Bentuk command INSERT dasar adalah sebagai berikut:

Memasukkan lebih dari satu baris dalam satu INSERT
Memasukkan lebih dari satu baris dapat dilakukan menggunakan satu buah command INSERT. Hal ini dilakukan dengan memisahkan input tiap baris dengan koma (',').

INSERT menggunakan data dari tabel lain
Kita juga dapat memasukkan data dari tabel yang lain menggunakan command INSERT INTO SELECT.

Pada command di atas, hanya memilih id, nama, dan kota dari tabel employee untuk dimasukkan ke dalam tabel employee2.
Mengubah data menggunakan UPDATE
Data yang telah di masukkan ke dalam tabel dapat kita ubah menggunakan command UPDATE. Bentuk umum command UPDATE adalah sebagai berikut.

Mengubah nilai berdasarkan nilai yang dimiliki sekarang

Mengganti data dari tabel
Data yang sudah masukkan dalam tabel dapat kita ganti dengan menggunakan command REPLACE.Format penggunaan command REPLACE sama dengan INSERT, namun ia menggantikan data yang memiliki nilai sama di indeks PRIMARY KEY atau UNIQUE.

Menghapus data dari tabel
Menghapus data tertentu dari sebuah tabel dalam database dapat dilakukan menggunakan command delete.

TUGAS MODUL 4

Membuat table job:
mysql> create table job(job_id int primary key,title varchar(20));
Query OK, 0 rows affected (0.12 sec)

Membuat table employee_join:
mysql> create table employee_join(id char(6) primary key,first_name varchar(20),last_name varchar(20),start_date date,end_date date,salary float(8,2),
city varchar(15),job_id int,foreign key (job_id)references job(job_id));
Query OK, 0 rows affected (0.21 sec)

Membuat view1:
mysql> create view view1 as
    -> select concat(first_name, " ",last_name) as name,salary,city,title as job_desc from employee_join,job where employee_join.job_id=job.job_id;
Query OK, 0 rows affected (0.09 sec)

 

Membuat view2:
mysql> create view view2 as
    -> select title as job_desc,count(*)as emp_count from employee_join,job where employee_join.job_id=job.job_id group by title;
Query OK, 0 rows affected (0.08 sec)

mysql> delimiter //
mysql> create trigger log_employee after delete
    -> on employee_join
    -> for each row
    -> begin
    -> insert into employee_bak
    -> values(old.id,old.first_name,old.last_name,old.start_date,old.end_date,old.salary,old.city,old.job_id);
    -> end //
Query OK, 0 rows affected (0.20 sec)

mysql> delimiter ;
mysql> delete from employee_join where id=6;
Query OK, 1 row affected (0.08 sec)

LATIHAN

1.    Buatlah   function   untuk   menampilkan   gabungan   first_name   dan   last_name   dengan   bentuk “last_name, first_name”.

mysql> delimiter //
mysql> create function revname(in_last_name varchar(15), in_first_name varchar(15))
    -> returns varchar(35)
    -> begin
    -> return concat(in_last_name,' ',in_first_name);
    -> end //
Query OK, 0 rows affected (0.07 sec)

2.    Buatlah procedure untuk menampilkan  job_description dari masukan sebuah id employee.  Berikut adalah contoh outputnya.

mysql> delimiter //
mysql> create procedure empjob(IN idname char(6))
    -> begin
    -> select*from employee_join where id like idname;
    -> end //
Query OK, 0 rows affected (0.00 sec)

SOAL

mysql> create table instruktur (nip char(6)primary key, namains varchar(20),jurusan varchar(20),asalkota varchar(20));
Query OK, 0 rows affected (0.13 sec)

mysql> create table matakuliah(nomk char(6)primary key,namamk varchar(30),sks int);
Query OK, 0 rows affected (0.13 sec)

mysql> create table kuliah(nip char(6),nomk char(6),ruangan char(5),jmlmhs int,foreign key(nip)references instruktur(nip),foreign key(nomk)references
matakuliah(nomk));
Query OK, 0 rows affected (0.20 sec)

2. Tuliskan  query  untuk mendapatkan data-data di  bawah  ini.  Tambahkan data pada  tabel  sesuai  dengan kebutuhan.

a)    Instruktur-instruktur jurusan 'Ilmu Komputer'
mysql> select*from instruktur where jurusan='Ilmu Komputer';

b)    Nomor mata kuliah yang pesertanya lebih dari 40 orang
mysql> select matakuliah.nomk from matakuliah join kuliah on matakuliah.nomk=kuliah.nomk where jmlmhs>40;

c)    Nomor dan mata kuliah yang pesertanya lebih dari 40 orang
mysql> select matakuliah.nomk,matakuliah.namamk from matakuliah join kuliah on matakuliah.nomk=kuliah.nomk where jmlmhs>40;

d)    nip instruktur yang mengampu mata kuliah dengan nomor 'KOM102'
mysql> select instruktur.nip from instruktur join kuliah on instruktur.nip=kuliah.nip where nomk='KOM102';

e)    nip instruktur yang mengampu mata kuliah 'Basis Data'
mysql> select kuliah.nip from kuliah join matakuliah on kuliah.nomk=matakuliah.nomk where namamk='Basis Data';

f)    nip dan nama instruktur yang mengampu mata kuliah 'Basis Data'
mysql> select instruktur.nip,instruktur.namains from instruktur join matakuliah join kuliah on instruktur.nip=kuliah.nip and  matakuliah.nomk=kuliah.n
omk where namamk='Basis Data';

g)    Nama mata kuliah dan ruangan yang diampu oleh 'Steve Jobs'
mysql> select matakuliah.namamk,kuliah.ruangan from instruktur join matakuliah join kuliah on instruktur.nip=kuliah.nip and  matakuliah.nomk=kuliah.nomk where namains='Steve Jobs';

h)    Jumlah total mahasiswa yang diampu oleh 'Steve Wozniak'
mysql> select sum(kuliah.jmlmhs) from kuliah join instruktur on kuliah.nip=instruktur.nip where namains='Steve Wozniak';

4. Buatlah   Trigger   untuk   pencatatan   perubahan   ruangan   untuk   sebuah   mata   kuliah.   Catatan
perubahan disimpan dalam tabel berikut:

Table catatan:
mysql> create table catatan(user_id varchar(15),deskripsi varchar(100));
Query OK, 0 rows affected (0.16 sec)

kemudian membuat trigernya,
trigger:
mysql> create trigger log_catatan after update
    -> on kuliah
    -> for each row
    -> begin
    -> insert into catatan
    -> values(user(),concat('merubah ruangan',new.ruangan,'dari ruangan',old.ruangan));
    -> end //
Query OK, 0 rows affected (0.11 sec)

Mengupdate data Kuliah:

mysql> update kuliah set ruangan='102' where nomk='KOM101';
Query OK, 1 row affected (0.07 sec)
Rows matched: 1  Changed: 1  Warnings: 0