ITC 220 SQL

Here is the SQL we did in class

Use CommunityAssist

--basic Select statement

Select FirstName, LastName
From Person

/*This is a quick way but
your can't choose the
order the columns*/

 Select * From Person

--aliasing field names
Select FirstName as [First Name], lastName as [Last Name]
From Person 

--returns only unique instances
Select Distinct EmployeeKey from Donation

--Calulations
Select DonationKey, DonationDate, DonationAmount, 
DonationAmount *.9  as Charity, DonationAmount * .1 as maintainance
From Donation

--ordering the output
Select DonationKey, DonationDate, DonationAmount, 
DonationAmount *.9  as Charity, DonationAmount * .1 as maintainance
From Donation
Order by DonationDate, DonationAmount Desc

--where Clause
Select * From PersonAddress
Where City='Kent'

Select DonationKey, donationDate, donationAmount
From Donation
Where DonationAmount > 500

-- >, <, >=, <=, !=

--like searches for patterns 
--% any number of characters
--_one character
Select * From Person where LastName Like 'Tan%'

Select * From Donation 
Where DonationDate between '3/15/2010' and '4/15/2010'

--use is with nulls
Select * From PersonAddress Where Apartment is Not Null

Select * from PersonAddress where City ='Bellevue' or City = 'Kent'

Select * From PersonAddress Where Apartment 
is not null and  not City  = 'Seattle'

--scalar functions are in line functions

Select Distinct MONTH(DonationDate) From Donation
Select DAY(DonationDate) From Donation
Select YEAR(DonationDate) from Donation

Select DATEDIFF(dd, '11/21/2011','5/1/2012')

--aggregate functions work accross multiple rows

Select COUNT(Personkey) from Person
Where LastName Like 'B%'

Select MAX(DonationAmount) From Donation

Select MIN(DonationAmount) From Donation

Select SUM(DonationAmount) From Donation

Select AVG(donationAmount) from Donation


Select Month(DonationDate) as Month,
SUM(DonationAmount) as total
From Donation
Group by MONTH(donationDate)

Select EmployeeKey, 
SUM(DonationAmount) as total
From Donation
Group by EmployeeKey

Select EmployeeKey, 
SUM(DonationAmount) as total
From Donation
Group by EmployeeKey
Having SUM(donationAmount) > 2000

--Join

Select lastName, firstname, Street, City, [State],Zip
From Person 
Inner Join PersonAddress
On Person.PersonKey=PersonAddress.PersonKey

--tables aliased
Select lastName, firstname, Street, City, [State],Zip
From Person p
Inner Join PersonAddress pa
On p.PersonKey=pa.PersonKey

Select lastName, firstname, Street, City, [State],Zip, ContactInfo as HomePhone
From Person p
Inner Join PersonAddress pa
On p.PersonKey=pa.PersonKey
Inner Join PersonContact pc
on p.PersonKey=pc.PersonKey
Where ContactTypeKey=1

--Insert update Delete

Insert into Person(LastName, FirstName)
Values('Blackmore','John'),
('More', 'Black')


Select * From Person

Update Person
Set LastName='BlueBeard',
FirstName='Jake'
Where PersonKey=53

Begin tran

Update Person
Set FirstName='Jason'
Where PersonKey=1

rollback tran
commit Tran

Delete from Person
Where PersonKey=52