Retrieve Data with the Select Statement

Working with databases means retrieving data from tables. This is usually done with the SELECT statement. This nine-part article series will teach you all about this workhouse of the coding world. It is excerpted from chapter three of the book Murach’s Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

In this chapter, you’ll learn how to code SELECT statements that retrieve data from a single table. You should realize, though, that the skills covered here are the essential ones that apply to any SELECT statement, no matter how many tables it operates on and no matter how complex the retrieval. So you’ll want to have a good understanding of the material in this chapter before you go on to the chapters that follow.

An introduction to the SELECT statement

To help you learn to code the SELECT statement, this chapter starts by presenting its basic syntax. Next, it presents several examples that will give you an idea of what you can do with this statement. Then, the rest of this chapter will teach you the details of coding this statement.

The basic syntax of the SELECT statement

Figure 3-1 presents the basic syntax of the SELECT statement. The syntax summary at the top of this figure uses some conventions that are used throughout this book. First, capitalized words are called keywords, and you must spell them exactly as shown, though you can use whatever capitalization you prefer. For example, the following are equivalent: “SELECT”, “Select”, “select” and “sELeCt”. Second, you must provide replacements for words in lowercase. For example, you must enter a list of columns in place of select_list, and you must enter a table name in place of table_source.

Beyond that, you can choose between the items in a syntax summary that are separated by pipes (|) and enclosed in braces ({ }) or brackets ([]). And you can omit items enclosed in brackets. If you have a choice between two or more optional items, the default item is underlined. And if an element can be coded multiple times in a statement, it’s followed by an ellipsis (…). You’ll see examples of pipes, braces, default values, and ellipses in syntax summaries later in this chapter. For now, compare the syntax in this figure with the coding examples in figure 3-2 to see how the two are related.

The syntax summary in this figure has been simplified so that you can focus on the four main clauses of the SELECT statement: the SELECT clause, the FROM clause, the WHERE clause, and the ORDER BY clause. Most of the SELECT statements you code will contain all four clauses. However, only the SELECT and FROM clauses are required.

The SELECT clause is always the first clause in a SELECT statement. It identifies the columns you want to include in the result set. These columns are retrieved from the base tables named in the FROM clause. Since this chapter focuses on retrieving data from a single table, the FROM clauses in all of the statements in this chapter name a single base table. In the next chapter, though, you’ll learn how to retrieve data from two or more tables. And as you progress through this book, you’ll learn how to select data from other sources such as views and expressions.

The WHERE and ORDER BY clauses are optional. The ORDER BY clause determines how the rows in the result set are to be sorted, and the WHERE clause determines which rows in the base table are to be included in the result set. The WHERE clause specifies a search condition that’s used to filter the rows in the base table. This search condition can consist of one or more Boolean expressions, or predicates. A Boolean expression is an expression that evaluates

The simplified syntax of the SELECT statement

  SELECT select_list
  FROM table_source
  [WHERE search_condition]
  [ORDER BY order_by_list]

The four clauses of the SELECT statement  

ClauseClauseClause

Description

SELECT

Describes the columns that will be included in the result set.

FROM

Names the table from which the query will retrieve the data.

WHERE

Specifies the conditions that must be met for a row to be included in the result set. This clause is optional.

ORDER BY

Specifies how the rows in the result set will be sorted. This clause is optional.

Description

  1. You use the basic SELECT statement shown above to retrieve the columns specified in the SELECT clause from the base table specified in the FROM clause and store them in a result set.
  2. The WHERE clause is used to filter the rows in the base table so that only those rows that match the search condition are included in the result set. If you omit the WHERE clause, all of the rows in the base table are included. 
     
  3. The search condition of a WHERE clause consists of one or more Boolean expressions, or predicates, that result in a value of True, False, or Unknown. If the combination of all the expressions is True, the row being tested is included in the result set. Otherwise, it’s not. 
     
  4. If you include the ORDER BY clause, the rows in the result set are sorted in the specified sequence. Otherwise, the sequence of the rows is not guaranteed by Oracle.

Note

  • The syntax shown above does not include all of the clauses of the SELECT statement. You’ll learn about the other clauses later in this book.

——————————————–Figure 3-1   The basic syntax of the SELECT statement

to True or False. When the search condition evaluates to True, the row is included in the result set.

In this book, we won’t use the terms “Boolean expression” or “predicate” because they don’t clearly describe the content of the WHERE clause. Instead, we’ll just use the term “search condition” to refer to an expression that evaluates to True or False.

{mospagebreak title=SELECT statement examples}

Figure 3-2 presents five SELECT statement examples. All of these statements retrieve data from the Invoices table.

The first statement in this figure retrieves all of the rows and columns from the Invoices table. This statement uses an asterisk (*) as a shorthand to indicate that all of the columns should be retrieved, and the WHERE clause is omitted so there are no conditions on the rows that are retrieved. You can see the results after this statement as they’re displayed by SQL Developer. Here, both horizontal and vertical scroll bars are displayed, indicating that the result set contains more rows and columns than can be displayed on the screen at one time.

Notice that this statement doesn’t include an ORDER BY clause. Without an ORDER BY clause, Oracle doesn’t guarantee the sequence in which the rows are presented. They might be in the sequence you expect, or they might not. As a result, if the sequence matters to you, you should include an ORDER BY clause.

The second statement retrieves selected columns from the Invoices table. As you can see, the columns to be retrieved are listed in the SELECT clause. Like the first statement, this statement doesn’t include a WHERE clause, so all the rows are retrieved. Then, the ORDER BY clause causes the rows to be sorted by the invoice_total column in ascending sequence. Later in this chapter, you’ll learn how to sort rows in descending sequence.

The third statement also lists the columns to be retrieved. In this case, though, the last column is calculated from two columns in the base table (credit_total and payment_total), and the resulting column is given the name total_credits. In addition, the WHERE clause specifies that only the invoice with an invoice_id of 17 should be retrieved.

The fourth SELECT statement includes a WHERE clause whose condition specifies a range of values. In this case, only invoices with invoice dates between May 1, 2008 and May 31, 2008 are retrieved. In addition, the rows in the result set are sorted by invoice date.

The last statement in this figure shows another variation of the WHERE clause. In this case, only those rows with an invoice_total greater than 50,000 are retrieved. Since none of the rows in the Invoices table satisfies this condition, the result set is empty.

A SELECT statement that retrieves all the data from the Invoices table

  SELECT *
  FROM invoices


(114 rows selected)

A SELECT statement that retrieves three columns from each row, sorted in ascending sequence by invoice_total

  SELECT invoice_number, invoice_date, invoice_total
  FROM invoices
  ORDER BY invoice_total


(114 rows selected)

A SELECT statement that retrieves two columns and a calculated value for a specific invoice

  SELECT invoice_id, invoice_total,
         (credit_total + payment_total) AS total_credits
  FROM invoices
  WHERE invoice_id = 17

A SELECT statement that retrieves all invoices between given dates

  SELECT invoice_number, invoice_date, invoice_total
  FROM invoices
  WHERE invoice_date BETWEEN ’01-MAY-2008′ AND ’31-MAY-2008′
  ORDER BY invoice_date


(70 rows selected)

A SELECT statement that returns an empty result set

  SELECT invoice_number, invoice_date, invoice_total
  FROM invoices
  WHERE invoice_total > 50000

——————————————–Figure 3-2   SELECT statement examples

Please check back for the second part of this series.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan