Yuzhu Areas: Modul 6 (Pertemuan 6 Latihan Soal )

1. Tuliskan query untuk membuat ketiga tabel tersebut.Tipe data menyesuaikan dengan nilai setiap kolom,seperti contoh data sebagai berikut:

Tabel instruktur

mysql> create database universitas;

mysql> use universitas;

Database changed

mysql> create table tabel_instruktur (nip char(5) primary key, nama_ins varchar(22), jurusan varchar(22), asal_kota varchar(15));

mysql> desc tabel_instruktur;

mysql> insert into tabel_instruktur values ('1','Steve Wozniak','Ilmu Komputer','Bantul');

mysql> insert into tabel_instruktur values ('2','Steve Jobs','Seni Rupa','Solo');

mysql> insert into tabel_instruktur values ('3','James Gosling','Ilmu Komputer','Klaten');

mysql> insert into tabel_instruktur values ('4','Bill Gates','Ilmu Komputer','Magelang');

mysql> select * from tabel_instruktur;

Tabel matakuliah

mysql> create table tabel_matakuliah (no_mk varchar(10) primary key, nama_mk varchar(40), sks char(1));

mysql> desc tabel_matakuliah;

mysql> insert into tabel_matakuliah values ('KOM101','Algoritma dan Pemrograman','3');

mysql> insert into tabel_matakuliah values ('KOM102','Basis Data','3');

mysql> insert into tabel_matakuliah values ('SR101','Desain Elementer','3');

mysql> insert into tabel_matakuliah values ('KOM201','Pemrograman Berorientasi Objek','3');

mysql> select * from tabel_matakuliah;

Tabel kuliah

mysql> create table tabel_kuliah(nip char(5), no_mk varchar(10), ruangan int(3), jml_mhs int(4), foreign key(nip) references

tabel_instruktur(nip), foreign key(no_mk) references tabel_matakuliah(no_mk));

mysql> desc tabel_kuliah;

mysql> insert into tabel_kuliah values ('1','KOM101','101','50');

mysql> insert into tabel_kuliah values ('1','KOM102','102','35');

mysql> insert into tabel_kuliah values ('2','SR101','101','45');

mysql> insert into tabel_kuliah values ('3','KOM201','101','55');

mysql> select * from tabel_kuliah;

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

a. Instruktur – instruktur jurusan ‘Ilmu Komputer’

Query menggunakan parameter kondisi WHERE

mysql> select * from tabel_instruktur where jurusan = 'Ilmu Komputer';

b. Nomor mata kuliah yang pesertanya lebih dari 40 orang

Query menggunakan parameter kondisi WHERE

mysql> select no_mk from tabel_kuliah where jml_mhs > 40;

c. Nomor dan mata kuliah yang pesertanya lebih dari 40 orang

Dengan Table Join

mysql> create table tabel_kuliah_join AS (select * from tabel_kuliah);

mysql> select * from tabel_kuliah_join;

mysql> alter table tabel_kuliah_join ADD nama_mk varchar(40) AFTER no_mk;

mysql> desc tabel_kuliah_join;

mysql> select * from tabel_kuliah_join;

mysql> UPDATE tabel_kuliah_join, tabel_matakuliah SET tabel_kuliah_join.nama_mk = tabel_matakuliah.nama_mk WHERE tabel_kuliah_join.no_mk=tabel_matakuliah.no_mk;

mysql> select * from tabel_kuliah_join;

Query menggunakan parameter kondisi WHERE

mysql> select no_mk,nama_mk from tabel_kuliah_join where jml_mhs > 40;

d. Nip instruktur yang mengampu mata kuliah dengan nomor ‘KOM102’

Query menggunakan parameter kondisi WHERE

mysql> select nip from tabel_kuliah_join where no_mk = 'KOM102';

e. Nip instruktur yang mengampu mata kuliah ‘Basis Data’

Query menggunakan parameter kondisi WHERE

mysql> select nip from tabel_kuliah_join where nama_mk = 'Basis Data';

f. Nip dan nama instruktur yang mengampu mata kuliah ‘Basis Data’

Dengan Table Join

mysql> create table tabel_kuliah_join2 AS (select * from tabel_kuliah_join);

mysql> select * from tabel_kuliah_join2;

mysql> alter table tabel_kuliah_join2 ADD nama_ins varchar(22) AFTER nip;

mysql> desc tabel_kuliah_join2;

mysql> select * from tabel_kuliah_join2;

