praktikum join

1.select first_name,last_name from employees
  where to_char(hire_date,'yyyy')>2007;

2.select last_name from employees
  where last_name like ('%n');

3.select count(employee_id)as "jabatan",d.department_name from employees e
  join departments d on (d.department_id=e.department_id)
  group by department_name;

4.select first_name||' '||last_name,salary from employees where 
  salary<5000 or salary>12000 order by salary asc;

4.select first_name||' '||last_name,salary from employees where 
  salary not between 5000 and 12000 order by salary asc;

5.select last_name,job_id,salary from employees
  where job_id='SA_REP' or job_id='ST_CLERK' and salary!=2500 
  and salary!=3500 and salary !=7000;

5.select last_name,job_id,salary from employees
  where (job_id='SA_REP' or job_id='ST_CLERK' )and salary not in(2500,3500,7000);


-------------------------------------------
latihan query join di skema HR
-------------------------------------------
1.select e.last_name,e.department_id,d.department_name from employees e
  join departments d on d.department_id=e.department_id;

2.select distinct(job_id),city from employees 
  join departments using (department_id) join locations using (location_id)
  where department_id=30;

3.select e.last_name,d.department_name,l.city from employees e
  join departments d on d.department_id=e.department_id
  join locations l on l.location_id=d.location_id
  where lower(l.city)='oxford';

4.select e.last_name,d.department_name from employees e
  join departments d on d.department_id=e.department_id
  where upper(e.last_name) like ('%A%') and upper(e.first_name) like ('%A%');

5.select first_name||' '||last_name as "ENAME",job_id as "JOB",department_id
  as "dept no",department_name as "Dname" from employees join departments using
  (department_id) join locations using(location_id);

6.select e.last_name as "nama",e.employee_id as "honor pegawai" ,m.last_name as "manager",
  m.employee_id as "Mgr" from employees e,employees m
  where e.manager_id=m.employee_id; 

7.select e.last_name as "nama",e.employee_id as "honor pegawai" ,m.last_name as "manager",
  m.employee_id as "Mgr" from employees e,employees m
  where e.manager_id=m.employee_id(+) ;
8. SELECT e.last_name as "nama pegawai",e.department_id as "nomor department",      m.last_name as "kolega"
   from employees e join employees m on e.manager_id=m.employee_id where       e.department_id=80;

9. SELECT last_name "nama", hire_date from employees where  hire_Date > (select     hire_date from employees where last_name = 'Urman');

10.select e.last_name as "Pegawai", e.hire_date, m.last_name as "Manager", m.hire_date
     from employees e join employees m
     on e.manager_id=m.employee_id
     where to_char(e.hire_date,'dd-mm-yyyy')<to_char(m.hire_date,'dd-mm-yyyy');