Veritabanı Yönetim Sistemleri

Tablolar Arasındaki İlişkiler

Dr. Öğr. Üyesi Oğuz Emre Kural

2026

Öğrenme Kazanımları

Bu dersin sonunda:

  • Tek tablodaki veri tekrarını somut bir örnek üzerinden açıklayabilirsiniz.
  • Birincil ve yabancı anahtarları gerçek bir şemada gösterebilirsiniz.
  • Bire bir, bire çok ve çoka çok ilişkileri basit bir ER diyagramında okuyabilir, ara tablonun görevini açıklayabilirsiniz.
  • Alt sorgu ve JOIN ile birden fazla tablodaki bilgiyi bir araya getirebilirsiniz.
  • Küme işlemleri, GROUP BY ve HAVING ile ilişkisel veriye yeni sorular sorabilirsiniz.

DB’den Örnek Bir Kayıt

muzik.db dosyasındaki tracks tablosunda bir satır şöyleydi:

track_id title artist album genre
2254 Bohemian Rhapsody Queen Greatest Hits I Rock

Parçayla ilgili bilgilere tek satırdan ulaşmak kolaydı. Bu, ilk sorgularımız için iyi bir başlangıçtı.

Queen Adı Kaç Kez Yazılıyor?

track_id title artist album
419 A Kind Of Magic Queen Greatest Hits II
420 Under Pressure Queen Greatest Hits II
2254 Bohemian Rhapsody Queen Greatest Hits I

artist ve album değerleri her parça satırında yeniden yer alıyor.

Tekrarlanan Bilginin Bedeli

Bir satırda Queen, başka bir satırda yanlışlıkla Quen yazdığımızı düşünelim. Aynı sanatçı için iki farklı ad oluşur.

Bu, yalnızca yazım hatası değildir. Sanatçıya göre arama ve sayım sonuçları da değişir.

Peki sanatçının adını her parçada tutmak zorunda mıyız?

Aynı Katalog, İki Düzen

İki dosyada da aynı 3.503 parça var. Fark, albüm ve sanatçı adını nerede tuttuğumuz.

İlk ders: muzik.db Örnek bilgi Bu ders: chinook.db
tracks
Tek satırda 		Bohemian Rhapsody Track.Name
Greatest Hits I Album.Title
Queen Artist.Name

chinook.db içinde toplam 11 tablo var. Burada bu parçayla ilgili üçünü görüyoruz.

İki Tablo Arasında Eksik Olan Ne?

Track ve Album ayrı tablolarda. Önce bağlantı sütunlarını gizleyip yalnızca adlara bakalım.

Track (seçili kayıtlar)
Name
Bohemian Rhapsody
We Will Rock You
We Will Rock You
Album (seçili kayıtlar)
Title
Greatest Hits I
News Of The World

We Will Rock You iki kez geçiyor. Bu hâliyle hangi satırın hangi albüme ait olduğunu tablolardan okuyamayız.

Tabloları Bağlayan Sayı

Aynı kayıtlara bu kez AlbumId sütunuyla bakalım.

Track
TrackId Name AlbumId
2254 Bohemian Rhapsody 185
2269 We Will Rock You 185
2271 We Will Rock You 186
Album
AlbumId Title
185 Greatest Hits I
186 News Of The World

AlbumId değeri 185 olan parçalar Greatest Hits I albümüne, 186 olan parça News Of The World albümüne bağlı.

chinook.db Dosyasını Açalım

chinook.db dosyasını indirin. muzik.db ile aynı klasöre koyabilirsiniz.

Terminalde dosyanın bulunduğu klasöre geçip şu komutu çalıştırın:

sqlite3 -readonly chinook.db

İlk dersteki çıktı ayarlarını yeniden yapın:

.headers on
.mode box
.nullvalue NULL

Dosyanın İçindeki Tablolar

SQLite isteminde:

.tables
Album          Employee       InvoiceLine    PlaylistTrack
Artist         Genre          MediaType      Track
Customer       Invoice        Playlist

Bugün ağırlıkla Artist, Album, Track, Genre, Playlist ve PlaylistTrack tablolarını kullanacağız.

.tables SQL sorgusu değil, sqlite3 komutudur.

Bir Parçanın İzini Sürelim

Bohemian Rhapsody için üç ayrı tabloda şu kayıtlar var:

Track

TrackId Name AlbumId
2254 Bohemian Rhapsody 185

Album

AlbumId Title ArtistId
185 Greatest Hits I 51

Artist

ArtistId Name
51 Queen

Track.AlbumId = 185 bizi albüme, Album.ArtistId = 51 sanatçıya götürür.

İlişkisel Veritabanı

İlişkisel veritabanında bilgiler, tanımlı sütunlara sahip tablolarda tutulur. Tablolar arasındaki bağlantıları anahtar değerleri kurar.

