SQL SELECT statement and SELECT query samples

Discover sample SQL SELECT statements and result sets in this excerpt from "SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL, Second Edition." These sample databases and SQL statements will help you understand the many concepts and techniques involving the SELECT statement.

Sample Statements

Now that we've covered the basic characteristics of the SELECT statement and SELECT query, let's take a look at some examples of how these operations are applied in different scenarios. These examples encompass each of the sample databases, and they illustrate the use of the SELECT statement, the SELECT query, and the two supplemental techniques used to establish columns for the translation statement. We've also included sample result sets that would be returned by these operations and placed them immediately after the SQL syntax line. The name that appears immediately above a result set has a twofold purpose: It identifies the result set itself, and it is also the name that we assigned to the SQL statement in the example.

In case you're wondering why we assigned a name to each SQL statement, it's because we saved them! In fact, we've named and saved all the SQL statements that appear in the examples here and throughout the remainder of the book. Each is stored in the appropriate sample database (as indicated within the example), and we prefixed the names of the queries relevant to this chapter with "CH04." You can follow the instructions in the Introduction of this book to load the samples onto your computer. This gives you the opportunity to see these statements in action before you try your hand at writing them yourself.

Note: Just a reminder: All the column names and table names used in these examples are drawn from the sample database structures shown in Appendix B.

Sales Orders Database

"Show me the names of all our vendors."

Translation Select the vendor name from the vendors table
Clean Up Select the vendor name from the vendors table
SQL SELECT VendName
FROM Vendors

CH04_Vendor_Names (10 rows)

 
 VendName
Shinoman, Incorporated
Viscount
Nikoma of America
ProFormance
Kona, Incorporated
Big Sky Mountain Bikes
Dog Ear
Sun Sports Suppliers
Lone Star Bike Supply
Armadillo Brand

"What are the names and prices of all the products we carry?"

Translation Select product name, retail price from the products table
Clean Up Select product name, retail price from the products table
SQL SELECT ProductName, RetailPrice
FROM Products

CH04_Product_Price_List (40 rows)

 
ProductName Retail Price
Trek 9000 Mountain Bike $1,200.00
Eagle FS-3 Mountain Bike $1,800.00
Dog Ear Cyclecomputer $75.00
Victoria Pro All Weather Tires $54.95
Dog Ear Helmet Mount Mirrors $7.45
Viscount Mountain Bike $635.00
Viscount C-500 Wireless Bike Computer $49.00
Kryptonite Advanced 2000 U-Lock $50.00
Nikoma Lok-Tight U-Lock $33.00
Viscount Microshell Helmet $36.00
< < more rows here > >

 "Which states do our customers come from?"

Translation Select the distinct state values from the customers table
Clean Up Select the distinct state values from the customers table
SQL SELECT DISTINCT CustState
FROM Customers

CH04_Customer_States (4 rows)

 
CustState
CA
OR
TX
WA

Entertainment Agency Database

"List all entertainers and the cities they're based in, and sort the results by city and name in ascending order."

Translation Select city and stage name from the entertainers table and order by city and stage name
Clean Up Select city and stage name from the entertainers table and order by city and stage name
SQL SELECT EntCity, EntStageName
FROM Entertainers
ORDER BY EntCity ASC, EntStageName ASC

CH04_Entertainer_Locations (13 rows)

 
EntCity EntStageName
Auburn Caroline Coie Cuartet
Auburn Topazz
Bellevue Jazz Persuasion
Bellevue Jim Glynn
Bellevue Susan McLain
Redmond Carol Peacock Trio
Redmond JV & the Deep Six
Seattle Coldwater Cattle Company
Seattle Country Feeling
Seattle Julia Schnebly
< < more rows here > >

"Give me a unique list of engagement dates. I'm not concerned with how many engagements there are per date."

Translation Select the distinct start date values from the engagements table
Clean Up Select the distinct start date values from the engagements table
SQL SELECT DISTINCT StartDate
FROM Engagements

CH04_Engagement_Dates (64 rows)

 
StartDate
2007-09-01
2007-09-10
2007-09-11
2007-09-15
2007-09-17
2007-09-18
2007-09-24
2007-09-29
2007-09-30
2007-10-01
< < more rows here > >

School Scheduling Database

"Can we view complete class information?"

