SQL Subqueries

Tremblant, Quebec

It is possible for SQL queries to contain other queries, called subqueries. The values of one query are passed to another query.

Subqueries can be utilized in SELECT statements, as well as the INSERT, UPDATE, and DELETE statements.

Subqueries allow you to connect queries together, so that you can query more than one table at a time.

Subqueries can either be nested within another query or it can be connected to another query using a keyword. When the subquery is nested in a SELECT statement, the innermost query is processed first. When a subquery is connected to another query using a keyword, the last subquery is processed first.

note: MySQL versions that are prior to version 4.1 do not support subqueries. MySQL uses JOINS to accomplish the same thing as subqueries.

1. Create a query to find the name and address of customers with W1001 and L2003 and D1002. The information is contained in two tables, Customer and Orders.

note: IS NULL is used to check columns for null values

This is the Orders table:

Orders

CustomerID	PlanID	OrderDate	CancellationDate
1	D1002	11/11/2009
10	D1002	3/15/2008	3/30/2008
10	D1003	3/15/2008
10	D2202	1/1/2010
2	W1001	3/15/2011
2	L2003	3/15/2011
2	L1001	3/20/2011
3	L2002	7/10/2010
3	L2001	7/10/2010
4	L2004	2/15/2011
7	L2004	5/14/2011
6	D1002	11/4/2010
6	D1101	11/4/2010
5	I1001	6/6/2009
8	I1001	3/14/2009
6	I1001	11/4/2010
7	L2001	5/13/2011
4	L2001	2/15/2011
8	L2001	3/17/2009
8	L2002	3/17/2009
9	L2003	6/11/2010

a) Query the Orders table for the customers with PlanIds W1001 and L2003 and D1002. Check to make sure Cancellaton Date IS NULL values:

QueryOrders:

SELECT CustomerID
FROM Orders

WHERE CancelationDate IS NULL

AND PlanID IN ('W1001', 'L2003','D1002');

Results:

QueryOrders

CustomerID
1
2
2
6
9

This is the Customer table:

Customer

FirstName	LastName	HomePhone	Address	State	City	PostalCode
Steve	Dentins	(808) 897-4321	2211 22nd Ave N	GA	Atlanta	98718
Sam	Elliot	(719) 898-2134	1601 Center loop	FL	Tampa	98982
Tom	Williams	(898) 321-2234	3890-A Cherry loop	ND	Fargo	58729
Jacob	Lin		2609 40th Ave S	OK	Tulsa	36711
Timothy	Coney	(202) 827-4331	1900 3rd St. N	OR	Salem	44812
Jill	Stephens	(813) 215-5551	1544 33rd Ave E	FL	Miami	98911
Tina	West	(813) 565-4984	1000 45th Ave N	FL	Miami	98911
Shawn	Leaven	(813) 248-8854	1908 22nd Ave S	FL	Miami	98912
Jackie	Justin		1800 4rd St. N	FL	Miami	98902
Lilly	Ward	(813) 244-5522	1010 33rd Ave N	FL	Miami	98909
Larry	Carr	(808) 423-7894	114 - C 45th Ave N	HI	Honolulu	96818
Lisa	Glenn	(808) 421-7515	1542 33rd Ave N	HI	Honolulu	96818

b) Query the Customer table to get the name and address of the customers returned by the first query, QueryOrders:

SELECT *

FROM Customer

WHERE CustomerID;

Results:

QueryCustomers

CustomerID	FirstName	LastName	HomePhone	Address	State	City	PostalCode
1	Steve	Dentins	(808) 897-4321	2211 22nd Ave N	GA	Atlanta	98718
2	Sam	Elliot	(719) 898-2134	1601 Center loop	FL	Tampa	98982
3	Tom	Williams	(898) 321-2234	3890-A Cherry loop	ND	Fargo	58729
4	Jacob	Lin		2609 40th Ave S	OK	Tulsa	36711
5	Timothy	Coney	(202) 827-4331	1900 3rd St. N	OR	Salem	44812
6	Jill	Stephens	(813) 215-5551	1544 33rd Ave E	FL	Miami	98911
7	Tina	West	(813) 565-4984	1000 45th Ave N	FL	Miami	98911
8	Shawn	Leaven	(813) 248-8854	1908 22nd Ave S	FL	Miami	98912
9	Jackie	Justin		1800 4rd St. N	FL	Miami	98902
10	Lilly	Ward	(813) 244-5522	1010 33rd Ave N	FL	Miami	98909
12	Larry	Carr	(808) 423-7894	114 - C 45th Ave N	HI	Honolulu	96818
13	Lisa	Glenn	(808) 421-7515	1542 33rd Ave N	HI	Honolulu	96818

