F.Murat ALTINIŞIK

Bunun için IlkDB adlı database’i restore ediniz.
http://blog.fethimurat.com/wp-content/uploads/2010/08/JoinDB.zip

-- kategoriler tablosu ve urunler tablosu
select * from Kategoriler
select * from Urunler

-- tüm kategorileri listeleyin eğer varsa ürünlerinin adlarını da listeleyin

select k.KategoriAdi,u.UrunAdi
from Kategoriler k
left join Urunler u on k.KategoriID=u.KategoriID

-- tüm ürünleri ve varsa dahil oldukları kategorilerinin adlarını da getirin
select u.UrunAdi,k.KategoriAdi
from Kategoriler k
right join Urunler u on u.KategoriID=k.KategoriID

-- tedarikciler tablosu oluşturalım
create table Tedarikciler
(
   TedarikciID int primary key identity(1,1),
   FirmaAdi varchar(50)
)
go

insert into Tedarikciler values('TedA')
insert into Tedarikciler values('TedB')
insert into Tedarikciler values('TedC')

alter table Urunler
add TedarikciID int references Tedarikciler (TedarikciID)

select * from Tedarikciler
select * from Urunler

-- kategorisi olmayan ürünün tedarikcisi verildi
update Urunler set TedarikciID=1
where KategoriID is null

-- br ürün daha tedarikcisiyle birlikte kullanılacak
update Urunler set TedarikciID=3
where UrunAdi like '%ace%'

select * from Urunler

-- tüm tedarikcileri varsa getirdikleri ürünlerin adlarını ve varsa o ürünlerin dahil olduğu kategori adlarını listeleyin
select t.FirmaAdi,u.UrunAdi,k.KategoriAdi
from Tedarikciler t
left join Urunler u on u.TedarikciID=t.TedarikciID
left join Kategoriler k on k.KategoriID=u.KategoriID

-- urunleri ve kategori isimlerini getirirken varsa urunlerin tedarikci firmasını da getirin
select k.KategoriAdi,u.UrunAdi,t.FirmaAdi
from Kategoriler k
join Urunler u on u.KategoriID=k.KategoriID
left join Tedarikciler t on t.TedarikciID=u.TedarikciID

-- tüm kategorileri ve tüm ürünleri getirin eğer varsa ürünlerin tedarikci firmasını da getirin
-- full join
select k.KategoriAdi,u.UrunAdi,t.FirmaAdi
from Kategoriler k full join
Urunler u on u.KategoriID=k.KategoriID
left outer join Tedarikciler t on t.TedarikciID=u.TedarikciID

-- tüm eşleşen urunleri ve urunleri getiren tedarikcilerin adları yanında tüm kategorileri getirin
select u.UrunAdi,t.FirmaAdi,k.KategoriAdi
from Urunler u
join Tedarikciler t on t.TedarikciID=u.TedarikciID
full join Kategoriler k on k.KategoriID=u.KategoriID

--  eğer tüm tedarikciler tüm ürünleri getirebiliyor olsaydı tüm secenekleri listeleyelim
select u.UrunAdi,t.FirmaAdi
from Tedarikciler t
cross join Urunler u

Northwind

--------------JOIN-----------------
--  Inner Join
--  Sadece eşleşen verileri getirir

use master
go

create  database JoinDB
go

use JoinDB
go

create table Kategoriler
(
   KategoriID int primary key identity(1,1),
   KategoriAdi varchar(20)
)
go

create table Urunler
(
   UrunID int primary key identity(1,1),
   UrunAdi varchar(50),
   -- references ile foreign key oluşturuldu
   KategoriID int references Kategoriler(KategoriID),
   BirimFiyat decimal(6,2)
)

-- kategori ekleyelim
insert into Kategoriler
values('Gıda')

insert into Kategoriler(KategoriAdi)
values('Temizlik')

insert into Kategoriler
values('Kozmetik')

