New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Functions
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Cursors
Stored Procedures
Triggers
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

Functions

  1   2   3   4   5   page(1/5)


    Functions in SQL enable you to perform feats such as determining the sum of a column or converting all the characters of a string to uppercase. You will understand and be able to use all the following:

Aggregate functions

Date and time functions

Arithmetic functions

Character functions

Conversion functions

Miscellaneous functions


    These functions greatly increase your ability to manipulate the information you retrieved using the basic functions of SQL that were described earlier this week. The first five aggregate functions, COUNT, SUM, AVG, MAX, and MIN, are defined in the ANSI standard. Most implementations of SQL have extensions to these aggregate functions, some of which are covered today. Some implementations may use different names for these functions.

Aggregate Functions


    These functions are also referred to as group functions. They return a value based on the values in a column. (After all, you wouldn't ask for the average of a single field.) The examples in this section use the table TEAMSTATS:

COUNT


    The function COUNT returns the number of rows that satisfy the condition in the WHERE clause. Say you wanted to know how many ball players were hitting under 350. You would type

INPUT:

SQL> SELECT COUNT (*)
FROM TEAMSTATS
WHERE HITS/AB > .35;

    To make the code more readable, try an alias:

INPUT:

SQL> SELECT COUNT (*) NUM-BELOW-350
FROM TEAMSTATS
WHERE HITS/AB > .35;

    Would it make any difference if you tried a column name instead of the asterisk? (Notice the use of parentheses around the column names.) Try this:

INPUT:

SQL> SELECT COUNT (NAME) NUM-BELOW-350
FROM TEAMSTATS
WHERE HITS/AB > .35;

    The answer is no. The NAME column that you selected was not involved in the WHERE statement. If you use COUNT without a WHERE clause, it returns the number of records in the table.

INPUT:

SQL> SELECT COUNT (*) FROM TEAMSTATS;

SUM


    SUM does just that. It returns the sum of all values in a column. SUM works only with numbers.To find out how many singles have been hit, type and to get sum of more columns type,

INPUT:

SQL> SELECT SUM (SINGLES) TOTAL-SINGLES FROM TEAMSTATS;

INPUT:

SQL> SELECT SUM (SINGLES) TOTAL-SINGLES, SUM (DOUBLES) TOTAL-DOUBLES,
SUM (TRIPLES) TOTAL-TRIPLES, SUM (HR) TOTAL-HR FROM TEAMSTATS;

AVG


    The AVG function computes the average of a column. To find the average number of strike outs, use this:

INPUT:

SQL> SELECT AVG (SO) AVE-STRIKE-OUTS FROM TEAMSTATS;

    The following example illustrates the difference between SUM and AVG:

ANALYSIS:

    The team was batting over 300 in the previous example! What happened? AVG computed the average of the combined column hits divided by at bats, whereas the example with SUM divided the total number of hits by the number of at bats. For example, player A gets 50 hits in 100 at bats for a .500 average. Player B gets 0 hits in 1 at bat for a 0.0 average. The average of 0.0 and 0.5 is .250. If you compute the combined average of 50 hits in 101 at bats, the answer is a respectable .495. The following statement returns the correct batting average:


MAX


    If you want to find the largest value in a column, use MAX. For example, what is the highest number of hits?

INPUT:

SQL> SELECT MAX (HITS) FROM TEAMSTATS;

    Unfortunately, you can't use aggregate functions in where clause, it gives ERROR. The error message is a reminder that this group function (remember that aggregate functions are also called group functions. "Subqueries: The Embedded SELECT Statement," covers the concept of subqueries and explains a way to find who has the MAX hits.

    What happens if you try a nonnumeric column?

INPUT:

SQL> SELECT MAX (NAME) FROM TEAMSTATS;

    Here's something new. MAX returns the highest (closest to Z) string. Finally, a function that works with both characters and numbers.

MIN


    MIN does the expected thing and works like MAX except it returns the lowest member of a column. To find out the fewest at bats type and for alphabets the following statement returns the name closest to the beginning of the alphabet: This sort of information can be useful when using statistical functions.

INPUT:

SQL> SELECT MIN (AB) FROM TEAMSTATS;

INPUT:

SQL> SELECT MIN (NAME) FROM TEAMSTATS;

NOTE: As we mentioned in the introduction, the first five aggregate functions are described in the ANSI standard. The remaining aggregate functions have become de facto standards, present in all important implementations of SQL. We use the Oracle7 names for these functions. Other implementations may use different names.


VARIANCE


    VARIANCE produces the square of the standard deviation, a number vital to many statistical calculations. It works like this:

INPUT:

SQL> SELECT VARIANCE (HITS) FROM TEAMSTATS;

STDDEV


    The final group function, STDDEV, finds the standard deviation of a column of numbers, as demonstrated by this example:

INPUT:

SQL> SELECT STDDEV (HITS) FROM TEAMSTATS;

    These aggregate functions can also be used in various combinations:

INPUT:

SQL> SELECT COUNT (AB),
AVG (AB),
MIN (AB),
MAX (AB),
STDDEV (AB),
VARIANCE (AB),
SUM (AB)
FROM TEAMSTATS;

  1   2   3   4   5   page(1/5)