Thursday, February 21, 2013

SQL Subqueries

Print Friendly and PDF

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


No comments:

Post a Comment

"Comment As:" anonymous if you would rather not sign into an account!