-- kategorileri listeleyelim
select * from Kategoriler

-- tüm identity ve veri bilgilrini siler sıfır tablo oluşturur
truncate table Kategoriler

insert into Urunler
values('Tahıl',1,12.3)

insert into Urunler
(UrunAdi,KategoriID,BirimFiyat)
values('Ace',2,6.7)

insert into Urunler
(UrunAdi,BirimFiyat)
values('Parfüm',107.89)

select * from Urunler
select * from Kategoriler

-- tüm kategorileri ve ürünlerini listeleyin...
-- on dan sonra yazılan eşlemenin sağda solda olmasının  hiçbir yerde önemi yok.
-- Sadece inner join de tablo sırasının önemi yok. Outer join de tablo sırası önemli olacak
select u.UrunAdi,k.KategoriAdi
from Urunler as u
inner join Kategoriler k
on u.KategoriID=k.KategoriID

-- Northwind den hangi ürünü hangi tedarikçi getiriyor listeleyin
-- ddl lerde go kullanıyoruz.
-- dml lerde gerek yok
use Northwind
go

select s.CompanyName,p.ProductName
from Suppliers s
join Products p
on p.SupplierID=s.SupplierID

-- hangi şirket hangi tarihte sipariş vermiş
-- Customers(Müşteriler)
-- Orders (Siparişler)
-- CustomerID
select c.CompanyName,o.OrderDate
from Orders o
join Customers c on o.CustomerID=c.CustomerID

-- hangi kategorideki hangi urunu hangi tedarikci getiriyormuş???
-- categories, products, suppliers
-- tablo join tablo on bilmem ne join tablo on bilmem ne
select p.ProductName 'Ürün Adı',
s.CompanyName 'Tedarikçi',
c.CategoryName 'Kategori'
from Products p
join Categories c on c.CategoryID=p.CategoryID
join Suppliers s on s.SupplierID=p.SupplierID

-- hangi çalışanım hangi müşteriden hangi tarihte hangi üründen kac tane sipariş almış?
select p.ProductName,
o.OrderDate,
c.CompanyName,
e.FirstName+' '+e.LastName as eleman,
od.Quantity
from Orders o
join Employees e on e.EmployeeID=o.EmployeeID
join Customers c on c.CustomerID=o.CustomerID
join [Order Details] od on od.OrderID=o.OrderID
join Products p on p.ProductID=od.ProductID

-- her çalışanın adını soyadını tek kolon olarak, bu çalışanın yaşını ve aldığı toplam sipariş sayısını listeleyiniz...
-- Employees, Orders
select e.FirstName+' '+e.LastName,
COUNT(o.OrderID) as 'Sipariş Sayısı',
DATEDIFF(YYYY,BirthDate,GETDATE()) as Yas
from Employees e
join Orders o on o.EmployeeID=e.EmployeeID
group by e.FirstName+' '+e.LastName,DATEDIFF(YYYY,BirthDate,GETDATE())

-- her kategorinin adını o kategorideki en fazla para kazandıran ürünün adını ve kazandırdığı miktarı listeleyin
/*
    Gıda : ekmek 4  ve turşu  8
    temizlik : ace 67  rinso  5

    cıktı;

    Gıda      turşu 8
    temizlik  ace   67
*/
select * from
(
   select c.CategoryName,p.ProductName,
   SUM(od.UnitPrice*od.Quantity) as Toplam
   from Categories c
   join Products p on p.CategoryID=c.CategoryID
   join [Order Details] od on p.ProductID=od.ProductID
   group by c.CategoryName,p.ProductName
) as t
where Toplam=
(
   select MAX(Toplam) from
   (
      select c.CategoryName,p.ProductName,
      SUM(od.UnitPrice*od.Quantity) as Toplam
      from Categories c
      join Products p on p.CategoryID=c.CategoryID
      join [Order Details] od on p.ProductID=od.          ProductID
      group by c.CategoryName,p.ProductName
   ) as tt
   where tt.CategoryName=t.CategoryName
)

