sql distinct join

select productID,ProductName , UnitPrice

from products

where UnitPrice > all

(select UnitPrice from products where categoryID=2)

select * from products

select distinct(ProductID) from [order details]

select OD.*,P.CategoryID

from [order details] OD inner join Products P

on OD.ProductID = P.ProductID

where P.CategoryID=2 and OD.OrderID between 10250 and 10300

select OD.*

from [order details] OD join (select ProductID from Products where categoryID=2) as P

on OD.ProductID = P.ProductID

where OD.OrderID between 10250 and 10300

declare @sayi int

–set @sayi = select count(*) from products where UnitsInStock < 5

select @sayi = count(*) from products where UnitsInStock < 5

select @sayi

if @sayi > 0

begin

select * from products where UnitsInStock < 5

end

else

select ‘Kritik bir durum yok’

———————————————————————

if(select count(*) from products where UnitsInStock < 5) > 0

begin

select * from products where UnitsInStock < 5

end

else

select ‘Kritik bir durum yok’

———————————————————————-

if exists (select top 1 ProductID from products where UnitsInStock < 5)

begin

select * from products where UnitsInStock < 5

end

else

select ‘Kritik bir durum yok’

select ProductID,ProductName

from products

where exists (

select P.ProductID

from products p left outer join [order details] OD

on p.productID = OD.ProductID

where OD.ProductID is null

)

declare @max money

select @max = max(unitprice)

from products

where categoryID = 3

select @max

select * from products

where unitprice > @max

— kategori id 3 olan ürenlerden kaç adet satılmış

declare @categoryid int

set @categoryid = 10

if exists (select top 1 categoryID from products where categoryID = @categoryid)

begin

select count(*)

from [Order Details] OD join Products P

on OD.ProductID = p.ProductID

where p.categoryID = @categoryid

end

else

begin

select ‘categoryID = ‘ + convert(varchar,@categoryid) + ‘ olan bir kategori yok!!!’

select ‘KATEGORILER’

select distinct(categoryID) from products

end

select * from [Order Details]

select top 3 * from products

select 0.0 as Discount

union all

select 0.10

union all

select 0.20

union all

select 0.30

select p.ProductID,Discount * 100 as ‘Discount’,

P.UnitPrice * ( 1-discount) as ‘Price’

from products p cross join

(select 0.0 as Discount

union all

select 0.10

union all

select 0.20

union all

select 0.30) as D

where p.productID = 10

select * from ogretmenler

select * from ogrenciler

select Adi , Soyadi from ogretmenler

union all

select Adi , Soyadi from ogrenciler

select * from ogrenciders

select O.Adi , O.Soyadi ,D.DersAdi ,OD.Notu,

case when Notu between 85 and 100 then ‘*****’

when notu between 70 and 84 then ‘****’

when notu between 50 and 69 then ‘***’

when notu between 25 and 49 then ‘**’

when notu between 10 and 24 then ‘*’

else ‘–‘ end as ‘Durum’

from OgrenciDers OD right join ogrenciler O

on OD.OgrenciID = O.OgrID

left join Dersler D

on OD.DersID = D.DersID

select OrderID,ProductID,(UnitPrice * Quantity) * ( 1 – Discount) as ‘Price’,Discount

from [order details]

compute sum((UnitPrice * Quantity) * ( 1 – Discount)),AVG(Discount)

select sum((UnitPrice * Quantity) * ( 1 – Discount)),AVG(Discount)

from [order details]

select top 5 with ties O.Adi , O.Soyadi ,D.DersAdi ,OD.Notu,

case when Notu between 85 and 100 then ‘*****’

when notu between 70 and 84 then ‘****’

when notu between 50 and 69 then ‘***’

when notu between 25 and 49 then ‘**’

when notu between 10 and 24 then ‘*’

else ‘–‘ end as ‘Durum’

from OgrenciDers OD right join ogrenciler O

on OD.OgrenciID = O.OgrID

left join Dersler D

on OD.DersID = D.DersID

where D.DersAdi is not null and OD.Notu is not null

order by OD.Notu desc

select D.DersAdi ,OD.Notu

from OgrenciDers OD right join ogrenciler O

on OD.OgrenciID = O.OgrID

left join Dersler D

on OD.DersID = D.DersID

where D.DersAdi is not null and OD.Notu is not null

group by OD.Notu,D.DersAdi

with rollup

select D.DersAdi ,OD.Notu

from OgrenciDers OD right join ogrenciler O

on OD.OgrenciID = O.OgrID

left join Dersler D

on OD.DersID = D.DersID

where D.DersAdi is not null and OD.Notu is not null

group by D.DersAdi,OD.Notu

with cube

sql distinct join