Senaryo: Toptan Satış Sistemi
Bu senaryoda toptan satış sisteminin veritabanı tasarlanacaktır . Sistemde kategoriler,
müşteriler , personeller ,sipariş detayı,sipariş ,tedarikçiler ve ürünler bulunmaktadır .
1. Tabloların Oluşturulması
Aşağıdaki tablolar oluşturulacaktır:
Müşteriler
Personeller
Siparişler
Sipariş Detayları
Katagoriler
Tedarikciler
Ürünler
2. Zor Seviye SQL Sorguları
Belirli bir tarih aralığında en çok sipariş edilen ürünleri listeleme.
Müşterinin en çok sipariş verdiği ürün/kategoriyi bulma.
Müşterilerin son siparişi ve toplam sipariş adedi (veya toplam ürünler )
3. Trigger Örnekleri
Stoktan ürün düşüren Trigger (Sipariş verildiğinde, SiparisDetay tablosuna kayıt
eklenince, ilgili ürünün Urunler tablosundaki stok miktarı otomatik azaltılır)
Sipariş verildiğinde sipariş tarihi yazan bir trigger.
4. Stored Procedure Örnekleri
Stok seviyesi belirli bir değerden az olan ürünleri listeleyen procedure
Bir tedarikçiden alınan tüm ürünleri listeleyen procedure
Siparişteki toplam ürün sayısını ve toplam fiyatı getiren procedure.
5. View Kullanımı
En çok sipariş verilen ürünler için bir view oluşturma.
Son 1 ayda oluşturlan siparişlerin müşteri bazlı özetini döndüren view.
1. Tabloların Oluşturulması
CREATE TABLE Kategoriler (
KategoriID INT NOT NULL PRIMARY KEY,
KategoriAdi VARCHAR(255)
);
CREATE TABLE Musteriler (
MusteriID INT NOT NULL PRIMARY KEY,
SirketAdi NVARCHAR(100) NOT NULL,
YetkiliAdi NVARCHAR(100),
Sehir NVARCHAR(50),
Ulke NVARCHAR(50),
Telefon NVARCHAR(20)
);
CREATE TABLE Personeller (
PersonelID INT NOT NULL PRIMARY KEY,
Ad NVARCHAR(50),
Soyad NVARCHAR(50),
Pozisyon NVARCHAR(100)
);
CREATE TABLE Tedarikciler (
TedarikciID INT NOT NULL PRIMARY KEY,
FirmaAdi VARCHAR(255),
IletisimAdi VARCHAR(255),
Sehir VARCHAR(100),
Ulke VARCHAR(100)
);
CREATE TABLE Urunler (
UrunID INT NOT NULL PRIMARY KEY,
UrunAdi NVARCHAR(100) NOT NULL,
BirimFiyati DECIMAL(10,2) NOT NULL,
StokMiktari INT,
KategoriID INT,
TedarikciID INT,
Barkod NVARCHAR(50),
CONSTRAINT UQ_Urunler_Barkod UNIQUE (Barkod),
FOREIGN KEY (TedarikciID) REFERENCES Tedarikciler(TedarikciID),
FOREIGN KEY (KategoriID) REFERENCES Kategoriler(KategoriID)
);
CREATE TABLE Siparisler (
SiparisID INT NOT NULL PRIMARY KEY,
MusteriID INT,
PersonelID INT,
SiparisTarihi DATE,
KargoUcreti DECIMAL(10,2),
FOREIGN KEY (MusteriID) REFERENCES Musteriler(MusteriID),
FOREIGN KEY (PersonelID) REFERENCES Personeller(PersonelID)
);
CREATE TABLE SiparisDetay (
SiparisDetayID INT NOT NULL PRIMARY KEY,
SiparisID INT,
UrunID INT,
Miktar INT,
BirimFiyat DECIMAL(10,2),
FOREIGN KEY (SiparisID) REFERENCES Siparisler(SiparisID),
FOREIGN KEY (UrunID) REFERENCES Urunler(UrunID)
);
Primary Key ve Unique Index Tanımlamaları:
Urunler Tablosu:
o Primary Key: UrunID (Her ürün sisteme benzersiz bir kimlik ile
kaydedilir.)
o Unique Index:Barkod (Aynı barkod numarasına sahip iki ürün
olamaz.)
o Musteriler Tablosu:
o Primary Key: MusteriID (Her müşteri sistemde tekil bir kimlikle
tanımlanır.)
Siparisler Tablosu:
o Primary Key: SiparisID (Her sipariş için sistemde benzersiz bir
kayıt oluşturulur.)
SiparisDetay Tablosu:
o Primary Key: SiparisDetayID (Her sipariş kalemi ayrı bir
kimliğe sahiptir.)
o Unique Index: SiparisID, UrunID (Aynı siparişte bir ürün sadece
bir kez yer alabilir. Aynı ürün ikinci kez eklenmek istenirse sistem
engeller.)
Tür Adı Kaynak Sütun Hedef
PK PK_SiparisDetayID [Link] —
FK FK_SiparisDetay_Siparisler [Link] Siparisler
FK FK_SiparisDetay_Urunler [Link] Urunler
PK PK_Siparisler [Link] —
FK FK_Siparisler_Musteriler [Link] Musteriler
FK FK_Siparisler_Personeller [Link] Personeller
PK PK_Urunler [Link] —
FK FK_Urunler_Tedarikciler [Link] Tedarikciler
UQ UQ_Urunler_Barkod [Link] —
2. Zor Seviye SQL Sorguları
1. Belirli bir tarih aralığında en çok satan ürünleri listeleme
SELECT [Link], SUM([Link]) AS ToplamSatisAdedi
FROM SiparisDetay sd
JOIN Urunler u ON [Link] = [Link]
JOIN Siparisler s ON [Link] = [Link]
WHERE [Link] BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY [Link]
ORDER BY ToplamSatisAdedi DESC;
2. Müşterinin en çok sipariş verdiği kategori:
SELECT [Link], [Link], SUM([Link] * [Link]) AS
ToplamHarcama
FROM Siparisler s
JOIN Musteriler m ON [Link] = [Link]
JOIN SiparisDetay sd ON [Link] = [Link]
JOIN Urunler u ON [Link] = [Link]
JOIN Kategoriler k ON [Link] = [Link]
WHERE [Link] = 1
GROUP BY [Link], [Link]
ORDER BY ToplamHarcama DESC;
3. Her müşterinin son siparişi ve toplam sipariş tutarı:
SELECT [Link], [Link], [Link], [Link],
SUM([Link] * [Link]) AS ToplamTutar
FROM Musteriler m
JOIN Siparisler s ON [Link] = [Link]
JOIN SiparisDetay sd ON [Link] = [Link]
JOIN Urunler u ON [Link] = [Link]
WHERE [Link] = (
SELECT MAX(SiparisTarihi)
FROM Siparisler
WHERE MusteriID = [Link]
)
GROUP BY [Link], [Link], [Link], [Link];
Tablo Bağlantılı Olduğu Bağlantı Türü
Urunler Kategoriler, Tedarikciler N–1
Siparisler Musteriler, Personeller N–1
SiparisDetay Siparisler, Urunler N–1
3. Trigger Örnekleri
-- 1. Sipariş verildiğinde stoğu azaltan trigger:
CREATE TRIGGER trg_StokDusur
ON SiparisDetay
AFTER INSERT
AS
BEGIN
UPDATE Urunler
SET StokMiktari = StokMiktari - [Link]
FROM Urunler u
INNER JOIN inserted i ON [Link] = [Link]
END;
-- 2. Sipariş verildiğinde sipariş tarihi yazan trigger:
CREATE TRIGGER trg_SiparisTarihiYaz
ON Siparisler
AFTER INSERT
AS
BEGIN
PRINT 'Yeni bir sipariş verildi. Tarih: ' + CONVERT(VARCHAR,
GETDATE(), 120)
END;
4. Stored Procedure Örnekleri
-- 1. Az stokta kalan ürünleri listeleyen procedure:
CREATE PROCEDURE AzStokUrunler
AS
BEGIN
SELECT UrunAdi, StokMiktari
FROM Urunler
WHERE StokMiktari < 20
END;
-- 2. Belirli tedarikçiden alınan ürünler:
CREATE PROCEDURE TedarikciUrunleri
@TedarikciID INT
AS
BEGIN
SELECT UrunAdi, BirimFiyati, StokMiktari
FROM Urunler
WHERE TedarikciID = @TedarikciID
END;
-- 3. Siparişteki toplam kalem ve toplam fiyat:
CREATE PROCEDURE SiparisOzet
@SiparisID INT
AS
BEGIN
SELECT COUNT(*) AS UrunSayisi,
SUM(Miktar * BirimFiyat) AS ToplamTutar
FROM SiparisDetay
WHERE SiparisID = @SiparisID
END;
5. View Kullanımı
-- 1. En çok sipariş edilen ürünler:
CREATE VIEW EnCokSiparisEdilenUrunler AS
SELECT [Link], SUM([Link]) AS ToplamAdet
FROM SiparisDetay sd
JOIN Urunler u ON [Link] = [Link]
GROUP BY [Link];
-- 2. Son 1 ayda oluşturulan siparişlerin müşteri bazlı özeti:
CREATE VIEW SonBirAySiparisOzet AS
SELECT [Link], [Link] AS MusteriAdi, [Link],
SUM([Link] * [Link]) AS SiparisTutari
FROM Siparisler s
JOIN Musteriler m ON [Link] = [Link]
JOIN SiparisDetay sd ON [Link] = [Link]
JOIN Urunler u ON [Link] = [Link]
WHERE [Link] >= DATEADD(MONTH, -1, GETDATE())
GROUP BY [Link], [Link], [Link];
SELECT [Link], SUM([Link]) AS TotalSold
FROM OrderDetails od
JOIN Products p ON [Link] = [Link]
GROUP BY [Link]
ORDER BY TotalSold DESC;