SQL

--set the database context
Use CommunityAssist
--returns all columns and all rows from the Person table
--the asterisk is a wild cards for all columns
Select * from Person

--choose the column to display
SELECT PersonLastName, 
PersonFirstName, 
PersonUserName
FROM Person;

--alias with as
SELECT PersonLastName AS [Last Name], 
PersonFirstName AS [First Name], 
PersonUserName AS [User Name]
FROM Person;

--alias without as
SELECT PersonLastName [Last Name], 
PersonFirstName [First Name], 
PersonUserName [User Name]
FROM Person;

--the where criteria selects which "rows" to display
--the or lets you choose multiple values
Select * From PersonAddress
Where City='Kent' or City ='Shoreline'

-- nulls cannot be = to anything so
--that we use "is"
Select * from PersonAddress
Where Apartment is null
And City='Kent'

Select * from PersonAddress
Where Apartment is not null
And City='Kent'

--use of not
Select * from PersonAddress
where Not City='Seattle'

Select * from PersonAddress
where  City != 'Seattle'

Select * From Donation
--between
Select * From Donation
Where DonationDate between '8/1/2013' and '8/30/2013'

--like, the % is a wild card meaning any number
--of characters
Select PersonLastName, PersonFirstName
From Person
Where PersonLastName like '%ay%'

Select * From Donation
--scalar date functions
Select Distinct Month(DonationDate) AS [Month] from Donation
Select Distinct  Year(DonationDate) as [Year] from Donation
Select Distinct Day(DonationDate) As [Day] from Donation

Select Distinct Month(DonationDate) AS [Month] ,
Year(DonationDate) as [Year],
Day(DonationDate) As [Day]
From Donation

Select * from Donation
--datediff function
Select DonationKey, donationAmount, DateDiff(dd,DonationDate, DonationConfirmDate) [Days to confirmation]
From Donation
Where DateDiff(dd,DonationDate, DonationConfirmDate) > 3

--aggregate functions
Select count(DonationKey) as [total Number] From Donation
Select Sum(DonationAmount) as total from Donation
Select AVG(DonationAmount) as Average from Donation
Select max(DonationAmount) as Maximum from Donation
Select min(DonationAmount) as Minimum from Donation

--group by
--you must group by any column not included in the aggregate function
Select Month(DonationDate) as [Month], Sum(DonationAmount) as Total
From Donation
Group by Month(DonationDate)

--use of having
--when you have an aggregate value in the criteria
--you need to use "having" instead of where
Select PersonKey, count(personKey) from Donation
Group by Personkey
having count(personKey) > 1

Select * From Donation
-- inner join
Select [PersonLastName],[PersonFirstName], Apartment, Street, City
[PersonUsername], DonationDate,
DonationAmount
From Donation
Inner join Person
on person.PersonKey=Donation.PersonKey
inner Join PersonAddress
on person.PersonKey=PersonAddress.PersonKey

--outer join
Select ServiceName, sg.ServiceKey
From CommunityService cs
left outer Join ServiceGrant sg
on cs.ServiceKey=sg.ServiceKey
Where sg.ServiceKey is null

--sub query examples
Select PersonFirstName,PersonlastName, DonationAmount From Donation
inner join Person on Person.PersonKey=Donation.PersonKey
Where DonationAmount=(Select Max(DonationAmount) From donation)

Select Distinct (Select Max(DonationAmount) from Donation) as [Max],
(Select Min(donationAmount) From donation) as [Min],
(Select Avg(DonationAmount) From Donation) as Average
From Donation