İleri SQL: GROUP BY, NULL, alt sorgular, DML ve view

Altıncı haftada tek tek satırları seçip birleştirdin. Bu hafta satırları özetliyoruz (kaç tane, ortalama ne), sorgunun içine sorgu koyuyoruz ve veriyi değiştiriyoruz. Vize SQL bölümündeki zor sorular neredeyse hep şu üç şeyden birine dayanır: GROUP BY/HAVING ayrımı, NULL’un garip davranışı ve “her X’i yapanlar” (bölme) sorusu.

Altın kurallar:

1. WHERE satırı, HAVING grubu süzer. Aggregate fonksiyon WHERE içine giremez.
2. NULL ile karşılaştırma TRUE ya da FALSE değil, UNKNOWN verir. WHERE sadece TRUE olan satırları tutar.
3. NOT IN ve NULL birlikte zehirlidir. Alt sorguda bir NULL varsa NOT IN hiçbir satır döndürmez.
4. “Her/tüm (every/all)” geçen soru bölme sorusudur. Çift NOT EXISTS ile yaz.
5. COUNT(*) satır sayar, COUNT(sütun) NULL olmayan değer sayar.

Örnek veritabanı 6. haftadakiyle aynı (department, instructor, student, course, teaches, takes).

Aggregate fonksiyonlar

Beş temel aggregate: COUNT, SUM, AVG, MIN, MAX. Bir sütundaki çok değeri tek değere indirirler.

SELECT COUNT(*), COUNT(grade), COUNT(DISTINCT stu_id) FROM takes;

14 kayıt var. Biri NULL notlu (Cem, CS101), o yüzden COUNT(grade) 13. Ders alan farklı öğrenci 6 (Firat yok).

SELECT SUM(salary), AVG(salary), MIN(salary), MAX(salary) FROM instructor;

SQLite’ta AVG her zaman REAL döndürür (typeof = real), SUM tam sayı sütunda INTEGER kalır. Bu yüzden AVG(credits) 3.4285714285714284 verir. Ama 7/2 hâlâ 3: sadece AVG kendiliğinden ondalığa geçer.

Boş bir kümede COUNT 0, diğerleri NULL döner: WHERE salary > 100000 ile COUNT(*), SUM(salary), AVG(salary) sonucu 0, NULL, NULL.

GROUP BY ve HAVING

GROUP BY satırları ortak değere göre gruplara ayırır, aggregate her gruba ayrı uygulanır.

SELECT dept_name, COUNT(*) AS n, AVG(salary) AS avg_sal
FROM instructor GROUP BY dept_name;

Gruplara koşul koymak için HAVING:

SELECT dept_name, AVG(salary) AS avg_sal
FROM instructor GROUP BY dept_name HAVING AVG(salary) > 80000;

WHERE ile HAVING birlikte

“20’den fazla kredisi olan öğrencilerden en az 2 tanesi bulunan bölümler”:

SELECT dept_name, COUNT(*) AS n
FROM student
WHERE tot_cred > 20
GROUP BY dept_name
HAVING COUNT(*) >= 2;

Adım adım: WHERE önce Ece (15), Firat (0) ve Gizem’i (10) atar. Kalanlar gruplanır: CS 2 (Ada, Bora), EE 1 (Cem), MATH 1 (Defne). HAVING sadece CS’yi bırakır.

FROM student          7 satır
WHERE tot_cred > 20   4 satır: Ada, Bora, Cem, Defne
GROUP BY dept_name    CS{Ada,Bora}  EE{Cem}  MATH{Defne}
HAVING COUNT(*) >= 2  CS
SELECT                CS, 2

TUZAK · gruplanmamış sütun. Standart SQL’de SELECT listesinde aggregate dışındaki her sütun GROUP BYda olmalı. SELECT dept_name, name, MAX(salary) FROM instructor GROUP BY dept_name PostgreSQL’de hata verir. SQLite hata vermez ve name için gruptan bir satır seçer (MAX ile kullanılınca en yüksek maaşlının adı: Aksoy, Kaya, Celik, Yildiz). Sınavda bu sorguyu hatalı say.

Sıfırları saymak

Her dersin öğrenci sayısı, hiç öğrencisi olmayanlar dahil:

SELECT c.course_id, COUNT(t.stu_id) AS enrolled
FROM course c LEFT JOIN takes t ON c.course_id = t.course_id
GROUP BY c.course_id;

COUNT(*) yazsaydın BI100 için 1 çıkardı, çünkü LEFT JOIN onu (BI100, NULL, NULL) satırıyla getirir.

NULL ve üç değerli mantık