Buradaki ilişki yolu:

Artist  51 Queen
   ↕
Album  185 Greatest Hits I
   ↕
Track  2254 Bohemian Rhapsody

Tek tablodaki görünümü gerektiğinde bu bağlantıları kullanarak yeniden elde ederiz.

Bağlantılar Hep Aynı mı?

Bir albüme birden fazla parça bağlanabiliyor. Aşağıdaki üç çizimde bağlantı sayıları nasıl değişiyor?

Bir öğrencinin bir karta, bir albümün birçok parçaya ve çalma listelerinin ortak parçalara bağlandığı üç şema

İlişkilerin Üç Biçimi

Biçim Örnek Nasıl okunur?
Bire bir Öğrenci ve öğrenci kartı Bir öğrencinin en fazla bir kartı, bir kartın en fazla bir sahibi olur.
Bire çok Album ve Track Bir albüme birçok parça bağlanabilir. Her Track kaydı en fazla bir albüme bağlıdır.
Çoka çok Playlist ve Track Bir listede birçok parça bulunur; aynı parça başka listelerde de yer alabilir.

Öğrenci kartı örneği Chinook’ta yok. Çalma listeleri ile parçalar arasındaki bağlantıyı PlaylistTrack tutuyor.

ER Diyagramı Nedir?

ER (Entity–Relationship, varlık–ilişki) diyagramı, veritabanında hangi varlıkların bulunduğunu ve bunların nasıl ilişkilendiğini gösterir.

Album Track

Album ve Track ayrı tablolar. Aralarındaki çizgi, bu iki tablonun ilişkili olduğunu gösteriyor.

Chinook’un ER Diyagramı

Chinook veritabanındaki 11 tablo ve bağlantı uçlarında sıfır, bir, çok işaretleriyle gösterilmiş ilişki çoklukları

Bu çizimde 11 tablonun tamamı var. Bağlantı uçlarındaki daire, dik çizgi ve çatal kaç kaydın eşleşebileceğini gösteriyor.

ER Diyagramındaki İşaretler

Çizginin uçları, iki tablo arasında kaç kayıt eşleşebileceğini gösterir.

0Eşleşme olmayabilir
1Tek kayıt
ÇokBirçok kayıt olabilir

Daireyle dik çizgi bir aradaysa sıfır veya bir; daireyle çatallı uç bir aradaysa sıfır veya daha fazla kayıt eşleşebilir.

Bir Sanatçının Albümleri

Chinook’a bir sanatçı, albümü eklenmeden de kaydedilebilir. Albümleri varsa hepsi aynı sanatçıya bağlanır. Album tarafındaki 0..çok işareti bunu gösterir.

Artist Album 0..çok

Chinook’ta Queen’e üç albüm bağlı. Azymuth içinse bu katalogda albüm kaydı yok.

Bir Albümün Sanatçısı

Chinook’ta her Album kaydının bir ArtistId değeri var. Bu yüzden albümden sanatçıya doğru bakınca 1 görüyoruz.

Artist Album 1 0..çok

Greatest Hits I kaydındaki ArtistId 51; bu değer Queen kaydını gösteriyor.

Türü Henüz Belirlenmemiş Bir Parça

Track.GenreId boş bırakılabilir. Türü henüz girilmemiş bir parça kaydedildiğinde, o parça hiçbir Genre kaydıyla eşleşmez. Türü girilirse bir kayıtla eşleşir: 0..1.

Genre Track 0..1 0..çok

Bohemian Rhapsody kaydında GenreId 1, yani türü Rock. Alan boş olsaydı türü henüz belirtilmemiş olacaktı.

Aynı Adlı İki Kayıt

We Will Rock You adına göre arayınca iki parça kaydı çıkıyor.

Parça adı Sanatçı Albüm TrackId
We Will Rock You Queen Greatest Hits I 2269
We Will Rock You Queen News Of The World 2271

ISBN, kitabın belirli baskısına yayıncılıkta verilen kimliktir. TrackId ise Chinook'un bir parça kaydına verdiği numaradır.

Birincil Anahtar

Birincil anahtar (primary key), tablodaki bir kaydı tekil olarak tanımlar.

Track tablosundaki iki kayda bakalım:

Track
TrackId Name AlbumId
77 Enter Sandman 9
1801 Enter Sandman 148

77: Apocalyptica’nın yorumu. 1801: Metallica’nın kaydı.

Bu iki satırı hangi sütun ayırıyor?

Her Tablonun Kendi Anahtarı

Bu üç tabloda kayıtları ayıran birincil anahtarlar:

Tablo Kayıt Birincil anahtar
Artist Queen ArtistId = 51
Album Greatest Hits I AlbumId = 185
Track Bohemian Rhapsody TrackId = 2254

Bohemian Rhapsody satırındaki AlbumId = 185 değeri, Album tablosunda 185 numaralı kaydı bulmamızı sağlar.

