Table Expressions

--table expressions
--sub query in the from clause
--sub queries must be aliased

Select PersonLastName, PersonFirstName, PersonEmail, City
From (Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey = pa.PersonKey
where PersonAddressCity='Bellevue') as BellevueResidents

--same but with wildcard
Select *
From (Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey = pa.PersonKey
where PersonAddressCity='Bellevue') as BellevueResidents

--another table expression example
Select GrantType, total,  Average
From (Select GrantTypeName GrantType, Sum(GrantRequestAmount) as Total,
Avg(GrantRequestAmount) as Average
From GrantRequest gr
inner join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by GrantTypeName) as TypeTotals

--Common table expressions are similar
--to reqular expressions except
--the subquery is defined first which
--is better logically

With BellevueResidents as
(
 Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
    From Person p
    inner join PersonAddress pa
    on p.PersonKey = pa.PersonKey
    where PersonAddressCity='Bellevue'
) Select * from BellevueResidents
go
-- a second example
with typeTotals as
(
  Select GrantTypeName GrantType, Sum(GrantRequestAmount) as Total,
  Avg(GrantRequestAmount) as Average
  From GrantRequest gr
  inner join GrantType gt
  on gr.GrantTypeKey=gt.GrantTypeKey
  Group by GrantTypeName
)Select GrantType, Total, Average from TypeTotals

go
--Declare @City nvarchar(255) ='Kent'
Declare @city nvarchar(255)
set @city ='Kent';

With BellevueResidents as
(
 Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
    From Person p
    inner join PersonAddress pa
    on p.PersonKey = pa.PersonKey
    where PersonAddressCity=@city
) Select * from BellevueResidents

go

--views are basically stored queries-- they usually
--represent "a view" some user has of the data
--they also can be used to obscure the underlying
--structure of the database, because the user
--of a view can only see the aliases for the columns
--and can't see the underlying tables
Create view vw_Employee
As
Select
PersonLastName [Last Name],
PersonFirstName [First Name],
PersonEmail Email,
EmployeeHireDate [Hire Date],
EmployeeAnnualSalary [Annual Salary],
PositionName [Position]
From Person p
inner join Employee e
on p.Personkey=e.PersonKey
inner join EmployeePosition ep
on e.EmployeeKey = ep.EmployeeKey
inner join Position ps
on ep.PositionKey=ps.PositionKey


Go
Select [last name], [first name], Email from vw_Employee
Where [Annual Salary] > 0
order by [Last Name]

--schemabinding makes it so that the
--tables underlying the view
--cannot be changed
--at least not without removing the view
go
Create view vw_Donations with schemabinding
As
Select PersonLastName,
PersonFirstName,
PersonEmail,
DonationDate,
DonationAmount
From dbo.person p
inner join dbo.donation d
on p.PersonKey=d.PersonKey
go
Select * from vw_Donations

go
--this won't work because table bound to view 
Alter table Donation
Drop column DonationDate
go
--a schema, in this context, is a set of objects 
--with common ownership
--dbo, database owner, is the default schema
Create schema managerSchema
go

--here is a view that is owned by the managerSchema
Create view managerSchema.RequestSummary
As
Select GrantTypeName GrantType, Sum(GrantRequestAmount) as Total,
  Avg(GrantRequestAmount) as Average
  From GrantRequest gr
  inner join GrantType gt
  on gr.GrantTypeKey=gt.GrantTypeKey
  Group by GrantTypeName


  go
  --table valued function is a function that
  --returns a table as a result
  --this one takes a parameter @City
  --that must be provided by the user
  Create function fx_GetCities(@city nvarchar(255))
  returns table
  As
  Return
  (
 Select PersonLastname, PersonFirstName, PersonEmail, PersonAddressCity City
    From Person p
    inner join PersonAddress pa
    on p.PersonKey = pa.PersonKey
    where PersonAddressCity=@city
 )

 --using the function
 go
 Select * from dbo.fx_GetCities('Kent')