CRUD şu anlama gelir:

• Create
• Read
• Update
• Delete

Read kısmına zaten çok hakimsin: SELECT. Şimdi C, R ve D’yi pratik edelim!

İşleri basit tutmak ve SQL ifadelerinin kendisine odaklanabilmek için, basit bir SQLite veritabanı kullanacağız ve bunu online bir ortamda çalıştıracağız. Sadece şunu bil: aynı işlemleri herhangi bir SQL veritabanında da yapabilirsin.

Ayrıca bu komutları Python kodu üzerinden de, SQLite için sqlite3, birçok farklı veritabanı sistemi için SQLAlchemy veya Google BigQuery için google-cloud-bigquery gibi kütüphanelerle çalıştırabilirsin.

Bu göreve boş bir veritabanı ile başlayacaksın. Amacın, Workintech öğrencileriyle ilgili temel bilgileri saklamak için kapsamlı bir veritabanı oluşturmak amacıyla temel CRUD işlemlerini kullanmak.

Bu veritabanı, öğrencilerin first_name, last_name ve birth_city bilgilerini saklayan students adında bir tablodan oluşmalıdır. Daha sonra öğrencilerimizin katıldığı bootcamp’leri takip etmek için ikinci bir tablo ekleyeceğiz.

• SQLite Online sitesine git. Kayıt olmana gerek yok, her şeyi ücretsiz özelliklerle yapabilirsin.

  

• + butonuna, ardından Create SQLite DB seçeneğine tıkla.

  

• Veritabanının adını school olarak belirle.

  

• Bir sorguyu Run etmek istediğinde sonucu görmek için yeşil butona tıklaman gerekecek.

  

Şu an için veritabanının boş olduğunu görmelisin. Henüz hiç tablo yok.

1. Uygun SQL sorgusunu kullanarak tabloyu oluştur.

   Dikkatli ol! SQL sözdizimi, kullandığın ortama göre küçük farklılıklar gösterebilir. İnternette yardım ararken SQLite kullandığını belirtmeyi unutma!

   💡 İpucu

   CREATE TABLE anahtar kelimesini kullanabilirsin.

   Ayrıca tablo için bir Primary Key gerekir; bu, her öğrenci için benzersiz bir tanımlayıcıdır. Adını id koy. integer olmalı ve autoincrement özelliğine sahip olmalı: her öğrenci eklediğinde önceki sayıdan bir büyük yeni bir numara otomatik atanmalıdır.

   Sakladığımız değerlerin not null olduğunu da belirtmelisin: tam adı olmayan veya doğum şehri olmayan bir öğrenci olamaz.

   Çözüm

   CREATE TABLE students (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     first_name TEXT NOT NULL,
     last_name TEXT NOT NULL,
     birth_city TEXT NOT NULL
   );

   
   

1. Bir öğrencinin eklenmesini istemek için hangi alanlar sağlanmalıdır?

   💡 İpucu

   Sağlamamız gereken sadece 3 alan var. Toplam 4 alanımız var. Atlayabileceğimiz bir alan var.

   Çözüm

   first_name
   last_name
   birth_city

   Yeni bir öğrenci oluştururken veritabanı motoru yeni kayıt için id değerini otomatik üretir. Tabloyu oluştururken id sütunu için AUTOINCREMENT belirtmemizin nedeni budur.

   
   

2. Şimdi kendini veritabanına öğrenci olarak ekle!

   💡 İpucu

   INSERT INTO

   Çözüm

   INSERT INTO students (first_name, last_name, birth_city)
   VALUES ('your_first_name', 'your_last_name', 'your_birth_city');

   
   

3. Kaydın tabloda gerçekten oluşturulduğunu kontrol et 🧐

   Çözüm

   SELECT * FROM students;

   
   