Yabancı Anahtar

Yabancı anahtar (foreign key), başka bir tablodaki kayda işaret eder.

Track
TrackId Name AlbumId
2254 Bohemian Rhapsody 185
Album
AlbumId Title ArtistId
36 Greatest Hits II 51
185 Greatest Hits I 51
Artist
ArtistId Name
50 Metallica
51 Queen

Parçadaki AlbumId değeri bizi hangi albüme götürür?

Parça satırında albüm adını, albüm satırında sanatçı adını tekrar etmeyiz. İlgili kayıtların kimliklerini tutarız.

Bir Sanatçı, Birden Çok Albüm

ArtistId = 51 olan Queen’in üç albümü var:

Artist
ArtistId Name
51 Queen
Album
AlbumId Title ArtistId
36 Greatest Hits II 51
185 Greatest Hits I 51
186 News Of The World 51

Her albüm tek bir sanatçı kaydına işaret ediyor. Bir sanatçı ise birçok albüme bağlanabiliyor. Buna bire çok ilişki deriz.

Çoka Çok İlişki: Çalma Listeleri

Smells Like Teen Spirit (TrackId = 1990) birden fazla listede bulunuyor:

Playlist
PlaylistId Name
1 Music
5 90’s Music
8 Music
PlaylistTrack
PlaylistId TrackId
1 1990
5 1990
8 1990
Track
TrackId Name
1990 Smells Like Teen Spirit

PlaylistTrack yalnızca kimlik çiftlerini saklar. Liste adı Playlistte, parça adı Tracktedir.

Ara Tablo Ne Saklar?

PlaylistTrack tablosunun iki sütunu var:

PlaylistId TrackId
1 1990
5 1990
8 1990

Tek bir satırın anlamı: “Bu parça bu listede yer alıyor.”

Birincil anahtar (PlaylistId, TrackId) çiftidir. TrackId = 1990 farklı listelerde bulunabilir; (1, 1990) çifti tekrar edemez.

Şemada Anahtarları Görmek

SQLite isteminde şu komut tablo tanımını gösterir:

.schema PlaylistTrack

Tanımın önemli kısmı:

PRIMARY KEY (PlaylistId, TrackId)
FOREIGN KEY (PlaylistId) REFERENCES Playlist (PlaylistId)
FOREIGN KEY (TrackId) REFERENCES Track (TrackId)

Birincil anahtar satırı benzersiz kılar; iki yabancı anahtar bağlantının uçlarını tanımlar.

Kısa Kontrol: 2254 Nereye Bağlanır?

TrackId = 2254 kaydının AlbumId değeri 185. AlbumId = 185 kaydının ArtistId değeri 51.

Bu parçanın albümü ve sanatçısı nedir?

AlbumId = 185 → Greatest Hits I. ArtistId = 51 → Queen.

İlişkisel Modelin Üç İşlemi

Tabloları sorgularken üç hareket sık görülür:

İstediğimiz SQL’de karşılığı
Bazı satırları seçmek WHERE
Bazı sütunları seçmek SELECT içindeki sütun listesi
İlişkili satırları birleştirmek JOIN ... ON ...

İlişkisel cebirde bunlara sırasıyla seçme (σ), izdüşüm (π) ve birleştirme (⨝) denir. SQL bu düşünceleri veritabanında çalıştırmamızı sağlar.

Albümleri Bulmak İçin İki Adım

Soru: Queen’in albümleri hangileri?

Album tablosunda sanatçı adı yok, ArtistId var. Önce Queen’in kimliğini, sonra bu kimliğe bağlı albümleri bulabiliriz.

Artist.Name = Queen  →  ArtistId = 51
ArtistId = 51        →  Album satırları

Adım 1: Sanatçının Kimliği

SELECT "ArtistId"
FROM "Artist"
WHERE "Name" = 'Queen';
ArtistId
51

ArtistId bir sanatçıyı işaret eder; Name insanın okuyabileceği addır.

Adım 2: Albümler

SELECT "Title"
FROM "Album"
WHERE "ArtistId" = 51;
Title
Greatest Hits II
Greatest Hits I
News Of The World

Bu sorgu çalışır. Fakat 51 değerini önceki sorgudan elle aldık.

Alt Sorgu

Bir sorgunun sonucunu başka bir sorguda kullanabiliriz:

SELECT "Title"
FROM "Album"
WHERE "ArtistId" = (
    SELECT "ArtistId"
    FROM "Artist"
    WHERE "Name" = 'Queen'
);

İçteki sorgu Queen’in kimliğini sağlar. Dıştaki sorgu bu kimliğe bağlı albümleri getirir.

Alt Sorgu Birden Fazla Değer Döndürürse

Soru: Queen’in parçaları hangileri? Trackte sanatçı yok, AlbumId var. Queen’in üç albümü olduğu için içteki sorgu üç kimlik döndürür; = tek değer bekler, birden fazla değer için IN gerekir:

