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

Class Diagrams 1 for Show Tracker

Here are my class diagrams for show tracker. This is a first take and I expect to modify these classes as we discuss them. One change from class is that I have moved the announcement methods have been moved to the Venue class, because I think Venues are logically in control of shows.

subqueries

use Community_Assist

--subqueries
Select max(donationamount) from Donation

Select PersonLastName, DonationDate, DonationAmount
From donation
inner join Person
on Donation.PersonKey=Person.Personkey
Where DonationAmount=(Select max(DonationAmount) from Donation)

--Which employees Reviewed grants
Select PersonLastName, PersonFirstName, PersonEmail 
From Person
where personkey in 
   (Select personkey from Employee 
     where employeeKey in
  (Select EmployeeKey from GrantReview))

--Sub queries in the select clause
Select GrantTypeName, Sum(GrantRequestAmount) subtotal
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--add a subquery to get the overall total
Select GrantTypeName, Sum(GrantRequestAmount) subtotal,
(Select Sum(GrantRequestAmount) from GrantRequest)Total
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--add percent
Select GrantTypeName, Sum(GrantRequestAmount) subtotal,
(Select Sum(GrantRequestAmount) from GrantRequest)Total,
sum(GrantRequestAmount)/(Select Sum(GrantRequestAmount) from GrantRequest) * 100 [Percent]
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--formatted
Select GrantTypeName, format(Sum(GrantRequestAmount), '$#,##0.00') subtotal,
count(*) Number,
format((Select Sum(GrantRequestAmount) from GrantRequest),'$#,##0.00') Total,
format(sum(GrantRequestAmount)/(Select Sum(GrantRequestAmount) from GrantRequest),'##0.00%') [Percent]
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--Correlated sub query is a query
--where the inner query references the outer query
--Coordinated subqueries are queries in which the subquery 
--references the outer query. The subquery matches
--the granttypekey from gr1, the outer query with the granttypeKey
--from gr2 inside the subquery. The effect is to make sure
--that like is matched with like. GrantTypeKey 1 (Food) is matched 
--only against other granttypeKey 1 values, GrantTypeKey 2 (Rent)
--is matched only against other grantTypeKey 2 values, etc.
--a coordinated subquery is the SQL equivalent of a recursive
--function in other programming languages, and like them  
-- requires a lot of processor time 


Select GrantTypeKey, GrantRequestAmount
From GrantRequest gr1
Where GrantRequestAmount > 
(Select avg(GrantRequestAmount)
from GrantRequest gr2
Where gr1.GrantTypeKey=gr2.GrantTypeKey)

Select avg(GrantRequestAmount) from GrantRequest 
Where GrantTypekey=3

Here is the insert into table with a SELECT

--using a select to populate a table

Use Community_Assist

Create table PersonA
(
   personkey int,
   personLastName nvarchar(255),
   personfirstname nvarchar(255),
   personemail nvarchar(255)
)
Insert into PersonA
Select PersonKey, PersonLastName,
Personfirstname, personemail
From person
where PersonlastName like 'A%'

Select * from PersonA

Joins

--Joins
--cross join-a cross join basically
--joins every record from one table
--with every single record in the next
--rare sometimes happen by accident

use Community_Assist
Select * from GrantType

Select PersonLastname, GrantTypeName
From Person
Cross join GrantType
order by PersonLastName

Select PersonLastName, GrantTypename
From Person, GrantType

--accidental cross
Select PersonLastName, GrantTypename, GrantRequestDate, GrantRequestAmount
From Person, GrantType, GrantRequest
Where Person.PersonKey =GrantRequest.Personkey
--And Granttype.GrantTypeKey =GrantRequest.GrantTypeKey

--inner
Select * from Employee

--inner joins return all the matching records
Select Person.Personkey, EmployeeKey, PersonLastName, PersonFirstname, EmployeeHireDate, EmployeeAnnualSalary
From Person
inner join Employee
On Person.PersonKey=Employee.PersonKey

Select * from person where personkey =5

--with table aliases and leaving out inner
Select p.Personkey, EmployeeKey, PersonLastName, PersonFirstname, EmployeeHireDate, EmployeeAnnualSalary
From Person p
join Employee e
On p.PersonKey=e.PersonKey

Select PersonLastName, PersonFirstname, PositionName, EmployeeHireDate, EmployeeAnnualSalary
From Person p
join Employee e
On p.PersonKey=e.PersonKey
inner join EmployeePosition ep
on ep.EmployeeKey=e.EmployeeKey
Inner join Position pos
on pos.PositionKey=ep.PositionKey

--outer join returns everything from one table
--whether it is matched or not
--good for finding unmatched data

Select * from GrantType
Select* from GrantRequest

--left outer join
Select GrantTypeName, GrantRequestKey
From GrantType gt
left outer join GrantRequest gr
on gt.GrantTypeKey=gr.GrantTypeKey
where grantrequestkey is null

--right outer join
Select GrantTypeName, GrantRequestKey
From GrantRequest gr
Right outer join GrantType gt
on gt.GrantTypeKey=gr.GrantTypeKey
where grantrequestkey is null

