Archive for April, 2007

Selected Single-Row Functions

Wednesday, April 18th, 2007

A function is a block of predefined code. Single-row functions return only a single row for each record processed. These functions include functions that deal with characters to dates.

Type of Functions Functions
Case conversion UPPER, LOWER, INITCAP
Character manipulation SUBSTR, INSTR, LENGTH, LPAD/RPAD, RTRIM/LTRIM, REPLACE, TRANSLATE, CONCAT
Numeric ROUND, TRUNC, MOD, ABS
Date MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, TO_DATE, ROUND, CURRENT_DATE
Regular expressions REGEXP_LIKE, REGEXP_SUBSTR
Other NVL, NVL2, TO_CHAR, DECODE, SOUNDEX, TO_NUMBER

When using a function in a SELECT the output will be modified accordingly but the data will not change in the database unless the function is used in an INSERT statement. (more…)

Posted in Oracle, School | No Comments »

Group Functions

Wednesday, April 18th, 2007

Group functions are also called multiple-row functions. These functions return a result for a group of rows that they process. To add grouping capabilities the SELECT clause will have two new clauses GROUP BY and HAVING.

SELECT * | colname, colname…
FROM tablename
[WHERE condition]
[GROUP BY colname, colname…]
[HAVING group condition];

When using group functions there are two things that need to be kept in mind:

1. Using the DISTINCT keyword will include only unique values. The ALL keyword is defaulted and instructs Oracle to include all values except null.

2. All group functions except the COUNT(*) will ignore NULL values. (more…)

Posted in Oracle, School | No Comments »

Restricting Selects and Sorting Data

Thursday, April 5th, 2007

When you select data, there’s a good chance that you’re looking for something specific. In using the SELECT statement you can attach a WHERE clause to limit the data that you are looking through. If you’re very good and know what you’re looking for then you can use the WHERE clause to find exactly what you want.

WHERE Clause Syntax

SELECT [ DISTINCT | UNIQUE ] ( *, columnname, [ AS alias ], … )
FROM tablename
[ WHERE condition ]
[ GROUP BY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY columnname ];

(more…)

Posted in Oracle, School | No Comments »