Sharing Knowledge by Mithun Ashok

Strange solution for someone who is looking into this statement for the first time.

I have used XMLAGG, XMLELEMENT and EXTRACT functions. See if you can crack it.


XMLAGG is an aggregate/group function which aggregates all the values for a column.
XMLELEMENT is a function which creates XML tags with tag element E.
EXTRACT function then removes all the XML text and retains only text.

SELECT   deptno,  XMLAGG (XMLELEMENT (e, ename || ',')).EXTRACT ('//text()') as ENAMES
FROM
   emp
GROUP BY deptno;

This statement will give you an output as below,







If you observe in this output, there is an extra comma at the end of the ENAMEs list. To remove this, I shall use another function RTRIM which is used to trim characters on the right side.

SELECT   deptno,  RTRIM(XMLAGG (XMLELEMENT (e, ename || ',')).EXTRACT ('//text()'),',') as ENAMES
FROM
   emp
GROUP BY deptno;







In case if the output does not show the values properly then use the following command to restrict t…

Before we start writing a statement to find a Leap Year, let us first understand what is a Leap Year.

According to Gregorian Calendar each year consists of 365 day. In reality Earth takes approximately 365.242375 days to circle once around the Sun.

As the Gregorian calendar has only 365 days in a year, so if we didn't add a day on February 29 nearly every 4 years, we would lose almost six hours off our calendar every year. After only 100 years, our calendar would be off by approximately 24 days!

Lets look at it in more details,

Because the Earth rotates about 365.242375 times a year ...

... but a normal year is 365 days, ...

... so something has to be done to "catch up" the extra 0.242375 days a year.

So every 4th year we add an extra day (the 29th of February), which makes 365.25 days a year. This is fairly close, but is wrong by about 1 day every 100 years.

So every 100 years we don't have a leap year, and that results in 365.24 days per year (1 day less in 100 ye…