Oracle Advanced SQL (in Turkish)

In this video you will find my Oracle Advanced SQL presentation in an event supported by Oracle Türkiye.

Özgür Macit: Oracle Analytic functions, Hierarchical Queries, Model Clause, Regular Expressions from Hasan Tonguç YILMAZ on Vimeo.

Here is the demo I present in the video:

-- tablonun yaratilmasi

DROP TABLE yas;

CREATE TABLE yas (
ad_soyad VARCHAR2(30),
yas      NUMBER
);

-- tabloya verilerin eklenmesi

INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Kenan Muftu', 24 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Aysegul Macit', 8 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Erol Macit', 20 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Erturk Diriksoy', 22 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Ozgur Macit', 23 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Zeynep Derelioglu', 22 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Fatma Macit', 42 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Emir Omer Macit', 53 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Huseyin Tansu Ozer', 23 );
INSERT INTO yas ( ad_soyad, yas ) VALUES ( 'Mumin Irican', 24 );

COMMIT;

-- tablonun kontrol edilmesi

SELECT y.* FROM yas y;

-- NTILE ornegi

SELECT y.*,
NTILE(4) OVER ( ORDER BY y.yas ) grup
FROM yas y;

-- WIDTH_BUCKET ornegi

SELECT y.ad_soyad,
WIDTH_BUCKET( y.yas, 10, 30, 3 ) yukari_grup,
WIDTH_BUCKET( y.yas, 30, 10, 3 ) asagi_grup
FROM yas y;

-- tablonun dusurulmesi

DROP TABLE yas;

-- tablonun yaratilmasi

DROP TABLE dogum_gunleri;

CREATE TABLE dogum_gunleri (
ad_soyad VARCHAR2(30),
tip      VARCHAR2(10),
ay       NUMBER,
gun      NUMBER
);

-- tabloya ornek verilerin eklenmesi

INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Kenan Muftu', 'Arkadas', 9, 9 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Aysegul Macit', 'Aile', 3, 23 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Erol Macit', 'Aile', 3, 23 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Erturk Diriksoy' , 'Arkadas', 9, 26 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Ozgur Macit', 'Aile', 5, 10 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Zeynep Derelioglu', 'Arkadas', 6, 15 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Fatma Macit', 'Aile', 7, 20 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Emir Omer Macit', 'Aile', 3, 27 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Huseyin Tansu Ozer', 'Arkadas', 8, 19 );
INSERT INTO dogum_gunleri ( ad_soyad, tip, ay, gun ) VALUES ( 'Mumin Irican', 'Arkadas', 8, 6 );

COMMIT;

-- tablonun kontrol edilmesi

SELECT dg.* FROM dogum_gunleri dg;

-- LAG ornegi

SELECT dg.ad_soyad,
LAG( dg.ad_soyad, 1, '' ) OVER ( ORDER BY dg.ay, dg.gun ) onceki
FROM dogum_gunleri dg
WHERE tip = 'Arkadas';

-- LEAD ornegi

SELECT dg.ad_soyad,
LEAD( dg.ad_soyad, 1, '' ) OVER ( ORDER BY dg.ay, dg.gun ) sonraki
FROM dogum_gunleri dg
WHERE tip = 'Arkadas';

-- Bu yil icinde arkadaslarimin dogum gunlerini hangi sirayla kutlayacagiz?

SELECT dg.ad_soyad,
dg.ay,
dg.gun,
RANK() OVER ( ORDER BY dg.ay, dg.gun) sira
FROM dogum_gunleri dg
WHERE dg.tip = 'Arkadas'
ORDER BY sira;

-- Bu yil icinde arkadaslarimin ve ailemin dogum gunlerini ayri ayri hangi sirayla kutlayacagiz?

SELECT dg.*,
RANK() OVER ( PARTITION BY dg.tip
ORDER BY dg.ay, dg.gun) sira
FROM dogum_gunleri dg
ORDER BY dg.tip,
sira;

-- Bu yil icinde herkesin dogum gunlerini hangi sirayla kutlayacagiz?

SELECT dg.*,
RANK() OVER ( ORDER BY dg.ay, dg.gun) sira
FROM dogum_gunleri dg
ORDER BY sira;

-- DENSE_RANK ornegi

SELECT dg.ad_soyad,
dg.ay,
dg.gun,
DENSE_RANK() OVER ( ORDER BY dg.ay, dg.gun) sira
FROM dogum_gunleri dg
WHERE dg.tip = 'Aile'
ORDER BY sira;

