Sub Queries

--sub queries
use Automart
--If you want to see which service has the max price you
--need to use a subquery in the where clause
Select ServiceName, max(servicePrice) From Customer.AutoService
Group by ServiceName

Select ServiceName, ServicePrice From Customer.Autoservice
Where ServicePrice = (Select max(ServicePrice) from Customer.Autoservice)

--you can also use subqueries in the select clause
Select ServiceName, ServicePrice, (Select Max(ServicePrice) From customer.AutoService) as Maximum,(Select Max(ServicePrice) From customer.AutoService)-servicePrice as [Difference]
From Customer.AutoService


--this one goes a little crazy, the idea is that
--we will show the total count of auto's served
--the we will show the counts for each individual
--location and then what percent each represents
--of the total.
--there are three casts. The innermost cast converts
--the division to decimal to preserve the decimal part
--(count returns an integer)
--the next cast (second one in) converts the whole
--result to decimal to limit the number of decimal places
--showing. The outermost cast converst the whole expression
--to nvarchar in order to concatinate the % sign in
--
Select 
(Select Count(*) From Employee.VehicleService) Total,
LocationName, count(*) [Number per Location], 
cast(cast(cast (count(*) as decimal(4,2))
 / (Select Count(*) From Employee.VehicleService) * 100 as decimal(4,2)) 
 as Nvarchar) + '%' [Percent]
From Employee.VehicleService vs
inner join Customer.location loc
on vs.LocationID=loc.LocationID
Group by LocationName

--the in keyword returns any value that matches
--one of the values in the result set
--here the second query
Select Servicename from Customer.AutoService
Where AutoServiceID not in 
(Select AutoserviceID From Employee.VehicleServiceDetail)

--using not with in has the same result as an outer join
Select LocationName from Customer.Location
 where LocationID not in 
 (Select LocationID from Employee.VehicleService)
 
Select LocationName from Customer.Location
 where LocationID in 
 (Select LocationID from Employee.VehicleService)

 Select * From customer.AutoService

 --You can link several tables with "in"
--the logic is the same as for joins
--primary key to foreign key
--follow the relationship path to get the data
--you want
 Select LicenseNumber, VehicleMake, VehicleYear From Customer.vehicle
 Where vehicleID in
 (Select VehicleId from Employee.VehicleService 
 where VehicleServiceID in 
 (Select VehicleServiceID from Employee.VehicleServiceDetail
 Where AutoserviceID in 
 (Select AutoserviceID from Customer.AutoService 
 where ServiceName='Replace Alternator')))

 --exits returns a boolean yes/no
 Select * From Employee.VehicleServiceDetail 
 Where exists (Select AutoServiceID from Employee.VehicleServiceDetail where AutoserviceID =7)

--I often use exists to test for the existence of an object 
--if exists
 if exists
  (Select name from sys.Databases where name = 'CommunityAssist')
 Begin
 print 'yep, it''s here'
 End

 Select * from sys.Databases
 --The MagazineSubscription database is available in canvas files
 use MagazineSubscription
 Select * from Customer
 Select * From Magazine
 Select * From MagazineDetail
 Select * From SubscriptionType
 Select * From Subscription

--a correlated subquery is when the subquery uses a value in the 
 --outer query as part of its criteria
 --it results in something resembling a recursive function
 --in this case what it does is makes sure that
 --like is compared to like
 --subscription type 1 (one year) is compared only to other
 --subscription type 1's and subscription type 5 (five year) 
 --is compared only to other subscription type 5's etc.

 Select avg(SubscriptionPrice) From MagazineDetail where
 SubscriptTypeID=5

 Select SubscriptTypeId, MagDetID, SubscriptionPrice 
 From MagazineDetail md
 Where SubscriptionPrice >= 
 (Select Avg(SubscriptionPrice) From MagazineDetail md2
 where md.SubscriptTypeID=md2.SubscriptTypeID)
 And SubscriptTypeID = 5
 order by SubscriptTypeID