Kamis, 17 Januari 2013

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)
Diposting oleh di Tidak ada komentar:

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)
Diposting oleh di Tidak ada komentar:
Langganan: Postingan (Atom)