TUGAS MODUL 4

Tugas modul 2

mysql> create database employee;

Query OK, 1 row affected (0.01 sec)

mysql> use employee;

Database changed

mysql> create table employee(id int(11) auto_increment primary key,first_name varchar(15),last_name varchar(15),start_date date,end_date date,salary float(8,2),city varchar(10), description varchar(15));

Query OK, 0 rows affected (1.03 sec)

mysql> desc employee;

+-------------+-------------+------+-----+---------+----------------+

| Field       | Type        | Null | Key | Default | Extra          |

+-------------+-------------+------+-----+---------+----------------+

| id          | int(11)     | NO   | PRI | NULL    | auto_increment |

| first_name  | varchar(15) | YES  |     | NULL    |                |

| last_name   | varchar(15) | YES  |     | NULL    |                |

| start_date  | date        | YES  |     | NULL    |                |

| end_date    | date        | YES  |     | NULL    |                |

| salary      | float(8,2)  | YES  |     | NULL    |                |

| city        | varchar(10) | YES  |     | NULL    |                |

| description | varchar(15) | YES  |     | NULL    |                |

+-------------+-------------+------+-----+---------+----------------+

8 rows in set (0.25 sec)

mysql> insert into employee values(1,'Jason','Martin','1996-07-25','2006-07-25',1235.56,'Toronto','Programmer'),(2,'Alison','Mathews','1976-03-21','1986-02-21',6662.78,'Vancouver','Tester'),(3,'James','Smith','1978-12-12','1990-03-15',6545.78,'Vancouver','Tester'),(4,'Celia','Rice','1982-10-24','1999-04-21',2345.78,'Vancouver','Manager'),(5,'Robert','Black','1984-01-15','1998-08-08',2335.78,'Vancouver','Tester'),(6,'Linda','Green','1987-07-30','1996-01-04',4323.78,'New York','Tester'),(7,'David','Larry','1990-12-31','1998-02-12',7898.78,'New York','Manager'),(8,'James','Cat','1996-09-17','2002-04-15',1223.78,'Vancouver','Tester'),(9,'Hercule','Poirot','1973-05-23','2001-08-09',4313.98,'Brussels','Detective'),(10,'Lincoln','Rhyme','1999-05-25','2011-07-13',3213.98,'New York','Forensics'),(11,'Sherlock','Holmes','1923-08-12','1945-07-21',4124.21,'London','Detective');

Query OK, 11 rows affected (0.16 sec)

Records: 11  Duplicates: 0  Warnings: 0

mysql> select* from employee;

+----+------------+-----------+------------+------------+---------+-----------+-------------+

| id | first_name | last_name | start_date | end_date   | salary  | city      | description |

+----+------------+-----------+------------+------------+---------+-----------+-------------+

|  1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1235.56 | Toronto   | Programmer  |

|  2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6662.78 | Vancouver | Tester      |

|  3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6545.78 | Vancouver | Tester      |

|  4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2345.78 | Vancouver | Manager     |

|  5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2335.78 | Vancouver | Tester      |

|  6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4323.78 | New York  | Tester      |

|  7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7898.78 | New York  | Manager     |

|  8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1223.78 | Vancouver | Tester      |

|  9 | Hercule    | Poirot    | 1973-05-23 | 2001-08-09 | 4313.98 | Brussels  | Detective   |

| 10 | Lincoln    | Rhyme     | 1999-05-25 | 2011-07-13 | 3213.98 | New York  | Forensics   |

| 11 | Sherlock   | Holmes    | 1923-08-12 | 1945-07-21 | 4124.21 | London    | Detective   |

+----+------------+-----------+------------+------------+---------+-----------+-------------+

11 rows in set (0.03 sec)

mysql> create table jobs(job_id int primary key auto_increment, title varchar(20));

Query OK, 0 rows affected (0.17 sec)

mysql> desc jobs;

+--------+-------------+------+-----+---------+----------------+

| Field  | Type        | Null | Key | Default | Extra          |

+--------+-------------+------+-----+---------+----------------+

| job_id | int(11)     | NO   | PRI | NULL    | auto_increment |

| title  | varchar(20) | YES  |     | NULL    |                |

+--------+-------------+------+-----+---------+----------------+

2 rows in set (0.02 sec)

mysql> insert into jobs values(‘1’,’Programmer’),(‘2’,’Tester’),(‘3’,’Manager’),(‘4’,’Spy’),(‘5’,’Detective’),(‘6’,’Forensics’),(‘7’,’Developer’);

Query OK, 7 rows affected (0.06 sec)

Records: 7  Duplicates: 0  Warnings: 0

mysql> select * from jobs;

+--------+------------+

| job_id | title      |

+--------+------------+

|      1 | Programmer |

|      2 | Tester     |