4. Şimdi veritabanına birkaç öğrenci daha ekle:

   • Brüksel doğumlu Rick Broadhurst
   • Paris doğumlu Shirley Mayer

   Bunu tek bir INSERT ifadesiyle yapabilirsin.

   Çözüm

   INSERT INTO students (first_name, last_name, birth_city)
   VALUES
   ('Rick', 'Broadhurst', 'Brussels'),
   ('Shirley', 'Mayer', 'Paris')
   ;

   
   

Şimdiye kadar öğrencilerimizi tanımladık. Artık hangi bootcamp’e(ler)e katıldıklarını da takip etmek istiyoruz. Bunun için yeni bir tablo oluşturacağız: student_bootcamps. Bunu neden yaparız?

Görevin: hangi öğrencinin hangi bootcamp’e katıldığını takip etmek için student_bootcamps adında yeni bir tablo oluştur.

• Shirley "Data Analytics" ve "Data Science & AI" bootcamp’lerine katıldı.
• Rick "Data Science & AI" ve "Data Engineering" bootcamp’lerine katıldı.

CREATE TABLE student_bootcamps (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  student_id INTEGER NOT NULL,
  bootcamp TEXT NOT NULL
);

INSERT INTO student_bootcamps (student_id, bootcamp)
VALUES
  (1, 'Data Science & AI'),
  (2, 'Data Analytics'),
  (2, 'Data Science & AI'),
  (3, 'Data Science & AI'),
  (3, 'Data Engineering')
;

Şimdiye kadar harika iş çıkardık: tüm ilgili bilgileri saklamak için tablolar oluşturduk. Şimdi verimiz üzerinde bazı analitik işlemleri yapabilmek istiyoruz: öğrencilerimiz kaç bootcamp’e katıldı?

students tablosundan öğrenci kimlik verilerine, student_bootcamps tablosundan ise bootcamp bilgisine (sayım) ihtiyacımız olacak.

Aşağıdakileri elde etmek için iki tabloyu birleştiren bir SELECT ifadesi yaz:

• first_name,
• last_name,
• birth_city
• bootcamp_count.

SELECT students.first_name,
       students.last_name,
       COUNT(DISTINCT(student_bootcamps.bootcamp)) AS bootcamp_count
  FROM students
  JOIN student_bootcamps
    ON students.id = student_bootcamps.student_id
 GROUP BY students.id
;

Az önce harika ama karmaşık bir sorgu yazdık. İki operasyonel tablodan (students ve student_bootcamps) başlayarak analitik içgörüler ürettik. Fakat bu karmaşık sorguyu her seferinde yazmak istemiyoruz.

Sonucu saklamak için students_fidelity adında yeni bir tablo oluşturalım.

CREATE TABLE name AS
SELECT ...;

CREATE TABLE students_fidelity AS
SELECT students.first_name,
       students.last_name,
       students.birth_city,
       COUNT(DISTINCT(student_bootcamps.bootcamp)) AS bootcamp_count
  FROM students
  JOIN student_bootcamps
    ON students.id = student_bootcamps.student_id
 GROUP BY students.id
;

Rick Broadhurst, Workintech yönetimiyle iletişime geçti. Başvurusunda bir hata yapmış: Brüksel’de yaşıyor, ama Londra’da doğmuş.

Bu hatayı düzeltmek için Workintech yöneticisinin kaydı güncellemesine yardımcı olabilir misin?

UPDATE

UPDATE students
   SET birth_city = 'London'
 WHERE first_name = 'Rick'
   AND last_name = 'Broadhurst'
 ;

SELECT id
  FROM students
 WHERE first_name = 'Rick'
   AND last_name = 'Broadhurst'
;

UPDATE students
   SET birth_city = 'London'
 WHERE id = id_retrieved_by_previous_read_query
;

SELECT COUNT(*)
  FROM students
 WHERE first_name = 'Rick'
   AND last_name = 'Broadhurst'
;

Shirley Mayer de Workintech Admin ile iletişime geçti. Eğitime katılamayacak.

