Sub Queries

--having example
 --where always comes before group by
--having always comes after group by
--having is for criteria that uses a aggregate function 
Select LastName, FirstName, Sum(DonationAmount)
From Employee e
Inner join Person p
on p.PersonKey = e.PersonKey
inner join Donation d
on e.EmployeeKey =d.EmployeeKey
Where LastName like 'l%'
Group by LastName, FirstName
Having SUM(DonationAmount) > 9000

Select MAX(DonationAmount) from Donation

--using a sub query to get the info about
--which donation is the maximum
Select DonationKey, DonationDate, donationAmount, PersonKey
From Donation
Where DonationAmount = 
(Select MAX(DonationAmount) From Donation)

--all the donations greater than the average donation
Select DonationKey, DonationDate, donationAmount, PersonKey
From Donation
Where DonationAmount > 
(Select Avg(DonationAmount) From Donation)

Select AVG(donationAmount) From Donation

--include the average as a subquery in the select
Select DonationKey, DonationDate, donationAmount,
(Select AVG(donationAmount) From Donation) as Average, PersonKey
From Donation
Where DonationAmount > 
(Select Avg(DonationAmount) From Donation)

--this assigns the subquery to a variable and uses it
--whereever the subquery would be used
Declare @Average money
Select @Average=AVG(DonationAmount) From Donation
Select DonationKey, DonationDate, donationAmount,
@Average as Average, PersonKey
From Donation
Where DonationAmount > @Average

--uses subqueries to get the differance
--between the donation and the average donation
Select DonationKey, DonationDate, donationAmount,
(Select AVG(donationAmount) From Donation) as Average, 
DonationAmount-(Select AVG(donationAmount) From Donation) 
as [Difference],
PersonKey
From Donation
Where DonationAmount > 
(Select Avg(DonationAmount) From Donation)

--use in and a subquery as a substitute
--for a join
Select LastName, Firstname
From Person
Where PersonKey in
 (Select PersonKey from Donation)

--this gets the lastname into the query by means
--of a subquery (which contains a join) 
Select (select lastname From Person p where p.personkey =
personAddress.personkey) Lastname,PersonAddressKey, Street, Apartment, 
[State], City, Zip, PersonKey
From PersonAddress
Where PersonKey in (Select PersonKey From Donation)

--a three table sub query
Select LastName, Firstname
From Person
where PersonKey in 
(Select PersonKey From Employee where EmployeeKey in
 (Select EmployeeKey from ServiceGrant))

--three table subquery with not in
--returns everyone who is not an employee
Select LastName, Firstname
From Person
where PersonKey  in 
(Select PersonKey From Employee where EmployeeKey  not in
 (Select EmployeeKey from ServiceGrant))
 
 
 
 --use of any
 --returns any value that is greater than any other value
 --eveything except the minimum value
 Select DonationAmount from Donation
 where DonationAmount > any 
 (Select DonationAmount from Donation)
 
 --use of all, returns a value that is greater than
 --or equal to all the values
 --same as returning the maximum value
 Select DonationAmount from Donation
 where DonationAmount >= all 
 (Select DonationAmount from Donation)
 
 --exists returns a boolean. This tests
 --where a particular table exists in a system table
 if exists 
  (Select name from sys.tables where name='donation')
 begin
 print 'yep it''s there'
 end
 else
 Begin
 print 'Nope it''s not'
 end
 
 --some system table stuff
 Select name from sys.Tables
 
 use master
 
 Select name from sys.databases
 
 use CommunityAssist
 
 --this system stored procedure returns everything
 --from the system tables about the table
 --Donation
 exec sp_Help  'Donation'