SQL temeli: DDL, kısıtlar, SELECT ve join

SQL (Structured Query Language), ilişkisel veritabanıyla konuşmanın standart dili. Dördüncü ve beşinci haftada ilişkisel cebirle (relational algebra) ne istediğini matematiksel olarak yazdın. SQL aynı fikri okunabilir bir dille yazar. Sınavda en çok puanı “write an SQL query that…” soruları getirir. Bu sayfada iki şeyi öğreneceksin: tabloyu kurmak (DDL) ve tablolardan veri çekmek (SELECT ve join).

Altın kurallar:

1. SELECT-FROM-WHERE okuma sırası FROM → WHERE → SELECT. Önce tabloyu kur, sonra satırları süz, en son sütunları seç.
2. SQL tekrarları silmez. İlişkisel cebirde $\pi$ tekrarları atar, SQL’de bunun için DISTINCT yazman gerekir.
3. Her join’in bir koşulu olmalı. Koşulsuz FROM r, s Kartezyen çarpımdır.
4. NULL hiçbir şeye eşit değil, kendine bile. NULL arıyorsan IS NULL yaz.
5. “Eşi olmayanlar” sorusu = outer join + IS NULL (ya da 7. haftadaki NOT EXISTS).

Örnek veritabanı

Bütün ders boyunca aynı küçük üniversite veritabanını kullanıyoruz. Altı tablo:

Dikkat et: Firat (S6) hiç ders almıyor, Gizem’in bölümü NULL, Yildiz ders vermiyor, Biology’yi kimse vermiyor ve almıyor, Cem’in CS101 notu henüz NULL. Bu boşluklar outer join ve NULL sorularının malzemesi.

DDL: tabloyu kurmak

DDL (Data Definition Language), şemayı tanımlar. Satır değil, yapı yaratır.

CREATE TABLE course (
  course_id TEXT PRIMARY KEY,
  title     TEXT NOT NULL,
  dept_name TEXT REFERENCES department(dept_name),
  credits   INTEGER CHECK (credits BETWEEN 1 AND 6)
);

CREATE TABLE takes (
  stu_id    TEXT REFERENCES student(stu_id),
  course_id TEXT REFERENCES course(course_id),
  grade     TEXT,
  PRIMARY KEY (stu_id, course_id)
);

Sık kullanılan veri tipleri (data types): INT/INTEGER, NUMERIC(p,d) (p basamak, d’si ondalık), VARCHAR(n) (en çok n karakter), CHAR(n) (sabit n), DATE, REAL. SQLite tiplere gevşek bakar (TEXT, INTEGER, REAL), standart SQL ve diğer sistemler daha katıdır.

Bütünlük kısıtları (integrity constraints)

Yabancı anahtar (foreign key) iki yönden korur. Var olmayan öğrenciye kayıt ekleyemezsin. Ders alan bir öğrenciyi de silemezsin: DELETE FROM student WHERE stu_id='S1' hata verir, çünkü takes hâlâ S1’e işaret ediyor. Silmeye izin vermek istiyorsan davranışı sen seçersin:

CREATE TABLE advisor (
  stu_id  TEXT PRIMARY KEY REFERENCES student(stu_id) ON DELETE CASCADE,
  inst_id TEXT NOT NULL REFERENCES instructor(inst_id)
);

ON DELETE CASCADE ile öğrenci silinince advisor satırı da kendiliğinden silinir. Diğer seçenekler ON DELETE SET NULL ve varsayılan davranış olan reddetmek.

TUZAK · SQLite’ta FK kontrolü varsayılan olarak kapalı. SQLite yabancı anahtarları ancak PRAGMA foreign_keys = ON; çalıştırılınca uygular. Doğrulama script’imiz bunu açıyor. Standart SQL’de ve PostgreSQL’de bu kontrol hep açıktır.

ALTER ve DROP

ALTER TABLE student ADD COLUMN email TEXT;   -- var olan satırlarda email = NULL
DROP TABLE club;                             -- tablo ve şeması tamamen gider
DELETE FROM club;                            -- satırlar gider, boş tablo kalır

Temel sorgu: SELECT-FROM-WHERE

SELECT name, salary FROM instructor WHERE dept_name = 'CS' AND salary > 85000;

Bu sorgu ilişkisel cebirde $\pi_{name,\,salary}(\sigma_{dept\_name='CS' \land salary>85000}(instructor))$ demek. Eşleme şöyle:

DISTINCT ve tekrarlar

SELECT dept_name FROM instructor 6 satır döndürür: CS, CS, EE, MATH, MATH, BIO. SQL bir çoklu küme (multiset, bag) döndürür. DISTINCT ekleyince:

AS ve aritmetik

SELECT name, salary / 12 AS monthly FROM instructor WHERE dept_name = 'CS';

TUZAK · tam sayı bölmesi. SQLite’ta (ve PostgreSQL’de) iki INTEGER’ın bölümü tam sayıdır, ondalık atılır. salary / 12.0 yazınca 7916.666666666667 ve 6666.666666666667 gelir.