mysql> UPDATE tabel_kuliah_join2, tabel_instruktur SET tabel_kuliah_join2.nama_ins = tabel_instruktur.nama_ins WHERE tabel_kuliah_join2.nip=tabel_instruktur.nip;

mysql> select * from tabel_kuliah_join2;

Query menggunakan parameter kondisi WHERE

mysql> select nip, nama_ins from tabel_kuliah_join2 where nama_mk = 'Basis Data';

g. Nama mata kuliah dan ruangan yang diampu oleh ‘Steve Jobs’

Query menggunakan parameter kondisi WHERE

mysql> select nama_mk, ruangan from tabel_kuliah_join2 where nama_ins = 'Steve Jobs';

h. Jumlah total mahasiswa yang diampu oleh ‘Steve Wozniak’

mysql> select SUM(jml_mhs) from tabel_kuliah_join2 where nama_ins = 'Steve Wozniak';

i. Nomor dan nama instruktur yang mengampu mahasiswa terbanyak

mysql> select nip, nama_ins, max(jml_mhs) from tabel_kuliah_join2;

j. Nomor dan nama instruktur yang belum mengampu mata kuliah apapun

3. Buatlah view untuk mendapatkan data berikut ini:

a. Nomor dan nama instruktur yang belum mengampu mata kuliah apapun

b. Jumlah mata kuliah yang diampu oleh setiap instruktur

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

Nama kolom	Tipe data	Keterangan
user_id	VARCHAR(15)	User MySQL yang melakukan perubahan.Didapatkan dari fungsi user()
deskripsi	VARCHAR(100)	Dituliskan seperti contoh sebagai berikut : 'Merubah ruangan KOM121 dari ruang 101 ke ruang 102'

mysql> create table roomChanges(user_id varchar(15), deskripsi varchar(100));

mysql> delimiter **

mysql> create trigger room_ruangan AFTER UPDATE

-> ON tabel_kuliah

-> FOR EACH ROW

-> BEGIN

-> INSERT INTO roomChanges

-> VALUES (user(), CONCAT('merubah ruangan ',NEW.no_mk,' dari ruang ',OLD.ruangan, ' to ',NEW.ruangan));

-> end **

mysql> delimiter ;

mysql> update tabel_kuliah set ruangan=102 where no_mk='KOM101';

mysql> select * from roomChanges;

5. Buatlah fungsi atau prosedure sesuai kasus berikut ini:

a. Fungsi untuk menampilkan jumlah kuliah yang diadakan disebuah ruangan (nama ruangan dimasukkan sebagai input)

mysql> delimiter **

mysql> create function countRoom(ruangan int(3))

-> returns int(2)

-> begin

-> return concat('1');

-> end **

mysql> delimiter ;

mysql> select countRoom('102');

mysql> delimiter **

mysql> create function countRoom(ruangan int(3))

-> returns int(2)

-> begin

-> return concat('0');

-> end **

mysql> delimiter ;

mysql> select countRoom('103');

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(no_mk varchar(10))

-> returns varchar(8)

-> begin

-> return concat('102');

-> end **

mysql> delimiter ;

mysql> select getRoom('KOM102');

mysql> delimiter **

mysql> create function getRoom(no_mk varchar(10))

-> returns varchar(12)

-> begin

-> return concat('not found');

-> end **

mysql> delimiter ;

mysql> select getRoom('KOM103');

c. Prosedure 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_insName varchar(22))

-> begin

-> select nama_mk, ruangan from tabel_kuliah_join2 where nama_ins like nama_insName;

-> end ^^

mysql> delimiter ;

mysql> call showRoom('Steve Jobs');

mysql> delimiter ^^

mysql> create procedure showRoom(in nama_insName varchar(22))

-> begin

-> select nama_mk, ruangan from tabel_kuliah_join2 where nama_ins like nama_insName;

-> end ^^

mysql> delimiter ;

mysql> call showRoom('Steve Wozniak');

d. Prosedur untuk menampilkan jumlah SKS yang diampu oleh seorang instruktur (nama instruktur dimasukkan sebagai input)

Dengan Table Join

mysql> create table tabel_kuliah_join3 AS (select * from tabel_kuliah_join2);

mysql> select * from tabel_kuliah_join3;

mysql> alter table tabel_kuliah_join3 ADD sks char(1) AFTER no_mk;

mysql> UPDATE tabel_kuliah_join3, tabel_matakuliah SET tabel_kuliah_join3.sks = tabel_matakuliah.sks WHERE tabel_kuliah_join3.no_mk=tabel_matakuliah.no_mk;