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
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;
2.
Buatlah view untuk menampilkan
job_description dan jumlah employee untuk masing-masing job.
mysql>
CREATE VIEW view02 AS
-> SELECT description as job_desc,
COUNT(*) AS emp_count FROM employee GROUP BY job_desc;
3.
Buatlah sebuah trigger untuk menyimpan
data yang dihapus dalam tabel employee_join. Data yang dihapus tersebut
tersimpan dalam tabel baru bernama employee_bak.
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);
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 ;