--hangi kategorideki(CategotyName) hangi üründen(ProductName) hangi tarihte(OrderDate) hangi müşteriye(CompanyName) hangi çalışan(FirstName,LastName) tarafından kaç tane(Quantity) satılmıştır?
select c.CategoryName,
p.ProductName,
o.OrderDate,
od.Quantity,e.FirstName+' '+e.LastName,
cu.CompanyName
from
Categories c
join Products p on p.CategoryID=c.CategoryID
join [Order Details] od on od.ProductID=p.ProductID
join Orders o on o.OrderID=od.OrderID
join Employees e on e.EmployeeID=o.EmployeeID
join Customers cu on  cu.CustomerID=o.CustomerID

Northwind database devam

-- çalışanların ID değerlerini ve o çalışanın toplamda aldığı sipariş sayısını hesaplayın...
select EmployeeID,
COUNT(OrderID) as 'Aldığı Sipariş Sayısı'
from Orders
group by EmployeeID

-- her müşterinin ID değerlerini ve verdikleri sipariş sayılarını listeleyin
-- Orders siparişler tablosu
-- CustomerID müşteri ID değeri
select CustomerID,COUNT(OrderID)
from Orders
group by CustomerID

-- Her siparişte toplamda ne kadar para kazanmışız?
select OrderID, SUM(UnitPrice*Quantity)
from [Order Details]
group by OrderID

-- her ürünün ID değerini ve bu ürünlerin satışlarından ne kadar para kazandığımızı listeleyin
select ProductID, SUM(UnitPrice*Quantity)
from [Order Details]
group by ProductID

-- her kategoride toplam kaç ürün var?
-- CategoryID ve ürün sayısı kolonları
-- Products tablosu
select CategoryID,COUNT(ProductID)
from Products
group by CategoryID

-- ürün sayısı 10 dan fazla olan kategorilerin ID değerlerini ve ürün sayılarını listeleyin
-- having => group by ın "where" dir.
select CategoryID,COUNT(ProductID)
from Products
group by CategoryID
having COUNT(ProductID)>10

/*select kolonlar
  from tablolar
  where şartlar
  group by kolonlar
  having sartlar
  order by kolonlar
*/

-- toplam satılan miktarı adet bazlı olarak 50 den fazla olan ürünlerin ID değerlerini ve satılan miktarlarını listeleyin
select ProductID,SUM(Quantity)
from [Order Details]
group by ProductID
having SUM(Quantity)>50

-- Birim fiyatı 15 den fazla olan ürünler arasında toplam kazandırdığı para 2000 den büyük olan ürünlerin ID değerlerini ve kazandırdıkları miktarı listeleyin
-- [Order Details] tablosu
-- UnitPrice => birim fiyat
-- Quantity => miktar
select ProductID,SUM(UnitPrice*Quantity)
from [Order Details]
where UnitPrice>15
group by ProductID
having SUM(UnitPrice*Quantity)>2000

-- en fazla sipariş veren müşterilerin ID değerlerini ve sipariş sayılarını listeleyin
select CustomerID,COUNT(OrderID)
from Orders
group by CustomerID
having COUNT(OrderID)=
(
    select MAX(Deger) from
    (
       select COUNT(OrderID) as Deger
       from Orders
       group by CustomerID
    )as t
)

-- en fazla ürüne sahip kategorilerin ID değerleri ve içerdikleri ürün sayılarını listeleyin
-- products tablosu
select CategoryID,COUNT(ProductID)
from Products
group by CategoryID
having COUNT(ProductID)=
(
    select MAX(Deger) from
    (
       select COUNT(ProductID) as Deger
       from Products
       group by CategoryID
    )as t
)

Bu konu Northwind database ile ilgili olduğundan dolayı database’i indirip restore etmeyi unutmayınız.
http://blog.fethimurat.com/wp-content/uploads/2010/08/NorthWind.zip

