Notes

USE master
GO
CREATE DATABASE AdemOkumusDB
GO
USE AdemOkumusDB
GO


CREATE TABLE tblMarkalar
(
marka_id INT PRIMARY KEY IDENTITY,
marka_ad NVARCHAR(50)
)

CREATE TABLE tblKategoriler
(
kat_id INT PRIMARY KEY IDENTITY,
kat_ad NVARCHAR(50)
)

CREATE TABLE tblUrunler
(
urun_id INT PRIMARY KEY IDENTITY,
urun_ad NVARCHAR(50),
marka_id INT NULL REFERENCES tblMarkalar(marka_id) ON DELETE SET NULL,
kat_id INT NULL REFERENCES tblKategoriler(kat_id) ON DELETE SET NULL,
stok_adet INT,
fiyat MONEY
)

CREATE TABLE tblPersoneller
(
per_id INT PRIMARY KEY IDENTITY,
per_ad NVARCHAR(50),
per_soyad NVARCHAR(50)
)

CREATE TABLE tblSatislar(
satis_id INT PRIMARY KEY IDENTITY,
per_id INT NULL REFERENCES tblPersoneller(per_id) ON DELETE SET NULL,
tarih_saat DATETIME DEFAULT GETDATE()
)

CREATE TABLE tblSatisDetaylar
(
detay_id INT PRIMARY KEY IDENTITY,
satis_id INT REFERENCES tblSatislar(satis_id) ON DELETE CASCADE,
urun_id INT NULL REFERENCES tblUrunler(urun_id) ON DELETE SET NULL,
adet INT,
tutar MONEY
)

INSERT INTO tblMarkalar VALUES('Marka 1')
INSERT INTO tblMarkalar VALUES('Marka 2')
INSERT INTO tblMarkalar VALUES('Marka 3')
INSERT INTO tblMarkalar VALUES('Marka 4')
INSERT INTO tblMarkalar VALUES('Marka 5')

INSERT INTO tblKategoriler VALUES('Kategori 1')
INSERT INTO tblKategoriler VALUES('Kategori 2')
INSERT INTO tblKategoriler VALUES('Kategori 3')
INSERT INTO tblKategoriler VALUES('Kategori 4')
INSERT INTO tblKategoriler VALUES('Kategori 5')

INSERT INTO tblPersoneller VALUES('Kemalettin', 'Kamiloğlu')
INSERT INTO tblPersoneller VALUES('Muhittin', 'Sarı')
INSERT INTO tblPersoneller VALUES('Hayrettin', 'Lacivert')

INSERT INTO tblUrunler VALUES('Ürün 1', 1, 1, 100, 15)
INSERT INTO tblUrunler VALUES('Ürün 2', 2, 1, 175, 25)
INSERT INTO tblUrunler VALUES('Ürün 3', 3, 4, 82, 100)
INSERT INTO tblUrunler VALUES('Ürün 4', 1, 5, 122, 24)
INSERT INTO tblUrunler VALUES('Ürün 5', 5, 2, 133, 23)
INSERT INTO tblUrunler VALUES('Ürün 6', 4, 2, 152, 18)
INSERT INTO tblUrunler VALUES('Ürün 7', 3, 4, 187, 12)
INSERT INTO tblUrunler VALUES('Ürün 8', 2, 5, 134, 5)
INSERT INTO tblUrunler VALUES('Ürün 9', 2, 3, 157, 80)
INSERT INTO tblUrunler VALUES('Ürün 10', 3, 5, 110, 35)
INSERT INTO tblUrunler VALUES('Ürün 11', 4, 4, 90, 84)
INSERT INTO tblUrunler VALUES('Ürün 12', 1, 3, 25, 70)
INSERT INTO tblUrunler VALUES('Ürün 13', 2, 1, 27, 20)
INSERT INTO tblUrunler VALUES('Ürün 14', 4, 2, 18, 150)
INSERT INTO tblUrunler VALUES('Ürün 15', 1, 2, 78, 124)


INSERT INTO tblSatislar VALUES(1, GETDATE())
INSERT INTO tblSatislar VALUES(2, GETDATE())
INSERT INTO tblSatislar VALUES(1, GETDATE())
INSERT INTO tblSatislar VALUES(3, GETDATE())
INSERT INTO tblSatislar VALUES(3, GETDATE())
INSERT INTO tblSatislar VALUES(2, GETDATE())