NULL “bilinmiyor” ya da “yok” demek. Aritmetikte bulaşıcıdır: 5 + NULL = NULL. Karşılaştırmada ise sonuç UNKNOWN olur: NULL = NULL bile UNKNOWN.

OR için: TRUE OR UNKNOWN = TRUE, FALSE OR UNKNOWN = UNKNOWN. NOT UNKNOWN = UNKNOWN. SQLite’ta SELECT NULL = NULL, NULL AND 0, NULL OR 1, NULL AND 1, NOT NULL sonucu NULL, 0, 1, NULL, NULL (0 = FALSE, 1 = TRUE).

Sonuçlar:

• WHERE dept_name = NULL hiçbir satır döndürmez. Doğrusu WHERE dept_name IS NULL → Gizem.
• WHERE dept_name <> 'CS' 3 satır döndürür (Cem, Defne, Firat). Gizem gelmez: NULL <> 'CS' UNKNOWN.
• WHERE grade = 'A' OR grade <> 'A' 14 değil 13 satır verir. “Her şey” gibi görünen koşul NULL’u kaçırır.
• GROUP BY ise NULL’ları bir grup sayar: GROUP BY dept_name öğrencilerde NULL 1, CS 3, EE 2, MATH 1 verir.

Alt sorgular (subqueries)

IN

I1’in (Aksoy) verdiği bir dersi alan öğrenciler:

SELECT name FROM student
WHERE stu_id IN (SELECT t.stu_id FROM takes t JOIN teaches te ON t.course_id = te.course_id
                 WHERE te.inst_id = 'I1');

Sonuç: Ada, Bora, Cem, Defne, Ece. Aksoy CS101 ve CS305 veriyor; CS101’i S1, S2, S3, S5, CS305’i S1, S4 almış. IN tekrar üretmez, Ada bir kez gelir.

NOT IN tuzağı ve NOT EXISTS

Öğrencisi olmayan bölümleri arayalım:

SELECT dept_name FROM department
WHERE dept_name NOT IN (SELECT dept_name FROM student);

Sonuç boş. Alt sorgu Gizem’in NULL’unu da döndürür. 'BIO' NOT IN (CS, EE, MATH, NULL) aslında 'BIO' <> 'CS' AND ... AND 'BIO' <> NULL demek ve son terim UNKNOWN. Doğrusu:

SELECT d.dept_name FROM department d
WHERE NOT EXISTS (SELECT * FROM student s WHERE s.dept_name = d.dept_name);

TUZAK · NOT IN + NULL. Alt sorgu tek bir NULL bile döndürürse NOT IN hiçbir zaman TRUE olmaz. Ya NOT EXISTS kullan ya da alt sorguya WHERE ... IS NOT NULL ekle. IN için böyle bir sorun yok.

EXISTS ve korelasyonlu alt sorgu

EXISTS (alt sorgu) alt sorgu en az bir satır döndürürse TRUE. Alt sorgu dıştaki satıra referans veriyorsa korelasyonlu (correlated) denir; dıştaki her satır için yeniden değerlendirilir.

SELECT c.course_id FROM course c
WHERE EXISTS (SELECT * FROM takes t WHERE t.course_id = c.course_id AND t.grade = 'A');

Sonuç: CS101, CS305, EE210, MA101, MA202 (en az bir A notu verilmiş dersler).

Kendi bölüm ortalamasından fazla kazananlar:

SELECT i.name, i.salary FROM instructor i
WHERE i.salary > (SELECT AVG(i2.salary) FROM instructor i2 WHERE i2.dept_name = i.dept_name);

Skaler alt sorgu

Tek satır, tek sütun döndüren alt sorgu bir değer gibi kullanılabilir, SELECT listesinde bile:

SELECT d.dept_name,
       (SELECT COUNT(*) FROM instructor i WHERE i.dept_name = d.dept_name) AS n_inst
FROM department d;

Sonuç: BIO 1, CS 2, EE 1, MATH 2. FROM içinde de alt sorgu olur: SELECT ... FROM (SELECT dept_name, AVG(salary) AS avg_sal FROM instructor GROUP BY dept_name) WHERE avg_sal > 80000 HAVING örneğiyle aynı sonucu verir.

ALL ve SOME

Standart SQL’de x > ALL (küme) “kümedeki her elemandan büyük”, x > SOME (küme) (ya da ANY) “en az birinden büyük” demek.

TUZAK · SQLite’ta ALL/SOME yok. > ALL (...) SQLite’ta syntax error verir. Sınavda standart yazımı bil, SQLite’ta MAX/MIN ile yaz. Celik 72000, MATH’teki 70000’den büyük ama 72000’den (kendisinden) büyük değil: SOME’da var, ALL’da yok.

Bölme: “hepsini alan” öğrenciler

