Oracle Development Unleashed (3rd Edition)

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.

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.

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.

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.

Page 237

continues

Page 238

Table 11.2. continued