SELECT "Name" FROM "Track"
WHERE "AlbumId" IN (
    SELECT "AlbumId" FROM "Album"
    WHERE "ArtistId" = (SELECT "ArtistId" FROM "Artist" WHERE "Name" = 'Queen')
);

Sonuç: Queen’in 45 parçası. Bire çok ilişki içteki sorguyu çok satırlı yapar; IN bu yüzden gerekir.

Ara Tablodan Geçen Alt Sorgu

Soru: Bohemian Rhapsody hangi çalma listelerinde? Yol: Track → PlaylistTrack → Playlist.

SELECT "Name" FROM "Playlist"
WHERE "PlaylistId" IN (
    SELECT "PlaylistId" FROM "PlaylistTrack"
    WHERE "TrackId" = (SELECT "TrackId" FROM "Track" WHERE "Name" = 'Bohemian Rhapsody')
);

Sonuç: Music, Music. Aynı ad, farklı listeler: PlaylistId 1 ve 8.

Çoka çok ilişkide ara tablo yalnızca geçiş noktasıdır; adlar iki uçtaki tablolardan gelir.

Alt Sorgu İçin Kısa Kontrol

Track tablosunda GenreId var, tür adı yok. Rock parçalarının adlarını getirmek için eksik kısmı düşünün:

SELECT "Name"
FROM "Track"
WHERE "GenreId" = ( ... );
SELECT "GenreId"
FROM "Genre"
WHERE "Name" = 'Rock'

Yeni Örnek: Deniz Aslanları

sea_lions.db dosyasında iki tablo var. sea_lions deniz aslanlarının kimliğini ve adını, migrations ise göç mesafesini ve gün sayısını tutuyor.

sea_lions.db dosyasını indirin. Açık Chinook isteminden .quit ile çıkın. Terminalde dosyanın bulunduğu klasöre geçin:

sqlite3 -readonly sea_lions.db
.headers on
.mode box
.nullvalue NULL
.tables

İki Tabloda Aynı Kimlikler

sea_lions
id name
10484 Ayah
11728 Spot
11729 Tiger
11732 Mabel
11734 Rick
11790 Jolee
migrations
id distance days
10484 1000 107
11728 1531 56
11729 1370 37
11732 1622 62
11734 1491 58
11735 2723 82
11736 1571 52
11737 1957 92

Ayah’ın göç bilgisi hangi satırda? Aynı id değerini iki tabloda bulmak yeterli.

Eşleşmeyi Satırlarda Görelim

Tam tablodan üçer satıra yakından bakalım. Hangi kayıtlar yan yana gelebilir?

sea_lions
id name
10484 Ayah
11728 Spot
11790 Jolee
migrations
id distance
10484 1000
11728 1531
11735 2723
Eşleşenler
id name distance
10484 Ayah 1000
11728 Spot 1531

Önce Ayah'ın iki satırını bulun.

INNER JOIN: Eşleşen Satırlar

JOIN, burada yalnızca iki tabloda da bulunan id değerlerini getirir:

SELECT s."id", s."name", m."distance", m."days"
FROM "sea_lions" AS s
JOIN "migrations" AS m ON s."id" = m."id"
ORDER BY s."id";
id name distance days
10484 Ayah 1000 107
11728 Spot 1531 56
11729 Tiger 1370 37
11732 Mabel 1622 62
11734 Rick 1491 58

JOIN yazmak burada INNER JOIN yazmakla aynıdır. Jolee ve 11735 numaralı göç sonuçta yoktur.

İki Tabloda da id Var

Yalnızca id yazarsak hangi tablonun sütununu istediğimiz belirsizdir. Tablo adını veya kısa adını belirtiriz:

s."id"     -- sea_lions.id
m."id"     -- migrations.id

ON s."id" = m."id" satırı, hangi kayıtların eşleşeceğini söyler. SELECT satırıysa sonuçta hangi sütunları göreceğimizi belirler.

Bu dosyada migrations.id şemada yabancı anahtar olarak tanımlanmamış. JOIN için ON koşulunu yazmamız yine yeterli.

Kısa Takma Adlar

AS s ve AS m, sorgu içinde tablo adlarını kısaltır:

SELECT s."name", m."distance"
FROM "sea_lions" AS s
JOIN "migrations" AS m ON s."id" = m."id"
WHERE s."name" = 'Ayah';

Sonuç: Ayah’ın göç mesafesi 1000. s ve m kalıcı tablo adları değildir.

LEFT JOIN: Jolee de Kalsın

LEFT JOIN, soldaki sea_lions satırlarının tamamını korur:

