SQL Queries for Mere Mortals: A Hands-On Guide to Data Manipulation in SQL (3rd Edition) by John Viescas & Michael J. Hernandez

Author:John Viescas & Michael J. Hernandez
Language: eng
Format: mobi
Publisher: Pearson Education
Published: 2014-06-10T04:00:00+00:00

5. Getting More Than Simple Columns

“Facts are stubborn things.”

—Tobias Smollett

Gil Blas de Santillane

Topics Covered in This Chapter

What Is an Expression?

What Type of Data Are You Trying to Express?

Changing Data Types: The CAST Function

Specifying Explicit Values

Types of Expressions

Using Expressions in a SELECT Clause

That “Nothing” Value: Null

Sample Statements

Summary

Problems for You to Solve

In Chapter 4, “Creating a Simple Query,” you learned how to use a SELECT statement to retrieve information from one or more columns in a table. This technique is useful if you’re posing only simple requests to the database for some basic facts. However, you’ll need to expand your SQL vocabulary when you begin working with complex requests. In this chapter, we’ll introduce you to the concept of an expression as a way to manipulate the data in your tables to calculate or generate new columns of information. Next, we’ll discuss how the type of data stored in a column can have an important impact on your queries and the expressions you create. We’ll take a brief detour to the CAST function, which you can use to actually change the type of data you’re including in your expressions. You’ll learn to create a constant (or literal) value that you can use in creative ways in your queries. You’ll learn to use literals and values from columns in your table to create expressions. You’ll learn how to adjust the scope of information you retrieve with a SELECT statement by using expressions to manipulate the data from which the information is drawn. Finally, you’ll explore the special Null value and learn how it can impact how you work with expressions that use columns from your tables.

What Is an Expression?

To get more than simple columns, you need to create an expression. An expression is some form of operation involving numbers, character strings, or dates and times. It can use values drawn from specific columns in a table, constant (literal) values, or a combination of both. We’ll show you how to generate literal values later in this chapter. After your database completes the operation defined by the expression, the expression returns a value to the SQL statement for further processing. You can use expressions to broaden or narrow the scope of the information you retrieve from the database. Expressions are especially useful when you are asking “what if” questions. Here’s a sample of the types of requests you can answer using expressions:

“What is the total amount for each line item?”

“Give me a mailing list of employees, last name first.”

“Show me the start time, end time, and duration for each class.”

“Show the difference between the handicap score and the raw score for each bowler.”

“What is the estimated per-hour rate for each engagement?”

“What if we raised the prices of our products by 5 percent?”

As you’ll learn as you work through this chapter, expressions are a very valuable technique to add to your knowledge of SQL. You can use expressions to “slice and dice” the plain-vanilla data in your columns to create more meaningful results in your queries.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.