-- tablonun dusurulmesi

DROP TABLE dogum_gunleri;

-- tablonun yaratilmasi

DROP TABLE telefon_gorusmeleri;

CREATE TABLE telefon_gorusmeleri (
ad_soyad       VARCHAR2(20),
yil            NUMBER,
gorusme_sayisi NUMBER
);

-- tabloya verilerin eklenmesi

INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Kenan Muftu', 2004, 12 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Kenan Muftu', 2005, 16 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Kenan Muftu', 2006, 28 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Kenan Muftu', 2007, 65 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Fatma Macit', 2004, 109 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Fatma Macit', 2005, 243 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Fatma Macit', 2006, 98 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Fatma Macit', 2007, 78 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Mumin Irican', 2004, 1 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Mumin Irican', 2005, 3 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Mumin Irican', 2006, 0 );
INSERT INTO telefon_gorusmeleri ( ad_soyad, yil, gorusme_sayisi ) VALUES ( 'Mumin Irican', 2007, 2 );

COMMIT;

-- tablonun kontrol edilmesi

SELECT * FROM telefon_gorusmeleri;

-- Her kisi icin her yil, o yil ve onceki yillarda toplam kac telefon gorusmesi yapmisim?

SELECT tg.ad_soyad,
tg.yil,
SUM( tg.gorusme_sayisi) OVER ( PARTITION BY tg.ad_soyad
ORDER BY tg.yil ) toplam_gs
FROM telefon_gorusmeleri tg
ORDER BY tg.ad_soyad,
tg.yil;

-- Her kisi icin her yil, o yil ve onceki yil ortalamada kac telefon gorusmesi yapmisim?

SELECT tg.ad_soyad,
tg.yil,
AVG( tg.gorusme_sayisi) OVER ( PARTITION BY tg.ad_soyad
ORDER BY tg.yil
ROWS 1 PRECEDING ) ortalama_gs
FROM telefon_gorusmeleri tg
ORDER BY tg.ad_soyad,
tg.yil;

-- ROLLUP ornegi

SELECT tg.ad_soyad,
tg.yil,
SUM ( tg.gorusme_sayisi ) toplam_gs
FROM telefon_gorusmeleri tg
WHERE tg.ad_soyad IN ( 'Fatma Macit', 'Mumin Irican' )
GROUP BY ROLLUP( tg.ad_soyad,
tg.yil );

-- CUBE ornegi

SELECT tg.ad_soyad,
tg.yil,
SUM ( tg.gorusme_sayisi ) toplam_gs
FROM telefon_gorusmeleri tg
WHERE tg.ad_soyad IN ( 'Fatma Macit', 'Mumin Irican' )
GROUP BY CUBE( tg.ad_soyad,
tg.yil );

-- tablonun dusurulmesi

DROP TABLE telefon_gorusmeleri;

-- tablonun yaratılması

DROP TABLE kaplumbaga_ailesi;

CREATE TABLE kaplumbaga_ailesi (
kimlik_no INTEGER PRIMARY KEY,
ata       REFERENCES kaplumbaga_ailesi,
isim      VARCHAR2(30)
);

-- tabloya verilerin girilmesi

INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 1, NULL, 'Dede Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 2, 1, 'Hala Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 3, 1, 'Baba Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 4, 3, 'Evlat Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 5, 4, 'Torun Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 6, 1, 'Amca Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 7, 6, 'Erkek Kuzen Kaplumbaga' );
INSERT INTO kaplumbaga_ailesi (kimlik_no, ata, isim ) VALUES ( 8, 6, 'Disi Kuzen Kaplumbaga' );

COMMIT;

-- tablonun kontrol edilmesi

SELECT * FROM kaplumbaga_ailesi;

-- CONNECT BY PRIOR ... START WITH ornegi

SELECT ka.isim,
ka.kimlik_no,
ka.ata
FROM kaplumbaga_ailesi ka
CONNECT BY PRIOR ka.kimlik_no = ka.ata
START WITH ka.ata IS NULL ;

-- CONNECT BY PRIOR ... START WITH ornegi

SELECT SYS_CONNECT_BY_PATH( isim, '/' ) agac
FROM kaplumbaga_ailesi ka
CONNECT BY PRIOR ka.kimlik_no = ka.ata
START WITH ka.ata IS NULL ;

-- tablonun dusurulmesi

DROP TABLE kaplumbaga_ailesi;