SQL

--set the database context
Use Community_Assist;

--simple selects the * is a wild card for return all columns

Select * From Person;

Select PersonLastName, PersonFirstName, PersonEmail
From Person;

Select PersonLastName, PersonFirstName, PersonEmail
From Person
Where PersonLastName='Tanner'
And not PersonFirstName='Chelsea'; --!=, <>

Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastname

Select PersonLastName, PersonFirstName, PersonEmail
From Person
order by PersonLastname desc, PersonFirstName desc;

Select * from Donation
Where DonationDate > '9/3/2015';

--date and time functions
Select Distinct Year(DonationDate) From Donation;
Select Distinct Month(DonationDate) From Donation;
Select Distinct Day(DonationDate) From Donation;
Select Distinct DatePart(hour,DonationDate) from Donation;
Select Distinct DatePart(MILLISECOND,DonationDate) from Donation;
Select GetDate();

Select * From Donation
Where Month(DonationDate) between 9 and 10;

--aggregate functions sum, avg, max, min, count

Select Sum(DonationAmount) as total From Donation
Where Month(DonationDate) between 9 and 10;

Select count(DonationAmount) as number From Donation
Where Month(DonationDate) between 9 and 10;

Select Avg(DonationAmount) as Average From Donation
Where Month(DonationDate) between 9 and 10;

Select Max(DonationAmount) as Average From Donation
Where Month(DonationDate) between 9 and 10;

Select Min(DonationAmount) as Average From Donation
Where Month(DonationDate) between 9 and 10;

--group by any field not part of an aggregate function

Select Year(DonationDate) as [Year], 
Sum(DonationAmount) as total 
From Donation
Group by Year(DonationDate);

Select * from Donation

--inner joins :Combine two tables

Select PersonLastName, PersonFirstName, PersonEmail, DonationDate, DonationAmount
From Person
inner join Donation
on Person.PersonKey=Donation.PersonKey --show how tables relate
order by DonationAmount desc

Select * From GrantRequest
--single line comment
/* this is
a multiline
comment
*/

--three table inner join
Select PersonLastName, PersonFirstName, GrantTypeName, GrantRequestDate,
GrantRequestExplanation, GrantRequestAmount
From Person
inner join GrantRequest
on Person.PersonKey=GrantRequest.PersonKey
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey

--inserts

Insert into Person(PersonLastName, PersonFirstName, 
PersonEmail,  PersonEntryDate)
Values('Simpson', 'Homer','homer@yahoo.com', GetDate())

Insert into PersonAddress( PersonAddressStreet, PersonAddressCity, 
PersonAddressState, PersonAddressZip, PersonKey)
Values('10001 Bart Street', 'Springfield', 'WA','98000', IDENT_CURRENT('Person')) 
--ident_current is a function that returns the last autonumber created in the table in 

Insert into Donation (PersonKey, DonationDate, DonationAmount)
Values(IDENT_CURRENT('Person'),GetDate(), 5)

Select * from PersonAddress
Select * from Donation

--updates change existing data. You should always
--use a where criteria
--begin tran allows an undo if you make a mistake

begin tran

Update Person
Set PersonFirstName='Jason',
PersonEmail='jasonAnderson@gmail.com'
Where Personkey=1

select * from Person

rollback tran --undo what you did

Commit Tran--commit it, write it to the database