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;
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';
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');
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;
mysql> select
* from tabel_kuliah_join3;
Perintah procedurenya
mysql> delimiter ^^
mysql> create procedure getSks(in nama_insName varchar(22))
-> begin
-> select nama_ins, SUM(sks)
from tabel_kuliah_join3 where nama_ins like nama_insName;
-> end ^^
mysql> delimiter ;
mysql> call getSks('Steve Jobs');
mysql> call getSks('Steve Wozniak');
Tidak ada komentar:
Posting Komentar