Functions

Use Community_Assist

Select PersonLastName from Person where PersonLastName LIKE 'D_n%'

Select Top 5 GrantRequestDate, GrantTypekey, GrantRequestAmount
From GrantRequest
order by GrantRequestAmount Desc;

Select GrantRequestDate, GrantTypekey, GrantRequestAmount
From GrantRequest
order by GrantRequestAmount Desc
offset 5 rows fetch next 4 rows only;

--scalar functions
Select Year(GrantRequestDate) as [Year], GrantTypeKey, GrantRequestAmount
From GrantRequest;
Select Month(GrantRequestDate)  as [Month], GrantTypeKey GrantType, GrantRequestAmount Amount
From GrantRequest;
Select Day(GrantRequestDate)  as [Day], GrantTypeKey GrantType, GrantRequestAmount Amount
From GrantRequest;
Select DatePart(second,GrantRequestDate) from GrantRequest;
Select DateDiff(Day,GetDate(),'3/23/2017') [Days Left in Quarter]
Select DonationAmount, cast(DonationAmount as decimal(10,2))* .10 Operations, 
DonationAmount * .90 Charity From donation

Select format(DonationAmount, '$ #,##0.00') as Amount from Donation

Declare @SocialSec as nchar(9)
Set @SocialSec='519551234'

Select SUBSTRING(@socialSec,1,3) + '-' + SUBSTRING(@socialSec,4,2) + '-' + SUBSTRING(@socialSec,6,4)
SSNumber

--Aggregate functions

Select sum(donationAmount) as total from Donation
Select avg(donationAmount) as total from Donation
Select Max(donationAmount) as total from Donation
Select min(donationAmount) as total from Donation
Select Max(PersonLastName) as total from Person

Select GrantTypeKey, format(Avg(GrantRequestAmount),'$ #,##0.00') as Average, 
format(sum(GrantRequestAmount),'$ #,##0.00') as total
 from GrantRequest
 Where GranttypeKey = 5
Group by GrantTypeKey

Select GrantTypeKey, format(Avg(GrantRequestAmount),'$ #,##0.00') as Average, 
format(sum(GrantRequestAmount),'$ #,##0.00') as total
 from GrantRequest
 Where not GrantTypeKey = 2 --!= <>
Group by GrantTypeKey
Having Avg(GrantRequestAmount) > 400

select name from sys.Tables
Select * from sys.Tables
Select * from sys.all_columns where Object_id=373576369

Select * from sys.databases