Kutuphane Veritabanı Örneği

1. Veritabanı Oluşturulur

CREATE DATABASE KutuphaneDB;

2. Tablolar oluşturulur

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)
);

Ö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: “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 5: 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 6: 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 7: 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 8: 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 9: 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.