-- Employees tablosundan calışanların adlarını(FirstName) soyadlarını(LastName) ve yaşlarını listeleyin

select FirstName,LastName,
DATEDIFF(YYYY,BirthDate,GETDATE())
from Employees

select FirstName,LastName,
YEAR(GETDATE())-YEAR(BirthDate) as Yas
from Employees
order by Yas asc-- 0 dan 100000...
                -- a dan z ye

select FirstName,LastName,
YEAR(GETDATE())-YEAR(BirthDate) as Yas
from Employees
order by Yas desc,FirstName asc

-- en eski 3 personeli getirin
select top 3 FirstName,LastName,HireDate
from Employees
order by HireDate -- default olarak asc dir

-- en yeni 4 personeli getirin
select top 4 FirstName,LastName,HireDate
from Employees
order by HireDate desc

-- en yaşlı çalışanın adını soyadını ve yaşını getirin
select top 1 FirstName,LastName,
YEAR(GETDATE())-YEAR(BirthDate) as Yas
from Employees
order by Yas desc

-- Margaret ile aynı tarihte doğmuş bir personel ekleyelim
insert into Employees(FirstName,LastName,BirthDate)
values('Yunus','Ardıc',
  (
    select BirthDate from Employees where FirstName ='Margaret'
  )
)

select * from Employees order by BirthDate asc

-- en yaşlı çalışanların adlarını, soyadlarını ve yaslarını getirin
select FirstName,LastName,DATEDIFF(YYYY,BirthDate,GETDATE()) as Yas
from Employees
where DATEDIFF(YYYY,BirthDate,GETDATE())=
(
   select Top 1 DATEDIFF(YYYY,BirthDate,GETDATE()) as   Yas from Employees order by Yas desc
)

-- hangi ülkelerle iş yapıyoruz
select distinct Country
from Customers 

-- Ödev 1) Tost seven çalışanların adı soyadı ve not bilgilerini getirin
-- Employees tablosu
-- Yunus a özel: O verinin uzunluğunu ve kacıncı indekste yazıldığını bulacak "toast"
-- Ödev 2) Beverages adlı kategorideki ürünlerin adlarını getirin
-- Categories tablosundaki CategoryName kolonu. Products tablosu ProductName kolonu
-- Ödev 3) Mayumi's isimli tedarikci(Supplier) ın getirdiği ürünlerin(Product) adlarını(ProductName) getirin 

-- Aggreagte Function
-- max,min,avg,sum,count
--> max => en büyük değer
--> min => en küçük değer
--> avg => ortalama
--> sum(hücre değerlerinin toplamı)
--> count null olmayan satır sayısı
/*
    ID   Ad Yas  Hede
    ------------------
    1    a   12  NULL
    2    b   13  Veri

    => max(Yas) 13
    => min(Yas) 12
    => avg(yas)  12.5
    => avg(ID)    1.5
    => count(ID)  2
    => count(Hede) 1
    => sum(Yas)   25
*/

-- birim fiyatı (UnitPrice) en pahalı olan ürünün(Products) birim fiyatını getirin
select MAX(UnitPrice)
from Products

-- bu zamana kadar toplam kaç sipariş(Orders tablosu) almışım?
select COUNT(OrderID) as SS from Orders

-- toplam kaç çalışanım var?
select COUNT(EmployeeID) from
Employees

-- Toplam kaç çalışanım var ve bu çalışanlarımın adını gösterin?
select FirstName, COUNT(EmployeeID)
from Employees

-- Listedeki ürünlerin(products tablosu) ortalama birim fiyatını (UnitPrice) gösterin
select AVG(UnitPrice) from Products

select Top 10 * from [Order Details]

-- sipariş başına ortalama ne kadar para kazanmışım?
select SUM(UnitPrice*Quantity)/ COUNT(distinct OrderID)
from [Order Details]

