SQL

Use CommunityAssist

--Simple Selects
Select lastname, Firstname from Person

Select * From Person

/*
this is a
multiline 
comment 
*/
--alias and sort
Select lastname as [Last name], firstname as [First Name]
From Person
order by lastname 
--sort descending
Select lastname as [Last name], firstname as [First Name]
From Person
order by lastname Desc

Select * From PersonAddress
Order by City Desc, zip

Select * From Donation

Select Distinct YEAR(DonationDate) as [Year], Month(DonationDate) from Donation

Select LastName, firstname from Person
Where PersonKey=15

Select * From PersonAddress
Where City='Seattle'

Select * From PersonAddress
Where not City = 'seattle'

-- > < >= <= !=
Select * From Donation
where DonationAmount > 500

Select * from Donation
Where DonationDate Between '3/1/2010' and '3/31/2010'

Select * from Donation
Where DonationDate >= '3/1/2010' and DonationDate <='3/31/2010'

--aggregate function count, max, min, sum,avg
Select COUNT(DonationAmount) as Number from Donation

Select SUM(DonationAmount) as Total from donation

Select AVG(DonationAmount) as Average from Donation

Select MAX(donationAmount) as biggest from donation

Select Year(donationDate) as [Year], Month(DonationDate) as [Month],
SUM(DonationAmount) as Total
From Donation
Group by YEAR(DonationDate), MONTH(donationDate)
Having SUM(donationAmount) > 5000
Order by Year(donationDate)


Select * from Person
Select * From PersonAddress

--inner joins
Select LastName, Firstname, Street, Apartment,City, [State], zip
From Person
Inner join PersonAddress
on Person.PersonKey=PersonAddress.PersonKey

Select LastName, Firstname, Street, Apartment,City, [State], 
zip, donationDate, DonationAmount
From Person p
Inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
inner join Donation d
on p.PersonKey=d.PersonKey

Select Servicename, GrantKey
From [Service] s
Left Outer join ServiceGrant sg
on s.ServiceKey=sg.ServiceKey
Where GrantKey is null


Select * from PersonAddress
Where Apartment is not null

Insert into Person(LastName, FirstName)
Values('Smith', 'Joe'),
('Doe', 'Jane')

Select * from Person
--explicitly setting up a transaction
--can allow you to have an undo
Begin tran

Update Person
Set Firstname='fsdakfsdahk'
Where PersonKey=1

Rollback tran --undoes transaction

Commit Tran -- commits and writes the transaction

Delete from PersonAddress --will delete all records in the table



Select * from PersonAddress