SQL SELECT statement and SELECT query samples

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.

Sales Orders Database

"Show me the names of all our vendors."

CH04_Vendor_Names (10 rows)

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

CH04_Product_Price_List (40 rows)

 "Which states do our customers come from?"

CH04_Customer_States (4 rows)

Entertainment Agency Database

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

CH04_Entertainer_Locations (13 rows)

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

CH04_Engagement_Dates (64 rows)

School Scheduling Database

"Can we view complete class information?"

CH04_Class_Information (76 rows)

"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."

CH04_Building_List (6 rows)

Bowling League Database

"Where are we holding our tournaments?"

CH04_Tourney_Locations (7 rows)

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

CH04_Tourney_Dates (14 rows)

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?"

CH04_Recipe_Classes_And_Titles (15 rows)

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

CH04_Recipe_Class_Ids(6 rows)

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

• 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.