Connect the queries using the IN keyword. Because the last query is processed first, working upward, make QueryOrders the subquery by surrounding it with parenthesis:

SELECT *

FROM Customer

WHERE CustomerID

IN

(SELECT CustomerID

FROM Orders

WHERE CancelationDate IS NULL

AND PlanID IN ('W1001', 'L2003','D1002'));

Results:

QuerySubQuery

CustomerID	FirstName	LastName	HomePhone	Address	State	City	PostalCode
1	Steve	Dentins	(808) 897-4321	2211 22nd Ave N	GA	Atlanta	98718
2	Sam	Elliot	(719) 898-2134	1601 Center loop	FL	Tampa	98982
6	Jill	Stephens	(813) 215-5551	1544 33rd Ave E	FL	Miami	98911
9	Jackie	Justin		1800 4rd St. N	FL	Miami	98902

2. Create Query to list a description of service plans that Shawn Leaven purchased:

The Customer, Orders, and Service Plan tables should be queried.

a) Query the Customer table:

SELECT CustomerID

FROM Customer

WHERE FirstName ='Shawn' AND LastName = 'Leaven';

Results:

Querya

CustomerID
8

b) Query the Orders table to get the plans Shawn Leaven purchased:

SELECT PlanID
FROM Orders
WHERE CustomerID;

c) Query the ServicePlans table using the PlanIds retreived from Queryb to get the service plan descriptions for Shawn Leaven. :

Here is the ServicePlans table:

ServicePlans

PlanID	PlanName	PlanDescription	PlanPrice	DiscountedPrice
D1002	Digital Cable	Digital cable plan	$40.00	$35.00
D1003	Digital Phone	Cell phone Plan	$40.00	$35.00
D1101	Data IP Services A	Plan A Data/IP services	$100.00	$85.00
D2202	Data IP Services B	Plan B Data/IP services	$150.00	$125.00
E1001	Email	Premium e-mail	$9.99	$6.99
I1001	Internet	Internet/email	$19.99	$16.99
L2001	Local Phone	In state calling	$39.00	$29.99
L2002	Long Distance A	Plan A Out of state per/min	$0.12	$0.11
L2003	Long Distance B	Plan B Out of state per/min	$0.09	$0.08
L2004	Long Distance C	Plan C Out of state per/min	$0.10	$0.09
W1001	Web Hosting	hosting, site creation	$19.99	$16.99

Queryc:

SELECT *
FROM ServicePlans
WHERE PlanID;

Now, reverse the order of the queries so that Queryc is the first query and put parenthesis around Querya and Queryb to make subqueries and join them with the IN keyword:


SELECT *
FROM ServicePlans
WHERE PlanID

IN

(SELECT PlanID
FROM Orders
WHERE CustomerID

IN

(SELECT CustomerID
FROM Customer
WHERE FirstName ='Shawn' AND LastName = 'Leaven'));

Results:

QuerySvcPlanSL

PlanID	PlanName	PlanDescription	PlanPrice	DiscountedPrice
I1001	Internet	Internet/email	$19.99	$16.99
L2001	Local Phone	In state calling	$39.00	$29.99
L2002	Long Distance A	Plan A Out of state per/min	$0.12	$0.11

 

Using Calculated Fields

DATEPART (DatePart, DateValue)

In Microsoft SQL Server, the DATEPART function allows you to analyze a date and return a value.

The DatePart argument can be any part of the date, such as year, quarter, month, dayofyear, day, week, weekday, hour, minute, and second. The DateValue arguement is the date.

3. Create a query using the DATEPART function to show the customers who purchased a service plan in 2010, listing the first and last names with the phone number.



Query1: The Orders table should be queried to get the customer id (see Orders table above).

SELECT CustomerID
FROM Orders
WHERE DATEPART ('yyyy', OrderDate) = 2010;

Results:

Query1

CustomerID
10
3
3
6
6
6

Query 2: Create a query to list the names and phone numbers of the customers selected from Query1, by combining two queries:


Query2:


SELECT FirstName, LastName, HomePhone
FROM Customer
WHERE CustomerID

IN

(SELECT CustomerID
FROM Orders
WHERE DATEPART ('yyyy', OrderDate) = 2010);

Results:

Query2

FirstName	LastName	HomePhone
Tom	Williams	(898) 321-2234
Jill	Stephens	(813) 215-5551
Lilly	Ward	(813) 244-5522