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)

LATIHAN MODUL 5

1.Membuat FUNCTION untuk menampilkan gabungan first_name dengan last_name dengan bentuk “last_name, first_name

mysql> delimiter /
mysql> create function revName(ffirst_name varchar(15), flast_name varchar(15)) returns varchar(35)
    -> begin
    -> return concat(ffirst_name,', ',flast_name);
    -> end/
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;

Screenshot program


2.Membuat PROCEDURE untuk membuat job_description dari masukkan sebuah id employee

mysql> delimiter /
mysql> create procedure empJob(in idi int)
    -> begin
    -> select first_name, last_name, title from employee_join join job using(job_id) where id like idi;
    -> end/
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

TUGAS MODUL 4

Berikut Ini Adalah Isi Table Employee_Join Dan Job




1.Membuat View yang berisi gabungan nama(gabungan first_name dan last_name),salary, city, dan job description)

mysql> create view gabung_nama as select concat(first_name,' ',last_name) as name, salary,city, title as job_desc from employee_join join job using(job_id);
Query OK, 0 rows affected (0.05 sec) 

Screenshot program



2.View untuk menampilkan job_description dan jumlah employee untuk masing-masing job

mysql> create view vjob_desc as select distinct (title) as job_desc, count(job_id) as emp_count from employee_join join job using(job_id) group by title;
Query OK, 0 rows affected (0.00 sec)

Screenshot program



3.Membuat TRIGGER untuk menyimpan data yang dihapus dalam table employee_join ke table employee_bak

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.00 sec)
mysql> delimiter ;

Screenshot program

TUGAS MODUL 1

1.Membuat Table bernama ‘event’

Membuat database pethouse terlebih dahulu caranya:
mysql> create database pethouse;
Query OK, 1 row affected (0.00 sec)
Menggunakan database pethouse
mysql> use pethouse;
Database changed
Membuat Tabel bernama event
mysql> create table event(name varchar(20),date date,type varchar(15),remark varchar(255));
Query OK, 0 rows affected (1.00 sec)

Melihat deskripsi table event
mysql> desc event;


4 rows in set (0.06 sec)

Screen shot :


2.Mengisikan data table dengan insert

mysql> insert into event values('Fluffy','1995-05-15','litter','4 kittens, 3 female, 1 male');
Query OK, 1 row affected (0.05 sec)
mysql> insert into event values('Buffy','1993-06-23','litter','5 puppies, 2 female, 3 male');
Query OK, 1 row affected (0.05 sec)

mysql> insert into event values('Buffy','1994-06-19','litter','3 puppies, 3 female');
Query OK, 1 row affected (0.00 sec)
mysql> insert into event values('Chirpy','1999-03-21','vet','needed beak straightened');
Query OK, 1 row affected (0.03 sec)

Screen shot :




3.Mengisikan data table dengan LOAD DATA
mysql> load data local infile 'D://IST AKPRIND/SEMESTER - 5/Sistem Manajemen Basis Data/Tugas/Tugas-2/insert1.txt' into table event;
Query OK, 6 rows affected, 6 warnings (0.00 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 6

4.Menampilkan data yang sudah dibuat dengan SELECT
mysql> select*from event;