Sistem Manajemen Basis Data

LATIHAN MODUL 6

1.QUERY UNTUK TABLE INSTRUKTUR, MATA KULIAH DAN KULIAH

Membuat database Universitas
mysql> create database universitas;
Query OK, 1 row affected (0.00 sec)

Menggunakan database universitas
mysql> use universitas;
Database changed

Membuat tabel instruktur
mysql> create table instruktur(nip int auto_increment primary key, namains varchar(30), jurusan varchar(30), asalkota varchar(20));
Query OK, 0 rows affected (0.00 sec)

Memasukkan data ke dalam table instruktur
mysql> insert into instruktur values(1,'Steve Wozniak','Ilmu Komputer','Bantul');
Query OK, 1 row affected (0.00 sec)
mysql> insert into instruktur values('','Steve Jobs','Seni Rupa','Solo');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into instruktur values('','James Gosling','Ilmu Komputer','Klaten');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into instruktur values('','Bill Gates','Ilmu Komputer','Magelang');
Query OK, 1 row affected, 1 warning (0.02 sec)

screenshot program


Membuat tabel matakuliah
mysql> create table matakuliah(nomk varchar(7) primary key, namamk varchar(30) not null, sks char(1));
Query OK, 0 rows affected (0.01 sec)

Memasukkan data ke tabel matakuliah
mysql> insert into matakuliah values('KOM101','Algoritma dan Pemrograman','3');
Query OK, 1 row affected (0.02 sec)
mysql> insert into matakuliah values('KOM102','Basis Data','3');
Query OK, 1 row affected (0.00 sec)
mysql> insert into matakuliah values('SR101','Desain Elementer','3');
Query OK, 1 row affected (0.00 sec)
mysql> insert into matakuliah values('KOM201','Pemrograman Berorientasi Objek','3');
Query OK, 1 row affected (0.00 sec)



Membuat tabel kuliah
mysql> create table kuliah(nip int not null, nomk varchar(7) not null, ruangan int(3) not null, jmlmhs int(2) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> alter table kuliah add foreign key(nip) references instruktur(nip) on update cascade on delete cascade;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table kuliah add foreign key(nomk) references matakuliah(nomk) on update cascade on delete cascade;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

Memasukkan data ke table kuliah
mysql> insert into kuliah values(1,'KOM101',101,50);
Query OK, 1 row affected (0.06 sec)
mysql> insert into kuliah values(1,'KOM102',102,35);
Query OK, 1 row affected (0.00 sec)
mysql> insert into kuliah values(2,'SR101',101,45);
Query OK, 1 row affected (0.01 sec)
mysql> insert into kuliah values(3,'KOM201',101,55);
Query OK, 1 row affected (0.00 sec)



2.QUERY UNTUK MENDAPATKAN DATA-DATA SESUAI 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 nomk from kuliah where jmlmhs>40;


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

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


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


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

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


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

i.Nomor dan nama instruktur yang mengampu mahasiswa terbanyak
mysql> select nip, namains from kuliah join instruktur using(nip) having max(jmlmhs);



j.Nomor dan nama instruktur yang belum mengampu mata kuliah apapun
mysql> select nip, namains from instruktur where nip not in (select nip from kuliah);

3.MEMBUAT VIEW UNTUK MENDAPATKAN DATA-DATA
a.Nomor dan nama instruktur yang belum mengampu mata kuliah apapun
mysql> create view vblmmengampu as select nip, namains from instruktur where nip not in (select nip from kuliah);
b.Jumlah mata kuliah yang diampu oleh setiap instruktur
mysql> create view vjumlahmk as select namains, count(nomk) as jumlah from instruktur join kuliah using(nip) group by namains;
Query OK, 0 rows affected (0.20 sec)


4.MEMBUAT TRIGGER UNTUK PENCATAT PERUBAHAN RUANGAN UNTUK SEBUAH MATA KULIAH.
Membuat table catatan terlebih dahulu
mysql> create table catatan(user_id varchar(15), deskripsi varchar(100));
Query OK, 0 rows affected (0.08 sec)

Membuat TRIGGER update_kuliah
mysql> delimiter /
mysql> create trigger update_kuliah after update on kuliah for each row
    -> begin
    -> insert into catatan values(user(),concat('Merubah ruangan ',old.nomk,' dari ruang ',old.ruangan,' ke ruang ',new.ruangan));
    -> end/
Query OK, 0 rows affected (1.65 sec)
mysql> delimiter ;
mysql> update kuliah set ruangan=102 where nomk='KOM101';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0



5.MEMBUAT FUNGSI ATAU PROSEDURE SESUAI KASUS BERIKUT INI:
a.Fungsi untuk menampilkan jumlah kuliah yang diadakan di sebuah ruangan (nama ruangan dimasukkan sebagai input)
mysql> delimiter /
mysql> create function countRoom(in_room int(3)) returns int
    -> begin
    -> declare jml int;
    -> select count(ruangan) into jml from kuliah where ruangan like in_room;
    -> return jml;
    -> end/
Query OK, 0 rows affected (0.00 sec)




b.Fungsi untuk mendapatkan nama ruangan tempat sebuah mata kuliah diadakan (nomor mata kuliah dimasukkan sebagai input). Berikan nilai 'not found' jika sebuah mata kuliah belum diberi ruangan
mysql> delimiter /
mysql> create function getRoom(in_mk varchar(7)) returns int
    -> begin
    -> declare jml int;
    -> select ruangan into jml from kuliah where nomk like in_mk;
    -> return jml;
    -> end/
Query OK, 0 rows affected (0.00 sec)




c.Procedure   untuk menampilkan nama mata kuliah dan ruangan yang diampu oleh seorang instruktur (nama instruktur dimasukkan sebagai input)
mysql> delimiter /
mysql> create procedure showRoom(in nama varchar(30))
    -> begin
    -> select namamk, ruangan from kuliah join instruktur using(nip) join matakuliah using(nomk) where namains like nama;
    -> end/
Query OK, 0 rows affected (0.00 sec)

SCREENSHOT PROGRAM



d.Procedure   untuk menampilkan jumlah SKS yang diampu oleh seorang instruktur (nama instruktur dimasukkan sebagai input)
mysql> delimiter /
mysql> create procedure getSks(in nama varchar(30))
    -> begin
    -> select namains, sum(sks) from kuliah join instruktur using(nip) join matakuliah using(nomk) where namains like nama;
    -> end/
Query OK, 0 rows affected (0.01 sec)