--OPERATORS positif negatif PERTAMA
select * from hr.employees e
where E.salary = -1
/
select * from hr.employees e
where -E.SALARY < -1000
/
--OPERATORS positif negatif KEDUA
select * from hr.employees e
where trunc(sysdate) - hire_date > 4000
/
select trunc(sysdate) - hire_date from HR.EMPLOYEES e
/
select -e.salary from HR.EMPLOYEES e
/
select e.employee_id. E.SALARY + 500 salary from hr.employees e
where E.salary + 500 > 1000
/
select e.employee_id. E.SALARY /2 salary from hr.employees e
where E.salary /2 > 1000
/
--CONCATTT
select e.employee_id, e.first_name || ' ' || e.last_name full_name,
concat(concat(e.first_name, ' '), last_name) full_name2
from hr.employees e
/
--UNIONNN
--UNION
--untuk mendapatkan query 1 dan 2
select department_id from departments
union
select department_id from employees
--Union alll
select department_id from departments
union all
select distinct department_id from employees
--INTERSECTTT
select department_id from departments
intersect
select department_id from employees
/
--MINUSSS
select department_id from departments
minus
select department_id from employees
/
select department_id from employees
minus
select department_id from departments
/
--ROUNDD
select round(15.9345,1) from dual
/
select round(15.9645,2) x, round(15.9345,2) yfrom dual
/
--CEILL
select ceil(15.9645) x, ceil(15.9355) y from dual
/
--FLOORR
select floor(15.9645) x, ceil(15.9355) y from dual
/
--INITCAPPP
select initcap('testing oracle pl/sql') from dual
/
select initcap('testing oracle pl/sql') initcap, upper ('testing oracle pl/sql') upper ,
lower ('TESTING ORACLE PL/SQL') lower from dual
/
--LPADDD
select lpad('testing', 30,'*') xx from dual
/
select lpad('*', 30,'*') xx from dual
/
select lpad('testing', 30,'*') xx, lpad('_',30,'_') line from dual
/
--RPADDD
select rpad('testing', 30,'*') xx from dual
/
select rpad('testing', 30,'*') xx, rpad('_',30,'_') line from dual
/
--LTRIMMM RTIMM
select ltrim(' testing ')x, rtrim(' testing ') y from dual
/
select ltrim(' testing ')x, ltrim(rtrim(' testing ')) y from dual
/
--REPLACE
select replace(replace('testing oracle pl/sql','oracle','java '),'i','e') X from dual
/
select replace('testing oracle pl/sql','t','m') X from dual
/
--LENGTHHH REPLACE
select length(replace(replace('testing oracle pl/sql','oracle','java '),'i','e')) X from dual
/
--INSTR REPLACE
select instr('training oracle pl/sql','i') X from dual
/
select instr('training oracle pl/sql','i',5) X from dual
/
select instr('training oraclei pl/sql','i',5,2) X from dual
/
--ADD_MONTHSSS
select add_months(sysdate,1) from dual
/
select add_months(sysdate,1) BULAN_DEPAN,
add_months(sysdate, -1) BULAN_KEMARIN,
trunc(sysdate, 'mm')AWAL_BULAN,
trunc(add_months(sysdate,1),'mm') BULAN_DEPAN,
last_day(sysdate)eom,
next_day(to_date('01/05/2014','DD/MM/YYYY'),'TUESDAY') next_day from dual
/
--next_day(to_date('01/05/2014','DD/MM/YYYY'),'TUESDAY') Maksudnya setelah tgl 1 tgl berapa hari TUESDAY
/
--TO_CHARR
--Konversi dari tgl ke string
select to_char(sysdate, 'DD/MM/YYYY') from dual
/
select to_char(sysdate+100, 'DD/MM/YYYY')A,
to_char(sysdate+100, 'DD MON YYYY')B,
to_char(sysdate+100, 'DD MONTH YYYY')C,
to_char(sysdate, 'hh24:MI:SS')D,
to_char(sysdate+(1/24), 'hh24:MI:SS')E, --Menambah Jam
to_char(123456789, '999,999,999,999.00')F,
to_char(123456789, 'fm999,999,999,999.00')G,
to_date(11052014, 'ddmmyyyy')H,
to_date('01/05/2014', 'dd/mm/yyyy')I,
to_date('01052014', 'ddmmyyyy')J,
to_date(20140501, 'yyyymmdd')K,
to_number(to_char(sysdate, 'yyyymmdd'))L
from dual
/
--KonversiTANGGAL
select to_date(11052014, 'ddmmyyyy')H,
to_date('01/05/2014', 'dd/mm/yyyy')I,
to_date('01052014', 'ddmmyyyy')J,
to_date(20140501, 'yyyymmdd')K,
to_number(to_char(sysdate, 'yyyymmdd'))L
from dual
/
--KONVERSI NUMBER
--AGGREGATE FUNCTION
--MAX, MIN, SUM, AVG, COUNT
select max(salary), min(salary), sum(salary), avg(salary), count(employee_id)
from employees
/
--GROUPING DEPARTMENT
select department_id, max(salary), min(salary), sum(salary), avg(salary), count(employee_id)
from employees group by department_id
/
select department_id, first_name, max(salary), min(salary), sum(salary), avg(salary), count(employee_id)
from employees group by department_id, first_name
/
select department_id, first_name, count(0)
from employees group by department_id, first_name
having count(0) > 1
/
select department_id, max(salary), min(salary), sum(salary), avg(salary), count(employee_id)
from employees group by department_id
having min(salary) > 1500
/
select department_id, max(salary), min(salary), sum(salary), avg(salary), count(employee_id)
from employees
group by department_id
having min(salary) > 1500
and count(employee_id) >=2
/
select department_id, max(salary), min(salary), sum(salary), avg(salary), count(employee_id)
from employees
having min(salary) > 1500 --seperti where groupp
and count(employee_id) >=2
group by department_id
/
select department_id, max(salary), min(salary), sum(salary), avg(salary), count(employee_id)
from employees
where department_id is not null
having min(salary) > 1500 --seperti where groupp
and count(employee_id) >=2
group by department_id
/
--DISTINCT (Sifatnya Optional Dipakai untuk memfilter tabel)
--
-- 1. Buat query untuk menampilkan nama dan gaji dari pegawai yang memiliki gaji lebih dari $2850.
select first_name, salary from employees
where (salary) > 2850
/
-/*2. Buat Query untuk menampilkan nama pegawai dan nomor departmen dari pegawai tersebut untuk
pegawai yang mempunyai nomor pegawai = 7566*/
select first_name, department_id from employees
where department_id = 7566
/
-/*3. tampilkan nama dan gaji untuk semua pegawai yang memiliki gaji yang tidak berada dalam range antara
$1500 sampai $2850*/
select first_name, salary from employees
where salary not between 1500 and 2850
/
select first_name, salary from employees
group by first_name, salary
having salary not between 1500 and 2850
/
-/*4. Tampilkan nama pegawai, kerjaan dan tanggal mulai pegawai bekerja antara 20 Feb 1981 sampai 1 Mei 1981.
Urutkan dengan urutan menaik pada kolom tgl mulai kerja*/
select first_name, job_id, hire_date from employees
where hire_date between to_date '20 feb 1981' and '01 mei 1981'
/
-/*5. Tampilkan nama pegawai dan nomor department dari semua pegawai dalam department 10 dan department
30 dalam urutan menaik berdasarkan nam*/
select first_name, department_id from employees
where department_id like 10 or department_id like 30
order by first_name asc
/
select first_name, department_id from employees
where department_id in (10,30)
order by first_name
/
-/*6. Tampilkan nama dan gaji pegawai yang memiliki gaji lebih dari $1500 dan bekerja pada nomor department 10
atau 30. beri judul kolom 'Pegawai' dan 'Gaji Sebulan'*/
select first_name pegawai, salary gaji from employees
where salary > 1500 and department_id in (10,30)
order by first_name
/
-/*7. tampilkan nama dan tanggal mulai kerja dari seorang pegawai yang mulai bekerja di tahun 1982 */
select first_name pegawai, hire_date tgl_mulai from employees
where to_char(hire_date, 'YYYY') = '1999'
/
select first_name pegawai, hire_date tgl_mulai from employees
where hire_date like '%99'
/
-/*8. Tampilkan nama dan pekerjaan pegawai yang tidak mempunyai manager (nilai kolom mgr=NULL)
ENAME JOB
-------- --------
KING PRESIDENT
*/
select first_name nama, job_id job from employees
where nvl(manager_id,0)=0 --nvl = null value
/
select first_name nama, job_id job from employees
where nvl(department_id,0)=0 --nvl = null value
/
-/*9. Tampilkan nama, gaji dan komisi dari semua pegawai yg memiliki komisi(kolom komisi tidak sama dengan NULL).
Urutkan berdasarkan gaji dan komisi, dengan urutan menurun */
select first_name nama, salary gaji from employees
where not nvl(salary,0)=0 --nvl = null value
/
-/*10. tampilkan nama dari semua pegawai yg memiliki nama yang mengandung karakter 'A' pada huruf ketiga */
select first_name nama from employees where upper(first_name) like '__A'
/
-/*11. tampilkan nama pegawai yang memiliki dua huruf 'L' pada namanya, dan bekerja di department 30, atau
managernya = '7782' */
select first_name nama, department_id Dept from employees
where upper(first_name) like '%LL%' and (department_id = 30 or manager_id = 7782)
/
/*12. Tampilkan nama, pekerjaan dan gaji dari semua pegawai yang memiliki pekerjaan 'CLERK' atau 'ANALYST'
dan gajinya tidak sama dengan $1000, $3000 atau $5000 */
select ename, job, sal from scott.emp
where (job like 'CLERK' or job like 'ANALYST') and (sal >= 1000 or sal >= 3000 or sal >= 5000)
/
-/*13. Tamilkan nama, gaji dan komisi dari semua pegawai yang memiliki jumlah komisi lebih dari jumlah
gaji setelah dinaikkan 10% */
select ename, sal, comm from scott.emp where comm > (sal + (sal*0.1))
/
--Budi santoso memiliki gaji 1500 tapi menginginkan gaji sebesar 6000
--Susi susanti memiliki gaji 2000 tapi menginginkan gaji sebesar 8000
select first_name || ' ' || last_name || ' memiliki gaji'|| ' ' || salary ||
' tapi menginginkan gaji sebesar ' || salary * 4 from employees
/
/
-/* Function DECODE
search1 nilai pembanding
DECODE (col/expresion,search1,result1
[,search2,result2....]
[default, ]
*/
decode (flag_ketemu,true,'ketemu','tidak ketemu')
decode (flag_ketemu,true,'ketemu',decode('asdfg','x','ketemu','tidak ketemu'))
if flag_ketemu then 'ketemu' else 'tidak ketemu'
select job, sal, decode(job, 'ANALYST', sal*1.1,
'CLERK', sal*1.15,
'MANAGER',sal*2,
sal)
-- NESTED
select ename , null(to_char(mgr), 'TIDAK PUNYA MANAGER')
-- MENAMPILKAN BANYAK TABEL
-- JOIN
-- EQUIJOY
select department_id from departments
select distinct department_id from employees
where department_id is not null
/
select d.department_id dept_tb_dept, d.department_name dept_name,
e.department_id dept_tb_emp, e.first_name || ' ' || e.last_name full_name
from departments d, employees e
where d.department_id (+)= e.department_id
/
--JOIN
select d.department_id dept_tb_dept, d.department_name dept_name,
e.department_id dept_tb_emp, e.first_name || ' ' || e.last_name full_name
from employees e right outer join departments d
on d.department_id = e.department_id
/
select d.department_id dept_tb_dept, d.department_name dept_name,
e.department_id dept_tb_emp, e.first_name || ' ' || e.last_name full_name
from employees e left outer join departments d
on d.department_id = e.department_id
/
--6 May 2014
-/*Cara buat schema/user SCOTT ==> buka schema SYSTEM, Schema Browse,pilih SCOTT dan USER,
unlock scot, double click scott masukan user n password baru, hilangkan ceklis pasword,
selesai dan masuk ke user SCOTT */
-/* 1. Tampilkan nama, pekerjaan, dan nomer department dari table
pegawai, untuk pegawai yang pekerjaannya = “CLERK” */
select ename, job, deptno
from emp
where job = 'CLERK';
##CREATED SANUSI
-- ======================= --
-- ===== SOAL BAB 5 ===== --
-- ======================= --
-/*1. Tampilkan nilai tertinggi, terendah, jumlah dan rata-rata gaji dari seluruh
pegawai. Beri judul kolom MAKSIMUM MINIMUM TOTAL RATA2.*/
select max(sal)maksimum,min(sal)minimum,sum(sal)jumlah,avg(sal)rata2
from emp
/
-/*2. Tampilkan nilai tertinggi, terendah, jumlah dan rata-rata gaji pada tiap-tiap
jenis pekerjaan yang ada.. Beri judul kolom MAKSIMUM MINIMUM
TOTAL RATA2. */
select job pekerjaan, max(sal), min(sal), sum(sal), avg(sal)
from emp group by job order by job --order by job artinya mngurutkan sesuai abjad
/
-/*3. Tampilkan nama pekerjaan dan jumlah pegawai yang bekerja pada tiap-tiap
pekerjaan tersebut ! */
select job pekerjaan, count(ename)
from emp group by job order by job
/
-/*4. Buat query yang menampilkan perbedaan antara nilai terendah dan nilai
tertinggi dari gaji pegawai, beri judul kolom PERBEDAAN. */
select max(sal) - min (sal) perbedaan from emp
/
-/*5. Buat query untuk menampilkan nama department, lokasinya, jumlah pegawai
yang bekerja, dan rata-rata gaji pada department tersebut. Bulatkan rata-rata
gaji ke 2 angka desimal. */
select d.dname, d.loc, count(E.EMPNO) jumlah_pegawai, round (avg(e.sal),2) rata2_gaji
from emp e, dept d
where d.deptno = E.DEPTNO
group by deptno, dname, loc
/
update emp set sal = 800
/
##CREATED SANUSI
-- ======================= --
-- ============ BAB 6 ============ --
-- ======================= --
-/*1. Tampilkan data nama pegawai dan tanggal mulai bekerja untuk semua
pegawai yang bekerja di department yang sama dengan ‘BLAKE’ tidak
termasuk ‘BLAKE’. */
select ename, hiredate from emp
where deptno = ( select deptno
from emp
where ename like 'BLAKE')
and ename not in ('BLAKE')
/
-/*2. Tampilkan nomer dan nama pegawai untuk semua pegawai yang gajinya lebih
dari rata-rata. Urutkan menurun berdasarkan besar gaji. */
select empno, ename from emp
where sal > (select avg(sal)
from emp)
/
select empno, ename from emp
where sal > (select avg(sal)
from emp)
order by sal desc
/
-/*3. Tampilkan nomer dan nama pegawai untuk semua pegawai yang bekerja di
department yang sama dengan pegawai yang memiliki nama yang
mengandung huruf ‘T’. */
select empno, ename from emp
where deptno in ( select deptno from emp where ename like '%T%')
/
select ename nama from emp where upper(ename) like '%T%'
/
select empno, ename, deptno from emp
where deptno in ( select deptno
from emp
where ename like '%T%')
/
-/*4. Tampilkan nama pegawai, nomer department dan pekerjaan untuk semua
pegawai yang department tempatnya bekerja berlokasi di kota ‘DALLAS’. */
select ename , deptno, job from emp
where deptno in (select deptno from dept where loc like 'DALLAS')
select ename , deptno, job from emp
where deptno in (select deptno from dept where loc = 'DALLAS')
/
-- ======================= --
-- ===== EXISTS CARANYA ===== --
-- ======================= --
select ename , deptno, job
from emp e
where exists (select 1
from dept d
where D.DEPTNO =e.deptno
and d.loc = 'DALLAS')
/
-- ======================= --
-- ===== CARI LOKASI DALLAS DAN GAJI LEBIH DARI 1500 ===== --
-- ======================= --
select ename , deptno, job
from emp e
where e.sal > 1500
and
where exists (select 1
from dept d
where D.DEPTNO =e.deptno
and d.loc = 'DALLAS')
/
-/*5. Tampilkan nama dan gaji dari semua pegawai yang merupakan bawahan dari
‘KING’ */
select ename, sal from emp
where mgr = (select empno
from emp
where ename = 'KING')
and ename not in ('KING')
order by ename
/
-/*6. Tampilkan nomer department, nama pegawai dan pekerjaan dari semua
pegawai yang ada di department SALES. */
select deptno, ename, job from emp
where deptno in (select deptno
from dept
where dname = 'SALES')
/
-/*7. Tampilkan nomer dan nama pegawai untuk semua pegawai yang bekerja di
department yang sama dengan pegawai yang memiliki nama yang
mengandung huruf ‘T’, dan gaji yang dimiliki lebih besar daripada rata-rata
gaji . */
select empno, ename from emp
where deptno in ( select deptno
from emp
where ename like '%A%')
and sal > (select avg(sal) from emp)
order by ename
/
##CREATED SANUSI
-- ======================= --
--VIEW = SECARA FISIK TIDAK ADA NAMUN BERSIFAT LOGICAL
-- ======================= --
-- ======================= --
-- ============ BAB 7 ============ --
-- ======================= --
-/*1. Buat query untuk menampilkan nama pegawai, nomor department, dan gaji dari
pegawai yang memiliki nomor department dan gaji yg sesuai dgn yg dimiliki oleh pegawai
yg mempunyai komisi. */
select ename, deptno, sal from emp
where (deptno, sal) in ( select deptno, sal
from emp
where nvl(comm,0) > 0)
/
/
-/*2.tampilkan nama pegawai, nama department dan gaji dari pegawai yg memiliki gaji dan
komisi yg sama dgn pegawai yg bekerja di kota 'DALLAS' */
select '1' cat,ename, dname, sal
from emp e, dept d
where D.DEPTNO = E.DEPTNO
and (sal, nvl(comm,0)) in ( select sal, nvl(comm,0) comm
from emp f, dept x
where f.deptno = x.deptno
and x.loc='DALLAS')
--order by sal
union all
select '2' cat,ename, dname, sal
from emp e, dept d
where D.DEPTNO = E.DEPTNO
and (sal, nvl(comm,0)) in ( select sal, nvl(comm,0) comm
from emp f
where f.deptno = d.deptno
and d.loc='DALLAS')
order by sal
/
/
/
select ename, dname, sal
from emp e, (select deptno, dname from dept d
where loc = 'DALLAS') d
where D.DEPTNO = E.DEPTNO
and (sal, nvl(comm,0)) in ( select sal, nvl(comm,0) comm
from emp f
where f.deptno = d.deptno)
order by sal
/
/
-/*3. Buat query untuk menampilkan nama pegawai tanggal mulai bekerja dan gaji
untuk semua pegawai yg memiliki gaji dan komisi yg sama dgn yg dimiliki oleh 'SCOTT' */
select ename, hiredate, sal
from emp e
where (sal, nvl(comm,0)) in (select sal, nvl(comm,0) comm
from emp
where ename='SCOTT')
and ename <> 'SCOTT'
order by sal
/
-/*4.Buat query untuk menampilkan */
select ename, job, sal
from emp
where sal > (select max(sal) from emp where job = 'CLERK')
order by sal desc
/
/
-/*5. */
select ename, deptno,
(select dname
from dept d
where d.deptno = e.deptno) dname
from emp e
--==== SUBQUERY ADA 3 YAITU :
-- select
-- from
--where cloos
--cuma function yg bisa di select sedangkan prosedur tidak bisa
-- MENGAHAPUS / DELETE
truncate from namatable = menghapus data secara permanen tanpa perlu commit
select * from all_tables
select * from all_tables
where owner = 'SCOTT'
select * from all_tab_cols
where owner = 'SCOTT'
-/*SAVEPOINT = MENYIMPAN DATA DI TABLESPACE UNDO DAN PADA SAAT PROSES PENYIMPANAN ERROR
MAKA AKAN TERJADI COMMIT OTOMATIS */
--CREATE TABLE
create table dept_bck
as
select * from dept
where 1 = 0
/
insert into dept_bck
select * from dept
/
select * from dept_bck
/
insert into dept_bck (deptno, dname)
select deptno, dname from dept
/
-- CREATE TABLE MY_EMPLOYEE
create table my_employee
(
ID NUMBER(6) not null,
LAST_NAME VARCHAR2(25 ) ,
FIRST_NAME VARCHAR2(25 ),
USERID VARCHAR2(8),
SALARY NUMBER(9,2),
CONSTRAINT PK_my_employee PRIMARY KEY (ID)
)tablespace DATA;
INSERT ALL
into my_employee values(1, 'Patel', 'Ralph','rpatel',795)
into my_employee values(2, 'Dancs', 'Betty','bdancs',860)
into my_employee values(3, 'Birl', 'Ben','bbirl',1100)
into my_employee values(4, 'Newan', 'Chad', 'cnewan', 750)
into my_employee values(5, 'Ropeburn', 'Audry', 'aropebur',1550)
select * from dual;
-- untuk menghapus table
DROP TABLE my_employee
-/*4. Ubah nama akhir pegawai bernomor 3 menjadi 'Direxler' */
update my_employee
set LAST_NAME = 'Direxler'
where ID = 3
-- ======================= --
-- SAVE POINT, UPDATE, ROOLBACK
-- ======================= --
select * from emp
where empno in (7499, 7788)
-- UPDATE
update emp
set sal = 2000
where empno = 7499
--SAVE POINT
savepoint emp_upd
update emp
set sal = 1000
where empno = 7788
--ROLLBACK
rollback to savepoint emp_upd
##CREATED SANUSI
-- ======================= --
-- ============ SOAL LATIHAN 1 ============ --
-- ======================= --
-/*1.1. Tampilkan nilai rata-rata, total jumlah, nilai maksimum dan nilai minimum
dari gaji seluruh pegawai.*/
select avg(sal) rata2, sum(sal) total, max(sal) maksimum, min(sal) minimum from emp
/
-/*2.Tampilkan Jumlah Karyawan yang berada di tiap department */
select deptno Dep, count(ename) from emp
group by deptno order by deptno
/
-/*3Tampilka n nomer department, pekerjaan dan total gaji dari seluruh pegawai */ --BELUM
select deptno, job, sum(sal) from emp
-/*4. Tampilkan nomer department, pekerjaan dan total gaji dari seluruh pegawai
yang dikelompokkan berdasarkan nomer department dan pekerjaan */
select deptno, job, sum(sal) from emp
group by deptno and job
/
-/*5. */
select deptno, max(sal)
from emp
where sal > 2900
group by deptno order by deptno
-/*6 */ --BELUM
select job pekerjaan, sal gaji from emp e
where sal > 5000
and
where e.deptno in (select e.deptno
from dept
where dname = 'SALES')
-/*7. */ --BELUM
select avg(sal) from emp
-/*8. */ --
select max(sal) maksimum, min(sal) minimum, sum(sal) total, avg(sal) rata2 from emp
-/*9 */
select job pekerjaan, count(job) jumlah from emp group by job order by job
-/*10. */
select ename nama, job pekerjaan from emp
where job = (select job from emp where empno = 7369)
-/*11. */
select ename nama, job pekerjaan from emp
where job = (select job from emp where empno = 7369)
and sal > (select sal from emp where empno = 7876)
-/*12 */
select ename, job from emp
where sal = (select min(sal) from emp)
-/*13. */
select deptno, min(sal) from emp
group by deptno having min (sal) > (select min(sal) from emp where deptno = 20)
-/*14. */
-/*15. */
-/*16. */
insert into dept (deptno, dname, loc) values(50, 'IT','JAKARTA')
-/*17. */
-- ======================= --
-- ============ CMD ============ --
-- ======================= --
C:\Users\user>sqlplus scott/scott@orcl -- scott = username n scott=paswrd
--untuk mengatur table agar tidak berantakan
SQL> set line 1000
SQL> set time on
SQL> set serverout on
14:05:20 SQL> commit;
Commit complete.
14:05:29 SQL> @D:\select_insert.sql;
silahkan masukkan dept no :50
silahkan masukkan nama department :ADMINISTRASI
silahkan masukkan location :BANGKOK
old 2: values(&deptno, '&dname', '&loc')
new 2: values(50, 'ADMINISTRASI', 'BANGKOK')
1 row created.
7 May 2014
select first_name from employees
where first_name like '%c%'
select * from hr.employees
where mod(employee_id, 100) = 0
-- CONSTRAINT ==> untuk menjaga konsistensi data
-- alternatif bila tidak menggunakan SEQUENCE untuk AUTO NUMBER :
-- 1. buat counter di table master
--INDEXS
--1 NAMA INDEX BISA MEMILIKI LEBIH DARI 1 FIELD YG DIKELOMPOKAN
--VIEW
create or replace view v_emp
as
select * from emp
/
insert into v_emp (empno)
values (5000)
/
select * from v_emp
where empno = 5000
/
create or replace view v_emp
as
select empno employee_number, ename employee_name, job, mgr, hiredate, sal, comm, deptno
from scott.emp
/
insert into v_emp3 (employee_number)
values (7000)
create or replace view v_emp2
as
select * from emp
/
insert into v_emp2 (empno)
values (6000)
/
create view dept_sum_vu
(name, minsal, maxsal, avgsal)
as
select dname, min(e.sal),max(e.sal),avg(e.sal)
from emp e, dept d
where e.deptno=d.deptno
group by d.dname
/
create or replace view v_emp2
as
select empno employee_number, ename employee_name, job, mgr, hiredate, sal, comm, deptno
from scott.emp
/
select * from v_emp2
/
--FUNCTION
create or replace function f_get_emp_name(p_emp number) return varchar2
is
v_name varchar2(100);
begin
select ename into v_name from emp where empno = p_emp;
return v_name;
end;
/
create or replace view v_emp4
as
select empno, f_get_emp_name(empno) emp_name
from emp
/
-- 9 MAY 2014
create or replace procedure proc_Print_Emp (p_dept HR.EMPLOYEES.department_ID%type,
p_Total_Emp in out number,
p_status out varchar2)
is
nTotEmp number := 0;
begin
dbms_output.put_line ('Nilai Total Awal : '||p_Total_Emp||'_'||p_status);
for rec_emp in (select employee_id, first_name, last_name, salary
from employees
where department_id = decode(p_Dept,0,department_id,p_Dept))
loop
nTotEmp := nTotEmp +1;
dbms_output.put_line (rec_emp.employee_id||chr(9)||rec_emp.first_name||
chr(9)||rec_emp.last_name||chr(9)||rec_emp.salary);
end loop;
p_Total_Emp := nTotEmp;
p_status := 'X';
end;
##CREATED SANUSI
--=======================
--=======================
declare
nDept number := 20;
nTotEmp number := 20;
vStatus varchar2(1);
begin
proc_Print_Emp (p_dept => nDept,
p_Total_Emp => nTotEmp,
p_status => vStatus);
dbms_output.put_line ('Nilai Total Akhir : ' || nTotEmp);
dbms_output.put_line ('Status : ' ||vStatus);
END;
--=======================
--=======================
CREATE OR REPLACE FUNCTION F_GET_EMP_NAME (P_EMP NUMBER,
P_MANAGER OUT HR.EMPLOYEES.MANAGER_ID%TYPE)
RETURN VARCHAR2
IS
ENAMES VARCHAR2(100);
MGR_ID HR.EMPLOYEES.MANAGER_ID%TYPE;
BEGIN
SELECT FIRST_NAME, MANAGER_ID
INTO ENAMES, MGR_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP;
P_MANAGER := MGR_ID;
RETURN ENAMES;
END;
/
##CREATED SANUSI
--=======================
--=======================
/
declare
nMgr number;
vEname varchar2(100);
begin
vEname := F_GET_EMP_NAME (P_EMP => 100,
P_MANAGER => nMgr);
dbms_output.put_line('Nama Karyawan : '|| vEname || ' dengan kode manager '||
(case
when nMgr is null then 'No Manager'
else to_char(nMgr)
end
)
);
end;
/
/
select * from employees
--=======================
--=======================
CREATE OR REPLACE PACKAGE PKG_EMP
IS
PROCEDURE proc_Print_Emp (p_dept HR.EMPLOYEES.department_ID%type,
p_Total_Emp in out number,
p_status out varchar2);
FUNCTION F_GET_EMP_NAME (P_EMP NUMBER,
P_MANAGER OUT HR.EMPLOYEES.MANAGER_ID%TYPE)
RETURN VARCHAR2;
END;
/
##CREATED SANUSI
--=======================
--=======================
/
CREATE OR REPLACE PACKAGE BODY PKG_EMP
IS
PROCEDURE proc_Print_Emp (p_dept HR.EMPLOYEES.department_ID%type,
p_Total_Emp in out number,
p_status out varchar2)
is
nTotEmp number := 0;
begin
dbms_output.put_line ('Nilai Total Awal : '||p_Total_Emp||'_'||p_status);
for rec_emp in (select employee_id, first_name, last_name, salary
from employees
where department_id = decode(p_Dept,0,department_id,p_Dept))
loop
nTotEmp := nTotEmp +1;
dbms_output.put_line (rec_emp.employee_id||chr(9)||rec_emp.first_name||
chr(9)||rec_emp.last_name||chr(9)||rec_emp.salary);
end loop;
p_Total_Emp := nTotEmp;
p_status := 'X';
end;
FUNCTION F_GET_EMP_NAME (P_EMP NUMBER,
P_MANAGER OUT HR.EMPLOYEES.MANAGER_ID%TYPE)
RETURN VARCHAR2
IS
ENAMES VARCHAR2(100);
MGR_ID HR.EMPLOYEES.MANAGER_ID%TYPE;
BEGIN
SELECT FIRST_NAME, MANAGER_ID
INTO ENAMES, MGR_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = P_EMP;
P_MANAGER := MGR_ID;
RETURN ENAMES;
END;
END;
/
/
--=======================
--=======================
#TELKOMSEL
select substr(TGL_AKTIVASI1,1,10), count(*) from Z_BJ_CUG_TRANS
group by substr(TGL_AKTIVASI1,1,10)
--=======================
# Untuk mencari data berdasarkan interval tgl sesuai tgl di dalam table. contoh dari tgl '2014-07-01' s/d '2014-07-03'
SELECT date, main_community, region,branchname, sub_branchname, SUM(total_usage)
FROM revenue_weekly_1_juli_2014
WHERE date BETWEEN '2014-07-01' and '2014-07-03'
GROUP BY date, main_community, region,branchname, sub_branchname
Tidak ada komentar:
Posting Komentar