SELECT s."id", s."name", m."distance"
FROM "sea_lions" AS s
LEFT JOIN "migrations" AS m ON s."id" = m."id"
ORDER BY s."id";
Sonuçtan üç satır
id name distance
10484 Ayah 1000
11728 Spot 1531
11790 Jolee NULL

Eşleşen iki satır görünüyor. Jolee ne olacak?

RIGHT JOIN: 11735 de Kalsın

RIGHT JOIN, sağdaki migrations satırlarının tamamını korur:

SELECT s."name", m."id", m."distance"
FROM "sea_lions" AS s
RIGHT JOIN "migrations" AS m ON s."id" = m."id"
ORDER BY m."id";
Sonuçtan üç satır
name id distance
Ayah 10484 1000
Spot 11728 1531
NULL 11735 2723

11735 numaralı göçün adı ne olacak?

FULL JOIN: İki Taraf da Kalsın

FULL JOIN, eşleşenleri ve iki taraftaki eşleşmeyen kayıtları birlikte getirir:

SELECT s."id" AS "sea_lion_id", s."name",
       m."id" AS "migration_id", m."distance"
FROM "sea_lions" AS s
FULL JOIN "migrations" AS m ON s."id" = m."id";
Sonuçtan dört satır
sea_lion_id name migration_id distance
10484 Ayah 10484 1000
11728 Spot 11728 1531
11790 Jolee NULL NULL
NULL NULL 11735 2723

Önce solda eşleşmeyen Jolee'yi düşünün.

Dört JOIN’in Satır Sayısı

Sorgu Satır sayısı Korunan eşleşmesiz kayıtlar
INNER JOIN 5 Yok
LEFT JOIN 6 Jolee
RIGHT JOIN 8 11735, 11736, 11737
FULL JOIN 9 Her iki taraftakiler

6 + 8 - 5 = 9: her iki tablodaki kayıtları sayarken beş eşleşmeyi iki kez saymayız.

NATURAL JOIN Neyi Seçer?

NATURAL JOIN, iki tabloda aynı adlı sütunları eşleşme koşulu olarak kendisi seçer. Bu örnekte ortak sütun id olduğu için sonuç ilk bakışta kullanışlı görünür.

Biz koşulu açıkça yazacağız:

JOIN "migrations" AS m ON s."id" = m."id"

Şemaya ileride başka bir ortak sütun adı eklenirse NATURAL JOINin eşleşme koşulu da değişir.

Chinook’a Geri Dönelim

Deniz aslanları örneğinde aynı id değerlerini eşleştirdik. Chinook’ta yol farklı: Track.AlbumId, Album.AlbumId değerine; Album.ArtistId de Artist.ArtistId değerine bağlanır.

sea_lions.db isteminden çıkıp chinook.db dosyasını yeniden açın:

.quit
sqlite3 -readonly chinook.db
.headers on
.mode box
.nullvalue NULL

JOIN Yolunu Seçmek

Soru: Bohemian Rhapsody’yi kim söylüyor?

Başlangıç Bağlantı Sonuç
Track Track.AlbumId = Album.AlbumId Albüm
Album Album.ArtistId = Artist.ArtistId Sanatçı

Sorgu yazmadan önce bu yolu kâğıtta çizmek, hangi ON koşulunun gerektiğini açık eder.

Üç Tabloyu Birleştirmek

Sanatçı adını da istiyorsak Artist tablosuna devam ederiz:

SELECT t."Name" AS "Parça",
       a."Title" AS "Albüm",
       ar."Name" AS "Sanatçı"
FROM "Track" AS t
JOIN "Album" AS a ON a."AlbumId" = t."AlbumId"
JOIN "Artist" AS ar ON ar."ArtistId" = a."ArtistId"
WHERE t."TrackId" = 2254;
Parça Albüm Sanatçı
Bohemian Rhapsody Greatest Hits I Queen

Uygulama: Parça ve Türü

Track tablosunda GenreId var, tür adı Genre tablosunda. TrackId = 2254 için parça adını ve tür adını yan yana getirin.

SELECT t."Name", g."Name"
FROM "Track" AS t
JOIN "Genre" AS g ON g."GenreId" = t."GenreId"
WHERE t."TrackId" = 2254;

Sonuç: Bohemian Rhapsody, Rock.

Uygulama: Albümü Olmayan Sanatçılar

LEFT JOIN sonucunda eşleşmeyenleri seçebiliriz:

SELECT ar."Name"
FROM "Artist" AS ar
LEFT JOIN "Album" AS a
  ON a."ArtistId" = ar."ArtistId"
WHERE a."AlbumId" IS NULL;

Bu sorgu 71 sanatçı döndürür. NULL, sanatçı adının eksik olduğunu değil, bu sonuçta eşleşen bir albüm satırı bulunmadığını gösterir.

JOIN Sonucu Kalıcı Bir Tablo Değil

JOIN sorgusu bir sonuç kümesi üretir. Bu sonuç, sorgu sırasında görünür; kendiliğinden veritabanına yeni bir tablo olarak kaydedilmez.