Translation Select all columns from the classes table
Clean Up Select all columns * from the classes table
SQL SELECT *
FROM Classes

CH04_Class_Information (76 rows)

 
ClassID SubjectID ClassRoomID Credits StartTime Duration < <other columns> >
1000 11 1231 5 10:00 50
1002 12 1619 4 15:30 110
1004 13 1627 4 08:00 50
1006 13 1627 4 09:00 110
1012 14 1627 4 13:00 170
1020 15 3404 4 13:00 110
1030 16 1231 5 11:00 50
1031 16 1231 5 14:00 50
1156 37 3443 5 08:00 50
1162 37 3443 5 09:00 80
< < more rows here > >

"Give me a list of the buildings on campus and the number of floors for each building. Sort the list by building in ascending order."

Translation Select building name and number of floors from the buildings table, ordered by building name
Clean Up Select building name and number of floors from the buildings table , order ed by building name
SQL SELECT BuildingName, NumberOfFloors
FROM Buildings
ORDER BY BuildingName ASC

CH04_Building_List (6 rows)

 
BuildingName NumberOfFloors
Arts and Sciences 3
College Center 3
Instructional Building 3
Library 2
PE and Wellness 1
Technology Building 2

Bowling League Database

"Where are we holding our tournaments?"

Translation Select the distinct tourney location values from the tournaments table
Clean Up Select the distinct tourney location values from the tournaments table
SQL SELECT DISTINCT TourneyLocation
FROM Tournaments

CH04_Tourney_Locations (7 rows)

 
TourneyLocation
Acapulco Lanes
Bolero Lanes
Imperial Lanes
Red Rooster Lanes
Sports World Lanes
Thunderbird Lanes
Totem Lanes

 "Give me a list of all tournament dates and locations. I need the dates in descending order and the locations in alphabetical order."

Translation Select tourney date and location from the tournaments table and order by tourney date in descending order and location in ascending order
Clean Up Select tourney date and location from the tournaments table and order by tourney date in descending order and location in ascending order
SQL SELECT TourneyDate, TourneyLocation
FROM Tournaments
ORDER BY TourneyDate DESC, TourneyLocation ASC

CH04_Tourney_Dates (14 rows)

 
TourneyDate TourneyLocation
2008-08-15 Totem Lanes
2008-08-08 Imperial Lanes
2008-08-01 Sports World Lanes
2008-07-25 Bolero Lanes
2008-07-18 Thunderbird Lanes
2008-07-11 Red Rooster Lanes
2007-12-04 Acapulco Lanes
2007-11-27 Totem Lanes
2007-11-20 Sports World Lanes
2007-11-13 Imperial Lanes
< < more rows here > >

Recipes Database

"What types of recipes do we have, and what are the names of the recipes we have for each type? Can you sort the information by type and recipe name?"

Translation Select recipe class ID and recipe title from the recipes table and order by recipe class ID and recipe title
Clean Up Select recipe class ID and recipe title from the recipes table and order by recipeclass ID and recipe title
SQL SELECT RecipeClassID, RecipeTitle
FROM Recipes
ORDER BY RecipeClassID ASC, RecipeTitle ASC

CH04_Recipe_Classes_And_Titles (15 rows)

 
RecipeClassID RecipeTitle
1 Fettuccini Alfredo
1 Huachinango Veracruzana
(Red Snapper, Veracruz style)
1 Irish Stew
1 Pollo Picoso
1 Roast Beef
1 Salmon Filets in Parchment Paper
1 Tourtière
(French-Canadian Pork Pie)
2 Asparagus
2 Garlic Green Beans
3 Yorkshire Pudding
< < more rows here > >

 "Show me a list of unique recipe class IDs in the recipes table."

Translation Select the distinct recipe class ID values from the recipes table
Clean Up Select the distinct recipe class ID values from the recipes table
SQL SELECT DISTINCT RecipeClassID
FROM Recipes

CH04_Recipe_Class_Ids(6 rows)

 
RecipeClassID
1
2
3
4
5
6

Summary

In this chapter, we introduced the SELECT operation, and you learned that it is one of four data manipulation operations in SQL. (The others are UPDATE, INSERT, and DELETE, covered in Part V.) We also discussed how the SELECT operation can be divided into three smaller operations: the SELECT statement, the SELECT expression, and the SELECT query.

