TUGAS MODUL 4
Table employee_join
Table job
1.
Buatlah view yang berisi name (gabungan
first_name dan last_name), salary, city, dan job_description dari kedua tabel
di atas.
CREATE VIEW
tampil1 (name, salary, city, job_desc) AS
SELECT
CONCAT(first_name, " ", last_name), salary, city, title FROM employee_join
JOIN job ON employee_join.job_id = job.job_id;
2.
Buatlah view untuk menampilkan job_description
dan jumlah employee untuk masing-masing job.
CREATE ALGORITHM = TEMPTABLE VIEW view2 (job_desc, emp_count) AS
SELECT job.title, count(job.job_id) FROM employee_join JOIN job ON employee_join.job_id = job.job_id GROUP BY
title;
3.
Buatlah sebuah trigger untuk menyimpan data yang
dihapus dalam tabel employee_join. Data yang dihapus tersebut disimpan dalam
tabel baru bernama employee_bak.
CREATE
TABLE employee_bak(id int(11) NOT NULL,fist_name varchar(15),last_name
varchar(15),start_date date,end_date date,salary float(8,2),city
varchar(10),job_id int(11));
Trigger
DELIMITER
$$
CREATE
TRIGGER employee_bak AFTER DELETE
ON
employee_join
FOR
EACH ROW
BEGIN
INSERT INTO employee_bak (id, fist_name,
last_name, start_date, end_date, salary, city, job_id)
VALUES (OLD.id, OLD.first_name,
OLD.last_name, OLD.start_date, OLD.end_date, OLD.salary, OLD.city, OLD.job_id);
END
$$
DELIMITER ;
Men-delete data pada id=12 pada
table employee_join
DELETE
FROM employee_join WHERE id = 12;
SELECT
* FROM employee_join;
SELECT
* FROM employee_bak;