The SELECT Statement
The SELECT command is used to retrieve data from a table.
Format:
SELECT [ALL | DISTINCT] {select-list | *}
FROM table-name
[WHERE search-condition]
[GROUP BY column-name [HAVING search-condition]]
[ORDER BY { column-name
| integer }
[ASC | DESC]
[,{ colname | integer }
[ASC |
DESC]]..]
ALL | DISTINCT: ALL specifies that duplicate rows will
be
displayed. If DISTINCT is specified, duplicate values will not be
selected. If
the clause is omitted, ALL is assumed.
* indicates that all columns in the table are to be selected.
select-list: the list of items, separated by commas, that is to be selected. The items are retrieved in the same order as they are listed in the select-list. The items can be column-names or expressions. The item may be preceded by one of the built-in functions; if a column built-in function is used in a select-list, all items in the select-list must contain a column built-in function unless the GROUP BY clause is being used.
FROM table-name: specifies the table or view from which the data is to be selected.
WHERE search-condition: is the condition to be applied in selecting the data; determines which rows are selected
GROUP BY column-name: this clause allows the built-in functions to be applied to groups of rows which have the same value for the column-name given.
A SELECT which includes a GROUP BY clause can also have a WHERE clause. In this case, only the rows satisfying the search-condition are grouped.
HAVING search-condition: specifies one or more conditions to be applied to groups, before the group is selected. That is, only groups which satisfy the condition will be selected.
ORDER BY { column-name | integer }: sorts the rows selected by the columns specified. If an integer is given, it specifies a column by giving its position in the select-list. Sort keys are listed left to right, major to minor.
ASC | DESC: specifies ascending or descending order. If omitted, ascending is assumed.
Examples:
To select all the data from the MANAGER table:
select * from manager;
To display the portfolio number and cash in the portfolio table:
select portfoliono,cash from portfolio;
Constants
Character string constants must be enclosed in single quotes ('). For example: 'Smith'
If the string must contain the character ', use two single quotes. For example: 'isn''t' would be stored internally as the string: isn't.
Date and time constants can be entered in the same way as character string constants - enclosed in single quotes; enter the date or time string in one of the valid date/time formats. For example, the following command would assign the date June 3, 2000 to the PURCHASEDATE column for the given row in the HOLDING table:
update holding set
purchasedate
= '03-Jun-2000'
where portfoliono = 'P003'
and
stocksymbol ='JFM';
Numeric constants consists of digits, a decimal (for non-integer values) with an optional sign. For example:
102 +36 3.1416 -506.7
are examples of valid numeric constants.
Arithmetic Expressions
An arithmetic expression is a combination of numeric valued column names and constants using the arithmetic operators + (add), - (subtract), * (multiply) and / (divide). Parentheses () can be used to group items in the expression.
For example:
select portfoliono,
stocksymbol,
purchasedate, numbershares*purchaseprice
from holding;
Normal precedence rules are applied to expressions involving more
than one
arithmetic operator.
String Expressions
The concatenation operator, ||, can be used to combine two values of type character. The operands can be character column values or string constants.
For example, if LAST_NAME has the value White and FIRST_NAME has the value J.S., the following expression:
LAST_NAME || ', ' || FIRST_NAME
will return the value: White,J.S.
Using Date and Time Operands in Expressions
Date and time values can be used in limited arithmetic expressions. The values can be incremented, decremented or subtracted (date + number, date – number).
If you add or subtract a constant from a date, the constant is assumed to be in days; for example:
sysdate + 7
will yield a date one week after the current date.
If two dates are subtracted (date1 – date2), the expression will yield the number of days between the two dates. For example:
Assume HIREDATE is a date type variable representing the date an employee was hired. The expression:
sysdate - hiredate
will yield the length
of time since
the employee was hired (in days). If number/24 is added to the date, the result will
show the number of
hours added to the date.
Search Conditions
The WHERE clause in the SELECT, UPDATE and DELETE commands includes a "search-condition" that determines which rows are to be selected, updated or deleted. The search-condition yields a true or false value.
Relational and Logical Operators
The following relational operators may be used in the search- condition to compare one value to another:
= equal to
!= not equal to
> greater than
< less than
>= greater than or equal to
<= less than or equal to
Examples:
To display the portfolio number of holdings containing stock symbol JFM:
select portfoliono from holding where stocksymbol = 'JFM';
Compound conditions can be created by using the logical operators AND, OR and NOT.
To display the portfolio number of holdings containing more than 200 Nortel shares:
select portfoliono from holding
where stocksymbol = 'NTL' and shares
>200;
Other Predicates
BETWEEN:
The BETWEEN predicate allows the selection or rows between two values.
Format:
expression1 [NOT] BETWEEN expression2 AND expression3
If the NOT is omitted, the condition is satisfied if expression1 is greater or equal to expression2 and less than or equal to expression3. If the NOT option is used, the condition is satisfied if expression1 is less than expression2 or greater than expression3.
Example:
To display the stock symbol of stocks costing between $50 and $80:
select stocksymbol from stock where currentprice between 50.00 and 80.00;
IN:
The IN predicate allows the selection or rows by comparing an expression to a list of values.
Format:
expression [NOT] IN (list-of-items)
If the NOT is omitted, the condition is satisfied if the expression is equal to any of the items in the list. The items in the list must be constants, separated by commas. If the NOT is included, the condition is satisfied if the expression is not equal to any of the items in the list.
Example:
To list the portfolio number of holdings which contain JFM, NTL or
select portfoliono
from holding
where stocksymbol in ('JFM','NTL','COS');
LIKE:
The LIKE predicate allows the retrieval of rows in which a column value partially matches a character string.
Format:
column-name [NOT] LIKE search-string [ESCAPE escape-character]
The search string may contain any combination of characters. The "_" (underscore) and "%" characters are "wildcard" characters. The "_" character can be used to represent "any character" - it is essentially a placemarker for a single character. The "%" character represents any string of zero or more characters.
If the search string must contain the "%" or "_" characters, the ESCAPE clause can be coded to override the use of % or _ as wildcard characters.
Examples:
To display rows in which the stock name consists of the letters Co followed by zero or more characters:
select * from stock where stockname like 'Co%';
The following SELECT command will display rows in the STOCK table in which the stock name has the letter i in the second position:
select * from stock
where
stockname like '_i%';
NULL:
This predicate provides a method of locating null values in a table.
Format:
column-name IS [NOT] NULL
The condition will be satisfied if the column-name is (or is not)
null.
The Dual Table
The Dual table is a small table (consists of one row and one column) that can be used to retrieve a constant value.
Example:
To list the current date:
select sysdate
from dual;