Bu yüzden aynı veriyi farklı sorular için farklı biçimlerde birleştirebiliriz:

  • Parça + albüm
  • Albüm + sanatçı
  • Çalma listesi + parça

Ara Tabloyla JOIN

Grunge listesindeki (PlaylistId = 16) ilk beş parça:

SELECT t."TrackId", t."Name"
FROM "PlaylistTrack" AS pt
JOIN "Track" AS t ON t."TrackId" = pt."TrackId"
WHERE pt."PlaylistId" = 16
ORDER BY t."TrackId"
LIMIT 5;
TrackId Name
52 Man In The Box
2003 Smells Like Teen Spirit
2004 In Bloom
2005 Come As You Are
2007 Lithium

Liste Adını Da Getirelim

Liste kimliği yerine adını görmek için üçüncü tabloyu ekleriz:

SELECT p."Name" AS "Liste", t."Name" AS "Parça"
FROM "Playlist" AS p
JOIN "PlaylistTrack" AS pt
  ON pt."PlaylistId" = p."PlaylistId"
JOIN "Track" AS t
  ON t."TrackId" = pt."TrackId"
WHERE p."PlaylistId" = 16
LIMIT 5;

Bu sorguda Playlist → PlaylistTrack → Track yolunu izliyoruz. LIMIT için sıra belirtmedik; hangi beş satırın geleceği garanti edilmez.

Alt Sorgu mu JOIN mi?

İkisi de tablo ilişkilerinden yararlanır. Sorunun istediği sonuç biçimi seçim yapmayı kolaylaştırır:

İstenen sonuç Kullanışlı başlangıç
Queen’in albüm adları; kimliği içeriden bul Alt sorgu
Parça, albüm ve sanatçı adını aynı satırda göster JOIN

Bu bir zorunluluk değildir. Aynı soruyu farklı SQL biçimleriyle çözebiliriz. Önemli olan, ilişkileri doğru okumak ve sonucu doğrulamaktır.

İki Listeden Ortak Bir Seçim

Bir arkadaş grubuyla müzik seçiyorsunuz.

  • Biri 90’s Music,
  • diğeri Heavy Metal Classic listesini açtı.

“İkisinde de olan parçalarla başlayalım” dediniz. Listeleri tek tek karşılaştırmadan bu parçaları nasıl bulursunuz?

İki Listeyi Küme Olarak Görmek

A: 90’s Music 1477 B: Heavy Metal Classic 26 1472 5 21

90’s Music listesinde 1477 parça var.

Heavy Metal Classic listesinde 26 parça var. İki liste henüz ayrı duruyor.

İki listede ortak 5 parça var. Looks That Kill bunlardan biri.

Her kayıt TrackId ile sayılır. Şema ölçekli çizilmedi.

Kesişim: Ortak Parçalar

A B 5 parça

A ∩ B: İki listede de bulunan parçalar. Looks That Kill bunlardan biri.

SQL’de: INTERSECT

Birleşim: Listelerin Tamamı

A B

A ∪ B: Listelerden en az birinde bulunan 1498 farklı parça. Ortak beş parça birer kez sayılır.

SQL’de: UNION

Fark: Yalnız İlk Liste

A B 1472 Black Dog

A − B: 90’s Music’te olup Heavy Metal Classic’te bulunmayan parçalar. Yönü ters çevirirsek sonuç değişir.

SQL’de: EXCEPT

Bu Soruları SQL’e Nasıl Sorarız?

Sorgu cümlesi Küme işlemi SQL’de
İki listede de hangi parçalar var? Kesişim INTERSECT
Listelerden en az birinde hangi parçalar var? Birleşim UNION
90’s Music’te olup Heavy Metal Classic’te olmayanlar hangileri? Fark EXCEPT

Liste adı Playlist.Name, parça adı Track.Name alanında. PlaylistTrack ise listeyle parçayı kimlikleri üzerinden bağlıyor. Sorgularda parça adını göstereceğiz, TrackId ile aynı adlı farklı kayıtları ayıracağız.

INTERSECT: Ortak Parçalar

Her iki listede de bulunan parçaların adlarını bulalım:

SELECT t."Name", t."TrackId"
FROM "PlaylistTrack" AS pt
JOIN "Track" AS t ON t."TrackId" = pt."TrackId"
WHERE pt."PlaylistId" = 5 -- 90’s Music
INTERSECT
SELECT t."Name", t."TrackId"
FROM "PlaylistTrack" AS pt
JOIN "Track" AS t ON t."TrackId" = pt."TrackId"
WHERE pt."PlaylistId" = 17 -- Heavy Metal Classic
ORDER BY "Name";

Sonuç: 5 ortak parça. İki sorgu da önce adı, sonra TrackId değerini döndürür.

Ortak Beş Parça

