Selects Functions

Use Community_Assist
--math
Select 5 * 2 /3 % 2

Select DonationAmount,
DonationAmount * .80 as Charity,
DonationAmount * .20 as Maintenance
from Donation

Select DonationAmount,
DonationAmount * .80  Charity,
DonationAmount * .20  Maintenance
from Donation;
--scaler
--Date Functions
Select * from Donation;
Select Distinct Year(DonationDate) [Year] from Donation;
Select Distinct Month(DonationDate) [Month] from Donation;
Select Distinct Day(DonationDate) [Day] from Donation;
Select DonationDate, DatePart(Minute,DonationDate) as [Minute] from Donation;

Select * from Donation
Where Year(DonationDate)=2015
And Month(donationDate) between 8 and 9;

Select GetDate()
Select * from Employee
Select DateDiff(Month,'2/21/2005','4/21/2013')[Difference]
Select DateAdd(day, 13,GetDate())

--formats
Select * from Donation
Select DonationDate, Format(DonationAmount,'$#,##0.00') [DonationAmount] from Donation
Select * from Contact
Select '(' + substring(ContactNumber,1,3) + ')' + 
substring(ContactNumber,4,3) 
+ '-' + substring(ContactNumber, 7,4) [Phone] from Contact

Select ContactNumber, 
Format(Cast(ContactNumber as bigint),
'(000)000-0000') from Contact
--Aggregate --operate across multiple rows
Select sum(DonationAmount) Total from Donation
Select Avg(DonationAmount) Total from Donation
Select count(DonationAmount) Total from Donation
Select Max(DonationAmount) Total from Donation
Select Min(DonationAmount) Total from Donation

Select Year(DonationDate) [Year],
format(Sum(DonationAmount),'$#,##0.00') Total
From Donation
Group by Year(DonationDate)

Select Year(DonationDate) [Year], Month(DonationDate) [Month],
format(Sum(DonationAmount),'$#,##0.00') Total
From Donation
Group by Year(DonationDate), Month(DonationDate)
Order by Year(DonationDate),Month(DonationDate)

Select Year(DonationDate) [Year],
Case Month(DonationDate)
   when 8  then 'August'
   when 9  then 'September'
   when 2 then 'February'
   when 4 then 'April'
end  as [Month],
DonationAmount
From Donation

Select Year(donationDate) [Year],
datename(Month,donationDate) [Month],
DonationAmount
From Donation

Select name from Sys.Databases
Select name from sys.Tables

I forgot to cover this in class. If you have an aggregate function in the criteria, you have to use "having" instead of "where"

--You must use HAVING with any criteria that uses an
--aggregate function
--these examples have both a having and a where clause
Select EmployeeKey, AVG(DonationAmount) Average
From Donation
Where EmployeeKey > 2
Group by EmployeeKey
Having AVG(DonationAmount) < 1000