Modul 5
1. Buat
function untuk menapilkan gabungan firs_name dan last_name dengan bentuk
“last_name,firs_name”.
2.
Buatlah procedure untuk menampilkan job
dari masukan sebuah idemployee.
mysql>
create database pethouse;
Query
OK, 1 row affected (0.03 sec)
mysql>
use pethouse;
Database
changed
mysql>
create table employee_join(id int(11) primary key,first_name
varchar(15),last_name varchar(15),start_date date,end_date date,salary
float(8,2),city varchar(10),job_id int(11),foreign key(job_id) references job(job_id));
Query
OK, 0 rows affected (0.09 sec)
mysql>
insert into employee_join
values('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');
Query
OK, 2 rows affected (0.11 sec)
Records:
2 Duplicates: 0 Warnings: 0
mysql>
insert into employee_join
values('3','james','smith','1978-12-12','1990-03-15','6545.78','vancoufer','2'),('4','celia','rice','1982-10-24','1999-04-21','2345.78','vancouver','3');
Query
OK, 2 rows affected (0.37 sec)
Records:
2 Duplicates: 0 Warnings: 0
mysql>
insert into employee_join
values('5','roberth','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');
Query
OK, 2 rows affected (0.36 sec)
Records:
2 Duplicates: 0 Warnings: 0
mysql>
insert into employee_join
values('7','david','larry','1990-12-31','1998-02-12','7898.78','new
york','3'),('8','james','cat','1996-09-17','2002-04-15','1233.78','vancouver','2');
Query
OK, 2 rows affected (0.05 sec)
Records:
2 Duplicates: 0 Warnings: 0
mysql>
insert into employee_join
values('9','hercule','poirod','1973-05-23','2001-08-09','4313.98','brussels','5'),('10','lincoln','rhyme','1999-05-25','2011-07-13','3213.98','new
york','6');
Query
OK, 2 rows affected (0.36 sec)
Records:
2 Duplicates: 0 Warnings: 0
mysql>
insert into employee_join
values('11','sherlock','holmes','1923-08-12','1945-07-21','4124.21','london','5');
Query
OK, 1 row affected (0.05 sec)
mysql>
create table job(job_id int(11) primary key,title varchar(25));
Query
OK, 0 rows affected (0.09 sec)
mysql>
insert into job
values('1','programer'),('2','tester'),('3','manager'),('4','spy'),('5','detective'),('6','vorensics'),('7','developer');
Query
OK, 7 rows affected (0.05 sec)
Records:
7 Duplicates: 0 Warnings: 0
mysql>
delimiter #
mysql>
create function full_name(in_first_name varchar(20), in_last_name varchar(20))
-> returns varchar(45)
-> begin
-> return concat(in_first_name,'
',in_last_name);
-> end #
Mysql>delimiter
;
delimiter
##
create procedure empjob()
begin
select*from job ##
1064
(42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '' at
line 3
delimiter #
create procedure getEmployee_joinbyjob_id(in
job_description int(3))
begin
select *from employee_join where job_id like
job_description;
end #
