Use Oracle Function in SQL Statement
Oracle SQL offers a number of built-in functions that allow it to perform mathematical calculations and string manipulation in-line in a query. Functions are as easy to use as selecting a column from a table, yet powerful enough to provide absolute values, sine, cosine, logarithms, string replacement using regular expressions and many other useful processes. In short, understanding and using Oracle SQL's built-in functions can make your SQL more concise, efficient and readable.
Instructions
Using Oracle SQL Functions
1. Use the ROUND function. Suppose you are a high school teacher and you track your students' test scores in a column called TestAverage from a table called TestScoreAverages, each average calculated out to five places. To report the scores to your students, you want to round the averages to the nearest integer. Such a query would look like this:
SELECT Name, ROUND(TestAverage,0) FROM TestScoreAverages
2. Use the CEIL function. If you were feeling especially generous you might use a similar function, Ceil, which always rounds a number up to the next whole number. That query would look like this:
SELECT Name CEIL(TestAverage) FROM TestScoreAverages
3. Use the AVG function. Suppose, however, that all you had was raw test score data from a table called TestScores. For each test a student takes, her name and test score receive a new row in a table which looks like this:
Name TestScore
Tim 93
Sarah 100
Frank 45
Tim 88
Sarah 100
Frank 67
Tim 91
Sarah 99
Frank 73
To calculate the average test score for each student with your SQL query, you could use the AVG SQL function combined with a Group By clause:
SELECT Name, AVG(TestScore)
FROM TestScores
GROUP BY Name
4. Use the MAX function. Again, imagine you are one of those especially soft-hearted teachers and you want your students represented only by their very best work. You can select the highest score for each student using the MAX function:
SELECT Name, MAX(TestScore)
FROM TestScores
GROUP BY Name
5. Use a mathematical expression within a function call. Now, instead of maximizing your students' scores by selecting only their best test, suppose you offer an extra credit assignment, which, if completed, will increase the score of each test by five percent. (For now imagine that every student completed the extra-credit assignment.) You can include mathematical expressions within the function call:
SELECT Name, AVG(TestScore+(TestScore*.05))
FROM TestScores
GROUP BY Name
6. Use the DECODE function. Finally, suppose, more realistically, that only some of the students completed the extra credit assignment. You track this with a column in the TestScores table called ExtraCredit. If the student completed the assignment, you set ExtraCredit to 1. If he did not, you set ExtraCredit to 0. You can now use the DECODE function to add logic to your query which will apply the five percent bonus only to the students who deserve it.
SELECT Name, DECODE(EXTRACREDIT, 1, AVG(TestScore+(TestScore*.05)) ,
0, AVG(TestScore)) as AverageTestScore
FROM TestScores
GROUP BY Name
The Decode function works like If/Then logic. The above is the equivalent of, "If ExtraCredit =1, then add five percent to each test score before averaging them; if ExtraCredit=0, then simply average the test scores."
Tags: SELECT Name, FROM TestScores, FROM TestScores GROUP, GROUP Name, Name TestScore