Workintech Admin’in onun kaydını veritabanından kaldırmasına yardımcı olabilir misin?

DELETE FROM

DELETE FROM students
 WHERE first_name = 'Shirley'
   AND last_name = 'Mayer'
;

DELETE FROM students
 WHERE id = id_retrieved_by_previous_read_query
;

Tamam. Veritabanımız düzeltildi. Her şey yolunda, değil mi?

students_fidelity adında bir tablo oluşturduğumuzu hatırla. Az önce kaynak tablolarda değişiklik yaptık. Bu değişikliklerin students_fidelity tablosuna yansıyıp yansımadığını kontrol et. Sence neden böyle?

CREATE TABLE students_fidelity AS SELECT ... sorgusunu tekrar çalıştır. Tablo zaten var olduğu için hata mesajı alacaksın ve SQL, üzerine yanlışlıkla yazmamızı engeller.

Bunu nasıl çözeriz? Bazı SQL dialektleri CREATE OR REPLACE TABLE students_fidelity AS SELECT ... gibi bir şeyi destekler. Bu ifade tam olarak dediğini yapar: ya tabloyu oluşturur ya da var olanın yerine koyar.

SQLite bunu desteklemiyor. Bunu bir alternatifle nasıl çözebileceğini bir dakika düşün.

Burada hatırlaman gereken önemli nokta, bu tür durumlarda bir tablo oluşturduğunda verinin otomatik güncellenmediğidir.

Düzenli güncellemeler için ne yapabiliriz? Tabloyu güncelleyen ifadeyi düzenli aralıklarla çalıştırabiliriz. Bunu farklı şekillerde yapabiliriz:

• Sorguyu makinemizde belirli aralıklarda zamanlamak için bir cron job kullanmak; örneğin her sabah 06:00’da veya daha sık güncellemeler için her saat.
• Aynı şeyi yapmak için BigQuery’nin "scheduled queries" gibi özelliklerini kullanmak.
• Apache Airflow gibi orchestration araçlarını kullanmak.

Ya da tamamen farklı bir yaklaşım benimseyebiliriz: views kullanmak. View, temelde adı atanmış bir sorgudur. View’i oluşturduktan sonra onu, tablo gibi, normal bir select sorgusunda kullanabilirsin.

Ancak bir view, tabloyla aynı şekilde çalışmaz: view veriyi değil, veriyi getiren sorguyu saklar. Dolayısıyla view’i her sorguladığında sorgu çalıştırılır ve en güncel veriyi alırsın. Bunun bir maliyeti vardır: view’i her sorguladığında tüm hesaplamalar yeniden hesaplanır. Use case’ine göre view veya table tercih edebilirsin.

Böyle bir view’i nasıl oluşturursun? Tablo oluşturmaya çok benzer:

CREATE VIEW student_fidelity AS SELECT ...;

Tablo mevcut olduğu sürece bu çalışmaz, bu yüzden önce tabloyu silmen gerekir. (Tehlikeli bölge! Bu işlem onay istemeden tabloyu tamamen siler.)

DROP TABLE student_fidelity;

Artık view’i oluşturabilirsin.

Bu view’in nasıl çalıştığını görelim:

1. SELECT * FROM students_fidelity; çalıştır.
2. Kaynak tablolarda bir şeyi değiştir (bir öğrencinin adı, bir bootcamp eklemek, ...).
3. 1. adımdaki sorguyu tekrar çalıştır.
4. Değişikliğin students_fidelity içinde anında yansıdığını gözlemle!

Sadece şunu unutma: bunun bir maliyeti var. View’i her sorguladığında tüm hesaplamalar yeniden çalışır. Bunu, daha önce hesaplanmış olanı tekrar kullandığın (ama şu anda güncel olmayabilecek) bir tabloyu sorgulamakla karşılaştır.

🚀 Tebrikler! SQL’de tüm CRUD işlemlerini pratik ettin ve tables ile views hakkında bilgi öğrendin!