MODUL SQL 4

PERTEMUAN 3  SQL

-          Membuat tabel employee_join

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 varchar(15));

-          Mengisi tabel employee_join

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

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

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

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

mysql> insert into employye values('3','James','Smith','1978-12-12','1990-03-15','6545.78','Vancouver','Tester');

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

mysql> insert into employye values('5','Robert','Black','1984-01-15','1998-08-08','2335.78','Vancouver','Tester');

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

mysql> insert into employye values('7','David','Larry','1990-12-31','1998-02-12','7898.78','NewYork','Manager');

mysql> insert into employye values('8','James','Cat','1996-9-17','2002-04-15','1233.78','Vancouver','Tester');

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

mysql> insert into employye alues('11','Lincoln','Rhyme','1999-05-25','2011-07-13','3213.98','New York','Forensics');

mysql> insert into employee values('12','Sherlock','Holmes','1923-08-12','1945-07-21','4124.21','London','Detectives');

1.      Buatlah view yang berisi name (gabungan first_name dan last_name), salary, city, dan job_description dari kedua tabel di atas.

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.

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)

 Untuk lebih lengkapnya dan jelasnya silahkan lihat DISINI  dan DISINI