ITC Resources: Subqueries

use Automart

--basic aggregate
Select  Max(ServicePrice) from Customer.AutoService


--If you want to see which service has the max price you
--need to use a subquery in the where clause
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 a
on a.LocationID=vs.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 Distinct ServiceName from Customer.AutoService
Where autoserviceId in (Select autoServiceId from Employee.VehicleServiceDetail)

--using not with in has the same result as an outer join
Select Distinct ServiceName from Customer.AutoService
Where autoserviceId not in (Select autoServiceId from Employee.VehicleServiceDetail)

--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=15))

--another example, also including a query
--in the select clause to return the name of the
--autoservice
Select (Select ServiceName from Customer.AutoService where AutoserviceID=12) ServiceName,
LicenseNumber, VehicleMake, VehicleYear
From Customer.Vehicle
where VehicleId in 
(Select VehicleID from Employee.VehicleService 
where VehicleServiceID in
(Select VehicleServiceID from Employee.VehicleServiceDetail 
 Where AutoserviceID=12))

--in can be used with litteral sets as well
Select * from Employee.VehicleServiceDetail where AutoserviceID in (9, 11, 12)

--exits returns a boolean yes/no
Select * from Employee.VehicleServiceDetail where not exists 
(Select AutoserviceID from Employee.VehicleServiceDetail where autoserviceID=12)

--I often use exists to test for the existence of an object 
if exists
(Select name from sys.Databases where name = 'communityAssist')
Begin
Print 'Yep it''s there'
End

Correlated subquery

Here is a database called Magazine that the subquery is based on

--Drop Database MagazineSubscription
--create the database no options used
Create Database MagazineSubscription
Go
--use the database
Use MagazineSubscription

/*Create the tables with constraints
This needs to be done in a certain order
The primary key tables, the one side of
a relation must be done before the many
side of a relation */

Create table Magazine
(
  MagID int identity(1,1),
  MagName varchar(100) not null,
  MagType char(10) not null default 'Monthly',
  Constraint PK_Magazine Primary Key(MagID),
  
)

Create table SubscriptionType
(
 SubscriptTypeID int identity(1,1),
    SubscriptTypeName char(15),
    Constraint PK_SubscriptionType Primary Key (SubscriptTypeID)
)

Create table MagazineDetail
(
 MagDetID int Identity(1,1),
 MagID int not null,
    SubscriptTypeID int not null,
 SubscriptionPrice money,
    Constraint PK_MagazineDetails Primary Key(MagDetID),
    Constraint FK1_MagazineDetails Foreign key(MagID)
  References Magazine(magID),
    Constraint FK2_MagazineDetails Foreign Key(SubscriptTypeID)
  References SubscriptionType(subscriptTypeID)
)

Create Table Customer
(
 CustID int identity(1,1),
 CustLastName varchar(30) not null,
 CustFirstName varchar(25)null,
 CustAddress varchar(100) not null,
 CustCity varchar(50) not null,
 CustState char(2) not null,
 CustZipcode char(11) not null,
 CustPhone char(10),
 Constraint PK_Customer Primary Key (custID)
)

Create Table Subscription
(
 SubscriptionID int identity(1,1),
 CustID int not null,
 MagDetID int not null,
 SubscriptionStart DateTime not null,
 SubscriptionEnd Datetime,
    Constraint PK_Subscription Primary Key (subscriptionID),
 Constraint FK1_Subscription Foreign Key (CustID)
  References Customer(custID),
 Constraint FK2_Subscription Foreign Key(MagDetID)
  References MagazineDetail(MagDetID)
)

--insert into magazine

Insert into Magazine(MagName, MagType)
Values('Procastinators Anonymous','Monthly')

Insert into Magazine(MagName, MagType)
Values('IT Toys','Monthly')

Insert into Magazine(MagName, MagType)
Values('FireEaters Quarterly','Quarterly')

Insert into Magazine(MagName, MagType)
Values('Waste Not Want Not, A hoarders guide','Monthly')

Insert into Magazine(MagName, MagType)
Values('SQL Server','Monthly')

Insert into Magazine(MagName, MagType)
Values('Extreme Programming','Monthly')

Insert into Magazine(MagName, MagType)
Values('Insurance Actualization algorythms','Quarterly')

Insert into Magazine(MagName, MagType)
Values('Doctor Who Magazine','Weekly')

Insert into Magazine(MagName, MagType)
Values('XBox Anonymous','Monthly')

