Kütüphane Veritabanı: Kitaplar ve Öğrenciler İçin SQL Çözümleri
Merhaba arkadaşlar! Bugün size Veritabanı-2 dersinin final uygulama sınavından çıktıktan sonra sınav anında neler yaşadığımı ve soruları nasıl çözdüğümü anlatmak istiyorum. Veritabanı-2 Uygulama Final Sınavında, Hocamız bize basit bir kütüphane veritabanı tasarımı verdi ve bu veritabanı hakkında çeşitli sorular ve veritabanı ile ilgili uygulamalı sorular yer alıyordu. Hadi gelin, SQL Veritabanı sınavında hangi sorularla karşılaştığımı ve veritabanı sorularına nasıl yanıtlar verdildiğine birlikte bakalım!!
Hocamız bize Görseldeki Microsoft Access de yapılmış kütüphane veri tabanı örneğini verdi ve bizden bu veritabanını, Microsoft SQL Server için hazırlamamızı ve verdiği sorulara göre SQL sorguları, Fonksiyonlar, stored procedure’lar, trigger’lar (tetikleyiciler) ve karmaşık sorgular gibi veritabanı işlemleri yapmamızı istedi.. O zaman hadi başlayalım!!
i.Kütüphane için SQL Veritabanı Oluşturma
İlk olarak, kütüphane veritabanımı oluşturmakla başladım. “ CREATE DATABASE ” kodunu kullanarak “KutuphaneDB” adında bir kütüphane veritabanını oluşturdum:
CREATE DATABASE KutuphaneDB;Sonrasında hocamızın verdiği AccessDB şemasını SQL’ de sütunlara çevirdim.
CREATE TABLE islem (
islemno INT,
ogrno INT,
kitapno INT,
atarih DATE,
vtarih DATE
);
CREATE TABLE ogrenci (
ogrno INT,
ograd VARCHAR(50),
ogrsoyad VARCHAR(50),
cinsiyet VARCHAR(10),
dtarih DATE,
sinif VARCHAR(20)
);
CREATE TABLE kitap (
kitapno INT,
isbnno VARCHAR(20),
kitapadi VARCHAR(100),
yazarno INT,
turno INT,
sayfasayisi INT,
puan INT
);
CREATE TABLE yazar (
yazarno INT,
yazarad VARCHAR(50),
yazarsoyad VARCHAR(50)
);
CREATE TABLE tur (
turno INT,
turadi VARCHAR(50)
);Artık kütüphane için SQL veritabanının tabloları hazır olduğuna göre, veritabanına örnek veriler eklemeye ve kütüphane için hazırlanmış SQL veritabanında sorgular yapmaya başlayabiliriz.
ii.Kütüphane veritabanına örnek veri girişi
-- İşlem tablosuna veri ekleme
INSERT INTO islem (islemno, ogrno, kitapno, atarih, vtarih)
VALUES (1, 101, 201, '2023-06-01', '2023-06-10'),
(2, 102, 202, '2023-06-02', '2023-06-12'),
(3, 103, 203, '2023-06-03', '2023-06-13'),
(4, 104, 204, '2023-06-04', '2023-06-14'),
(5, 105, 205, '2023-06-05', '2023-06-15'),
(6, 106, 206, '2023-06-06', '2023-06-16'),
(7, 107, 207, '2023-06-07', '2023-06-17');
-- Öğrenci tablosuna veri ekleme
INSERT INTO ogrenci (ogrno, ograd, ogrsoyad, cinsiyet, dtarih, sinif)
VALUES (101, 'Ahmet', 'Yılmaz', 'Erkek', '2002-01-10', '12. Sınıf'),
(102, 'Ayşe', 'Kaya', 'Kadın', '2003-03-15', '11. Sınıf'),
(103, 'Mehmet', 'Demir', 'Erkek', '2002-05-20', '12. Sınıf'),
(104, 'Zeynep', 'Aksoy', 'Kadın', '2003-07-25', '11. Sınıf'),
(105, 'Emre', 'Yıldız', 'Erkek', '2002-09-30', '12. Sınıf'),
(106, 'Selin', 'Arslan', 'Kadın', '2003-11-05', '11. Sınıf'),
(107, 'Murat', 'Kara', 'Erkek', '2002-12-10', '12. Sınıf');
-- Kitap tablosuna veri ekleme
INSERT INTO kitap (kitapno, isbnno, kitapadi, yazarno, turno, sayfasayisi, puan)
VALUES (201, '9786052980001', 'Kırmızı Pazartesi', 1, 1, 300, 8),
(202, '9789750723843', 'İstanbul Hatırası', 2, 2, 400, 9),
(203, '9789753638013', 'Sineklerin Tanrısı', 3, 1, 350, 7),
(204, '9789750725236', 'Kürk Mantolu Madonna', 4, 2, 250, 8),
(205, '9789753638044', '1984', 5, 1, 320, 9),
(206, '9786053753468', 'Cingöz Recai', 6, 2, 280, 7),
(207, '9789750738588', 'Dönüşüm', 7, 1, 200, 8);
-- Yazar tablosuna veri ekleme
INSERT INTO yazar (yazarno, yazarad, yazarsoyad)
VALUES (1, 'Ahmet', 'Ümit'),
(2, 'Orhan', 'Pamuk'),
(3, 'William', 'Golding'),
(4, 'Sabahattin', 'Ali'),
(5, 'George', 'Orwell'),
(6, 'Peyami', 'Safa'),
(7, 'Franz', 'Kafka');
-- Tür tablosuna veri ekleme
INSERT INTO tur (turno, turadi)
VALUES (1, 'Roman'),
(2, 'Matematik'),
(3, 'Bilim Kurgu'),
(4, 'Tarih'),
(5, 'Psikoloji'),
(6, 'polisiye'),
(7, 'Klasik');Soru 1: İşlem Süresi Hesaplama Fonksiyonu
İlk soru, verilen bir işlem numarasına göre kitabın kaç gün öğrencide kaldığını hesaplayan bir SQL fonksiyonunu yazmaktı. Bu soruyu çözmek için aşağıdaki SQL kodunu kullandım:
CREATE FUNCTION hesapla_kitap_kalma_suresi(@islemno INT)
RETURNS INT
AS
BEGIN
DECLARE @atarih DATE;
DECLARE @vtarih DATE;
DECLARE @kalma_suresi INT;
SELECT @atarih = atarih, @vtarih = vtarih
FROM islem
WHERE islemno = @islemno;
SET @kalma_suresi = DATEDIFF(DAY, @atarih, @vtarih);
RETURN @kalma_suresi;
END;Yukarıdaki SQL kodunda, hesapla_kitap_kalma_suresi adında bir fonksiyon tanımlanmıştır. Bu fonksiyon, verilen işlem numarasına göre ilgili işlemde kitabın öğrencide kaç gün kaldığını hesaplar. İşlem tablosundan alış tarihini (atarih) ve iade tarihini (vtarih) çeker ve bu tarihler arasındaki gün farkını hesaplar. Elde edilen sonuç, @kalma_suresi değişkenine atanır ve fonksiyon tarafından döndürülür.
Bu SQL fonksiyonunu kullanarak, örneğin aşağıdaki gibi bir sorgu ile belirli bir işlem numarasına göre kitabın öğrencide kaldığı gün sayısını elde edilebilir:
SELECT hesapla_kitap_kalma_suresi(1) AS kalinan_gun;Soru 2: Matematik Türünde Kitap Alan Öğrencilerin Adını Listeleme
İkinci soru, kütüphaneden “Matematik” türünde kitap alan öğrencilerin adını listeleyen bir SQL sorgusunu yazmaktı. Bu soruyu çözmek için aşağıdaki SQL kodunu kullandım:
SELECT o.ograd
FROM ogrenci o
INNER JOIN islem i ON o.ogrno = i.ogrno
INNER JOIN kitap k ON i.kitapno = k.kitapno
INNER JOIN tur t ON k.turno = t.turno
WHERE t.turadi = 'Matematik';Yukarıdaki sorgu, ogrenci tablosunu islem, kitap ve tur tablolarıyla birleştirir. tur tablosunda "Matematik" türüne karşılık gelen turadi değerini filtreler ve bu türe sahip olan kitapların, ilgili öğrencilerin adlarını (ograd) listeleyerek sonuç döndürür.
Soru 3: Yeni Yazar Ekleme İşlemi
Üçüncü soru, yeni bir yazarın bilgilerini veritabanına kaydetmek için kullanılacak basit bir stored procedure yazmaktı. Bu soruyu çözmek için aşağıdaki SQL kodunu kullandım:
CREATE PROCEDURE sp_YazarEkle
@yazarad NVARCHAR(50),
@yazarsoyad NVARCHAR(50)
AS
BEGIN
INSERT INTO yazar (yazarad, yazarsoyad)
VALUES (@yazarad, @yazarsoyad);
END;Yukarıdaki stored procedure (sp_YazarEkle), yazar tablosuna yeni bir yazar eklemek için kullanılır. @yazarad ve @yazarsoyad parametreleri, yeni yazarın adı ve soyadını alır. Stored procedure içinde INSERT INTO ifadesiyle yazar tablosuna yeni bir kayıt eklenir.
Bu stored procedure’ı kullanarak yeni bir yazarı veritabanına kaydetmek için aşağıdaki gibi bir çağrı yapabilirsiniz:
EXEC sp_YazarEkle 'Yeni Yazar Adı', 'Yeni Yazar Soyadı';Soru 4: Otomatik Alış Tarihi Güncelleme Triggerı
Dördüncü soru, öğrenci bir kitap ödünç aldığında otomatik olarak “atarih” yani alış tarihine güncel zaman bilgisi girilmesi gereken bir trigger oluşturmaktı. Bu soruyu çözmek için aşağıdaki SQL kodunu kullandım:
CREATE TRIGGER tr_ogrenci_kitap_alis
ON islem
AFTER INSERT
AS
BEGIN
UPDATE islem
SET atarih = GETDATE()
WHERE islemno IN (SELECT islemno FROM inserted);
END;Bu trigger, bir öğrenci kitap ödünç aldığında otomatik olarak alış tarihini güncelleyerek istenen işlemi gerçekleştiriyor.
Soru 5: “Peyami Safa” İsimli Yazarın Kitaplarının Adını Listeleme
Beşinci soru, “Server Bedi” isimli yazarın kitaplarının adını listeleyen bir SQL sorgusunu yazmaktı. Bu soruyu çözmek için aşağıdaki SQL kodunu kullandım:
SELECT k.kitapadi
FROM kitap k
INNER JOIN yazar y ON k.yazarno = y.yazarno
WHERE y.yazarad = 'Peyami' AND y.yazarsoyad = 'Safa';Bu sorgu, “Peyami Safa” isimli yazarın kitaplarının adını vererek istenen sonucu veriyor. Bu komutun amacı, yazar adı “Peyami”, yazar soyadı “Safa” olan yazarın, kitap adını yazdırmaktır.
Yukarıdaki sorgu, kitap tablosunu yazar tablosuyla birleştirir ve yazarın adını (yazarad) ve soyadını (yazarsoyad) kullanarak "Peyami Safa" nın kitaplarını filtreler. Sonuç olarak, yazarın kitaplarının adlarını (kitapadi) listeleyerek döndürür.
Bu sorguyu çalıştırdığınızda, “Peyami Safa” nın kitaplarının adlarını elde edebilirsiniz.
Soru 6: Einstein’ın Kitaplarını Alan Öğrencilerin Adını ve Soyadını Listeleme
Altıncı soru, “Einstein” adına veya soyadına sahip yazarların kitaplarını alan öğrencilerin adını ve soyadını listeleyen bir SQL sorgusunu yazmaktı. Bu soruyu çözmek için aşağıdaki SQL kodunu kullandım:
SELECT o.ograd, o.ogrsoyad
FROM ogrenci o
INNER JOIN islem i ON o.ogrno = i.ogrno
INNER JOIN kitap k ON k.kitapno = i.kitapno
INNER JOIN yazar y ON k.yazarno = y.yazarno
WHERE y.yazarad = 'Einstein' OR y.yazarsoyad = 'Einstein';Bu sorgu, “Einstein” adına veya soyadına sahip yazarların kitaplarını alan öğrencilerin adını ve soyadını listeleyerek istenen sonucu veriyor.
Daha uzunca anlatırsam, Bu SQL sorgusu, ogrenci tablosunu islem, kitap ve yazar tablolarıyla birleştirir. Kitapların yazarını temsil eden yazar tablosu üzerinden "Einstein" ismini veya soyadını filtreler. Bu şekilde "Einstein" ismini veya soyadını taşıyan yazarlara ait kitapları alan öğrencilerin adını (ograd) ve soyadını (ogrsoyad) listeleyerek sonuç döndürür.
Soru 7: Erkek ve kız Öğrenci Sayılarını Listeleyen Sorgu
Yedinci ve son soru, kaç tane erkek ve kaç tane kadın öğrencinin olduğunu aynı tabloda listeleyen bir SQL sorgusunu yazmaktı. Bu soruyu çözmek için aşağıdaki SQL kodunu kullandım:
SELECT cinsiyet, COUNT(*) AS sayi
FROM ogrenci
GROUP BY cinsiyet;Bu SQL sorgusu, ogrenci tablosunu kullanarak kaç tane erkek ve kaç tane kız öğrenci olduğunu listeleyen bir sorgudur. cinsiyet sütununu gruplayarak her bir cinsiyet için toplam öğrenci sayısını COUNT(*) fonksiyonuyla hesaplar.
Soru 8: En Popüler Yazarları Listeleyen SQL Sorgusu (ilk 5 yazar)
Veritabanındaki yazarlar arasında en yüksek puana sahip olan ve en fazla ödünç alınan kitapları listeleyen bir sorgu.
SELECT y.yazarad, y.yazarsoyad, COUNT(*) AS kitap_sayisi
FROM kitap k
INNER JOIN yazar y ON k.yazarno = y.yazarno
GROUP BY y.yazarad, y.yazarsoyad
ORDER BY COUNT(*) DESC
LIMIT 5;Yukarıdaki sorgu, kitap ve yazar tablolarını birleştirerek kitap sayısına göre en çok kitap yazan ilk 5 yazarı listeleyecektir. Yazar adı, yazar soyadı ve kitap sayısı bilgilerini içeren sonuçları kitap sayısına göre azalan sırada sıralar.
Soru 9: En Son Hangi Kitap Öğrenci Tarafından İade Edildi?
En son hangi kitabın hangi öğrenci tarafından iade edildiğini bulmak için bir sorgu.
SELECT k.kitapadi
FROM kitap k
INNER JOIN islem i ON i.kitapno = k.kitapno
WHERE i.vtarih = (
SELECT MAX(vtarih)
FROM islem
);Yukarıdaki SQL sorgusu, kitap ve islem tablolarını birleştirerek en son iade edilen kitabın adını listeler. İlgili tabloları kitapno sütunu üzerinden birleştirir ve i.vtarih alanının maksimum değeriyle eşleşen kaydı seçer.
Bu sorguyu veritabanınıza uygulayarak en son hangi kitabın öğrenci tarafından iade edildiğini bulunur.
Soru 10: Hangi Öğrenci En Fazla Kitap Ödünç Almış?
Hangi öğrencinin kütüphaneden en fazla kitap ödünç aldığını bulmak için bir sorgu.
SELECT o.ograd, o.ogrsoyad, COUNT(*) AS kitap_sayisi
FROM ogrenci o
INNER JOIN islem i ON i.ogrno = o.ogrno
GROUP BY o.ograd, o.ogrsoyad
HAVING COUNT(*) = (
SELECT MAX(kitap_sayisi)
FROM (
SELECT COUNT(*) AS kitap_sayisi
FROM islem
GROUP BY ogrno
) AS t
);Yukarıdaki SQL sorgusu, ogrenci ve islem tablolarını birleştirerek en fazla kitap ödünç alan öğrencinin adını ve soyadını listeler. İlgili tabloları ogrno sütunu üzerinden birleştirir ve öğrencilere ait kitap sayısını COUNT(*) fonksiyonuyla hesaplar. Ardından HAVING koşuluyla en yüksek kitap sayısına sahip öğrenciyi filtreler.
Sınav boyunca kütüphane veritabanıyla ilgili çeşitli soruları çözdüm ve veritabanı işlemlerini gerçekleştirdim.Açıkçası hiç de kolay değildi. Önce fonksiyonu kafamda oluşturmam sonra komuta çevirmem gerekiyordu.. Fonksiyonlar, stored procedure’lar, trigger’lar ve karmaşık sorgular kullandım. Veritabanı-2 dersi final uygulama sınavının iyi geçtiğini söyleyebilirim.
Bu deneyim, kütüphane veritabanlarının nasıl oluşturulduğunu, veri ilişkilerini nasıl kurduğumuzu ve veri sorgulama işlemlerini nasıl gerçekleştirdiğimizi daha iyi anlamamı sağladı.
Kütüphane Veritabanı Yönetimi: SQL Fonksiyonları ve Trigger’lar” “Basit Kütüphane Veritabanı Oluşturma ve Yönetme”
Bu makalenin, Veritabanı-2 dersi final uygulama sınavına hazırlanan veya konuyla ilgilenen diğer öğrencilere yardımcı olacağını umuyorum. SQL sorgularınızı her zaman dikkatlice kontrol etmeyi ve veritabanı yönetimiyle ilgili yeni bilgiler öğrenmeyi unutmayın. Bu süreçte başarılar dilerim!
ileri okuma (MS Access Sorgu Örnekleri)
Eğer Makalemi beğendiyseniz bir alkış bırakın ve beni Medium ve Github da takip edin!!
