Sorting Database Columns With the SELECT Statement

In this conclusion to a multi-part series on using the SELECT statement in an Oracle database, you’ll learn how to sort a result set by an alias, an expression, or a column number. This article is excerpted from chapter three of the book Murach’s Oracle SQL and PL/SQL, written by Joel Murach (Murach Publishing; ISBN: 9781890774509).

How to sort a result set by an alias, an expression, or a column number

Figure 3-18 presents three more techniques that you can use to specify sort columns. First, you can use a column alias that’s defined in the SELECT clause. The first SELECT statement in this figure, for example, sorts by a column named address, which is an alias for the concatenation of the vendor_city, vendor_state, and vendor_zip_code columns. Within the address column, the result set is sorted by the vendor_name column.

You can also use an arithmetic or string expression in the ORDER BY clause, as illustrated by the second example in this figure. Here, the expression consists of the vendor_contact_last_name column concatenated with the vendor_contact_first_name column. Here, neither of these columns is included in the SELECT clause.

The last example in this figure shows how you can use column numbers to specify a sort order. To use this technique, you code the number that corresponds to the column of the result set, where 1 is the first column, 2 is the second column, and so on. In this example, the ORDER BY clause sorts the result set by the second column, which contains the concatenated address, then by the first column, which contains the vendor name. The result set returned by this statement is the same as the result set returned by the first statement.

Notice, however, that the statement that uses column numbers is more difficult to read because you have to look at the SELECT clause to see what columns the numbers refer to. In addition, if you add or remove columns from the SELECT clause, you may also have to change the ORDER BY clause to reflect the new column positions. As a result, we don’t recommend this coding technique.

An ORDER BY clause that uses an alias

SELECT vendor_name,
vendor_city || ‘, ‘ || vendor_state || ‘ ‘ || vendor_zip_code AS address
FROM vendors
ORDER BY address, vendor_name

An ORDER BY clause that uses an expression

SELECT vendor_name,
vendor_city || ‘, ‘ || vendor_state || ‘ ‘ || vendor_zip_code AS address
FROM vendors
ORDER BY vendor_contact_last_name || vendor_contact_first_name

An ORDER BY clause that uses column positions

SELECT vendor_name,
vendor_city || ‘, ‘ || vendor_state || ‘ ‘ || vendor_zip_code AS address
FROM vendors
ORDER BY 2, 1

Description

  1. The ORDER BY clause can include a column alias that’s specified in the SELECT clause.
  2. The ORDER BY clause can include any valid expression. The expression can refer to any column in the base table, even if it isn’t included in the result set.
  3. The ORDER BY clause can use numbers to specify the columns to use for sorting. In that case, 1 represents the first column in the result set, 2 represents the second column, and so on.

——————————————–Figure 3-18 How to sort a result set by an alias, an expression, or a column number

{mospagebreak title=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.

Terms

base table order of precedence

keyword function

filter parameter

Boolean expression date literal

predicate comparison operator

expression logical operator

column alias compound condition

string expression pseudo column

concatenate subquery

concatenation operator string pattern

literal value mask

string literal wildcard

string constant null value

arithmetic expression nested sort

arithmetic operator

Exercises

Run some of the examples in this chapter

In these exercises, you’ll use Oracle SQL Developer to run some of the scripts for the examples in this chapter. This assumes that you already know how to use SQL Developer, as described in chapter 2.

  1. Start Oracle SQL Developer.
  2. Open the script for fig3-02a that you should find in this directory: c:murachoracle_sqlscriptsch03. Then, press the F9 key or click on the Execute Statement button to run the script. This shows you the data that’s in the Invoices table that you’ll be working with in this chapter.
  3. Open and run the script for fig3-02b.
  4. Open and run the scripts for any of the other examples in this chapter that you’re interested in reviewing.

    Enter and run your own SELECT statements

    In these exercises, you’ll enter and run your own SELECT statements. To do that, you can open the script for an example that is similar to the statement you need to write, copy the statement into a new Worksheet window, and then modify the statement. That can save you both time and syntax errors.
  5. Write a SELECT statement that returns three columns from the Vendors table: vendor_name, vendor_contact_last_name, and vendor_contact_first_name. Then, run this statement.

    Next, add code to this statement so it sorts the result set by last name and then first name. Then, run this statement again. This is a good way to build and test a statement, one clause at a time.
  6. Write a SELECT statement that returns one column from the Vendors table named full_name. Create this column from the vendor_contact_first_name and vendor_contact_last_name columns, and format it like this: last name, comma, space, first name (for example, “Doe, John”). Next, sort the result set by last name and then first name. Then, filter the result set for contacts whose last name begins with the letter A, B, C, or E.
  7. Write a SELECT statement that returns four columns from the Invoices table named Due Date, Invoice Total, 10%, and Plus 10%. These columns should contain this data:

    Due Date The invoice_due_date column

    Invoice Total The invoice_total column

    10% 10% of the value of invoice_total

    Plus 10% The value of invoice_total plus 10%

    (For example, if invoice_total is 100, 10% is 10, and Plus 10% is 110.) Next, filter the result set so it returns only those rows with an invoice total that’s greater than or equal to 500 and less than or equal to 1000. Then, sort the result set in descending sequence by invoice_due_date.
  8. Write and run a SELECT statement that returns four columns from the Invoices table named Number, Total, Credits, and Balance Due. These columns should include this data:

    Number The invoice_number column

    Total The invoice_total column

    Credits Sum of the payment_total and credit_total columns

    Balance Due Invoice_total minus the sum of payment_total and credit_total

    Next, filter for invoices with a balance due that’s greater than or equal to $500. Then, sort the result set by balance due in descending sequence. Last, use the ROWNUM pseudo column so the result set contains only the rows with the 10 largest balance dues.

    Work with nulls and use the Dual table
  9. Write a SELECT statement that returns the balance due and the payment date from the Invoices table, but only when the payment_date column contains a null value. Then, modify the WHERE clause so it returns any invalid rows (rows in which the balance due is zero and the payment date is null).
  10. Use the Dual table to create a row with these columns:

    Starting Principal Starting principle which should be equal to $51,000

    New Principal Starting principal plus a 10% increase

    Interest 6.5% of the new principal

    Principal + Interest The new principal plus the interest (add the expression you used for the new principal calculation to the expression you used for the interest calculation)

    Now, add a column named “System Date” that uses the TO_CHAR function to show the results of the SYSDATE function when it’s displayed with this format:

    ‘dd-mon-yyyy hh24:mi:ss’

    This format will display the day, month, year, hours, minutes, and seconds of the system date, and this will show that the system date also includes a time. (You should be able to figure out how to use the TO_CHAR and SYSDATE functions by studying figure 3-7.)
[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye