Page 234
ROLE ROLLBACK SEGMENT SCHEMA SEQUENCE SNAPSHOT SNAPSHOT LOG SYNONYM TABLE TABLESPACE TRIGGER USER VIEW
To retrieve data from the database, use the SELECT statement. Once again, proper privileges are required and are maintained by the DBA. The SELECT statement has the following format:
SELECT column(s) FROM tables(s) WHERE conditions are met GROUP BY selected columns ORDER BY column(s);
Every SQL statement ends with a semicolon (;). When you write SQL scripts (disk files) that will be executed, you can also use a slash (/) to terminate the SQL statement.
NOTE |
The run slash (/) must appear in column 1 of SQL scripts or the SQL statement is not executed. |
When SELECT column(s) is used, it is assumed that all the columns constrained by the WHERE clause are retrieved. It is sometimes necessary to retrieve only columns that are distinct from one another. To do this, use the reserved word DISTINCT before the column descriptions. In the following example, a SELECT statement is used to retrieve all the cities and states from the addresses table (defined previously):
SELECT city, state FROM addresses;
Page 235
When this code runs, every city and state is retrieved from the table. If 30 people live in Rochester, NY, the data is displayed 30 times. To see only one occurrence for each city and state, use the DISTINCT qualifier, as shown in the following example:
SELECT DISTINCT city, state FROM addresses;
This causes only one row to be retrieved for entries with Rochester, NY.
NOTE |
Use of DISTINCT is not meant as a panacea for poorly written WHERE clauses. It is best to think through the relations of the tables being constrained to avoid Cartesian joins. |
The FROM clause contains a listing of all tables needed for the query. You can use table aliases to help simplify queries, as shown in the following example:
SELECT adrs.city, adrs.state FROM addresses adrs;
In this example, the alias adrs is given to the table addresses. The alias is used to differentiate columns with the same name from different tables.
The WHERE clause is used to list the criteria necessary to restrict the output from the query or to join tables in the FROM clause:
SELECT DISTINCT city, state FROM addresses WHERE state in (`CA','NY','CT') AND city is NOT NULL;
This example retrieves cities and states that are in the states of California, New York, and Connecticut. The check for NOT NULL cities does not bring data back if the city field is not inserted.
The GROUP BY clause tells Oracle how to group the records together when certain functions are used:
SELECT dept_no, SUM(emp_salary) FROM emp GROUP BY dept_no;
The GROUP BY example lists all department numbers once with the summation of the employee salaries for that particular department.
Page 236
Functions are an intrinsic part of any SQL statement. Table 11.1 shows a complete list of SQL internal function categories, along with default return values.
Table 11.1. Internal function categories and common return values.
Category | Common Return Value |
| |
Character | VARCHAR2 |
Conversion | None |
Date | DATE |
Miscellaneous | None |
Number | NUMBER |
Although most character functions return a VARCHAR2, some functions return other values. Table 11.2 lists available character functions, along with a brief description, argument list, and return value if different from the most likely return value for the set of functions. Optional arguments are enclosed in square brackets. All internal character functions take the following form:
function ASCII (char VARCHAR2) return VARCHAR2
Table 11.2. Character functions.
Function | Description | Arguments | Return Value |
| | | |
ASCII | Returns standard collating code for character. | char VARCHAR2 | NUMBER |
CHR | Returns character for collating code. | num NUMBER | |
CONCAT | Returns str2 appended to str1. | str1 VARCHAR2, str2 VARCHAR2 | |
INITCAP | Returns str1 with the first letter of each word in uppercase and all others in lowercase. | str1 VARCHAR2 |
Page 237
Function | Description | Arguments | Return Value |
| | | |
INSTR | Returns starting position of str2 in str1. Search begins at pos for the nth occurrence. If pos is negative, the search is performed backwards . Both pos and n default to 1. The function returns 0 if str2 is not found. | str1 VARCHAR2, str2 VARCHAR2 [, pos NUMBER [, n NUMBER]] | |
INSTRB | Similar to INSTR except pos is a byte position. | str1 VARCHAR2, str2 VARCHAR2 [, pos NUMBER [, n NUMBER]] | |
LENGTH | Returns character count in str and for datatype CHAR; length includes trailing blanks. | str CHAR or str VARCHAR2 | NUMBER |
LENGTHB | Similar to LENGTH; returns byte count of str including trailing blanks for CHAR. | str CHAR or str VARCHAR2 | NUMBER |
LOWER | Returns str with all letters in lowercase. | str CHAR or str VARCHAR2 | CHAR or VARCHAR2 |
LPAD | Left-pads str to length len with characters in pad, which defaults to a single blank. Returns first len characters in str if str is longer than len. | str VARCHAR2 len NUMBER [, pad VARCHAR2] | |
LTRIM | Returns str with characters removed up to first character not in set; set defaults to a single blank. | str VARCHAR2 [, set VARCHAR2] | |
NLS_INITCAP | Similar to INITCAP except a sort sequence is specified by nlsparms. | str VARCHAR2 [, nlsparms VARCHAR2] | |
NLS_LOWER | Similar to LOWER except a sort sequence is specified by nlsparms. | str VARCHAR2 [, nlsparms VARCHAR2] |
continues
Page 238
Table 11.2. continued
Function | Description | Arguments | Return Value |
| | | |
NLS_UPPER | Similar to UPPER except a sort sequence is specified by nlsparms. | str VARCHAR2 [, nlsparms VARCHAR2] | |
NLSSORT | Returns str in sort sequence specified by nlsparms. | str VARCHAR2 [, nlsparms VARCHAR2] | RAW |
REPLACE | Returns str1 with all occurrences of str2 replaced by str3. If str3 is not specified, all occurrences of str2 are removed. | str1 VARCHAR2, str2 VARCHAR2, [str3 VARCHAR2] | |
RPAD | Similar to LPAD except str is right- padded with len sequence of characters in pad. | str VARCHAR2, len VARCHAR2, [, pad VARCHAR2] | NUMBER |
RTRIM | Similar to LTRIM except trailing characters are removed from str after the first character not in set. | str VARCHAR2 [, set VARCHAR2] | |
SOUNDEX | Returns phonetic representation of str. | str VARCHAR2 | |
SUBSTR | Returns substring of str starting at pos for length len or to the end of str if len is omitted. For pos < 0, SUBSTR counts backward from the end of str. | str VARCHAR2, pos NUMBER [, len NUMBER] | |
SUBSTRB | Similar to SUBSTR except works on bytes, not characters. | str VARCHAR2, pos NUMBER [, len NUMBER] | |
TRANSLATE | Replaces all occurrences of set1 with set2 characters in str. | str VARCHAR2, set1 VARCHAR2, set2 CHAR | |
UPPER | Returns all letters in uppercase. | str CHAR or str VARCHAR2 |