Table Expressions

--Table expression

--a sub query in the from clause--querying a result set

use Community_Assist

Select PersonKey, [Last], [First], City 
From
(Select p.Personkey, PersonLastName [Last], PersonFirstName [First], PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey
Where PersonAddressCity = 'Bellevue') as BellevueResidents

Select PersonKey, [Last], [First], City 
From
(Select p.Personkey, PersonLastName [Last], PersonFirstName [First], PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey) as Residents
Where City = 'Bellevue' 

Select RequestMonth, [Grant Type Name],  Count([Grant Type Name]) as [Count]
from
(Select Month(GrantRequestDate) RequestMonth, GrantTypeName [Grant Type Name]
   From GrantRequest gr
   inner join GrantType gt
   on gt.GrantTypeKey=gr.GrantTypeKey ) as [Grant Type Counts]
group by RequestMonth, [Grant Type Name]

--CTE (Common Table Expression)
go
with Residents as
(
Select p.Personkey, PersonLastName [Last], PersonFirstName [First], PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey

)
Select PersonKey, [Last], [First], City  From Residents Where City = 'Kent'

with GrantCount as
(
   Select Month(GrantRequestDate) RequestMonth, GrantTypeName [Grant Type Name]
   From GrantRequest gr
   inner join GrantType gt
   on gt.GrantTypeKey=gr.GrantTypeKey 
)
Select RequestMonth, [Grant Type Name], Count([Grant Type Name]) as [Count]
From GrantCount
Group by RequestMonth, [Grant Type Name]


Declare @City nvarchar(255)
Set @city='Redmond'

Select Distinct PersonKey, [Last], [First], City 
From
(Select p.Personkey, PersonLastName [Last], PersonFirstName [First], PersonAddressCity City
From Person p
inner join PersonAddress pa
on p.PersonKey=pa.PersonKey) as Residents
Where City = @City


--views
go
Create view vw_Employees
As 
Select 
PersonLastName LastName, 
PersonFirstName FirstName,
EmployeeHireDate HireDate,
EmployeeAnnualSalary 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 po
on po.PositionKey=ep.PositionKey

go

--order by is forbidden in views
Select LastName, FirstName, Position from vw_Employees
order by LastName

/*
insert or update through view if
no column is aliased
no joins
sub queries */
go
Alter view vw_Employees with schemabinding
As 
Select 
PersonLastName LastName, 
PersonFirstName FirstName,
EmployeeHireDate HireDate,
EmployeeAnnualSalary Salary,
PositionName [Position]
From dbo.Person p
inner join dbo.Employee e
on p.PersonKey=e.PersonKey
inner join dbo.EmployeePosition ep
on e.EmployeeKey=ep.EmployeeKey
inner join dbo.Position po
on po.PositionKey=ep.PositionKey
go
Create schema Donor

Create view Donor.vw_DonorInfo
As
Select PersonLastName,PersonFIrstname, PersonEmail,
DonationDate, DonationAmount
From Person p
inner Join Donation d
on p.PersonKey=d.PersonKey
go
--table valued function
create function fx_EmployeeGrantCount
(@EmployeeKey int)
returns table
as 
Return
Select gr.GrantRequestKey, GrantRequestDate, GrantReviewDate,
 PersonKey, GrantRequestExplanation, 
 GrantRequestAmount, GrantAllocationAmount
 From GrantRequest gr
 inner join GrantReview gv
 on gr.GrantRequestKey=gv.GrantRequestKey
 Where employeeKey = @EmployeeKey

 Select Sum(GrantRequestAmount) Request, Sum (GrantAllocationAmount) allocation 
 from dbo.fx_EmployeeGrantCount(2)

 Select distinct employeeKey from GrantReview

 Select distinct gr1.GrantTypeKey, c.GrantRequestAmount
 From dbo.GrantRequest as gr1
 Cross Apply
 (Select GrantTypeKey, GrantRequestAmount, GrantRequestKey
 From dbo.GrantRequest as gr2
 where gr1.GrantTypeKey = gr2.GrantTypeKey
 order by GrantRequestAmount desc, GrantTypeKey desc
 offset 0 rows fetch first 3 rows only) as c

