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
Post a Comment