Home arrow Oracle arrow Page 2 - Retrieve Data with the Select Statement

SELECT statement examples - Oracle

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).

TABLE OF CONTENTS:
  1. Retrieve Data with the Select Statement
  2. SELECT statement examples
By: Murach Publishing
Rating: starstarstarstarstar / 0
June 30, 2011

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Murach Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: