SQL
Aggregate functions:
avg(...), sum(...),
min(...), max(...)
count(*)
count(...), count(distinct
...), count(all...)
Some
other useful functions:
initcap
- function that capitalizes first character and lowercases
remaining.
greatest
- returns greatest value among its args.
Example: greatest (2, 5, 11,
3) returns 11.
greatest (‘2’,
‘5’, ‘11’, ‘3’) returns ‘5’
(compares lexicographically for strings)
least
- returns least value among its args.
Decode Function
It is like a case statement.
//
Example 1: (Converts column region from ‘N’ to North, ‘S’
to South and so on)
//
Example 2: (book_type and
pub_name are
columns in below query)
We could have also achieved
the above by a double group-by function as
group
by pub_name, book_type
This would avoid the use of
decode but then the resulting table would not have all the data
related to pub_name in one row (it would be spread across multiple
rows).
While this may be ok if the
result of this query has to be processed in Java program but the same
may not be good if the result of this query has to be feeded into
another query.
A problem with above
approach (as compared to double group by) is that it can work only if
the book_type values are known beforehand and their count is not
much. If that were not so, then it would be a pain to write all the
known book-types into one query and SQL also might have some
limitation on that (like CASE statement has).
Nevertheless, the above is a
great example to see usefulness of decode for smaller range columns.
Also, if we need to break up a column into ranges, then decode is
very powerful, as shown in next example:
//
Example 3:
Below query selects SAL column into ranges.
//
greatest(SAL,3001) will select SAL only if SAL > 3001
//
least (SAL,9999) will select SAL only if SAL < 9999
// And
if the two are equal, then we will get 1 else 0
//
Hence column named “$3001-9999” in the output will return
// the
number of salaries between 3001 and 9999
|