İki listede de bulunan beş kayıt:

Parça adı (Name) TrackId
Enter Sandman 1801
Fast As a Shark 3
Looks That Kill 1984
Princess of the Dawn 5
Restless and Wild 4

Enter Sandman adlı 77 numaralı başka bir kayıt yalnız 90’s Music’tedir. Ad tek başına kayıtları ayırmaya yetmez.

UNION: İki Listedeki Parçalar

Parça iki listede birden olsa bile aynı kayıt sonuçta bir kez görünür:

SELECT t."Name", t."TrackId"
FROM "PlaylistTrack" AS pt
JOIN "Track" AS t ON t."TrackId" = pt."TrackId"
WHERE pt."PlaylistId" = 5 -- 90’s Music
UNION
SELECT t."Name", t."TrackId"
FROM "PlaylistTrack" AS pt
JOIN "Track" AS t ON t."TrackId" = pt."TrackId"
WHERE pt."PlaylistId" = 17; -- Heavy Metal Classic

Sonuç: 1498 farklı kayıt. Black Dog, Looks That Kill ve Balls to the Wall sonuçtadır. UNION, aynı kayıt iki listede olsa da onu bir kez gösterir.

EXCEPT: Yalnız İlk Listedekiler

90’s Music listesinde olup Heavy Metal Classic listesinde bulunmayan parçaların adları:

SELECT t."Name", t."TrackId"
FROM "PlaylistTrack" AS pt
JOIN "Track" AS t ON t."TrackId" = pt."TrackId"
WHERE pt."PlaylistId" = 5 -- 90’s Music
EXCEPT
SELECT t."Name", t."TrackId"
FROM "PlaylistTrack" AS pt
JOIN "Track" AS t ON t."TrackId" = pt."TrackId"
WHERE pt."PlaylistId" = 17; -- Heavy Metal Classic

Sonuç: 1472 kayıt. Black Dog bunlardan biri. Sıra değişince soru değişir. 17 EXCEPT 5 yazarsak yalnız Heavy Metal Classic’te olan 21 kayıt gelir.

Toplulaştırmayı Hatırlayalım

İlk derste tüm tablodaki kayıtları saymıştık:

SELECT COUNT(*) FROM "Track";

Sonuç: 3503.

“Her müzik türünde kaç parça var?” Tek bir toplam sayı yerine her tür için ayrı bir sayı gerekiyor.

Satırları Kümelere Ayırmak

Track tablosundan altı satır seçelim. Aynı GenreId değerine sahip satırları nasıl ayırırız?

Altı örnek satır
TrackId GenreId
1 1
64 2
77 3
2 1
78 3
3 1
GenreId değerine göre
1TrackId: 1, 2, 3COUNT(*) = 3
2TrackId: 64COUNT(*) = 1
3TrackId: 77, 78COUNT(*) = 2

Hangi satırlar aynı GenreId değerini taşıyor?

GROUP BY

Altı satırda gördüğümüz işlemi şimdi Track tablosunun tamamına uygulayalım:

SELECT "GenreId", COUNT(*) AS "Parça sayısı"
FROM "Track"
GROUP BY "GenreId";
GenreId Parça sayısı
1 1297
2 130
3 374
4 332
5 12

Sonuç 25 satır, her tür için bir tane; tablo ilk beşini gösteriyor.

GROUP BY ve JOIN Birlikte

SELECT g."Name" AS "Tür", COUNT(*) AS "Parça sayısı"
FROM "Track" AS t
JOIN "Genre" AS g ON g."GenreId" = t."GenreId"
GROUP BY g."GenreId", g."Name"
ORDER BY "Parça sayısı" DESC;
Tür Parça sayısı
Rock 1297
Latin 579
Metal 374
Alternative & Punk 332
Jazz 130

Tür kimliği yerine adı için Genre bağlandı; tablo ilk beş satırı gösteriyor.

Her Küme İçin Ortalama

AVG de küme başına çalışır. Türlere göre ortalama süre, en uzun üçü:

SELECT g."Name" AS "Tür", ROUND(AVG(t."Milliseconds")) AS "Ortalama süre"
FROM "Track" AS t
JOIN "Genre" AS g ON g."GenreId" = t."GenreId"
GROUP BY g."GenreId", g."Name"
ORDER BY "Ortalama süre" DESC LIMIT 3;
Tür Ortalama süre
Sci Fi & Fantasy 2911783.0
Science Fiction 2625549.0
Drama 2575284.0

En Az 100 Parçalık Türler

Az önce GROUP BY ile her türün parça sayısını bulduk. Şimdi raporda yalnızca en az 100 parçası olan türleri görmek istiyoruz.

Tür Parça sayısı
Rock 1297
Jazz 130
TV Shows 93

Bu üç türden hangileri raporda kalmalı?

