MODUL 5 SQL

PERTEMUAN 5 SQL

-          Membuat tabel employee_join

mysql> create table employee(id int 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 (0.01 sec)

Mengisi tabel employee

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

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

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

Query OK, 1 row affected (0.00 sec)

 

-          Membuat tabel jobs

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

Query OK, 0 rows affected (0.11 sec)

Mengisi tabel jobs

mysql> insert into jobs values('1','Programmer');

Query OK, 1 row affected (0.03 sec)

mysql> insert into jobs values('2','Tester');

Query OK, 1 row affected (0.00 sec)

mysql> insert into jobs values('3','Manager');

Query OK, 1 row affected (0.00 sec)

mysql> insert into jobs values('4','Spy');

Query OK, 1 row affected (0.00 sec)

mysql> insert into jobs values('5','Detective');

Query OK, 1 row affected (0.00 sec)

mysql> insert into jobs values('6','Forensics');

Query OK, 1 row affected (0.00 sec)

mysql> insert into jobs values('7','Developer');

Query OK, 1 row affected (0.00 sec)

 

1.      Membuat function untuk menampilkan gabungan first_name dan last_name

mysql> delimiter !

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

    -> returns varchar(35)

    -> begin

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

    -> end !

Query OK, 0 rows affected (0.02 sec)

mysql> delimiter ;

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

1.      Membuat procedure untuk menampilkan job_description dari masukan sebuah id employee

mysql> delimiter !

mysql> create procedure empJob (in idEmp int (11))

    -> begin

    -> select first_name, last_name, title from employee where id like idEmp;

    -> end!

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> alter table employee change description title varchar(15);

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> call empJob(4);

 

Untuk lebih lengkapnya dan jelasnya silahkan lihat DISINI

Terima kasih......