İlişkisel cebirdeki $\div$ işleminin SQL’de doğrudan karşılığı yok. “Bütün CS derslerini alan öğrenciler” sorusunu “alınmamış CS dersi olmayan öğrenciler” diye çevir:

SELECT s.name FROM student s
WHERE NOT EXISTS (
  SELECT c.course_id FROM course c WHERE c.dept_name = 'CS'
  EXCEPT
  SELECT t.course_id FROM takes t WHERE t.stu_id = s.stu_id);

Her öğrenci için “CS dersleri eksi onun aldıkları” hesaplanır. Küme boşsa öğrenci hepsini almış. Aynı sorgu çift NOT EXISTS ile de yazılır (sorular bölümünde MATH örneği var).

Küme işlemleri

SELECT course_id FROM takes WHERE stu_id = 'S1'
INTERSECT
SELECT course_id FROM takes WHERE stu_id = 'S2';

Sonuç: CS101, CS201. EXCEPT ile çevirirsen (S1 eksi S2) CS305, MA101 gelir. SELECT course_id FROM course EXCEPT SELECT course_id FROM takes hiç alınmamış dersi verir: BI100.

SELECT dept_name FROM instructor UNION SELECT dept_name FROM student 5 satır döndürür (NULL, BIO, CS, EE, MATH); UNION ALL ile 6 + 7 = 13. İki tarafın sütun sayısı ve tipleri uyumlu olmalı. Standart SQL’de INTERSECT ALL ve EXCEPT ALL da var, SQLite’ta yok.

Veri değiştirme: INSERT, UPDATE, DELETE

Her örnek taze bir veritabanı kopyasında çalıştırıldı.

INSERT INTO course (course_id, title, dept_name, credits)
VALUES ('CS410', 'Machine Learning', 'CS', 3);

-- 30+ kredili CS öğrencilerini yeni derse kaydet
INSERT INTO takes (stu_id, course_id, grade)
SELECT stu_id, 'CS410', NULL FROM student WHERE dept_name = 'CS' AND tot_cred >= 30;

İkinci ifade S1 ve S2 için iki satır ekler.

UPDATE instructor SET salary = salary * 1.05 WHERE salary < 75000;

Sahin 73500, Celik 75600, Yildiz 63000 olur, diğerleri değişmez. İki farklı kural varsa tek ifadede CASE kullan; iki ayrı UPDATE yazarsan birincinin yükselttiği satır ikincinin koşuluna girebilir:

UPDATE instructor
SET salary = CASE WHEN salary < 80000 THEN salary + 5000 ELSE salary + 2000 END;

Sonuç: Aksoy 97000, Demir 82000, Kaya 87000, Sahin 75000, Celik 77000, Yildiz 65000.

DELETE FROM takes WHERE grade = 'F';                     -- 14 -> 13 satır
DELETE FROM takes
WHERE stu_id IN (SELECT stu_id FROM student WHERE tot_cred < 20);   -- 13 -> 12

DELETE FROM instructor WHERE salary < (SELECT AVG(salary) FROM instructor) ilginç: silme sırasında ortalama değişir mi? Hayır. SQL alt sorguyu silmeden önce bir kez hesaplar (77000) ve sonra siler. FK kontrolü kapalı bir kopyada Sahin, Celik, Yildiz silinir. FK açıkken ise ifade reddedilir, çünkü Sahin ve Celik teaches tablosunda ders veriyor.

View

View, adı olan kayıtlı bir sorgudur. Veri kopyalamaz; her kullanıldığında tanımı yeniden çalışır.

CREATE VIEW cs_roster AS
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 = 'CS';

SELECT course_id, COUNT(*) AS n FROM cs_roster GROUP BY course_id;

takes tablosuna yeni bir CS öğrencisi kaydı eklenince view kendiliğinden 7’den 8 satıra çıkar. View’lar güvenlik (kullanıcıya sadece bazı sütunları göstermek) ve karmaşık sorguyu basitleştirmek için kullanılır. View üzerinden INSERT her sistemde mümkün değil; SQLite cannot modify cs_roster because it is a view der.

Sık yapılan hatalar

SELECT COUNT(*), COUNT(grade)
FROM takes
WHERE course_id = 'CS101';

SELECT dept_name FROM department
WHERE dept_name NOT IN (SELECT dept_name FROM student);

SELECT name FROM instructor
WHERE salary > ALL (SELECT salary FROM instructor
                    WHERE dept_name = 'MATH');

UPDATE student
SET tot_cred = tot_cred + 5
WHERE stu_id IN (SELECT stu_id FROM takes WHERE grade = 'A');

SELECT dept_name
FROM instructor
WHERE AVG(salary) > 75000
GROUP BY dept_name;