belajar subquery

subquery :
================

 select a.last_name,a.salary,a.department_id,b.salavg from employees a, 
 (select department_id,avg(salary)salavg
 from employees group by department_id) b 
 where a.department_id=b.department_id and a.salary>b.salavg; 

TOP-N Query :
===============
 
 select * from (select first_name,hire_date from employees where hire_date>='1-JAN-90'
 order by hire_date) where rownum<10;



 create table Pegawai(
  NIP number(4)primary key,
  NAMA_PEGAwAI varchar2(20),
  GAJI number(8),
  KODE_BAG number(2)
 );

 insert into pegawai values(1001,'MEVITA JOVI MAHARANI',2500000,50);
 insert into pegawai values(1002,'nazip zihni',10000000,41);
 insert into pegawai values(1003,'Yudi makmun',1500000,31);
 insert into pegawai values(1004,'BRAHMANTYO',1000000,10);
 insert into pegawai values(1005,'makmun',1500000,31);
 insert into pegawai values(1006,'bYO',1000000,31);

  SELECT NIP ,NAMA,GAJI FROM( 
  SELECT NIP ,NAMA_PEGAWAI AS NAMA,GAJI FROM PEGAWAI WHERE KODE_BAG=31)
  INLINE_VIEW;


 select a.last_name,a.salary,a.department_id,b.salavg from employees a, 
 (select department_id,avg(salary)salavg
 from employees group by department_id) b 
 where a.department_id=b.department_id and a.salary>b.salavg; 

 SELECT P.NAMA_PEGAWAI,P.GAJI,AVG_SAL.RATA2 "RATA-RATA GAJI PERBGAIAN",P.KODE_BAG FROM PEGAWAI P,
(SELECT KODE_BAG,AVG(GAJI) AS RATA2 FROM PEGAWAI GROUP BY KODE_BAG)AVG_SAL
 WHERE P.KODE_BAG=AVG_SAL.KODE_BAG; 

 SELECT ROWNUM,NIP,NAMA_PEGAWAI,GAJI FROM (SELECT NIP,NAMA_PEGAWAI,GAJI FROM PEGAWAI WHERE NIP<=1005
 ORDER BY GAJI DESC)SUMBER_DATA;

 SELECT ROWNUM AS RANGKING,NAMA_PEGAWAI,GAJI FROM (SELECT NIP,NAMA_PEGAWAI,GAJI FROM PEGAWAI WHERE NIP<=1006
 ORDER BY GAJI DESC)SUMBER_DATA
 WHERE ROWNUM<=5;


============================================================================================================

 CREATE TABLE ITEM(
  NO VARCHAR2(2),
  KODE_BARANG VARCHAR2(5),
  NAMA_BARANG VARCHAR2(15),
  CONSTRAINT FK_BARANG FOREIGN KEY(KODE_BARANG)REFERENCES BARANG(KODE_BARANG)
  );
 
  ALTER TABLE ITEM ADD CONSTRAINT PK_BARANG PRIMARY KEY(NO,KODE_BARANG);

  INSERT INTO ITEM VALUES ('01','B1','BERAS');
  INSERT INTO ITEM VALUES ('02','B1','BERAS');
  INSERT INTO ITEM VALUES ('03','B2','KOPI');
  INSERT INTO ITEM VALUES ('04','B3','GULA');
  INSERT INTO ITEM VALUES ('05','B2','KOPI');
  INSERT INTO ITEM VALUES ('06','B3','GULA');
  INSERT INTO ITEM VALUES ('07','B3','GULA');
  INSERT INTO ITEM VALUES ('08','B3','GULA');

 CREATE TABLE BARANG(
  KODE_BARANG VARCHAR2(5)PRIMARY KEY,
  NAMA_BARANG VARCHAR2(15)
 );

 INSERT INTO BARANG VALUES ('B1','BERAS');
 INSERT INTO BARANG VALUES ('B2','KOPI');
 INSERT INTO BARANG VALUES ('B3','GULA');

 SELECT ROWNUM AS RANGKING,B.KODE_BARANG,A.NAMA_BARANG,B.JLH_TERJUAL FROM 
 (SELECT KODE_BARANG,COUNT(KODE_BARANG)AS JLH_TERJUAL FROM ITEM
  GROUP BY KODE_BARANG
  ORDER BY JLH_TERJUAL DESC)B,BARANG A WHERE A.KODE_BARANG=B.KODE_BARANG
  AND ROWNUM <=3
  ORDER BY ROWNUM;

Comments

Popular Posts