Sub Queries

--subqueries
use Community_Assist
Select PersonLastName, PersonFIrstName, EmployeeHiredate, PositionName
from person p
inner join Employee e
on p.PersonKey = e.PersonKey
inner join EmployeePosition ep
on e.EmployeeKey = ep.EmployeeKey
inner join Position po
on po.PositionKey =ep.PositionKey

Select max(DonationAmount) From Donation

Select DonationKey, DonationDate, PersonKey, donationAmount
From Donation
Where DonationAmount = (Select Max(DonationAmount) from Donation)

Select GrantTypeName from GrantType
Where GrantTypeKey not in (Select GrantTypeKey from GrantRequest)

--meaningless
Select GrantTypeName from GrantType
Where GrantTypeKey not in (Select PersonKey from GrantRequest)

Select PersonLastName, PersonFirstname, PersonEmail
From Person
Where PersonKey in (Select Personkey from Employee)

Select PersonLastName, PersonFirstname, PersonEmail
From Person
inner join Employee
on Person.PersonKey=Employee.PersonKey

Select PersonFirstName, PersonLastname
From Person
where personKey in 
(Select PersonKey from Employee
where EmployeeKey in 
(Select employeeKey from GrantReview 
where GrantRequestStatus='Denied'))

Select GrantTypeName,format(Sum(GrantRequestAmount), '$ #,##0.00') as SubTotal,
Format((Select Sum(GrantRequestAmount) from GrantRequest), '$ #,##0.00') as Total,
format(Sum(GrantRequestAmount) / (Select Sum(GrantRequestAmount) from GrantRequest), '#0.00 %') as Percentage
From GrantRequest gr
Inner join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
Group by GrantTypeName

Select GrantTypeKey, Avg(GrantRequestAmount)
From GrantRequest
Group by GrantTypeKey

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

Joins

--Joins
--cross joins
--inner joins
-- outer joins  -full join

Use Community_Assist
Select PersonLastName, DonationAmount
From Person, Donation

Select PersonLastName, DonationAmount
From Person 
Cross Join Donation

Select PersonLastName, PersonEmail, ContactNumber, DonationAmount
From Person, Contact, Donation
Where Person.PersonKey=Contact.PersonKey
And Person.PersonKey = Donation.PersonKey
And ContactTypeKey=1

Select PersonLastName, PersonEmail, ContactNumber, DonationAmount
From Person
inner join Contact
on Person.PersonKey=contact.PersonKey
inner join Donation
on Person.PersonKey=Donation.PersonKey
where ContactTypeKey=1;

Select p.personKey,PersonLastName, PersonEmail, ContactNumber, DonationAmount
From Person p
inner join Contact c
on p.PersonKey=c.PersonKey
inner join Donation d
on p.PersonKey=d.PersonKey
where ContactTypeKey=1;

Select * from GrantReview

Select Year(GrantRequestDate) [Year], GrantTypeName, Sum(GrantRequestAmount) as Request, 
Sum(GrantAllocationAmount) as Granted, format(Sum(GrantAllocationAmount)/sum(GrantRequestAmount), '###.00 %') as Percentage
From GrantRequest gr
inner join GrantType gt
on gr.GrantTypeKey=gt.GrantTypeKey
inner join GrantReview gv
on gv.GrantRequestKey=gr.GrantRequestKey
Group by Year(GrantRequestDate), GrantTypeName
Order by Year(GrantRequestDate)

--outer
Select GrantTypeName, GrantRequest.GrantTypeKey
From GrantType
Left outer join GrantRequest
on GrantType.GrantTypeKey =GrantRequest.GrantRequestKey
Where GrantRequest.GrantTypeKey is null

Select ContactTypeName, Contact.ContactTypeKey
From ContactType
left outer Join Contact
on ContactType.ContactTypeKey=contact.ContactTypeKey
Where Contact.ContactTypeKey is null

Select ContactTypeName, Contact.ContactTypeKey
From Contact
right outer Join ContactType
on ContactType.ContactTypeKey=contact.ContactTypeKey
Where Contact.ContactTypeKey is null

Select Distinct ContactTypeName, Contact.ContactTypeKey
From ContactType
Full Join Contact
on ContactType.ContactTypeKey=contact.ContactTypeKey

