SELECT statement examples - Oracle

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.