-- Ödev 4) Northwind de CategoryuName değeri NULL olan kayıtların CategoryName ini MULL olarak gösterecek sorguyu yazınız

Bu yazı ile ilgili SirketDB dosyasını indirip Restore etmeyi unutmayınız.
http://blog.fethimurat.com/wp-content/uploads/2010/08/SirketDB.zip

-- dml (data manuplation language)
-- insert => ekle
-- delete => siler
-- upate  => güncelle
-- select => okumak

-- departman ekleyelim (yorum satırı)
insert into Departmanlar(Departman)
values('Departman1')

-- personel ekleyelim
insert into Personeller
(Ad,Soyad,KayitTarihi,Maas,DepartmanID)
values('Omer','Behçet','10.10.2010',47.54,1)

select * from Personeller

-- sadece ad ve soyad kaydedelim
insert into Personeller(Soyad,Ad)
values ('kabze','hasan')

-- maas değeri olmayan kayıtların
-- maaşını 3 yapalım(değer ataması)
-- veri güncellemesi
update Personeller set Maas=3
where Maas is null

-- bugün başlayıp yarın bitecek ve tanımı "yalandan proje" olacak bir proje kaydı ekleyiniz...
-- lütfen projeler tablosuna üstteki istek için bir insert yazınız
insert into Projeler
values('yalandan proje',GETDATE(),DATEADD(DD,1,GETDATE()))

select * from Projeler

-- ProjeID değeri 1 olan projenin tanımını "BilgeAdam projesi" olarak değiştirin
update Projeler
set ProjeTanimi='BilgeAdam Projesi'
where ProjeID=1

select * from Personeller

-- Personeller tablosunda herhangi bir departmana bağlı olmayan personelleri 1 nolü departmana bağlayınız
update Personeller
set DepartmanID=1
where DepartmanID is null

-- Ad değeri Om ile başlayan personelleri silin
-- format : delete from tablo (varsa) where Falan=deger

delete from Personeller
where Ad like 'om%'

select * from Projeler
select * from Personeller

-- Soyadı ka ile başlayan personelin adını getirelm
select Ad from Personeller
where Soyad like 'ka%'

-- departmanID si 1 olan personelin adını ve soyadını getirin
select Ad,soyad from Personeller
where DepartmanID=1

-- Maaşı 500 ile 3000 arasında olan personellerin Adını ve soyadını tek kolon olarak ve maaşını getirin
select Ad+' '+Soyad, Maas from Personeller
--where Maas>500 and Maas<3000
where Maas between 500 and 3000

-- personeller tablosunda adının 2. harfi a olan personellerin adını ve personelID sini getirin
select
cast(PersonelID as varchar(3))+' '+Ad as 'Personel'
from Personeller
where Ad like '_a%'

-- Maaşı 10000 den büyük olan ve departmanID değeri 3 olan personelin adını maaşını ve departmanID değerini getirin
select Ad,Maas,DepartmanID from Personeller
where Maas>10000 and DepartmanID=3

-- yazilim departmanındaki çalışanların adını ve soyadını listeleyin
select Ad,Soyad from Personeller
where DepartmanID=
(
   select DepartmanID from Departmanlar
   where Departman ='Yazilim'
)

-- Proje Tanımım Bil ile başlayan projeye ismi il ile başlayan personeli atayın
insert into Personel_Proje
values
(
   (
     select ProjeID from Projeler where
     ProjeTanimi like 'Bil%'
   )
   ,
   (
      select PersonelID from Personeller where
      Ad like 'il%'
   )
)

-- Proje tanımı içinde "ada" geçen projelerin proje tanımlarını ve başlangıc tarihlerini ve toplam kaç gün sürdüğünü(datediff) gösterin
select ProjeTanimi,BaslangicTarihi,BitisTarihi,DATEDIFF(dd,BaslangicTarihi,BitisTarihi)
from Projeler