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;
Özgür Macit