Functions

Use Community_Assist

Select PersonLastName from Person where PersonLastName LIKE 'D_n%'

Select Top 5 GrantRequestDate, GrantTypekey, GrantRequestAmount
From GrantRequest
order by GrantRequestAmount Desc;

Select GrantRequestDate, GrantTypekey, GrantRequestAmount
From GrantRequest
order by GrantRequestAmount Desc
offset 5 rows fetch next 4 rows only;

--scalar functions
Select Year(GrantRequestDate) as [Year], GrantTypeKey, GrantRequestAmount
From GrantRequest;
Select Month(GrantRequestDate)  as [Month], GrantTypeKey GrantType, GrantRequestAmount Amount
From GrantRequest;
Select Day(GrantRequestDate)  as [Day], GrantTypeKey GrantType, GrantRequestAmount Amount
From GrantRequest;
Select DatePart(second,GrantRequestDate) from GrantRequest;
Select DateDiff(Day,GetDate(),'3/23/2017') [Days Left in Quarter]
Select DonationAmount, cast(DonationAmount as decimal(10,2))* .10 Operations, 
DonationAmount * .90 Charity From donation

Select format(DonationAmount, '$ #,##0.00') as Amount from Donation

Declare @SocialSec as nchar(9)
Set @SocialSec='519551234'

Select SUBSTRING(@socialSec,1,3) + '-' + SUBSTRING(@socialSec,4,2) + '-' + SUBSTRING(@socialSec,6,4)
SSNumber

--Aggregate functions

Select sum(donationAmount) as total from Donation
Select avg(donationAmount) as total from Donation
Select Max(donationAmount) as total from Donation
Select min(donationAmount) as total from Donation
Select Max(PersonLastName) as total from Person

Select GrantTypeKey, format(Avg(GrantRequestAmount),'$ #,##0.00') as Average, 
format(sum(GrantRequestAmount),'$ #,##0.00') as total
 from GrantRequest
 Where GranttypeKey = 5
Group by GrantTypeKey

Select GrantTypeKey, format(Avg(GrantRequestAmount),'$ #,##0.00') as Average, 
format(sum(GrantRequestAmount),'$ #,##0.00') as total
 from GrantRequest
 Where not GrantTypeKey = 2 --!= <>
Group by GrantTypeKey
Having Avg(GrantRequestAmount) > 400

select name from sys.Tables
Select * from sys.Tables
Select * from sys.all_columns where Object_id=373576369

Select * from sys.databases

First ERD

Create tables

Use Community_Assist
Go
Select * from Person

Create database TestTables
use testTables
/*
this is 
a multine
comment
*/
Create table Student
(
 StudentKey int identity(1,1) primary key,
 StudentLastName nvarchar(255) not null,
 StudentFirstName nvarchar(255) null,
 StudentEmail nvarchar(255) not null
)

--this is a comment
Create table Course
(
    CourseKey int identity(1,1) primary key,
 CourseName nvarchar(255) not null,
 CourseCredits int default 5 -- default value

)

Create table Section
(
  SectionKey int identity(1,1),
  CourseKey int not null foreign key references Course(CourseKey),
  SectionYear int not null,
  SectionQuarter nvarchar(6),
  Constraint constraint_quarter check (SectionQuarter in ('Fall', 'winter', 'Spring', 'Summer')),
  Constraint PK_SectionKey primary key (SectionKey)
)

Create table Roster
(
    Rosterkey int identity(1,1) not null,
 SectionKey int not null,
 StudentKey int not null,
 RosterGrade decimal(2,1) null
   
)

Alter table Roster
Add Constraint PK_Roster primary key (RosterKey)

Alter table Roster
Add Constraint FK_Section Foreign Key (SectionKey)
References Section(SectionKey)

Alter table Roster
Add Constraint FK_Student Foreign Key (StudentKey)
References Student(StudentKey)

Alter Table Roster
Add Constraint ck_Grade Check (RosterGrade between 0 and 4)

Alter table Student
Add Constraint unique_email unique(StudentEmail)

Alter table Student
Add StudentId nvarchar(9)

Alter Table Student
Drop column StudentID