LIKE, BETWEEN, IN

LIKE kalıp eşleştirir: % herhangi uzunlukta dizi (boş da olabilir), _ tam bir karakter.

SELECT course_id, title FROM course WHERE title LIKE '%a%s';

Kalıp “içinde bir yerde a olsun ve s ile bitsin” diyor. Circuits s ile biter ama içinde a yok. course_id LIKE 'CS_01' ise CS101 ve CS201’i bulur.

TUZAK · LIKE ve büyük harf. Standart SQL’de LIKE büyük/küçük harfe duyarlıdır. SQLite ASCII harflerde duyarsızdır: title LIKE 'data%' SQLite’ta Data Structures ve Databases’ı bulur, PostgreSQL’de hiçbir şey bulmaz.

BETWEEN iki ucu dahil eder:

SELECT name, salary FROM instructor WHERE salary BETWEEN 70000 AND 85000;

IN bir listeye üyeliği sorar: WHERE dept_name IN ('EE', 'MATH') → Cem, Defne, Firat. Bu, dept_name = 'EE' OR dept_name = 'MATH' ile aynıdır.

ORDER BY

SELECT name, dept_name, salary FROM instructor ORDER BY dept_name ASC, salary DESC;

Önce bölüme göre artan, aynı bölümde maaşa göre azalan. ORDER BY olmadan satırların sırası garanti değildir. İlk n satır için SQLite ve MySQL LIMIT n, standart SQL FETCH FIRST n ROWS ONLY kullanır (SQLite ikincisini tanımaz).

Join: tabloları birleştirmek

Inner join ve üç yazım biçimi

EE öğrencilerinin aldığı dersler:

SELECT s.name, t.course_id, t.grade
FROM student s JOIN takes t ON s.stu_id = t.stu_id
WHERE s.dept_name = 'EE';

Aynı sonucu veren dört yazım (hepsi script’te çalıştırıldı):

FROM student s JOIN takes t ON s.stu_id = t.stu_id      -- ON: koşulu sen yazarsın
FROM student JOIN takes USING (stu_id)                  -- USING: bu sütunlarda eşitle
FROM student NATURAL JOIN takes                         -- aynı isimli TÜM sütunlar
FROM student, takes WHERE student.stu_id = takes.stu_id -- eski stil

Firat bu sonuçta yok. Inner join sadece eşi olan satırları tutar.

NATURAL JOIN tuzağı

student JOIN takes USING (stu_id) JOIN course USING (course_id) 14 satır verir, her kayıt bir kez. Ama:

SELECT name, course_id, dept_name FROM student NATURAL JOIN takes NATURAL JOIN course;

sadece 9 satır döndürür. Sebep: student ve course ikisinde de dept_name var. NATURAL JOIN onları da eşitler, sorgu sessizce “öğrencinin kendi bölümünden aldığı dersler” sorusuna dönüşür. Ada’nın MA101’i, Cem’in MA101 ve CS101’i, Defne’nin CS305’i ve Gizem’in MA202’si kaybolur.

Outer join

Inner join eşi olmayan satırları atar. Outer join onları tutar ve eksik tarafı NULL ile doldurur.

• LEFT JOIN: soldaki tüm satırlar.
• RIGHT JOIN: sağdaki tüm satırlar.
• FULL OUTER JOIN: iki taraftaki tüm satırlar.

Hiç ders almamış öğrenciler:

SELECT s.stu_id, s.name, t.course_id
FROM student s LEFT JOIN takes t ON s.stu_id = t.stu_id
WHERE t.course_id IS NULL;

Öğrenciler ve bölümleri, iki taraftaki yetimler dahil:

SELECT s.name, d.dept_name, d.building
FROM student s FULL OUTER JOIN department d ON s.dept_name = d.dept_name;

Gizem’in bölümü NULL olduğu için hiçbir bölümle eşleşmez. BIO’nun öğrencisi yok.

TUZAK · FULL OUTER JOIN her yerde yok. SQLite 3.39’dan (2022) önce RIGHT ve FULL join desteklemiyordu; bu sayfadaki sorgular 3.53’te çalıştırıldı. MySQL hâlâ FULL OUTER JOIN desteklemez. Orada LEFT JOIN sonucunu, sağdaki eşsiz satırlarla UNION yaparak aynı sonucu kurarsın.

Self join

Aynı tabloyu iki farklı takma adla kullanırsın. Aynı bölümde, diğerinden fazla kazanan hocalar:

SELECT a.name AS higher, b.name AS lower
FROM instructor a JOIN instructor b
  ON a.dept_name = b.dept_name AND a.salary > b.salary;

Takma ad (alias) zorunlu: instructor.salary > instructor.salary hangisinin hangisi olduğunu söyleyemez.

Sık yapılan hatalar

SELECT name, building
FROM student, department
WHERE budget > 300000;

SELECT name, course_id
FROM student NATURAL JOIN takes NATURAL JOIN course;

SELECT s.name, d.dept_name
FROM student s FULL OUTER JOIN department d
  ON s.dept_name = d.dept_name;