0244-0247

Previous Table of Contents Next

Page 244

Table 11.6. continued

Function	Description	Arguments	Return Value
LOG	Returns base_m log of n where m > 1 and n > 0.	m NUMBER, n NUMBER
MOD	Returns remainder of m/n.	m NUMBER, n NUMBER
POWER	Returns value of m n .	m NUMBER, n NUMBER
ROUND	Returns m rounded to n places.	m NUMBER, n NUMBER
SIGN	Returns _1 for n < 0, 0 for n = 0, and 1 for n > 0.	n NUMBER
SIN	Returns sine of a. Angle a must be in radians.	a NUMBER
SINH	Returns hyperbolic sine of n.	n NUMBER
SQRT	Returns square root of n.	n NUMBER
TAN	Returns tangent of a. Angle a must be in radians.	a NUMBER
TANH	Returns hyperbolic tangent of n.	n NUMBER
TRUNC	Returns m truncated to n places.	m NUMBER [, n NUMBER]

Look at some examples using functions:

 SELECT SUBSTR(addrs_1,1,30),                 city, state, zip FROM addresses WHERE addrs_1 is not null       AND UPPER(city)  =  `ROCHESTER'       AND TO_NUMBER(SUBSTR(zip,1,5)) > 14525       AND NVL(active_date,SYSDATE)  >  TO_DATE('01-JAN-90'); 

Notice the use of the UPPER function. When Oracle performs character string comparisons, the case of the strings in question must match exactly. Therefore, `Rochester' does not equal `ROCHESTER'. The UPPER function ensures that the column city is converted to uppercase prior to the comparison of the literal `ROCHESTER'.

The SUBSTR function is used to retrieve the characters 1 through 30 of column addrs_1. All remaining characters beyond 30 are not seen. This function is also used in the WHERE clause to retrieve the first five characters of the zip column before converting it to a numerical value. The comparison is made after the conversion takes place.

If the column active_date contains any nulls, they are included in the data set because of the NVL function. If active_date is null, the current date is returned before the comparison is made to the constant '01-JAN-90'. The constant '01-JAN-90' is converted to a date datatype to

Page 245

ensure format compatibility. For a complete list of all date formats, see "Elements of SQL" in Oracle's SQL Language Reference Manual.

Consider the following example using the SUM and AVG functions:

 SELECT dept_no,                SUM(emp_salary),                AVG(emp_salary) FROM emp WHERE dept_no = dept_no GROUP BY dept_no; 

Retrieved data presents the summation of employee salaries and the average salary by department. Notice that the GROUP BY clause was used in this query as the criteria for SUM and AVG calculations.

Know Your Tables and Views

To ensure that your data contains all the required columns and restrictions, you must be familiar with the database schema. If a schema diagram is not available, there are numerous ways to find out what tables or views might be needed for writing queries. One way is to look at some of the data dictionary tables.

To view all the data dictionary table names , issue the following SELECT statement:

 SELECT table_name FROM dictionary ORDER BY table_name; 

Some of the tables of interest in the data dictionary are all_tables, all_columns, all_views, and all_constraints.

To view the column names of these tables, issue DESC table_name. DESC stands for describe and table_name is the name of the table in question, such as all_tables. Therefore, DESC all_tables returns all the columns and their datatypes for the table all_tables.

Although the people responsible for developing a database schema are the best source of information when writing SQL against a schema, they are not always available. If this is the case, using the data dictionary tables makes it possible to determine what tables, views, and constraints are in effect for the application in question.

Joining Tables

Tables are physically joined in the FROM clause of your query. They are logically joined in the WHERE clause. Table columns that appear in the WHERE clause must have the table name (or alias) listed in the FROM clause. The WHERE clause constrains how data is selected from the related tables.

The way in which the WHERE clause is constructed greatly affects the performance of the query. A two-table join does not necessarily perform better than a ten-table join.

Page 246

TIP

As a rule of thumb in multi-table selects, the driving table should be the last table listed, for best performance. The table you have the most information about is the driving table.

If a lot of queries have a large number of tables joined together (more than seven tables, for example), you might need to consider denormalizing certain data elements to reduce the number of table joins. This type of denormalization might be required when user productivity or system performance has significantly decreased.

Table 11.7 shows three tables that you will work with for the examples.

Table 11.7. Table descriptions.

Table name	Column name	Datatype
emp	emp_id	number(6)
emp	adrs_id	number(6)
emp	first_name	varchar2(40)
emp	last_name	varchar2(40)
emp	dept_no	number(3)
emp	hire_date	date
emp	job_title	varchar2(40)
emp	salary	number(6)
emp	manager_id	number(6)
dept	dept_no	number(3)
dept	name	varchar(40)
dept	adrs_id	number(6)
addresses	adrs_id	number(6)
addresses	active_date	date
addresses	box_number	number(6)
addresses	adrs_1	varchar2(40)
addresses	adrs_2	varchar2(40)
addresses	city	varchar2(40)
addresses	state	varchar2(2)
addresses	zip	varchar2(10)

Page 247

In the following example, a query lists all departments with their corresponding employees and the city in which the department resides:

 SELECT d.name,        e.last_name,        e.first_name,        a.city FROM emp        e,      dept       d,      addresses  a WHERE d.dept_no    = e.dept_no   AND a.adrs_id    = d.adrs_id ORDER BY d.name,e.last_name,e.first_name; 

If you need to retrieve the employee city as well, the query can be

 SELECT d.name,        a.city  dept_city,        e.last_name,        e.first_name,        z.city  emp_city FROM emp        e,      dept       d,      addresses  a,      addresses  z WHERE  d.dept_no      = e.dept_no   AND  a.adrs_id      = d.adrs_id   AND  z.adrs_id      = e.adrs_id ORDER BY d.name,e.last_name,e.first_name; 

In this example, the addresses table was joined twice, enabling the city column to be retrieved for both the department and employee. To clarify the output, the query assigned aliases to the different city columns in the SELECT portion of the query.

The following example adds the employee's manager's name to the query:

 SELECT dep.name,        ad1.city  dept_city,        em1.last_name,        em1.first_name,        ad2.city  emp_city,        em2.first_name  em2.last_name manager FROM emp          em1,      dept       dep,      addresses  ad1,      addresses  ad2,      emp        em2 WHERE  dep.dept_no      = em1.dept_no   AND  ad1.adrs_id      = dep.adrs_id   AND  ad2.adrs_id      = em1.adrs_id   AND  em2.emp_id       = em1.manager_id ORDER BY    dep.name,em1.last_name,em1.first_name; 

The output from this query causes the manager (alias) column to appear as one column even though it is made from two columns. The symbol is used to concatenate columns.

Previous Table of Contents Next