--full join
Select GrantTypeName, GrantRequestKey
From GrantRequest gr
full join GrantType gt
on gt.GrantTypeKey=gr.GrantTypeKey

Select GranttypeName, count(GrantRequestAmount) [Count],
Sum(GrantRequestAmount) Total, 
Avg(grantRequestAmount) Average
From GrantRequest gr
join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by GrantTypeName

Select GranttypeName, count(GrantRequestAmount) [Count],
format(Sum(GrantRequestAmount),'$#,##0.00') Total, 
format(Avg(grantRequestAmount),'$#,##0.00') Average
From GrantRequest gr
join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by GrantTypeName

Activity diagrams

Here is the first login diagram

Here is the new show diagram

Here is a diagram that shows the split and join. Split implies threading--two things happening simultaneously

Here is the login with swim lanes. Swim lanes establish what areas are responsible for what actions

Functions

Use Community_Assist

--date functions
Select * from Donation
Select Distinct Year(DonationDate) from Donation
Select Distinct Month(DonationDate) from Donation
Select Distinct Day(DonationDate) from Donation

Select Distinct Year(DonationDate) [Year], Month(donationDate)[Month], 
Day(DonationDate) [Day] from Donation

Select Distinct datepart(hour,DonationDate) from donation
Select GetDate()
Select DateAdd(dd,30,GetDate()) [Month from now]
Select DateDiff(Month, GetDate(), '4/11/2019') [Year from now]
--format
select DonationDate, format(DonationAmount,'$#,##0.00') as Amount from Donation
select * from Contact
Select format(Cast(ContactNumber as bigint),'(000)000-0000') from contact
Select '(' + substring(contactnumber,1,3) + ')' + SUBSTRING(ContactNumber,4,3) 
+ '-' + substring(contactnumber,7,4) as phone from Contact

-- math
Select Donationkey, format(DonationAmount,'$#,##0.00') Amount, format(donationAmount * .30, '$#,##0.00') as maintenance,
format(donationAmount * .70,'$#,##0.00') as charity from donation
--+, -, *, /, % modulus
--Aggregate functions
Select Max(donationAmount) as biggest from Donation
Select Min(donationAmount)  as smallest from Donation
Select Sum(donationAmount) total from Donation
Select Avg(donationAmount) Average from Donation
Select Count(donationAmount) as [How many] from Donation

Select Year(DonationDate) [Year], 
format(Sum(donationAmount),'$#,##0.00') total
From Donation
Group By Year(donationDate)
Order by Year(DonationDate) 

Select Year(DonationDate) [Year], Month(donationDate) [Month], 
format(Sum(donationAmount),'$#,##0.00') total
From Donation
Group By Year(donationDate), Month(DonationDate)
Order by Year(DonationDate) desc, Month(donationDate)

Select Year(DonationDate) [Year],
Case Month(donationDate)
when 1 then 'Jan'
when 2 then 'Feb'
when 3 then 'Mar'
when 4 then 'Apr'
when 5 then 'May'
when 6 then 'Jun'
when 7 then 'Jul'
When 8 then 'Aug'
When 9 then 'Sep'
When 10 then 'Oct'
when 11 then 'Nov'
when 12 then 'Dec'
End as [Month],
DonationAmount
from Donation

Select Year(DonationDate) [Year],
Datename(Month,donationDate) [Month],
DonationAmount
from Donation

Select name from sys.Databases
Select * from sys.Tables
select name from sys.columns where OBJECT_ID=805577908

Use Cases 1

Here are the use cases we did in class

First Selects

Use Community_Assist;

Select PersonLastName, PersonFirstName
From Person;

Select * From Person

Select PersonLastName as [last Name], 
PersonFirstName as [First Name]
From Person;

Select PersonLastName, PersonFirstName
From Person
order by PersonLastName, PersonfirstName;

Select PersonLastName, PersonFirstName
From Person
order by PersonLastName Desc, PersonfirstName asc;

Select PersonLastName, PersonFirstName
From Person
where PersonLastName='Eliot'
order by PersonfirstName asc;

Select * from Donation
Where DonationAmount > 1000

Select * from Donation
Where DonationAmount <= 100

-- >, <, =, <=, >=, !=
Select  PersonKey, Donationamount from Donation
Order by PersonKey

Select Distinct PersonKey from Donation
Order by PersonKey

Select * from Donation where DonationDate>'9/5/15' 
and DonationDate <'9/6/15'

Select * from Donation where DonationDate between'9/5/15' 
and '9/6/15'

Select * from Person 
where PersonLastName like 'd_n%'

Select * from PersonAddress

Select * from PersonAddress where PersonAddressApt is not null
Select * from PersonAddress where PersonAddressApt is null
Select * from PersonAddress where Not PersonAddressCity='Seattle'

Top 10 and Offset

Use Community_Assist;

--top 10
Select top 10 donationDate, DonationAmount
From Donation
order by DonationAmount desc

--offset
Select donationDate, DonationAmount
From Donation
order by DonationAmount desc
offset 10 rows fetch next 5 rows only