More SQL

Use CommunityAssist

--functions Date time functions

Select Year(GetDate())
Select Distinct Year(DonationDate) as [Year] from Donation
Select DateDiff(dd,  '2/28/2013','1/1/2014')
Select DateAdd(dd, 30, '2/28/2013')

--aggregate functions
Select avg(DonationAmount) From Donation
Select Sum(DonationAmount) From Donation
Select Count(DonationAmount) from Donation
Select Max(DonationAmount) From Donation
Select Min(DonationAmount) From Donation

Select Year(DonationDate) as [Year], Month(DonationDate) as [Month],
 Sum(donationAmount) as Total from Donation
 Group by Year(DonationDate), Month(donationDate)
 order by Year(DonationDate)

--inner joins
Select LastName, FirstName, ContactInfo as Email
From Person
Inner Join PersonContact
on Person.PersonKey=personcontact.PersonKey
Where ContactTypeKey=6

Select LastName, FirstName, ContactInfo as Email, DonationDate
From Person
Inner Join PersonContact
on Person.PersonKey=personcontact.PersonKey
Inner Join Donation
on Person.PersonKey=Donation.PersonKey
Where ContactTypeKey=6

--insert update and delete

Insert into Person(lastName, firstName)
Values('Smith', 'Jason'),
('Doe' ,'Jamie')

Select * From Person

Insert into Person(lastName, firstName)
Values('Munz', 'Nelson')

Insert into PersonAddress(Street, Apartment, State, City, Zip, PersonKey)
Values('1000 nowhere',null,'IL', 'Springfield', '98000',ident_current('Person'))

Insert into Donation(DonationDate, DonationAmount, PersonKey, EmployeeKey)
Values(GetDate(),2.25,ident_current('Person'), 1)

Delete from person where Personkey=1

Update Person
Set FirstName='Jerry'
Where PersonKey=1063

Begin transaction

Delete from PersonContact

Select *  from PersonContact

Rollback tran

Commit Tran