Tugas Kuliah "Query Untuk Oracle 9i"

1.character function

LOWER --> menampilkan hurup kecil sajo
UPPEr --> Menampilkan hurup besar sajo
INITcap-->gabungan kecil/besar

ex:
select lower(last_name) LOWER, upper(first_name)
UPPER,initcap(title)INITCAP
from s_emp


concat-->mengabung dua karakter.
ex:
select concat(first_name,last_name)NAME from s_emp


SUBSTR-->
ex
select substr(title,1,6)SUBSTR from s_emp

LENGTH-->
ex:
select length(first_name)LENGth from s_emp

instr-->
ex:
last_name

2.number function

Round-->
ex:
select round(45.926,2)ROUND from dual;
Trunc-->
ex:
select trunc(45.926,2)TRUNC from dual;
MOD-->
ex:
select salary,mod(salary,100)MOD from s_emp;


3.date funtion
months_between-->
ex:
select sysdate,start_Date,months_between(sysdate,start_date)from s_emp;
add_months-->
ex: select sysdate,add_months(sysdate,8)from s_emp;

next_day-->
ex
select sysdate,next_day(sysdate,'saturday')from s_emp;

last_day-->
ex
select sysdate,last_day(sysdate)from s_emp;


ROUND_MONTH-->
select sysdate, round(sysdate,'Month')as ROUND_MONTH from s_emp;
select sysdate, round(sysdate,'year')as ROUND_YEAR from s_emp;

trunc_MONTH-->

select sysdate, trunc(sysdate,'Month')as trunc_MONTH from s_emp;
select sysdate, trunc(sysdate,'Year')as trunc_YEAR from s_emp;


4.case else & decode

select first_name,last_name,title,
CASE title WHEN 'President'THEN 1.10*salary
WHEN 'Warehouse Manager'THEN 1.15*salary
WHEN 'Stock Clerk'THEN 1.20*salary
ELSE salary END"REVISED-SALARY" from s_emp;



select first_name,last_name, title,salary,
DECODE(title,'President',1.10*salary,
'Warehouse Manager',1.15*salary,
'Stock Clerk', 1.20*salary,salary)
REVISED_SALARY
FROM s_emp;

desc s_emp----> melihat record
select*from S_EMP------> melihat tabel

restricting and sorting data:

a.restricting data
select*from S_EMP where condition(s);

ex: select*from s_emp
where salary>=1000;--->berdasarkan nilai
salary(kolomnya)
>=(kondisi pembanding)/comparison condition


tipe data orc: -varchar---->menyimpan data sesuai dgn jumlah variabel yg kt masukkan
-varchar2--->menyimpan data sesuai dgn karakter
-date
-number

select*from s_emp
where first_name='Carmen';

comparison condition:
>=,<=,=,>,<,<>
yg lain:
1. between....and....
syntax:
where column between....and....
ex: staff yg mempunyai salary antara 1000s/d 2500

select*from s_emp
where salary >=1000 and salary <=25000; atau select*from s_emp where salary between 1000 and 2500;
selain itu:
select*from s_emp where salary not between 1000 and 2500;
2. IN syntax:
where column IN(value1,value2,valuen);
ex: tampilkan staf yg punya salary 1000,100,1300,1400
select*from s_emp where salary=1100 or salary=1400;
atau
select*from s_emp where salary in(1100,1300,1400,100);
selain itu
select*from s_emp where salary NOT in(1100,1300,1400,100);
3. LIKE
contoh: tampilkan staf yg mana huruf kedua dari first namenya=O berikutnya bebas.
select*from s_emp where first_name LIKE '_o%';

many character menampil hurup awal M
select*from s_emp where first_name LIKE 'M%';

SORRTING DATA:
(ORDER BY) syntax:
ORDER BY colum Asc/desc;
order by diletakkan pada akhir sebuah query.
ex: tampilkan staff yg punya salary >=1000 kemudian
masukkan berdasarkan first_name dari kecil ke besar(A-Z)

select*from s_emp
where salary >=1000
order by first_name asc;


jika hanya menampilkan yang tertentu saja dari tabel tsb maka:

select last_name,first_name,dept_id,salary from s_emp
where salary >=1000
order by first_name;


menampilkan salary yg dijumlahkan dan membuat aliasnya:

select last_name,salary,salary*12 as baim
from s_emp


select last_name,title,salary,
case title when 'warehouse manager' then 1,10*salary
when 'president' then 1,15*salary
when 'stock clerk' then 1,20*salary
else salary end "revised salary"
from s_emp;

select last_name,title,salary,
decode(title 'warehouse manager',1,10*salary,
'president',1,15*salary,
'stock_clerk',1,20*salary,
salary)
"revised_salary"
from s_emp;