modul 4

mysql> create table employye(idjoin varchar(10), first_name varchar(15), last_name varchar(15),

 start_date varchar(12), end_date varchar(12), salary varchar(10), city varchar(20), job_desc v

archar(15));

mysql>  insert into employye values('1','Jason','Martin','1996-07-25','2006-07-25', '1235.56','

Toronto','Programmer');

Query OK, 1 row affected (0.07 sec)

mysql> insert into employye_join values('2','Alison','Mathews','1976-03-21','1986-02-21','6662.

78','Vancouver','Tester');

Query OK, 1 row affected (0.07 sec)

mysql> insert into employye values('2','Alison','Mathews','1976-03-21','1986-02-21','6662.78','

Vancouver','Tester');

Query OK, 1 row affected (0.06 sec)

mysql> insert into employye values('3','James','Smith','1978-12-12','1990-03-15','6545.78','Van

couver','Tester');

Query OK, 1 row affected (0.04 sec)

mysql> insert into employye values('4','Celia','Rice','1982-10-24','1999-04-21','2345.78','Vanc

ouver','Manager');

Query OK, 1 row affected (0.04 sec)

mysql> insert into employye values('5','Robert','Black','1984-01-15','1998-08-08','2335.78','Va

ncouver','Tester');

Query OK, 1 row affected (0.06 sec)

mysql> insert into employye values('6','Linda','Green','1987-07-30','1996-01-04','4323.78','New

 York','Tester');

Query OK, 1 row affected (0.07 sec)

mysql> insert into employye values('7','David','Larry','1990-12-31','1998-02-12','7898.78','New

 York','Manager');

Query OK, 1 row affected (0.07 sec)

mysql> insert into employye values('8','James','Cat','1996-09-17','2002-04-15','1233.78','Vanco

uver','Tester');

Query OK, 1 row affected (0.06 sec)

mysql> insert into employye values('10','Hercule','Poirot','1973-05-23','2001-08-09','4313.98',

'Brussels','Detective');

Query OK, 1 row affected (0.06 sec)

mysql> insert into employye values('11','Lincoln','Rhyme','1999-05-25','2011-07-13','3213.98','

New York','Forensics');

Query OK, 1 row affected (0.06 sec)

mysql> insert into employye values('12','Sherlock','Holmes','1923-08-12','1945-07-21','4124.21'

,'London','Detectives');

Query OK, 1 row affected (0.08 sec)

mysql> select*from employye;

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

+

| idjoin | first_name | last_name | start_date | end_date   | salary  | city      | job_desc

|

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

+

| 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 | 1233.78 | Vancouver | Tester

|

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

|

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

|

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

|

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

+

11 rows in set (0.00 sec)

1..)  Buatlah view yang berisi name (gabungan first_name dan last_name), salary, city, dan

job_description dari kedua tabel di atas.

Jawab :

mysql> select concat(first_name, " " , last_name), salary, city, job_desc from employye;

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

| concat(first_name, " " , last_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                           | 1233.78 | Vancouver | Tester     |

| Hercule Poirot                      | 4313.98 | Brussels  | Detective  |

| Lincoln Rhyme                       | 3213.98 | New York  | Forensics  |

| Sherlock Holmes                     | 4124.21 | London    | Detectives |

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

11 rows in set (0.00 sec)

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

Jawab:

mysql> CREATE ALGORITHM = TEMPTABLE VIEW tempview (job_desc, number_of_employye) AS SELECT job_

desc, count(job_desc) FROM employye GROUP BY job_desc;

Query OK, 0 rows affected (0.21 sec)

mysql> select*from tempview;

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

| job_desc   | number_of_employye |

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

| Detective  |                  1 |

| Detectives |                  1 |

| Forensics  |                  1 |

| Manager    |                  2 |

| Programmer |                  1 |

| Tester     |                  5 |

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

6 rows in set (0.17 sec)