TUGAS 2 MODUL 6
Sebuah database universitas memiliki 3 buah tabel sebagai berikut:
Buat database universitas
Buat
Table pada Database Universitas
Table
Instruktur
Table Instruktur CREATE TABLE instruktur ( nip INT
NOT NULL, namains VARCHAR(25), jurusan VARCHAR(25), asalkota VARCHAR(25), PRIMARY
KEY (nip));
Buat table instruktur
Table
matakuliah
CREATE
TABLE matakuliah ( nomk VARCHAR(6) NOT NULL, namamk VARCHAR(30), sks INT, PRIMARY
KEY (nomk));
Membuat table matakuliah
Table
kuliah
CREATE TABLE kuliah ( nip INT, nomk VARCHAR(6), ruangan
INT, jmlmhs INT,
FOREIGN KEY (nip) REFERENCES instruktur(nip), FOREIGN
KEY (nomk) REFERENCES matakuliah(nomk));
Membuat table kuliah
1.
Tuliskan
query untuk membuat ketiga tabel tersebut. Tipe data menyesuaikan dengan nilai
setiap kolom, seperti contoh data
sebagai berikut:
Tabel
instruktur
INSERT INTO instruktur VALUES
(1, 'Steve Wozniak', 'Ilmu Komputer', 'Bantul'),
(2, 'Steve Jobs', 'Seni Rupa', 'Solo'),
(3, 'James Gosling', 'Ilmu Komputer', 'Klaten'),
(4, 'Bill Gates', 'Ilmu Komputer', 'Magelang');
Table
Matakuliah
INSERT INTO matakuliah VALUES
('KOM101', 'Algoritma dan Pemrograman', 3),
('KOM102', 'Basis Data', 3),
('SR101', 'Desain Elementer', 3),
('KOM201', 'Pemrograman Berorientasi Objek', 3);
Table
Kuliah
INSERT INTO kuliah VALUES
(1, 'KOM101', 101, 50),
(1, 'KOM102', 102, 35),
(2, 'SR101', 101, 45),
(3, 'KOM201', 101, 55);
2.
Tuliskan query
untuk mendapatkan data-data
di bawah ini.
Tambahkan data pada
tabel sesuai dengan kebutuhan.
a.
Instruktur-instruktur
jurusan ‘Ilmu Komputer’
b.
Nomor
mata kuliah yang pesertanya lebih dari 40 orang
SELECT nomk FROM kuliah WHERE jmlmhs>40;
c.
Nomor
dan mata kuliah yang pesertanya lebih dari 40 orang
SELECT B.nomk, B.namamk FROM kuliah A JOIN
matakuliah B ON A.nomk = B.nomk WHERE A.jmlmhs>40;
SELECT nip FROM kuliah WHERE nomk='KOM102';
e.
nip
instruktur yang mengampu mata kuliah ‘Basis Data’
SELECT A.nip FROM kuliah A JOIN matakuliah B ON
A.nomk = B.nomk WHERE B.namamk='Basis Data';
f.
nip
dan nama instruktur yang mengampu mata kuliah ‘Basis Data’
SELECT A.nip, C.nama FROM kuliah A JOIN matakuliah B
ON A.nomk = B.nomk JOIN instruktur C ON
A.nip = C.nip WHERE B.namamk='Basis Data';
g.
Nama
mata kuliah dan ruangan yang diampu oleh ‘Steve Jobs’
SELECT namamk, ruangan FROM kuliah JOIN matakuliah
ON kuliah.nomk = matakuliah.nomk JOIN instruktur ON kuliah.nip = instruktur.nip
WHERE namains='Steve Jobs';
h.
Jumlah
total mahasiswa yang diampu oleh ‘Steve Wozniak’
SELECT SUM(jmlmhs) AS jmlmhs FROM kuliah JOIN
instruktur ON kuliah.nip = instruktur.nip WHERE namains='Steve Jobs';
i.
Nomor
dan nama instruktur yang mengampu mahasiswa terbanyak
SELECT instruktur.nip, instruktur.namains FROM
kuliah JOIN instruktur ON kuliah.nip = instruktur.nip WHERE
kuliah.jmlmhs=(SELECT MAX(jmlmhs) FROM kuliah);
j.
Nomor
dan nama instruktur yang belum mengampu mata kuliah apapun
SELECT B.nip, B.namains FROM instruktur B LEFT JOIN
kuliah A ON A.nip= B.nip WHERE A.nip IS NULL;
3.
Buatlah
view untuk mendapatkan data berikut ini:
a.
Nomor dan nama instruktur yang belum mengampu
mata kuliah apapun
CREATE VIEW view3 AS
SELECT B.nip, B.namains FROM instruktur B LEFT JOIN
kuliah A ON A.nip= B.nip WHERE A.nip IS NULL;
Query
membuat view view3
SELECT * FROM view3;
b.
Jumlah
mata kuliah yang diampu oleh setiap instruktur
CREATE ALGORITHM = TEMPTABLE VIEW view4(namains,
jumlah_matkul) AS SELECT B.namains, count(A.nip) FROM kuliah A INNER JOIN
instruktur B ON A.nip = B.nip GROUP BY A.nip;
Membuat
view view4
SELECT * FROM view4;
4.
Buatlah Trigger
untuk pencatatan perubahan
ruangan untuk sebuah
mata kuliah. Catatan
perubahan disimpan dalam tabel berikut:
Table
roomChanges
CREATE TABLE roomChanges(user_id
varchar(15),deskripsi varchar(100));
DESC roomChanges;
DELIMITER //
CREATE TRIGGER log_roomChanges AFTER UPDATE
ON kuliah
FOR EACH ROW
BEGIN
INSERT INTO roomChanges
VALUES(user(), CONCAT('Merubah ruangan',NEW.nomk,
'dari ruang ',OLD.ruangan,' ke ruang ',NEW.ruangan));
END //
DELIMITER ;
Buat trigger log_roomChanges pada table kuliah
UPDATE kuliah SET ruangan=102 WHERE nomk='KOM101';
SELECT * FROM roomChanges;
Melihat table pada roomChanges
5.
Buatlah
fungsi atau prosedur sesuai kasus berikut ini:
a.
Fungsi untuk
menampilkan jumlah kuliah
yang diadakan di
sebuah ruangan (nama
ruangan dimasukkan sebagai input)
DELIMITER //
CREATE FUNCTION countroom_A(getRoom INT)
RETURNS INT DETERMINISTIC
BEGIN
DECLARE jml INT;
SELECT count(ruangan) INTO jml FROM kuliah WHERE ruangan=102;
RETURN jml;
END //
DELIMITER ;
Membuat function countroom_A
SELECT countroom_A(102);
Menjalankan perintah function countroom_A
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.
DELIMITER //
CREATE FUNCTION getroom_B1(getNomk varchar(10))
RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
DECLARE room INT;
SELECT ruangan INTO room FROM kuliah WHERE
nomk=getNomk;
IF (room>0) THEN
RETURN room;
ELSE
RETURN"Not Found";
END IF;
END //
DELIMITER ;
Buat function getroom_B1
SELECT getroom_B1(‘KOM102’);
Menjalankan perintah function getroom_b1
SELECT getroom_B1(‘KOM103’);
Pemanggilan function apabila tidak ditemukan
c.
Prosedur untuk
menampilkan nama mata
kuliah dan ruangan
yang diampu oleh
seorang instruktur (nama instruktur dimasukkan sebagai input)
DELIMITER //
CREATE PROCEDURE showRoom1(getNamains varchar(225))
BEGIN
SELECT namamk, ruangan
FROM
kuliah
INNER JOIN matakuliah ON kuliah.nomk =
matakuliah.nomk
INNER JOIN instruktur ON kuliah.nip = instruktur.nip
WHERE instruktur.namains = getNamains;
END //
DELIMITER ;
Membuat procedure showRoom
CALL showRoom(Stave Jobs’);
Memanggil perintah procedure showRoom
CALL showroom('Steve Wozniak');
Memanggil procedure shoowRoom
d.
Prosedur untuk
menampilkan jumlah SKS yang
diampu oleh seorang
instruktur (nama instruktur
dimasukkan sebagai input)
DELIMITER //
CREATE PROCEDURE getSks(getNamains varchar(10))
BEGIN
SELECT namains, sum(sks)
FROM
kuliah
INNER JOIN matakuliah ON kuliah.nomk =
matakuliah.nomk
INNER JOIN instruktur ON kuliah.nip = instruktur.nip
LIKE getNamains;
END //
DELIMITER ;
Membuat procedure getSks
CALL getSks('Steve Jobs');
Memanggil Procedure getSks
CALL getSks('Steve Wozniak');
Memanggil perintah procedure getSks