|      3 | Manager    |

|      4 | Spy        |

|      5 | Detective  |

|      6 | Forensics  |

|      7 | Developer  |

+--------+------------+

7 rows in set (0.01 sec)

mysql> create table employee_join as (select*from employee);

Query OK, 11 rows affected (0.21 sec)

Records: 11  Duplicates: 0  Warnings: 0

mysql> alter table employee_join add job_id int;

Query OK, 11 rows affected (0.57 sec)

Records: 11  Duplicates: 0  Warnings: 0

mysql> desc employee_join;

+-------------+-------------+------+-----+---------+-------+

| Field       | Type        | Null | Key | Default | Extra |

+-------------+-------------+------+-----+---------+-------+

| id          | int(11)     | NO   |     | 0       |       |

| first_name  | varchar(15) | YES  |     | NULL    |       |

| last_name   | varchar(15) | YES  |     | NULL    |       |

| start_date  | date        | YES  |     | NULL    |       |

| end_date    | date        | YES  |     | NULL    |       |

| salary      | float(8,2)  | YES  |     | NULL    |       |

| city        | varchar(10) | YES  |     | NULL    |       |

| description | varchar(15) | YES  |     | NULL    |       |

| job_id      | int(11)     | YES  |     | NULL    |       |

+-------------+-------------+------+-----+---------+-------+

9 rows in set (0.02 sec)

mysql> update employee_join,jobs set employee_join.job_id=jobs.job_id where employee_join.description=jobs.title;

Query OK, 11 rows affected (0.21 sec)

Rows matched: 11  Changed: 11  Warnings: 0

mysql> select* from employee_join;

+----+------------+-----------+------------+------------+---------+-----------+-------------+--------+

| id | first_name | last_name | start_date | end_date   | salary  | city      | description | job_id |

+----+------------+-----------+------------+------------+---------+-----------+-------------+--------+

|  1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1235.56 | Toronto   | Programmer  |      1 |

|  2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6662.78 | Vancouver | Tester      |      2 |

|  3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6545.78 | Vancouver | Tester      |      2 |

|  4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2345.78 | Vancouver | Manager     |      3 |

|  5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2335.78 | Vancouver | Tester      |      2 |

|  6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4323.78 | New York  | Tester      |      2 |

|  7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7898.78 | New York  | Manager     |      3 |

|  8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1223.78 | Vancouver | Tester      |      2 |

|  9 | Hercule    | Poirot    | 1973-05-23 | 2001-08-09 | 4313.98 | Brussels  | Detective   |      5 |

| 10 | Lincoln    | Rhyme     | 1999-05-25 | 2011-07-13 | 3213.98 | New York  | Forensics   |      6 |

| 11 | Sherlock   | Holmes    | 1923-08-12 | 1945-07-21 | 4124.21 | London    | Detective   |      5 |

+----+------------+-----------+------------+------------+---------+-----------+-------------+--------+

11 rows in set (0.00 sec)

mysql> alter table employee_join drop description;

Query OK, 11 rows affected (0.38 sec)

Records: 11  Duplicates: 0  Warnings: 0

mysql> select* from employee_join;

+----+------------+-----------+------------+------------+---------+-----------+--------+

| id | first_name | last_name | start_date | end_date   | salary  | city      | job_id |

+----+------------+-----------+------------+------------+---------+-----------+--------+

|  1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1235.56 | Toronto   |      1 |

|  2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6662.78 | Vancouver |      2 |

|  3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6545.78 | Vancouver |      2 |

|  4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2345.78 | Vancouver |      3 |

|  5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2335.78 | Vancouver |      2 |

|  6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4323.78 | New York  |      2 |

|  7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7898.78 | New York  |      3 |

|  8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1223.78 | Vancouver |      2 |

|  9 | Hercule    | Poirot    | 1973-05-23 | 2001-08-09 | 4313.98 | Brussels  |      5 |

| 10 | Lincoln    | Rhyme     | 1999-05-25 | 2011-07-13 | 3213.98 | New York  |      6 |

| 11 | Sherlock   | Holmes    | 1923-08-12 | 1945-07-21 | 4124.21 | London    |      5 |

+----+------------+-----------+------------+------------+---------+-----------+--------+

11 rows in set (0.00 sec)

mysql> select * from jobs;

+--------+------------+

| job_id | title      |

+--------+------------+

|      1 | Programmer |

|      2 | Tester     |

|      3 | Manager    |

|      4 | Spy        |

|      5 | Detective  |

|      6 | Forensics  |

|      7 | Developer  |

+--------+------------+

7 rows in set (0.00 sec)

No 1

mysql> create view view1(name,salary,city,job_desc) as select concat(first_name," ",last_name),salary,city,description from employee;

