Temel Oracle Sql Cümleleri

Oracle üzerinde örnek database ile yazılan örnek DML cümleleri

İki ayrı kolonu birleştirip yeni bir kolon adıyla select işlemi

select first_name||' ' || last_name as "Adı Soyadı", CONCAT(first_name,last_name) adisoyadi FROM  employees;

Yeni bir kolon kaydı (oracle=>dual, mssql=>’string deger’)

select 'Mehmet FASIL' developer From Dual; --kolona isim atamak

As Kullanımı

select country_id sehir_id,country_name as Ulke_Id from COUNTRIES;
select country_id as "Şehir numarası" from countries;

İlk 5 Kayıt (mssql select top 5)

SELECT EMPLOYEE_ID,
  FIRST_NAME,
  LAST_NAME,
  EMAIL,
  PHONE_NUMBER,
  HIRE_DATE,
  JOB_ID,
  SALARY,
  COMMISSION_PCT,
  MANAGER_ID,
  DEPARTMENT_ID,
  ROWNUM,
  ROWID -- sqldeki unique identifier coklu kayitlardaki karisikliklari cozmek icin guzel cozum
FROM EMPLOYEES where employee_id < 199 and (first_name like 'N%' or first_name like 'M%') and ROWNUM <= 5 --top5 kayit
order by DEPARTMENT_ID desc, email desc;

Tek Değer Dönen Fonksiyonlar

select first_name,replace(first_name,'E','XXX') degisik from employees; --metin icerisinde harf degisikligi
select InitCap('Mehmet FASIL') From Dual;
select InitCap(UPPER(First_Name||' ' || last_name)) From employees;
select UPPER('aa aa a aaaa') from dual;
select LOWER('AAAAAAĞÜŞÖÜÖ') from dual;
select upper('i') from Dual;
select Upper(replace('Fasil','i','?')) From dual; --turkce i karakter sorununa care
select LPAD('XXX','10','Y') birinci , RPAD(1232,5,'yy') From Dual;
select trim('AAAA     ') From Dual;
select ltrim('    AAA     ') left, rtrim('    1111    ') from dual;
select RTRIM('Mehmet: )=?()=()=()=',')=?(') secilen From Dual; -- replace gibi çalisiyor aynisi ltrim icinde gecerli
select 'Mehmet''in Arabası' from Dual;
select 'Fasıl', Instr('Fasıl','as'), Instr('Fasılın','a',1,2)  from dual; --bi kelime icindeki aradigimiz degerin yeri
select Instr('Arabaaaaa','a',-1,5) from dual; -- tersten basliyoz
select length('qweqweqw') from dual;
select 100+100 From dual;
Select round(199.223554,4), round(199.49) From Dual;
select Trunc(199.27), Trunc(199.798) from dual;
Select Trunc(12.82,-1) From dual;
select round(12.15,1) from dual;
select Greatest(3,5,67,8,9,10,123),least('a','aa','z') From Dual;
select Least(-1,3.2,0) from dual;
select Greatest(MAX_SALARY,MIN_SALARY) FROM jobs;
select Round(sysdate,'Year') From dual; --yılın basi
select Round(sysdate+200,'Mon'),Next_Day(sysdate+7,'MONDAY') from dual;
select Trunc(sysdate+200, 'Year'),Trunc(sysdate) From Dual; --sysdate yanına gelen deger gun olarak aliniyor
select TRUNC(MONTHS_BETWEEN(sysdate+200,sysdate)) from dual;
select Add_Months(sysdate,2),sysdate,Last_Day(sysdate),Last_Day(sysdate)-sysdate from dual;

Diğer örneklerde görüşmek üzere.

Hope it works