SQL Part One

--set the database context
Use CommunityAssist

/*Over view or SQL commands
Part 1 */

--basic statement [Select columns from Table name]
--the * is a wild card for all
Select * from Person


Select PersonLastName, PersonFirstName,
PersonUserName
From Person;

--alias column names, the 'as'  keyword is optional
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person;

--Order by sorts a result set
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person
Order by PersonLastName

--Order by Desc

Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person
Order by [Last Name] Desc

--where clause limits the rows to those that match the criteria
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person 
where PersonLastName ='Anderson'
Order by [Last Name] Desc

--you can calculate
Select 50 * ((23/2.0 )+ 27) - 3

--The LIKE keyword lets you search for patterns 
--in this case everybody whose name begins with 'AND.'
--the % is a wildcard for any number of characters
Select PersonLastName as [Last Name], PersonFirstName as [First Name],
PersonUserName as [User Name]
From Person 
where PersonLastName Like 'And%'

--nulls cannot be = to anything you search for nulls
--with the IS keyword
Select * From PersonAddress
Where Apartment is null

--is not null
Select * From PersonAddress
Where Apartment is not null

Select * from ServiceGrant

--date Functions
Select Distinct Year(GrantDate) From ServiceGrant
Select Distinct Month(GrantDate) From ServiceGrant
Select Distinct Day(GrantDate) From ServiceGrant

--the datediff function subtracts on date from another in the specified units 
--dd is days, yy years, mm months
Select GrantReviewDate, GrantDate, DateDiff(dd,GrantDate,GrantReviewDate) as [Days before Review]
From ServiceGrant
Where DateDiff(dd,GrantDate,GrantReviewDate) > 2

--aggregate functions are functions that work
--on sets of rows at a time
Select format(Sum(GrantAllocation),'$#,###.00') as total from ServiceGrant 
Select Avg(GrantAllocation)as total from ServiceGrant 

Select Count(GrantAllocation)as total from ServiceGrant 
Where GrantAllocation > 500

Select Max(GrantAllocation)as total from ServiceGrant 
Select Min(GrantAllocation)as total from ServiceGrant 

--when mixing aggregate functions such as sum
--with non-aggregate functions or fields like Month(GrantDate)
--you have to GROUP BY all the non-aggregate values
Select Month(GrantDate) as [Month], Sum(GrantAllocation) as total
From ServiceGrant
Group by Month(GrantDate)

--having works like a where by you use it when you have
--an aggregate function in the criteria
Select ServiceKey, Sum(GrantAllocation)
From ServiceGrant
Group by ServiceKey
Having sum(GrantAllocation) > 2000

--inner join joins two tables. the keyword INNER is optional
Select ServiceName, Sum(GrantAllocation) as Total
From ServiceGrant
inner join CommunityService
on ServiceGrant.ServiceKey=CommunityService.ServiceKey
Group by ServiceName

--another inner join
Select p.PersonKey,PersonLastName, PersonFirstName, Street, City,[State],zip
From Person as p
join PersonAddress as pa
on p.PersonKey=pa.PersonKey

Select * From ServiceGrant

--joining multiple tables
--the person table is joined twice
--once for the client and once for the employee
Select GrantAmount, GrantDate, p.PersonLastName as Client, ServiceName, p2.PersonLastName as EmployeeName, GrantAmount
From Person p
join ServiceGrant sg
on p.PersonKey=sg.PersonKey
join CommunityService cs
on cs.ServiceKey=sg.ServiceKey
join Employee e
on e.EmployeeKey=sg.EmployeeKey
inner join person p2
on p2.PersonKey = e.PersonKey