The discussion then turned to the SELECT statement, where you were introduced to its component clauses. We covered the fact that the SELECT and FROM clauses are the fundamental clauses required to retrieve information from the database and that the remaining clauses—WHERE, GROUP BY, and HAVING—are used to conditionally process and filter the information returned by the SELECT clause.

We briefly diverged into a discussion of the difference between data and information. You learned that the values stored in the database are data and that information is data that has been processed in a manner that makes it meaningful to the person viewing it. You also learned that the rows of information returned by a SELECT statement are known as a result set.

Retrieving information was the next topic of discussion, and we began by presenting the basic form of the SELECT statement. You learned how to build a proper SELECT statement by using a three-step technique that involves taking a request and translating it into proper SQL syntax. You also learned that you could use two or more columns in the SELECT clause to expand the scope of information you retrieve from your database. We followed this section with a quick look at the DISTINCT keyword, which you learned is the means for eliminating duplicate rows from a result set.

Next, we looked at the SELECT query and how it can be combined with a SELECT statement to sort the SELECT statement's result set. You learned that this is necessary because the SELECT query is the only SELECT operation that contains an ORDER BY clause. We went on to show that the ORDER BY clause is used to sort the information by one or more columns and that each column can have its own ascending or descending sort specification. A brief discussion on saving your SELECT statements followed, and you learned that you can save your statement as a query or a view for future use.

Finally, we presented a number of examples using various tables in the sample databases. The examples illustrated how the various concepts and techniques presented in this chapter are used in typical scenarios and applications. In the next chapter, we'll take a closer look at the SELECT clause and show you how to retrieve something besides information from a list of columns.

The following section presents a number of requests that you can work out on your own.

Problems for You to Solve

Below, we show you the request statement and the name of the solution query in the sample databases. If you want some practice, you can work out the SQL you need for each request and then check your answer with the query we saved in the samples. Don't worry if your syntax doesn't exactly match the syntax of the queries we saved—as long as your result set is the same.

Sales Orders Database

  1. "Show me all the information on our employees."
    You can find the solution in CH04_Employee_Information (8 rows).
  2. "Show me a list of cities, in alphabetical order, where our vendors are located, and include the names of the vendors we work with in each city."
    You can find the solution in CH04_Vendor_Locations (10 rows).

Entertainment Agency Database

  1. "Give me the names and phone numbers of all our agents, and list them in last name/first name order."
    You can find the solution in CH04_Agent_Phone_List (9 rows).
  2. "Give me the information on all our engagements."
    You can find the solution in CH04_Engagement_Information (111 rows).
  3. "List all engagements and their associated start dates. Sort the records by date in descending order and by engagement in ascending order."
    You can find the solution in CH04_Scheduled_Engagements (111 rows).

School Scheduling Database

  1. "Show me a complete list of all the subjects we offer."
    You can find the solution in CH04_Subject_List (56 rows).
  2. "What kinds of titles are associated with our faculty?"
    You can find the solution in CH04_Faculty_Titles (3 rows).
  3. "List the names and phone numbers of all our staff, and sort them by last name and first name."
    You can find the solution in CH04_Staff_Phone_List (27 rows).

Bowling League Database

  1. "List all of the teams in alphabetical order."
    You can find the solution in CH04_Team_List (8 rows).
  2. "Show me all the bowling score information for each of our members."
    You can find the solution in CH04_Bowling_Score_Information (1,344 rows).
  3. "Show me a list of bowlers and their addresses, and sort it in alphabetical order."
    You can find the solution in CH04_Bowler_Names_Addresses (32 rows).

Recipes Database

  1. "Show me a list of all the ingredients we currently keep track of."
    You can find the solution in CH04_Complete_Ingredients_List (79 rows).
  2. "Show me all the main recipe information, and sort it by the name of the recipe in alphabetical order."
    You can find the solution in CH04_Main_Recipe_Information (15 rows).

TABLE OF CONTENTS


Exchange 2007 Storage Systems This chapter excerpt from  SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (2nd Edition) by John L. Viescas and Michael J. Hernandez, is printed with permission from Addison-Wesley Professional, Copyright 2007.

Click here for the chapter download or purchase the book here.

This was first published in July 2008

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close