Insert into Magazine(MagName, MagType)
Values('Beer bottle Target Practice','Quarterly')


--Inserts for subscription types

Insert into SubscriptionType (subscriptTypeName)
Values('Six Month')

Insert into SubscriptionType (subscriptTypeName)
Values('Yearly')

Insert into SubscriptionType (subscriptTypeName)
Values('Three Month')

Insert into SubscriptionType (subscriptTypeName)
Values('Two Year')

Insert into SubscriptionType (subscriptTypeName)
Values('five Year')

Insert into SubscriptionType (subscriptTypeName)
Values('Three Year')

--Inserts into MagazineDetail

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(1, 1, 23.99)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(1, 2, 38.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(1, 5, 74.29)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 1, 35.99)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 2, 52.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 4, 77.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(2, 5, 99.29)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(3, 2, 40.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(3, 5, 200.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 1, 25.00)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 3, 15.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 4, 40.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 5, 75.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(4, 6, 55.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(5, 2, 38.75)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(5, 6, 78.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(6, 2, 40.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(6, 6, 99.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(7, 5, 134.99)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 1, 15.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 2, 21.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 3, 8.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(8, 4, 35.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(9, 2, 38.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(10, 5, 78.50)

Insert into MagazineDetail(MagID, SubscriptTypeID, subscriptionPrice)
Values(10, 6, 58.50)

--Inserts into Customer

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Jordan','Mary','2002 South Mercer Street','Seattle','WA',
'98190','2065558828')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Danner','Thomas','100 Boardwalk South','Seattle','WA',
'98190','2065551001')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Terrance','Sarah','202 Rt 3','Bellevue','WA',
'98120','3605550128')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Camlin','Lenny','Somewhere Ave','Olympia','WA',
'98199','2535551010')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Sanderson','Lewis','101 Elsewhere Avenue','Seattle','WA',
'98190','2065550987')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Able','Tina','1000 West Blv','Bellingham','WA',
'98180','3605552020')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Evans','Karl','11 North Hill street','Tacoma','WA',
'98100','2535558998')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Zukof','Bob','WaterFront Blvd.','Bellevue','WA',
'98120','3605552435')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Martinez','Patrick','EastLake Blvd.','Seattle','WA',
'98220','2065553679')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Patterson','Lisa','1010 Binary Drive','Redmond','WA',
'98130','3605551100')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Franklin','Bernice','222 ITC Road','Olympia','WA',
'98199','3605552221')

Insert into customer(CustLastName, CustFirstName, CustAddress, 
CustCity, CustState, CustZipcode, CustPhone)
Values('Kim','Susan','111 Martin Luther King Way','Seattle','WA',
'98122','2065550742')

--Insert into subscription

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 4, '1/15/2006', '7/15/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 14, '1/15/2006', '1/15/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (2, 7, '1/15/2006', '1/15/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (3, 8, '1/2/2006', '1/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (4, 11, '2/01/2006', '2/01/2008')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (4, 4, '2/01/2006', '9/01/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (5, 16, '1/05/2006', '1/05/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 5, '2/15/2006', '2/15/2008')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 12, '2/15/2006', '2/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 18, '2/15/2006', '2/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (7, 18, '3/01/2006', '3/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (8, 20, '3/01/2006', '3/01/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (9, 22, '3/10/2006', '3/10/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (10, 4, '3/15/2006', '10/15/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (10, 23, '3/15/2006', '3/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (11, 11, '3/20/2006', '3/20/2008')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (12, 17, '4/01/2006', '4/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (1, 18, '4/01/2006', '4/01/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (4, 4, '4/15/2006', '10/15/2006')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (6, 24, '4/15/2006', '4/15/2009')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (10, 18, '4/15/2006', '4/15/2011')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (3, 14, '4/15/2006', '4/15/2007')

Insert into subscription(CustID, MagDetID, SubscriptionStart, SubscriptionEnd)
Values (12, 22, '4/15/2006', '4/15/2007')

Here is the correlated subquery

use MagazineSubscription

 --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 subscriptTypeID, MagDetID, SubscriptionPrice
From MagazineDetail md
Where subscriptionPrice >=
(Select Avg(SubscriptionPrice) from magazineDetail md2
 where md.SubscriptTypeID=md2.SubscriptTypeID)

ITC Resources

Monday, January 27, 2014

Subqueries

Correlated subquery

No comments:

Post a Comment

About Me

Blog Archive