```-------------------------
-- 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'