Page 101
10231325 10231325 10231326 10231326 10231328 10231328 10231329 10231329 10231327 10231327 10231330 10231330 10231316 10231316 10231303 10231303 10231310 10231310 10231318 10231318 10231324 10231324 10231304 10231304 10231312 10231312 10231321 10231321 10231313 10231313 10231311 10231311 10231323 10231323 10231307 10231307 31 rows selected.
| TIP |
You can perform arithmetic computations by selecting the problem from a utility table named DUAL, as inselect 3.14159*20 from dual; |
The results returned by every SELECT statement constitute a temporary table. Each retrieved record is a row in this temporary table, and each element of the select list is a column. If a query doesn't return any records, the temporary table can be thought of as empty. This behavior is a fundamental principle of the relational model.
In addition to specifying columns, you also can specify expressions in the select list. Expressions fall into the same datatypes as columns—character, numeric, and date. Through the use of operators, built-in functions, and constants, you can construct complex expressions to meet the needs of your application.
Keep in mind that Oracle considers each element in the select list to be a separate column, even if that expression references multiple columns.
Page 102
The arithmetic and logical operators used in SQL are similar to those used in C:
|
Description
|
Operator
|
| Addition | + |
| Subtraction | _ |
| Multiplication | * |
| Division | / |
| Less than | < |
| Less than or equal to | <= |
| Greater than | > |
| Greater than or equal to | >= |
| Equal to | = |
| Not equal to | != |
As an example, the Course table contains the units for each course. If the current cost per unit is $250, the cost of the course is obtained with the SELECT statement in Listing 5.4.
Listing 5.4. Using an arithmetic operator in a SELECT statement.
SQL> select Title, Units*250 2 from Course; TITLE UNITS*250 ---------------------------------------- --------- INTRO TO ANTHROPOLOGY 750 PRE-CALCULUS 750 GENERAL CALCULUS 750 NUMBER THEORY 750 ADVANCED ARITHMETIC 750 INTRO TO PSYCHOLOGY 750 ABNORMAL PSYCHOLOGY 750 EARLY AMERICAN HISTORY 750 MODERN EUROPEAN HISTORY 750 INTRO TO BIOLOGY 750 INTRO TO ECONOMICS 750 MONETARY POLICY 750 INTRO TO PHILOSOPHY 750 INTRO TO ENGLISH LIT 750 MODERN ENGLISH LIT 750 INTRO TO STRUCTURES 750 MODERN PHILOSOPHY 750 ANCIENT GREEK PHILOSOPHERS 750 MAMMALIAN ANATOMY 750 INVERTEBRATE ANATOMY 750 WORKSHOP ON MARX 750 WORKSHOP ON JEFFERSON 750
Page 103
INTRO TO CIRCUIT THEORY 750 INTRO TO DYNAMICS 750 SEMINAR ON CHAOS 500 SEMINAR ON NACIMERA 500 PHYS ANTHRO FIELD TECHNIQUES 750 EVOLUTIONARY GRAMMAR 500 SEMINAR ON THEME ANALYSIS 500 WORKSHOP ON NEUROSES 500 WORKSHOP ON NORMALITY 500 31 rows selected.
| TIP |
Instead of trying to remember the precedence rules for arithmetic operators used in SQL statements, you should always use parentheses if you're uncertain about the correct way to evaluate an expression. |
One of the most important string operators in SQL is the concatenation operator, ||. SQL syntax requires that string constants be enclosed in single quotes. This operator enables you to concatenate two or more strings, as shown in Listing 5.5.
Listing 5.5. Using a string operator.
SQL> select Last_Name || `: ` || Position 2 from Instructor; LAST_NAME||':'||POSITION ---------------------------------------------------- HITCHCOCK: PROFESSOR DANIELS: ASSOCIATE PROFESSOR EDWARDS: PROFESSOR CHANG: ASSISTANT PROFESSOR JASON: ASSOCIATE PROFESSOR TORRES: PROFESSOR ANGELO: ASSOCIATE PROFESSOR RICHARDSON: ASSISTANT PROFESSOR PARKER: PROFESSOR CHERNOW: ASSOCIATE PROFESSOR CHU: PROFESSOR WEISS: PROFESSOR RESTON: ASSOCIATE PROFESSOR BILLINGS: ASSISTANT PROFESSOR YOUNG: ASSOCIATE PROFESSOR NILAND: ASSOCIATE PROFESSOR BATES: ASSISTANT PROFESSOR 17 rows selected.
Page 104
The following are several reasons why you would want to concatenate strings:
select Salutation || ` ` || Last_Name from Customer order by Last_Name;
You can use the concatenation operator with more than two strings, as shown in Listing 5.6.
Listing 5.6. Multiple concatenations.
SQL> select Last_Name || `, ` || Position || `, can be contacted at ` || 2 Email 3 from Instructor; LAST_NAME||','||POSITION||',CANBECONTACTEDAT'||EMAIL ---------------------------------------------------------------------- HITCHCOCK, PROFESSOR, can be contacted at BHITCHCOCK@FLUGLE.EDU DANIELS, ASSOCIATE PROFESSOR, can be contacted at LDANIELS@FLUGLE.EDU EDWARDS, PROFESSOR, can be contacted at SEDWARDS@FLUGLE.EDU CHANG, ASSISTANT PROFESSOR, can be contacted at RCHANG@FLUGLE.EDU JASON, ASSOCIATE PROFESSOR, can be contacted at JJASON@FLUGLE.EDU TORRES, PROFESSOR, can be contacted at PTORRES@FLUGLE.EDU ANGELO, ASSOCIATE PROFESSOR, can be contacted at AANGELO@FLUGLE.EDU RICHARDSON, ASSISTANT PROFESSOR, can be contacted at ÂNRICHARDSON@FLUGLE.EDU PARKER, PROFESSOR, can be contacted at WPARKER@FLUGLE.EDU CHERNOW, ASSOCIATE PROFESSOR, can be contacted at BCHERNOW@FLUGLE.EDU CHU, PROFESSOR, can be contacted at SCHU@FLUGLE.EDU WEISS, PROFESSOR, can be contacted at RWEISS@FLUGLE.EDU RESTON, ASSOCIATE PROFESSOR, can be contacted at RRESTON@FLUGLE.EDU BILLINGS, ASSISTANT PROFESSOR, can be contacted at BBILLINGS@FLUGLE.EDU YOUNG, ASSOCIATE PROFESSOR, can be contacted at AYOUNG@FLUGLE.EDU NILAND, ASSOCIATE PROFESSOR, can be contacted at MNILAND@FLUGLE.EDU BATES, ASSISTANT PROFESSOR, can be contacted at JBATES@FLUGLE.EDU 17 rows selected.
Page 105
Oracle provides a rich set of built-in functions that you can use to manipulate and convert different types of data. These functions can be categorized as
You'll find explanations of many of these functions on Day 7, "Taking Advantage of SQL Built-In Functions."
You usually don't want to retrieve all the rows in a table, particularly if the table has many rows. SQL provides a WHERE clause in which you specify the criteria to be used for retrieving records.
A WHERE clause consists of one or more conditions that must be satisfied before a row is retrieved by the query. For example, if you want a list of freshmen attending Flugle College, you would specify Year = `FRESHMAN' in the WHERE clause, as shown in Listing 5.7.
Listing 5.7. Specifying a criterion in a WHERE clause.
SQL> select Last_Name, First_Name, Street_Address 2 from Student 3 where 4 Year = `FRESHMAN'; LAST_NAME FIRST_NAME STREET_ADDRESS ------------------------ ------------------------ ------------------ SMYTHE JACKSON 144 OLIVE AVE. REYNOLDS PAULA 7493 MAPLE ST. TANAKA JEFFREY 838 PECAN RD. MALLARD HENRY 123 WALNUT DR. POSEN HUGO 9100 MAPLE ST. CLAUSEN THOMAS 901 BIRCH RD. GOMEZ LINDA 9391 MAPLE ST. MASSEY RICHARD 431 PINE AVE. ANASTATIA ANNA 831 BIRCH RD. HOLMES IVAN 221 OLIVE AVE. 10 rows selected.