Table Expressions, CTEs and Views

use Community_Assist

--joins leftovers
Select * from Person, PersonAddress
Where Person.Personkey =PersonAddress.Personkey

Select PErsonFirstname, personlastname, pa.PersonKey
From Person p
Left join PersonAddress pa
on p.PersonKey=pa.PersonKey
Where pa.personkey is null

--table expressions and views
--subqueries in the FROM clause

Select lastname, firstname, City
from 
   (Select PersonLastName lastname, 
    PersonFirstName firstname, 
    PersonAddressCity City
    From Person p
    Inner join PersonAddress pa
 ON P.PersonKey=pa.PersonKey
    Where PersonAddressCity='Bellevue') as BellevueResidents

Select GrantTypeName, TotalGrants
From
    (Select GrantTypeName, Count(gr.GrantTypeKey) totalGrants
  From GrantRequest gr
  inner Join GrantType gt
  on gt.GrantTypeKey=gr.GrantTypeKey
  Group by GrantTypeName) as Grantcount

--common table expressions CTE

with BellevueResidents as
(
   Select PersonLastName lastname, 
    PersonFirstName firstname, 
    PersonAddressCity City
    From Person p
    Inner join PersonAddress pa
 ON P.PersonKey=pa.PersonKey
    Where PersonAddressCity='Bellevue'
)
Select lastname, firstname, City 
From BellevueResidents
go
with GrantCount as
(
  Select GrantTypeName, Count(gr.GrantTypeKey) totalGrants
  From GrantRequest gr
  inner Join GrantType gt
  on gt.GrantTypeKey=gr.GrantTypeKey
  Group by GrantTypeName
)
Select GrantTypeName, TotalGrants
From GrantCount

Declare @city nvarchar(255)
Set @City='Seattle';

with Residents as
(
Select PersonLastName lastname, 
PersonFirstName firstname, 
PersonAddressCity City
From Person p
Inner join PersonAddress pa
ON P.PersonKey=pa.PersonKey
Where PersonAddressCity=@city
)
Select lastname, firstname, City 
From Residents

go
Create Function fx_City(@City nvarchar(255))
Returns table
As
return
Select PersonLastName lastname, 
PersonFirstName firstname, 
PersonAddressCity City
From Person p
Inner join PersonAddress pa
ON P.PersonKey=pa.PersonKey
Where PersonAddressCity=@city
go
Select * from fx_City('Bellevue')

--views
go
Alter view vw_AnnualReport
As
Select Year(GrantRequestDate) [Year],
format(Sum(GrantRequestAmount), '$#,##0.00') Total,
format(Avg(GrantRequestAmount),'$#,##0.00') Average
From GrantRequest
Group by Year(GrantRequestDate)

Go

Select * from vw_AnnualReport where [Year]=2015

Select Year(GrantRequestDate) from vw_AnnualReport

go
Create View vw_Employees
As
Select PersonFirstName firstname,
PersonLastName lastname,
PersonEmail email,
personAddressStreet street,
personAddressCity city,
personAddressState [state],
EmployeeHireDate hiredate
From Person p
inner join personAddress pa
on p.PersonKey=pa.PersonKey
inner join Employee e
on e.PersonKey=p.PersonKey

Select * from vw_Employees order by HireDate
go
Alter View vw_Employees with schemabinding
As
Select PersonFirstName firstname,
PersonLastName lastname,
PersonEmail email,
personAddressStreet street,
personAddressCity city,
personAddressState [state],
EmployeeHireDate hiredate
From dbo.Person p
inner join dbo.personAddress pa
on p.PersonKey=pa.PersonKey
inner join dbo.Employee e
on e.PersonKey=p.PersonKey

go
Alter table Person
Drop column PersonEmail