Sql Dilinde Matematiksel İşlemler

 --Sql Dilinde Matematiksel İşlemler--

--SUM(): Toplamasını alır.

--AVG() : Ortalamasını alır.

--ABS() : Mutlak Değeri alır.

--CEILING() : En yakın sayı üstünne yuvarlanarak tam sayı haline getirir.

--FLOOR() : Yuvarlanarak tam sayı haline getirir.

--PI() : Pi sayısını getirir

--POWER() : Üssünü alır.

--RAND() : veri tipi fark etmeksizin Rastgele sayı üretir.

--ROUND() : Yuvarlama

--SIGN() : Sayısal değeri pozitif mi negatif mi?

--SQRT() : Karakökünü getirir

--SQUARE() : Karesini alır

--ISNUMERIC() : Rakam olup olmadığını bilgi verir

select SupplierID from Products group by SupplierID

select sum(unitprice) from Products

select*from Products

select ProductID,ProductName,UnitPrice*UnitsInStock from Products

select*from [Order Details] where ProductID=5

select SUM(quantity) from [Order Details] where ProductID=2

select AVG(Quantity) from [Order Details] group by ProductID order by ProductID desc

select MAX(unitPrice)from [order details]--en yüksek

select min(UnitPrice) from [Order Details]--en düşük

select COUNT(productId) from [Order Details] where UnitPrice>10 and UnitPrice<50

select SUM(UnitsInStock) from Products

select SUM(Freight)  from Orders where ShipVia=1

select sum(discount)  from [Order Details]--indirimleri toplar

select SUM(freight)   from Orders group by ShipCountry--nakliye ücreti

select  SUM(unitsonorder)  from Products where ReorderLevel>5 group by CategoryID--yeniden sipariş seviyesi

select count(unitsinstock) from Products where Discontinued=1 --durdurulan

select AVG(freight)  from orders where ShipCountry='France'

select avg(unitprice)  from Products where ProductName like 'c%' group by Supplierid

select avg(freight) from Orders where OrderDate>'01.01.1990' and ShipCountry like 'sweden'

select  avg(Unitprice) from Products group by CategoryID

select  max(freight)  from Orders where ShipVia=3

select  MAX(UnitsInStock) from Products group by SupplierID

select SUM(UnitsInStock) as [toplam urun miktar] from Products

select  SUM(freight),employeeid    from Orders group by EmployeeID 

select SUM(UnitsOnOrder) from Products group by SupplierID--sipariş üzerinde birimler

select SUM(unitsonorder),CategoryID from Products group by CategoryID 

select SUM(unitsinstock)  from Products where Discontinued=1 and UnitsInStock>10

--having içinde varolan işlemi gösterir gruplamada kullanılır

select COUNT(CustomerId)  from orders where CustomerID='alfki' group by CustomerID

select ShipCountry,COUNT(shipcountry) as Satişmiktarı from Orders where EmployeeID='3' group by ShipCountry order by ShipCountry

--ORDER BY ifadesi kayıtları belirtilen alanda büyükten küçüğe veya küçükten büyüğe göre sıralar. 

--ASC (ascending) parametresi ile küçükten büyüğe, DESC (descending) parametresi ile büyükten küçüğe göre sıralar. 

--Burada sadece sayısal alanlar değil metinsel alanlarda alfabetik olarak sıralanabilir.

Sql Giriş

 select*from Employees

select FirstName,LastName,Title from Employees

select*from customers 

select ContactName,Country from Customers --Tüm veriler

select ContactName,Country from customers

select*from [Order Details] where Quantity>5 and ProductID>20 and UnitPrice>20

select UnitPrice as birim_fiyati,Quantity as miktar,UnitPrice*Quantity as ucret from [Order Details]

select*from Orders

select*from orders where OrderDate>'1996-07-24'

select*from Orders where CustomerID<='e' --eden önceki harfleri gösterir

select*from Customers

select CustomerID,CompanyName,Address,Country,Phone  from Customers

select FirstName,LastName,Title,city,Country from Employees where Country='USA' and City='Seattle'

select*from Products where UnitsInStock='0'

select*from Employees where HireDate>'01.01.1993' and HireDate<'01.01.1994'

select*from [Order Details] where UnitPrice>20 or Quantity<10 

select UnitPrice,(UnitPrice*10)/100+UnitPrice as [KDV'li fiyat]  from [Order Details] where UnitPrice>50

select*from Customers where Country in ('France','Spain','mexico','canada')

select*from Employees where FirstName>'d'

select distinct country from Customers

select distinct quantity from [order details]

select distinct unitprice from [Order Details]

select*from Employees where region is null

select*from Employees where region is not null

select*from Employees order by FirstName asc

select*from Employees order by FirstName desc

select*from Products where UnitPrice>20 order by UnitPrice,UnitsInStock asc

select*from Employees order by city,title,FirstName asc

select*from Employees order by city,title,FirstName desc

select top 5*from Customers

select top 10 percent*from customers order by CustomerID desc

select top 20 percent*from Employees

select top 10*from Products where UnitPrice>20 order by NEWID()

select*from Employees where LastName like 'd%'--d ile başlayan isimler

select*from Employees where LastName like '%d%'--içinde d olan isimler

select*from Employees where LastName like '%h'--sonda h olan isimler

select*from Employees where LastName like '_u%' --soyisminde ikinci karakterinde u harfi olan

select*from Employees where FirstName like '[ar]%'

select*from Employees where FirstName like '[a-r]%' --a ile r arasında

select*from Employees where FirstName like '[^a]%' --adı a ile başlamayan

select FirstName,LastName as [isim soyisim] from Employees

select FirstName+' '+LastName as [isim soyisim] from Employees

select ProductName,UnitsOnOrder from Products where UnitsOnOrder!=0 order by UnitsOnOrder desc

select top 3*from Employees where title='sales representative' order by FirstName asc

select top 20 percent*from Products where UnitPrice between 10 and 30

select distinct SupplierID from Products

select CompanyName,ContactName  from Customers where ContactName like '_[a-c]%' --ikinci harfi a yada c olan contactname

select CustomerID from Customers where CustomerID like '%[cdn]'

select CustomerID from Customers where CustomerID like '[^ak]_[lsn]%'

select CustomerID from Customers where CustomerID like '_[^a]%'