SubQueries

Use MagazineSubscription

Select MagID From MagazineDetail
Where SubscriptionPrice = 
(Select MAX(SubscriptionPrice) From MagazineDetail)

Select MagID, SubscriptionPrice, 
(Select AVG(SubscriptionPrice) From MagazineDetail) as Average
From MagazineDetail
Where SubscriptionPrice >
(Select AVG(SubscriptionPrice) From MagazineDetail)

Insert into Customer(CustLastName, CustFirstName, 
CustAddress, CustCity, CustState, CustZipcode, CustPhone)
Values('John', 'Larry', '100 somewhere st','Seattle','Wa','98000',null)

Select CustID, CustLastName, CustFirstName
From Customer
Where CustID Not in (Select CustID from Subscription)

Select MagName 
 From Magazine
 Where MagID not in 
 (Select MagID 
  from MagazineDetail
  Where MagDetID in
  (Select MagDetID 
   from Subscription))
     
                  Select MagID 
      from MagazineDetail
   Where exists
   (Select MagDetID 
    from Subscription
    where SubscriptionPrice > 100)
     
Select MagdetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice >= all
(Select SubscriptionPrice
From MagazineDetail)

--any says that the subcription price must be
--greater than any one of the other prices
--the effect is to return all but the smallest price
Select MagdetID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice > any
(Select SubscriptionPrice
From MagazineDetail)

Select SubscriptTypeID, AVG(SubscriptionPrice) as Average
From MagazineDetail
Group by SubscriptTypeID

--this is a correlated subquery
--that means the subquery is dependent
--on a value from the main query
--for its completion
--it is equivalent to a recursive function
--in programming
--Note that like a self join the same table
--is aliased with two different aliases
--treating it like two tables
Select md.SubscriptTypeID, MagDetID, SubscriptionPrice
From magazineDetail md
Where SubscriptionPrice >=
(Select AVG(SubscriptionPrice)
From magazinedetail amd
where md.SubscripttypeID = amd.SubscripttypeID)

Select * from SubscriptionType

--exists returns a boolean does it exist
--in the subset or not
--a bit more efficient than in
Select Magname
From Magazine
Where Exists
(Select MagID
from MagazineDetail
Where SubscriptTypeID=5)

Use CommunityAssist
Select Distinct (Select MAX(hireDate) from Employee) as "Newest", 
(Select MIN(HireDate) From Employee) as "Oldest",
DateDiff(yy,  (Select MIN(HireDate) From Employee),
(Select MAX(hireDate) from Employee) ) as "Range"
From Employee