Rock ve Jazz kalır. TV Shows elenir. SQL’de bunu nasıl yazarız?

HAVING: Grupları Seçmek

GROUP BY tür başına bir grup oluşturur ve COUNT(*) parçaları sayar. HAVING bu sayıya göre grupları seçer.

SELECT g."Name", COUNT(*) AS "Parça sayısı"
FROM "Track" AS t
JOIN "Genre" AS g ON g."GenreId" = t."GenreId"
GROUP BY g."GenreId", g."Name"
HAVING COUNT(*) >= 100
ORDER BY "Parça sayısı" DESC;

Beş tür kalır: Rock, Latin, Metal, Alternative & Punk ve Jazz.

WHERE ve HAVING

Süresi en az üç dakika (180.000 ms) olan parçaları seçelim. Bu parçalardan en az 100 tanesi bulunan türleri bulalım.

SELECT g."Name", COUNT(*) AS "Parça sayısı"
FROM "Track" AS t
JOIN "Genre" AS g ON g."GenreId" = t."GenreId"
WHERE t."Milliseconds" >= 180000
GROUP BY g."GenreId", g."Name"
HAVING COUNT(*) >= 100;

WHERE süreye bakarak parçaları ayırır. HAVING kalan parçalar sayıldıktan sonra türleri seçer.

Sanatçıya Göre Sayalım

Her sanatçının parça sayısına ulaşmak için iki bağlantı gerekir:

SELECT ar."Name", COUNT(t."TrackId") AS "Parça sayısı"
FROM "Artist" AS ar
JOIN "Album" AS a ON a."ArtistId" = ar."ArtistId"
JOIN "Track" AS t ON t."AlbumId" = a."AlbumId"
GROUP BY ar."ArtistId", ar."Name"
HAVING COUNT(t."TrackId") >= 100
ORDER BY "Parça sayısı" DESC;
Sanatçı Parça sayısı
Iron Maiden 213
U2 135
Led Zeppelin 114
Metallica 112

Alıştırma 1: İlişkiyi Bulun

Track.TrackId = 2254 satırının AlbumId değeri 185. Album.AlbumId = 185 satırının ArtistId değeri 51.

Hangi iki eşitlik, parça adının yanına sanatçı adını getirir?

Track.AlbumId = Album.AlbumId
Album.ArtistId = Artist.ArtistId

Alıştırma 2: Albüm ve Sanatçı

Bohemian Rhapsody için albüm ve sanatçı adını tek sonuç satırında gösterin. Aynı isimli farklı parçalar olabileceği için TrackId = 2254 kullanın.

SELECT a."Title" AS "Albüm", ar."Name" AS "Sanatçı"
FROM "Track" AS t
JOIN "Album" AS a ON a."AlbumId" = t."AlbumId"
JOIN "Artist" AS ar ON ar."ArtistId" = a."ArtistId"
WHERE t."TrackId" = 2254;

Sonuç: Greatest Hits I, Queen.

Alıştırma 3: Boş Çalma Listeleri

Hiç parçası olmayan çalma listelerini bulmak istiyoruz. Hangi iki tablo, hangi JOIN türü ve hangi koşul gerekir?

SELECT p."PlaylistId", p."Name"
FROM "Playlist" AS p
LEFT JOIN "PlaylistTrack" AS pt ON pt."PlaylistId" = p."PlaylistId"
WHERE pt."TrackId" IS NULL;

Sonuç: 18 listeden dördü boş: Movies (2), Audiobooks (4), Audiobooks (6), Movies (7).

Alıştırma 4: Seyrek Türler

Ondan az parçası bulunan türleri getirmek için hangi koşul HAVING satırına yazılır? Sonucu tahmin edin: kaç tür gelir?

SELECT g."Name", COUNT(*) AS "Parça sayısı"
FROM "Track" AS t
JOIN "Genre" AS g ON g."GenreId" = t."GenreId"
GROUP BY g."GenreId", g."Name"
HAVING ...;
HAVING COUNT(*) < 10;

Sonuç: tek tür, Opera, 1 parça.

Hatalı Eşleşmeyi Düzeltin

Bu sorgu iki farklı kimlik türünü karşılaştırıyor:

SELECT t."Name", a."Title"
FROM "Track" AS t
JOIN "Album" AS a ON t."TrackId" = a."AlbumId";

Hangi sütunu değiştirmeliyiz?

JOIN "Album" AS a ON t."AlbumId" = a."AlbumId"

Bu Dersten Kalan Yol

Birden fazla tablodan veri isterken önce üç şeyi belirleyin:

  1. İstediğim sonuçta hangi bilgiler yer alacak?
  2. Bu bilgiler hangi tablolarda?
  3. Tablolar hangi anahtarlar üzerinden bağlı?

Sonra sorguyu yazın ve gelen satırların beklediğiniz anlamı taşıyıp taşımadığını kontrol edin.