Tugas 2 SMBD part 4

MODUL 4
Pertemuan 4 View dan Trigger
 

Tugas

Tabel employee_join

Tabel jobs

1.    Buatlah view yang berisi name (gabungan first-name dan last_name), salary, city, dan job_description dari kedua tabel di atas

Jawab

Syntax mysql :
mysql> CREATE VIEW view01 AS
       -> SELECT CONCAT(first_name, " ",last_name) as name, salary, city, title as job_desc FROM  employee_join NATURAL JOIN jobs;

Hasil

2.    Buatlah view untuk menampilkan job_description dan jumlah employee untuk masing-masing job.

Jawab

Syntax mysql :
mysql> CREATE VIEW view02 AS
         -> SELECT description as job_desc, COUNT(*) AS emp_count FROM employee GROUP BY job_desc;

Hasil



3.    Buatlah sebuah trigger untuk menyimpan data yang dihapus dalam tabel employee_join. Data yang dihapus tersebut tersimpan dalam tabel baru bernama employee_bak.

Jawab

Syntax mysql :mysql> CREATE TABLE employee_bak
    -> (id int not null default 0, first_name varchar(15), last_name varchar (15),
    -> start_date date, end_date date, salary float(8,2), city varchar(10),job_id int);

Hasil



mysql> DELIMITER $$
mysql> CREATE TRIGGER del_employee_join BEFORE 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,O
LD.job_id);
    -> END $$
mysql> DELIMITER ;

Hasil