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)
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
Loved this post? Then why don't you share It!
Posting Komentar