Discussion Question 1: Single Row and Group Functions By nature, a function retu
ID: 3823660 • Letter: D
Question
Discussion Question 1: Single Row and Group Functions
By nature, a function returns a single value. Discuss the difference between how a database single-row functions and group functions react within a query.
Discussion Question 2: Using Dates Within the Database
Working with dates in any database can cause some frustration. How do the Format:format(Hire_Date,"dddd dd mmmm yyyy") (Oracle TO_CHAR)and to_date('#DateFormat(timestamp1, "DD-mmm-YYYY")# (Oracle TO_DATE) functions help in working with date logic within a database?
Explanation / Answer
Discussion Question 1: Single Row and Group Functions:
A SQL function is similar to an operator in that it manipulates data
items and returns a result. Functions differ from operators in the
format in which they appear with their arguments. This format
allows them to operate on zero, one, two, or more arguments:
function(argument, argument, ...)
Functions are of these general types:
1) single row (or scalar) functions
2) group functions (or aggregate) functions
These functions differ in the number of rows upon which they act. A
single row function returns a single result row for every row of a
queried table or view, while a group function returns a single
result row for a group of queried rows.
Single row functions can appear in select lists (provided the SELECT
statement does not contain a GROUP BY clause), WHERE clauses, START
WITH clauses, and CONNECT BY clauses.
Group functions can appear in select lists and HAVING clauses. If
you use the GROUP BY clause in a SELECT statement, Oracle divides
the rows of a queried table or view into groups. In a query
containing a GROUP BY clause, all elements of the select list must
be either expressions from the GROUP BY clause, expressions
containing group functions, or constants. Oracle applies the group
functions in the select list to each group of rows and returns a
single result row for each group. If you omit the GROUP BY clause,
Oracle applies group functions in the select list to all the rows in
the queried table or view. You can also use group functions in a
HAVING clause in the statement to restrict the result rows returned.
An example using scott schema emp table:
select empno, ename, to_char(sal, '9,999.99') from emp; --here to_char is a single row function
select deptno, sum(sal) from emp group by deptno; --here sum() is a multiple row function.
Discussion Question 2: Using Dates Within the Database:
The DATE type is used in the same way as other built-in types such as INT. For example, the following SQL statement creates a relation with an attribute of type DATE:
create table x(a int, b date);
When a DATE value is displayed, Oracle must first convert that value from the special internal format to a printable string. The conversion is done by a function TO_CHAR, according to a DATE format. Oracle's default format for DATE is "DD-MON-YY". Therefore, when you issue the query
select b from x;
you will see something like:
B
---------
01-APR-98
Whenever a DATE value is displayed, Oracle will call TO_CHAR automatically with the default DATE format. However, you may override the default behavior by calling TO_CHAR explicitly with your own DATE format. For example,
SELECT TO_CHAR(b, 'YYYY/MM/DD') AS b
FROM x;
returns the result:
B
---------------------------------------------------------------------------
1998/04/01
The general usage of TO_CHAR is:
TO_CHAR(<date>, '<format>')
Now for inputting a DATE we use a function called TO_DATE, which converts a string to a DATE value, again according to the DATE format. Normally, you do not have to call TO_DATE explicitly: Whenever Oracle expects a DATE value, it will automatically convert your input string using TO_DATE according to the default DATE format "DD-MON-YY". For example, to insert a tuple with a DATE attribute, you can simply type:
insert into x values(99, '31-may-98');
Alternatively, you may use TO_DATE explicitly:
insert into x
values(99, to_date('1998/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
The general usage of TO_DATE is:
TO_DATE(<string>, '<format>')
where the <format> string has the same options as in TO_CHAR.
The built-in function SYSDATE returns a DATE value containing the current date and time on your system. For example,
select to_char(sysdate, 'Dy DD-Mon-YYYY HH24:MI:SS') as "Current Time"
from dual;
returns
Current Time
---------------------------------------------------------------------------
Tue 21-Apr-1998 21:18:27
You can compare DATE values using the standard comparison operators such as =, !=, >, etc.
You can subtract two DATE values, and the result is a FLOAT which is the number of days between the two DATE values. In general, the result may contain a fraction because DATE also has a time component. For obvious reasons, adding, multiplying, and dividing two DATE values are not allowed.
You can add and subtract constants to and from a DATE value, and these numbers will be interpreted as numbers of days. For example, SYSDATE+1 will be tomorrow. You cannot multiply or divide DATE values.
With the help of TO_CHAR, string operations can be used on DATE values as well. For example, to_char(<date>, 'DD-MON-YY') like '%JUN%' evaluates to true if <date> is in June.