-------------------------
-- Scalar Functions
-------------------------

------------------------
-- Numeric Functions
------------------------

-- * Ceiling 

SELECT productName , unitPrice , unitPrice * 0.3 , CEILING(unitPrice * 0.3)
FROM products 

-- * Floor 

SELECT productName , unitPrice , unitPrice * 0.3 , FLOOR(unitPrice * 0.3)
FROM products 

-- * Round 

-- * 0-4 down
-- * 5-9 up 

SELECT ROUND(154.89 , -2) 

SELECT productName , unitPrice , unitPrice * 0.3 , ROUND(unitPrice * 0.3 , 1 )
FROM products 

SELECT ROUND(RAND() * 10 , 1)  

--------------------------------
-- String Functions
--------------------------------

SELECT productName, UPPER(productName) , LOWER(productName)
FROM products 

SELECT productName , LEN(productName)
FROM products  

SELECT CHARINDEX('l' , 'hello world') 

SELECT productName  , CHARINDEX ('a' , productName)
FROM products /*might be usefull for substring*/

SELECT productName , SUBSTRING (productName , 1 , 3)
FROM products

SELECT lastName , firstName ,
LOWER(SUBSTRING(firstName , 1 ,2) + SUBSTRING(lastName , 1 , 4) + '@gmail.com') AS 'Email'
FROM employees 

SELECT productName , SUBSTRING (productName , CHARINDEX ('a' , productName) , 3)
FROM products

SELECT lastName , RIGHT(lastName , 3) , LEFT(lastName , 3)
FROM employees 

SELECT REPLACE('hello world' , 'hello' , 'goodbye cruel' ) 

SELECT productName , REPLACE(productName , 'a' , '$' )
FROM products

SELECT REPLICATE('*' , 10) 

SELECT REVERSE (productName )
FROM products 

SELECT LEN('sometext                                     ')

SELECT RTRIM('text                        ') 

SELECT LTRIM('          text') 

SELECT LTRIM(RTRIM('              test            ')) 

--------------------------------
-- Date Functions
--------------------------------

SELECT GETDATE()

SELECT YEAR(GETDATE())

SELECT lastName , hireDate , YEAR(hireDate) , MONTH(hireDate) , DAY(hireDate)
FROM employees
WHERE  MONTH(hireDate) = 8 

SELECT lastName , hireDate , YEAR(hireDate) , MONTH(hireDate) , DAY(hireDate) ,
							DATENAME(month , hireDate) , DATENAME(dw , hireDate)
FROM employees

SELECT lastName , hireDate , DATEPART(YEAR , hireDate) , DATEPART( MONTH , hireDate) , DATEPART(DAY, hireDate) ,
							 DATEPART(QQ , hireDate) ,
							 DATENAME(month , hireDate) , DATENAME(dw , hireDate)
FROM employees

SELECT lastName , hireDate , DATEADD(month , 3 , hiredate)
FROM employees 

SELECT lastName , birthDate , DATEDIFF(year , birthDate , getdate())
FROM employees 

-----------------------------------
-- Conversion Functions
-----------------------------------

SELECT lastName , hireDate , lastName + ' -- ' + CAST(hireDate AS varchar)
FROM employees 

SELECT lastName , hireDate , lastName + ' -- ' + CONVERT(varchar , hireDate)
FROM employees 

SELECT lastName , hireDate , lastName + ' -- ' + CONVERT(varchar , hireDate , 113)
FROM employees 

SELECT lastName , hireDate , lastName + ' -- ' + CONVERT(varchar , hireDate , 107)
FROM employees 

-----------------------------
-- Null Functions
-----------------------------

SELECT lastName , region , ISNULL(region , 'No Region')
FROM employees 

SELECT productName , unitPrice , ISNULL(unitPrice , 0) * 3
FROM products

----------------------------
-- CASE
----------------------------

SELECT productName , unitPrice ,
		CASE WHEN unitPrice BETWEEN 0 AND 50 THEN 'Low Price'
			 WHEN unitPrice BETWEEN 51 AND 60 then 'Medium Price'
			 WHEN unitPrice BETWEEN 61 AND 70 then 'High Price'
			 ELSE 'Other Price'
		END AS 'PriceGrades'
FROM products