Hasil Responsi PBDR [23-Mei-2010]

Alhamdulillah Responsi pertama PBDR sebelum UAS sudah terlaksana, terimakaasih pada temen-temen yang telah berpatisipasi aktif dalam kegiatan ini. Khususnya temen-temen di LAB BASDAT dari yang memberi soal dan jawaban awal dan yang membuat DDL sampai sebagian DML.

Data Definition Language

1.    Create DDL syntax to create the “EMPLOYEES” table.  Assume that all referenced tables have been available.

Create table EMPLOYEES(
employee_id number(6) constraint pkemp primary key,
first_name varchar2(20),
last_name varchar2(25) not null,
email varchar2(25),
constraint un1 unique(email),
phone_number varchar2(20),
constraint un2 unique(phone_number),
birth_date date not null,
hire_date date not null,
salary number(8),
constraint ck1 check(salary>0),
commision_pct number(2,2),
job_id varchar2(10),
constraint fk1 foreign key(job_id) references jobs,
department_id number(4),
constraint fk2 foreign key(department_id) references departments,
manager_id number(6),
constraint fk3 foreign key(manager_id) references employees(employee_id)
);

2.    Create DDL syntax to remove “PHONE_NUMBER” from “EMPLOYEES” table.

alter table employees
drop column phone_number;

3.    Create a table called “COPY_EMP” which copied the structure of “EMPLOYEES” table but has no data at all.

create table copy_emp as select * from employees where 1=0;

Data Modification Language

1.    Copy the data from “EMPLOYEES” table to “COPY_EMP” table, select only employees who work in the department named  “Information Technology”

insert into copy_emp
select * from employees
where department_id in (select department_id
from departments 
where department_name like 'Information Technology');

2.    As the inflation increase by 10% this year, the company will increase the salary by 15% for all employees who have worked for more than 5 years. Create the DML script.

update employees 
set salary=((salary*0.15)+salary)
where floor(months_between(sysdate,e.hire_date)/12)>=5;

3.    Create the query to delete all employees who aged more than 65 years old today.

delete employees
where floor(months_between(sysdate,birth_date)/12)>=65;

NB : Asumsi ada atribut birth_date pada tabel employees

Data Retrieval

1.    Create a query to retrieve employees’ last name along with their managers’ last name who work for more than 10 years.

select e.last_name, m.last_name
from employees e
join employees m
on e.manager_id=m.employee_id
where floor(months_between(sysdate,e.hire_date)/12)>=10;

2.    Create a query to get the manager name that has the most employees.

select first_name, last_name
from employees
where employee_id=(
select manager_id 
from employees
group by manager_id
having count(*)=(select max(count(*)) 
from employees
group by manager_id));

3.    Create a query to get how many vehicles that never been used at all.

select count(*) from vehicles
where license_plate not in
(select license_plate from vehicle_used);

4.    Create a query to get who has the lowest salary for each departments name

select e.last_name, e.salary from employees e join
(select department_id, min(salary) mingaji
from employees
group by department_id
order by department_id) m
on e.department_id=m.department_id
and e.salary=m.mingaji;

Demikian hasil responsi PBDR semoga bisa bermanfaat, dan kami minta maaf jika ada kesalahan atau kekurangan dalam mengisi responsi. Jika ada kesalahan syntax atau jawaban silahkan dikoreksi.