0234-0238

Previous Table of Contents Next

Page 234

 ROLE      ROLLBACK SEGMENT      SCHEMA      SEQUENCE      SNAPSHOT      SNAPSHOT LOG      SYNONYM      TABLE      TABLESPACE      TRIGGER      USER      VIEW 

Writing Queries

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

Built-In Functions

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

Character Functions

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

Previous Table of Contents Next