Example Queries

Many students learn well by seeing examples. Below you will find six example SQL queries from simple to intermediate.

Design and Data

Here is a simple design and sample data for a magazine and subscriber database. If you want to run the example queries, you can download and run this SQL script to create the magazine database.

An entity relationship diagram for magazines, subscriptions,
and subscribers normalized to third normal form

magazine
magazineKey	magazineName	price
1	Fishing in the Mojave	$13.95
2	Car Racing Made Easy	$15.45
3	Pine Cone Computing	$17.50
4	Cooking Like Mad	$18.00
5	If Only I Could Sing	$12.45
6	Beautiful Birds	$12.45
7	Corn Shucking for Fun and Profit	$15.05
8	MySQL Magic	$10.95

subscription
subscriptionKey	magazineKey	subscriberKey	startDate	length
1	1	1	2011-03-01	12
2	2	2	2011-03-01	14
3	6	3	2012-02-01	12
4	6	5	2012-02-01	12
5	4	3	2011-09-01	12
6	7	5	2012-07-01	24
7	7	4	2012-08-01	12
8	1	3	2011-05-01	12
9	1	4	2011-09-01	12
10	5	3	2011-12-01	12
11	3	3	2011-05-01	18

subscriber
subscriberKey	firstName	lastName	address	city	state	ZIP
1	Julie	Johnston	10336 NE 187th St	Bothell	WA	98012
2	Albert	Anderson	220 K Street Southeast	Auburn	WA	98002
3	Samantha	Sanders	316 Union Ave	Snohomish	WA	98290
4	Jose	Jimenez	187 27th Ave	Seattle	WA	98122
5	Lucy	Lamont	175 Smokey Point Dr	Lakewood	WA	98409
6	Walter	Wong	1073 South 323rd Street	Federal Way	WA	98003

Example Queries

Address List of All Subscribers

Write a SQL query that produces an address list of all subscribers ordered alphabetically by last name and then first name.

SELECT subscriberFirstName, subscriberLastName, subscriberAddress,
    subscriberCity, subscriberState, subscriberZIP
FROM subscriber
ORDER BY subscriberLastName, subscriberFirstName

firstName	lastName	address	city	state	ZIP
Albert	Anderson	220 K Street Southeast	Auburn	WA	98002
Jose	Jimenez	187 27th Ave	Seattle	WA	98122
Julie	Johnston	10336 NE 187th St	Bothell	WA	98012
Lucy	Lamont	175 Smokey Point Dr	Lakewood	WA	98409
Samantha	Sanders	316 Union Ave	Snohomish	WA	98290
Walter	Wong	1073 South 323rd Street	Federal Way	WA	98003

List of All Subscriptions

Write a SQL query that produces an list of all subscriptions ordered alphabetically by magazine name, subscriber last name, and then subscriber first name.

SELECT magazineName, subscriberFirstName, subscriberLastName,
    subscriptionStartDate, subscriptionLength
FROM magazine AS m
    INNER JOIN subscription AS p ON m.magazineKey = p.magazineKey
    INNER JOIN subscriber AS s ON p.subscriberKey = s.subscriberKey
ORDER BY magazineName, subscriberLastName, subscriberFirstName

magazineName	firstName	lastName	startDate	length
Beautiful Birds	Lucy	Lamont	2012-02-01	12
Beautiful Birds	Samantha	Sanders	2012-02-01	12
Car Racing Made Easy	Albert	Anderson	2011-03-01	14
Cooking Like Mad	Samantha	Sanders	2011-09-01	12
Corn Shucking for Fun and Profit	Jose	Jimenez	2012-08-01	12
Corn Shucking for Fun and Profit	Lucy	Lamont	2012-07-01	24
Fishing in the Mojave	Jose	Jimenez	2011-09-01	12
Fishing in the Mojave	Julie	Johnston	2011-03-01	12
Fishing in the Mojave	Samantha	Sanders	2011-05-01	12
If Only I Could Sing	Samantha	Sanders	2011-12-01	12
Pine Cone Computing	Samantha	Sanders	2011-05-01	18

List of Subscribers to Fishing in the Mojave

Write a SQL query that produces an list of the last and first names of the people that subscribe to “Fishing in the Mojave”.

SELECT s.subscriberFirstName, s.subscriberLastName
FROM subscriber AS s
    INNER JOIN subscription AS p ON s.subscriberKey = p.subscriberKey
    INNER JOIN magazine AS m ON p.magazineKey = m.magazineKey
WHERE m.magazineName = 'Fishing in the Mojave'
ORDER BY subscriberLastName, subscriberFirstName

firstName	lastName
Jose	Jimenez
Julie	Johnston
Samantha	Sanders

List of Magazines

Write a SQL query that produces a list of all magazines with a count of how many subscribers each magazine has.

SELECT magazineName, COUNT(subscriberKey) AS subscribers
FROM magazine AS m
    INNER JOIN subscription AS p ON m.magazineKey = p.magazineKey
GROUP BY p.magazineKey
ORDER BY magazineName

magazineName	subscribers
Beautiful Birds	2
Car Racing Made Easy	1
Cooking Like Mad	1
Corn Shucking for Fun and Profit	2
Fishing in the Mojave	3
If Only I Could Sing	1
Pine Cone Computing	1

Total Revenue

Write a SQL query that produces a list of all magazines with a count of how many subscribers each magazine has and the amount of total revenue that will be earned from the subscriptions to each magazine. The magazine price that is stored in the magazine table is for a twelve month subscription, so your query must account for length of each subscription.

SELECT magazineName, COUNT(subscriberKey) AS subscribers,
    SUM(magazinePrice) AS totalRevenue
FROM magazine AS m
    INNER JOIN subscription AS p ON m.magazineKey = p.magazineKey
GROUP BY p.magazineKey
ORDER BY magazineName

magazineName	subscribers	totalRevenue
Beautiful Birds	2	24.90
Car Racing Made Easy	1	15.45
Cooking Like Mad	1	18.00
Corn Shucking for Fun and Profit	2	30.10
Fishing in the Mojave	3	41.85
If Only I Could Sing	1	12.45
Pine Cone Computing	1	17.50

List of Active Subscriptions

Write a SQL query that produces a list of subscriptions that were active on October 1, 2011.

SELECT magazineName, subscriberFirstName, subscriberLastName
FROM magazine AS m
    INNER JOIN subscription AS p ON m.magazineKey = p.magazineKey
    INNER JOIN subscriber AS s ON p.subscriberKey = s.subscriberKey
WHERE '2011-10-01' BETWEEN p.subscriptionStartDate AND
    DATE_ADD(p.subscriptionStartDate, INTERVAL p.subscriptionLength MONTH)
ORDER BY magazineName, subscriberLastName, subscriberFirstName

magazineName	firstName	lastName
Car Racing Made Easy	Albert	Anderson
Cooking Like Mad	Samantha	Sanders
Fishing in the Mojave	Jose	Jimenez
Fishing in the Mojave	Julie	Johnston
Fishing in the Mojave	Samantha	Sanders
Pine Cone Computing	Samantha	Sanders