Chapter 3: How to retrieve data from a single table

In this chapter, you’ll learn how to code SELECT statements that retrieve data from a single table.

Perspective

The goal of this chapter has been to teach you the basic skills for coding SELECT statements. You'll use these skills in almost every SELECT statement you code. As you'll see in the chapters that follow, however, there's a lot more to coding SELECT statements than what's presented here. In the next three chapters, then, you'll learn additional skills for coding SELECT statements. When you complete those chapters, you'll know everything you need to know about retrieving data from a SQL Server database.

Terms

keyword
filter
Boolean expression
predicate
expression
column alias
substitute name
string expression
concatenate
concatenation operator
literal value
string literal
string constant
arithmetic expression
arithmetic operator
order of precedence
function
parameter
argument
date literal
comparison operator
logical operator
compound condition
subquery
string pattern
mask
wildcard
Integrated Full-Text Search (iFTS)
null value
nested sort

Exercises

1. Write a SELECT statement that returns three columns from the Vendors table: VendorContactFName, VendorContactLName, and VendorName. Sort the result set by last name, then by first name.
    
2. Write a SELECT statement that returns four columns from the Invoices table, named Number, Total, Credits, and Balance:
   
   Number Column alias for the InvoiceNumber column
   Total Column alias for the InvoiceTotal column
   Credits Sum of the PaymentTotal and CreditTotal columns
   Balance InvoiceTotal minus the sum of PaymentTotal and
   CreditTotal
   
   a. Use the AS keyword to assign column aliases.
   b. Use the = assignment operator to assign column aliases.
    
3. Write a SELECT statement that returns one column from the Vendors table named Full Name. Create this column from the VendorContactFName and VendorContactLName columns. Format it as follows: last name, comma, first name (for example, "Doe, John"). Sort the result set by last name, then by first name.
    
4. Write a SELECT statement that returns three columns:
   
   InvoiceTotal From the Invoices table
   10% 10% of the value of InvoiceTotal
   Plus 10% The value of InvoiceTotal plus 10%
   
   (For example, if InvoiceTotal is 100.0000, 10% is 10.0000, and Plus 10% is 110.0000.) Only return those rows with a balance due greater than 1000. Sort the result set by InvoiceTotal, with the largest invoice first.
    
5. Modify the solution to exercise 2a to filter for invoices with an InvoiceTotal that's greater than or equal to $500 but less than or equal to $10,000.
   
   
6. Modify the solution to exercise 3 to filter for contacts whose last name begins with the letter A, B, C, or E.
   
   
7. Write a SELECT statement that determines whether the PaymentDate column of the Invoices table has any invalid values. To be valid, PaymentDate must be a null value if there's a balance due and a non-null value if there's no balance due. Code a compound condition in the WHERE clause that tests for these conditions.