Query OK, 0 rows affected (0.16 sec)

mysql> select * from view1;

+-----------------+---------+-----------+------------+

| name            | salary  | city      | job_desc   |

+-----------------+---------+-----------+------------+

| Jason Martin    | 1235.56 | Toronto   | Programmer |

| Alison Mathews  | 6662.78 | Vancouver | Tester     |

| James Smith     | 6545.78 | Vancouver | Tester     |

| Celia Rice      | 2345.78 | Vancouver | Manager    |

| Robert Black    | 2335.78 | Vancouver | Tester     |

| Linda Green     | 4323.78 | New York  | Tester     |

| David Larry     | 7898.78 | New York  | Manager    |

| James Cat       | 1223.78 | Vancouver | Tester     |

| Hercule Poirot  | 4313.98 | Brussels  | Detective  |

| Lincoln Rhyme   | 3213.98 | New York  | Forensics  |

| Sherlock Holmes | 4124.21 | London    | Detective  |

+-----------------+---------+-----------+------------+

11 rows in set (0.02 sec)

No 2

mysql> create algorithm=temptable view view2(job_desc,emp_count) as select description,count(id) from employee group by description;

Query OK, 0 rows affected (0.07 sec)

mysql> select * from view2;

+------------+-----------+

| job_desc   | emp_count |

+------------+-----------+

| Detective  |         2 |

| Forensics  |         1 |

| Manager    |         2 |

| Programmer |         1 |

| Tester     |         5 |

+------------+-----------+

5 rows in set (0.02 sec)

No 3

mysql> create table employee_bak(id int(11) not null default 0,first_name varchar(15),last_nam varchar(15),start_date date,end_date date,salary float(8,2),city varchar(10),job_id int(11

));

Query OK, 0 rows affected (0.20 sec)

mysql> desc employee_bak;

+------------+-------------+------+-----+---------+-------+

| Field      | Type        | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| id         | int(11)     | NO   |     | 0       |       |

| first_name | varchar(15) | YES  |     | NULL    |       |

| last_nam   | varchar(15) | YES  |     | NULL    |       |

| start_date | date        | YES  |     | NULL    |       |

| end_date   | date        | YES  |     | NULL    |       |

| salary     | float(8,2)  | YES  |     | NULL    |       |

| city       | varchar(10) | YES  |     | NULL    |       |

| job_id     | int(11)     | YES  |     | NULL    |       |

+------------+-------------+------+-----+---------+-------+

8 rows in set (0.03 sec)

mysql> delimiter #

mysql> create trigger sesudah_delete 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.16 sec)

mysql> delimiter ;

mysql> delete from employee_join where id=11;

Query OK, 1 row affected (0.12 sec)

mysql> select* from  employee_join;

+----+------------+-----------+------------+------------+---------+-----------+--------+

| id | first_name | last_name | start_date | end_date   | salary  | city      | job_id |

+----+------------+-----------+------------+------------+---------+-----------+--------+

|  1 | Jason      | Martin    | 1996-07-25 | 2006-07-25 | 1235.56 | Toronto   |      1 |

|  2 | Alison     | Mathews   | 1976-03-21 | 1986-02-21 | 6662.78 | Vancouver |      2 |

|  3 | James      | Smith     | 1978-12-12 | 1990-03-15 | 6545.78 | Vancouver |      2 |

|  4 | Celia      | Rice      | 1982-10-24 | 1999-04-21 | 2345.78 | Vancouver |      3 |

|  5 | Robert     | Black     | 1984-01-15 | 1998-08-08 | 2335.78 | Vancouver |      2 |

|  6 | Linda      | Green     | 1987-07-30 | 1996-01-04 | 4323.78 | New York  |      2 |

|  7 | David      | Larry     | 1990-12-31 | 1998-02-12 | 7898.78 | New York  |      3 |

|  8 | James      | Cat       | 1996-09-17 | 2002-04-15 | 1223.78 | Vancouver |      2 |

|  9 | Hercule    | Poirot    | 1973-05-23 | 2001-08-09 | 4313.98 | Brussels  |      5 |

| 10 | Lincoln    | Rhyme     | 1999-05-25 | 2011-07-13 | 3213.98 | New York  |      6 |

+----+------------+-----------+------------+------------+---------+-----------+--------+

10 rows in set (0.00 sec)

mysql> select* from  employee_bak;

+----+------------+----------+------------+------------+---------+--------+--------+

| id | first_name | last_nam | start_date | end_date   | salary  | city   | job_id |

+----+------------+----------+------------+------------+---------+--------+--------+

| 11 | Sherlock   | Holmes   | 1923-08-12 | 1945-07-21 | 4124.21 | London |      5 |

+----+------------+----------+------------+------------+---------+--------+--------+

1 row in set (0.00 sec)