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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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
- "Show me all the information on our employees."
You can find the solution in CH04_Employee_Information (8 rows). - "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
- "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). - "Give me the information on all our engagements."
You can find the solution in CH04_Engagement_Information (111 rows). - "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
- "Show me a complete list of all the subjects we offer."
You can find the solution in CH04_Subject_List (56 rows). - "What kinds of titles are associated with our faculty?"
You can find the solution in CH04_Faculty_Titles (3 rows). - "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
- "List all of the teams in alphabetical order."
You can find the solution in CH04_Team_List (8 rows). - "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). - "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
- "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). - "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
- Part 1: How to use the SELECT statement in SQL
- Part 2: Translating information requests into SQL SELECT statements
- Part 3: Using DISTINCT in SQL to eliminate duplicate rows
- Part 4: Using the ORDER BY clause of the SELECT query in SQL
- Part 5: SQL SELECT statement and SELECT query samples
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.