INSERT INTO tblSatisDetaylar VALUES(1, 1, 2, 30)
INSERT INTO tblSatisDetaylar VALUES(1, 2, 1, 25)
INSERT INTO tblSatisDetaylar VALUES(1, 6, 2, 36)
INSERT INTO tblSatisDetaylar VALUES(2, 15, 1, 124)
INSERT INTO tblSatisDetaylar VALUES(2, 7, 4, 48)
INSERT INTO tblSatisDetaylar VALUES(2, 8, 2, 10)
INSERT INTO tblSatisDetaylar VALUES(3, 1, 2, 30)
INSERT INTO tblSatisDetaylar VALUES(3, 3, 2, 200)
INSERT INTO tblSatisDetaylar VALUES(3, 11, 1, 84)
INSERT INTO tblSatisDetaylar VALUES(3, 14, 1, 150)
INSERT INTO tblSatisDetaylar VALUES(3, 5, 2, 46)
INSERT INTO tblSatisDetaylar VALUES(4, 4, 2, 48)
INSERT INTO tblSatisDetaylar VALUES(4, 9, 1, 80)
INSERT INTO tblSatisDetaylar VALUES(4, 12, 2, 140)
INSERT INTO tblSatisDetaylar VALUES(5, 10, 2, 70)
INSERT INTO tblSatisDetaylar VALUES(5, 3, 1, 100)
INSERT INTO tblSatisDetaylar VALUES(5, 5, 1, 23)
INSERT INTO tblSatisDetaylar VALUES(5, 6, 2, 36)
INSERT INTO tblSatisDetaylar VALUES(6, 1, 2, 30)
INSERT INTO tblSatisDetaylar VALUES(6, 4, 1, 24)
INSERT INTO tblSatisDetaylar VALUES(6, 9, 1, 80)
INSERT INTO tblSatisDetaylar VALUES(6, 15, 1, 124)

SELECT * FROM tblMarkalar
SELECT * FROM tblKategoriler
SELECT * FROM tblPersoneller
SELECT * FROM tblUrunler
SELECT * FROM tblSatislar
SELECT * FROM tblSatisDetaylar

-- 3. SORU
SELECT u.urun_id,
u.urun_ad,
u.kat_id,
k.kat_ad,
u.marka_id,
m.marka_ad,
u.stok_adet,
u.fiyat
FROM tblUrunler u
JOIN tblMarkalar m ON u.marka_id = m.marka_id
JOIN tblKategoriler k ON u.kat_id = k.kat_id

-- 4. SORU
SELECT u.urun_id,
u.urun_ad,
u.kat_id,
k.kat_ad,
u.marka_id,
m.marka_ad,
u.stok_adet,
u.fiyat
FROM tblUrunler u
JOIN tblMarkalar m ON u.marka_id = m.marka_id
JOIN tblKategoriler k ON u.kat_id = k.kat_id
WHERE urun_id NOT IN (SELECT DISTINCT urun_id FROM tblSatisDetaylar)

--**************************************************
--5. Soru
SELECT urun_id,
urun_ad,
(SELECT SUM(adet) FROM tblSatisDetaylar WHERE urun_id = u.urun_id) AS satis_adet
FROM tblUrunler u

--5. Soru
SELECT urun_id,
urun_ad,
(SELECT SUM(adet) FROM tblSatisDetaylar GROUP BY urun_id HAVING urun_id = u.urun_id) AS satis_adet
FROM tblUrunler u

--5. Soru
SELECT u.urun_id,
u.urun_ad,
tbl.satis_adet
FROM tblUrunler u
JOIN (SELECT urun_id, SUM(adet) AS satis_adet FROM tblSatisDetaylar GROUP BY urun_id) tbl ON tbl.urun_id = u.urun_id

--***********************************
--6. Soru
SELECT kat_id,
kat_ad,
(SELECT COUNT(*) FROM tblUrunler WHERE kat_id = k.kat_id) AS urun_adet
FROM tblKategoriler k

--6. Soru
SELECT kat_id,
kat_ad,
(SELECT COUNT(*) FROM tblUrunler GROUP BY kat_id HAVING kat_id = k.kat_id) AS urun_adet
FROM tblKategoriler k

--6. Soru
SELECT k.kat_id,
k.kat_ad,
tbl.urun_adet
FROM tblKategoriler k
JOIN (
SELECT kat_id,
COUNT(*) AS urun_adet
FROM tblUrunler
GROUP BY kat_id
) tbl ON tbl.kat_id = k.kat_id

--*************************************************
--7. Soru
SELECT s.satis_id,
s.per_id,
p.per_ad,
p.per_soyad,
s.tarih_saat,
(SELECT SUM(tutar) FROM tblSatisDetaylar WHERE satis_id = s.satis_id) AS satis_tutar
FROM tblSatislar s
JOIN tblPersoneller p ON s.per_id = p.per_id

--8. Soru
SELECT tbl.per_id,
tbl.per_ad,
tbl.per_soyad,
SUM(tbl.satis_tutar) AS toplam_tutar
FROM (
SELECT s.satis_id,
s.per_id,
p.per_ad,
p.per_soyad,
s.tarih_saat,
(SELECT SUM(tutar) FROM tblSatisDetaylar WHERE satis_id = s.satis_id) AS satis_tutar
FROM tblSatislar s
JOIN tblPersoneller p ON s.per_id = p.per_id
) tbl
GROUP BY per_id, per_ad, per_soyad



--8. Soru
SELECT per_id,
per_ad,
per_soyad,
SUM(top_tutar) as toplam_tutar
FROM
(
SELECT s.per_id,
p.per_ad,
p.per_soyad,
tbl.top_tutar
FROM tblSatislar s
JOIN (
SELECT d.satis_id,
SUM(tutar) AS top_tutar
FROM tblSatisDetaylar d
GROUP BY d.satis_id
) tbl ON tbl.satis_id = s.satis_id
JOIN tblPersoneller p ON p.per_id = s.per_id
) asd
GROUP BY per_id, per_ad, per_soyad