TUGAS MODUL 5

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.01 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.01 sec)

No 1

mysql> delimiter #

mysql> create function revname(in_first_name varchar(15),in_last_name varchar(15))

    -> returns varchar(35)

    -> begin

    -> return concat(in_first_name,' ',in_last_name);

    -> end #

Query OK, 0 rows affected (0.30 sec)

mysql> delimiter ;

mysql> select revname(first_name, last_name) from employee;

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

| revname(first_name, last_name) |

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

| Jason Martin                   |

| Alison Mathews                 |

| James Smith                    |

| Celia Rice                     |

| Robert Black                   |

| Linda Green                    |

| David Larry                    |

| James Cat                      |

| Hercule Poirot                 |

| Lincoln Rhyme                  |

| Sherlock Holmes                |

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

11 rows in set (0.06 sec)

No 2

mysql> delimiter //

mysql> create procedure empjob(in id_employee int(2))

    -> begin

    -> select first_name, last_name, description from employee where id like id_employee;

    -> end //

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> call empjob(4);

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

| first_name | last_name | description |

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

| Celia      | Rice      | Manager     |

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

1 row in set (0.03 sec)

Query OK, 0 rows affected (0.09 sec)

mysql> call empjob(2);

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

| first_name | last_name | description |

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

| Alison     | Mathews   | Tester      |

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

1 row in set (0.01 sec)

Query OK, 0 rows affected (0.07 sec)