/*———————————————————–odev———————————————————-
her bir categoriden her bir şirketin kaç tane sipariş verdiğini
hatta sayıya bakarak eğer sayı>50 ise yeni sutunda çok iyi sattık < 50 ise az sattık falan yazdir
companyname,categoryname ini ver
—————————————————————————————————————————-*/
——————————odev 2———————————–
—- burada görünen null lar yerine toplam ya da ara toplam yazdır—
————————————————————————-
–>>>>>>>>>>>>>>>>>> ÇÖZÜM 1 >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
——————- ====>>> 1 . YOL ———————————————————————————
select CategoryName ,CompanyName,Quantity,
Durum = case
when quantity>50 then ‘Çok İyiyiz’
when quantity<50 then ‘Çok Kötü’
else ‘ilginç’
end
from categories ,customers,[order details]
where categoryID in (select categoryId from products p inner join [order details] od
on p.productID=od.productID where p.productID = od.ProductID )
and customerID in (select customerID from orders o inner join [order details] od
on o.orderID=od.orderID where o.orderID=od.orderID )
–order by companyname
–compute sum(quantity) by companyname
group by categoryname,companyname,quantity
order by categoryname
—————————————————————————————————————————-
–========================================= 2 . YOL ===================================================
select CategoryName ,CompanyName,Quantity,
Durum = case
when quantity>50 then ‘Çok İyiyiz’
when quantity<50 then ‘Çok Kötü’
else ‘ilginç’
end
from categories ca inner join products p
on ca.categoryID=p.categoryID
inner join [order details] od
on p.productID=od.productID
inner join orders o
on od.orderID=o.orderID
inner join customers cu
on o.customerId=cu.customerID
–order by companyname
–compute sum(quantity) by companyname
group by categoryname,companyname,quantity
order by categoryname
–=============================================================================================================
join sorular ve çözümler
Hiç yorum yok:
Yorum Gönder