SQL 2

This includes SQL 1

--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, PersonFirstName 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 ='Tanner'
Order by [Last Name] Desc

Select * From ServiceGrant
Where GrantAmount > 500

Select * from ServiceGrant where GrantDate>'8/9/2013'

Select * From ServiceGrant where GrantDate 
Between '9/1/2013' and '10/1/2013'

--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 'A%'

--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

Select Cast(Day(GrantDate)as varchar(2))+ '/' 
+ cast(Month(GrantDate) as varchar(2)) + '/' 
+ cast(Year(GrantDate)as varchar(4)) as Year 
From ServiceGrant

Select 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 as p
join ServiceGrant as sg
on p.PersonKey=sg.PersonKey
join CommunityService as cs
on cs.ServiceKey=sg.ServiceKey
join Employee as e
on e.EmployeeKey=sg.EmployeeKey
inner join person p2
on p2.PersonKey = e.PersonKey

--Data Manipulation queries
Insert into Person ( PersonLastName, PersonFirstName, PersonUsername)
Values('Kim','Jim','jimkim'),
('Morgan','Randy','rmorgan')

Select * from Person

Create Table PeopleTemp
(
 PersonKey int,
 PersonLastName nvarchar(255),
 PersonFirstName nvarchar(255)
)

Insert into PeopleTemp(personKey, PersonLastName, PersonFirstName)
Select PersonKey PersonLastName, PersonFIrstName
From Person

Select * from PeopleTemp

Insert into Person ( PersonLastName, PersonFirstName, PersonUsername)
Values('Lawrence', 'Ahab', 'Alaw')
Insert into PersonAddress([Street],[City],[Zip], PersonKey)
Values('1001 Westlake','Seattle','98001', ident_current('Person'))

Select * from Person
Select * From PersonAddress

update peopleTemp
Set PersonLastName= 'Smith'

Update Person
Set PersonLastName='Alberts',
PersonFirstName='Jay'
where PersonKey=1

Begin Tran

Rollback Tran
commit tran

Delete From Person Where Personkey =1

Drop table PeopleTemp

Select * from Person