ch04.htm

Learn SQL in an easy way...

<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT STDDEV(NAME)
</B>  2 <B> FROM TEAMSTATS;</B>

ERROR:
ORA-01722: invalid number
no rows selected
</FONT></PRE>
<P>These aggregate functions can also be used in various combinations:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT COUNT(AB),
 </B> 2<B>  AVG(AB),
  </B>3 <B> MIN(AB),
  </B>4<B>  MAX(AB),
 </B> 5<B>  STDDEV(AB),
  </B>6<B>  VARIANCE(AB),
 </B> 7 <B> SUM(AB)
 </B> 8<B>  FROM TEAMSTATS;</B>

COUNT(AB) AVG(AB) MIN(AB) MAX(AB) STDDEV(AB) VARIANCE(AB) SUM(AB)
--------- ------- ------- ------- ---------- ------------ -------
6         119.167       1     187     75.589      5712.97     715
</FONT></PRE>
<P>The next time you hear a sportscaster use statistics to fill the time between
plays, you will know that SQL is at work somewhere behind the scenes.
<H2><FONT COLOR="#000077">Date and Time Functions</FONT></H2>
<P>We live in a civilization governed by times and dates, and most major implementations
of SQL have functions to cope with these concepts. This section uses the table <TT>PROJECT</TT>
to demonstrate the time and date functions.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM PROJECT;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#000000"><B></B></FONT></PRE>
<PRE><FONT COLOR="#0066FF">TASK           STARTDATE ENDDATE
-------------- --------- ---------
KICKOFF MTG    01-APR-95 01-APR-95
TECH SURVEY    02-APR-95 01-MAY-95
USER MTGS      15-MAY-95 30-MAY-95
DESIGN WIDGET  01-JUN-95 30-JUN-95
CODE WIDGET    01-JUL-95 02-SEP-95
TESTING        03-SEP-95 17-JAN-96
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>This table used the Date data type.
	Most implementations of SQL have a Date data type, but the exact syntax may vary.
	
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077">ADD_MONTHS</FONT></H3>
<P>This function adds a number of months to a specified date. For example, say something
extraordinary happened, and the preceding project slipped to the right by two months.
You could make a new schedule by typing</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TASK,
 </B> 2<B>  STARTDATE,
</B>  3<B>  ENDDATE ORIGINAL_END,
</B>  4 <B> ADD_MONTHS(ENDDATE,2)
 </B> 5<B>  FROM PROJECT;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">TASK           STARTDATE ORIGINAL_ ADD_MONTH
-------------- --------- --------- ---------
KICKOFF MTG    01-APR-95 01-APR-95 01-JUN-95
TECH SURVEY    02-APR-95 01-MAY-95 01-JUL-95
USER MTGS      15-MAY-95 30-MAY-95 30-JUL-95
DESIGN WIDGET  01-JUN-95 30-JUN-95 31-AUG-95
CODE WIDGET    01-JUL-95 02-SEP-95 02-NOV-95
TESTING        03-SEP-95 17-JAN-96 17-MAR-96

6 rows selected.
</FONT></PRE>
<P>Not that a slip like this is possible, but it's nice to have a function that makes
it so easy. <TT>ADD_MONTHS</TT> also works outside the <TT>SELECT</TT> clause. Typing</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TASK TASKS_SHORTER_THAN_ONE_MONTH
</B>  2<B>  FROM PROJECT
 </B> 3<B>  WHERE ADD_MONTHS(STARTDATE,1) &gt; ENDDATE;</B>
</FONT></PRE>
<P>produces the following result:</P>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">TASKS_SHORTER_THAN_ONE_MONTH
----------------------------
KICKOFF MTG
TECH SURVEY
USER MTGS
DESIGN WIDGET
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You will find that all the functions in this section work in more than one place.
However, <TT>ADD MONTHS</TT> does not work with other data types like character or
number without the help of functions <TT>TO_CHAR</TT> and <TT>TO_DATE</TT>, which
are discussed later today.
<H3><FONT COLOR="#000077">LAST_DAY</FONT></H3>
<P><TT>LAST_DAY</TT> returns the last day of a specified month. It is for those of
us who haven't mastered the &quot;Thirty days has September...&quot; rhyme--or at
least those of us who have not yet taught it to our computers. If, for example, you
need to know what the last day of the month is in the column <TT>ENDDATE</TT>, you
would type</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT ENDDATE, LAST_DAY(ENDDATE)
  </B>2<B>  FROM PROJECT;</B>
</FONT></PRE>
<P>Here's the result:</P>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">ENDDATE   LAST_DAY(ENDDATE)
--------- -----------------
01-APR-95 30-APR-95
01-MAY-95 31-MAY-95
30-MAY-95 31-MAY-95
30-JUN-95 30-JUN-95
02-SEP-95 30-SEP-95
17-JAN-96 31-JAN-96
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>How does <TT>LAST DAY</TT> handle leap years?</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT LAST_DAY('1-FEB-95') NON_LEAP,
  </B>2<B>  LAST_DAY('1-FEB-96') LEAP
 </B> 3<B>  FROM PROJECT;
</B>
NON_LEAP  LEAP
--------- ---------
28-FEB-95 29-FEB-96
28-FEB-95 29-FEB-96
28-FEB-95 29-FEB-96
28-FEB-95 29-FEB-96
28-FEB-95 29-FEB-96
28-FEB-95 29-FEB-96
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You got the right result, but why were so many rows returned? Because you didn't
specify an existing column or any conditions, the SQL engine applied the date functions
in the statement to each existing row. Let's get something less redundant by using
the following:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT DISTINCT LAST_DAY('1-FEB-95') NON_LEAP,
 </B> 2<B>  LAST_DAY('1-FEB-96') LEAP
  </B>3 <B> FROM PROJECT;
</B></FONT></PRE>
<P>This statement uses the word <TT>DISTINCT</TT> (see Day 2, &quot;Introduction
to the Query: The <TT>SELECT</TT> Statement&quot;) to produce the singular result</P>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NON_LEAP  LEAP
--------- ---------
28-FEB-95 29-FEB-96
</FONT></PRE>
<P>Unlike me, this function knows which years are leap years. But before you trust
your own or your company's financial future to this or any other function, check
your implementation!
<H3><FONT COLOR="#000077">MONTHS_BETWEEN</FONT></H3>
<P>If you need to know how many months fall between month x and month y, use <TT>MONTHS_BETWEEN</TT>
like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TASK, STARTDATE, ENDDATE,MONTHS_BETWEEN(STARTDATE,ENDDATE)
     DURATION
 </B> 2<B>  FROM PROJECT;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">TASK           STARTDATE ENDDATE    DURATION
-------------- --------- --------- ---------
KICKOFF MTG    01-APR-95 01-APR-95         0
TECH SURVEY    02-APR-95 01-MAY-95 -.9677419
USER MTGS      15-MAY-95 30-MAY-95  -.483871
DESIGN WIDGET  01-JUN-95 30-JUN-95 -.9354839
CODE WIDGET    01-JUL-95 02-SEP-95 -2.032258
TESTING        03-SEP-95 17-JAN-96 -4.451613
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>Wait a minute--that doesn't look right. Try this:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TASK, STARTDATE, ENDDATE,
  </B>2<B>  MONTHS_BETWEEN(ENDDATE,STARTDATE) DURATION
 </B> 3<B>  FROM PROJECT;</B>

TASK           STARTDATE ENDDATE    DURATION
-------------- --------- --------- ---------
KICKOFF MTG    01-APR-95 01-APR-95         0
TECH SURVEY    02-APR-95 01-MAY-95 .96774194
USER MTGS      15-MAY-95 30-MAY-95 .48387097
DESIGN WIDGET  01-JUN-95 30-JUN-95 .93548387
CODE WIDGET    01-JUL-95 02-SEP-95 2.0322581
TESTING        03-SEP-95 17-JAN-96 4.4516129
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>That's better. You see that <TT>MONTHS_BETWEEN</TT> is sensitive to the way you
order the months. Negative months might not be bad. For example, you could use a
negative result to determine whether one date happened before another. For example,
the following statement shows all the tasks that started before May 19, 1995:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
  </B>2<B>  FROM PROJECT
 </B> 3 <B> WHERE MONTHS_BETWEEN('19 MAY 95', STARTDATE) &gt; 0;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">TASK           STARTDATE ENDDATE
-------------- --------- ---------
KICKOFF MTG    01-APR-95 01-APR-95
TECH SURVEY    02-APR-95 01-MAY-95
USER MTGS      15-MAY-95 30-MAY-95
</FONT></PRE>
<H3><FONT COLOR="#000077">NEW_TIME</FONT></H3>
<P>If you need to adjust the time according to the time zone you are in, the <TT>New_TIME</TT>
function is for you. Here are the time zones you can use with this function:</P>
<P>
<TABLE BORDER="1">
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT"><B>Abbreviation</B></TD>
		<TD ALIGN="LEFT"><B>Time Zone</B></TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">AST or ADT</TD>
		<TD ALIGN="LEFT">Atlantic standard or daylight time</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">BST or BDT</TD>
		<TD ALIGN="LEFT">Bering standard or daylight time</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">CST or CDT</TD>
		<TD ALIGN="LEFT">Central standard or daylight time</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">EST or EDT</TD>
		<TD ALIGN="LEFT">Eastern standard or daylight time</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">GMT</TD>
		<TD ALIGN="LEFT">Greenwich mean time</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">HST or HDT</TD>
		<TD ALIGN="LEFT">Alaska-Hawaii standard or daylight time</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">MST or MDT</TD>
		<TD ALIGN="LEFT">Mountain standard or daylight time</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">NST</TD>
		<TD ALIGN="LEFT">Newfoundland standard time</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">PST or PDT</TD>
		<TD ALIGN="LEFT">Pacific standard or daylight time</TD>
	</TR>
	<TR ALIGN="LEFT" rowspan="1">
		<TD ALIGN="LEFT">YST or YDT</TD>
		<TD ALIGN="LEFT">Yukon standard or daylight time</TD>
	</TR>
</TABLE>
</P>
<P>You can adjust your time like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT ENDDATE EDT,
 </B> 2<B>  NEW_TIME(ENDDATE, 'EDT','PDT')
</B>  3<B>  FROM PROJECT;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">EDT              NEW_TIME(ENDDATE
---------------- ----------------
01-APR-95 1200AM 31-MAR-95 0900PM
01-MAY-95 1200AM 30-APR-95 0900PM
30-MAY-95 1200AM 29-MAY-95 0900PM
30-JUN-95 1200AM 29-JUN-95 0900PM
02-SEP-95 1200AM 01-SEP-95 0900PM
17-JAN-96 1200AM 16-JAN-96 0900PM
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>Like magic, all the times are in the new time zone and the dates are adjusted.
<H3><FONT COLOR="#000077">NEXT_DAY</FONT></H3>
<P><TT>NEXT_DAY</TT> finds the name of the first day of the week that is equal to
or later than another specified date. For example, to send a report on the Friday
following the first day of each event, you would type</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT STARTDATE,
</B>  2<B>  NEXT_DAY(STARTDATE, 'FRIDAY')
 </B> 3<B>  FROM PROJECT;
</B></FONT></PRE>
<P>which would return</P>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">STARTDATE NEXT_DAY(
--------- ---------
01-APR-95 07-APR-95
02-APR-95 07-APR-95
15-MAY-95 19-MAY-95
01-JUN-95 02-JUN-95
01-JUL-95 07-JUL-95
03-SEP-95 08-SEP-95
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The output tells you the date of the first Friday that occurs after your <TT>STARTDATE</TT>.
<H3><FONT COLOR="#000077">SYSDATE</FONT></H3>
<P><TT>SYSDATE</TT> returns the system time and date:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT DISTINCT SYSDATE
</B>  2<B>  FROM PROJECT;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SYSDATE
----------------
18-JUN-95 1020PM
</FONT></PRE>
<P>If you wanted to see where you stood today in a certain project, you could type</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *
 </B> 2<B>  FROM PROJECT
 </B> 3<B>  WHERE STARTDATE &gt; SYSDATE;</B>

TASK           STARTDATE ENDDATE
-------------- --------- ---------
CODE WIDGET    01-JUL-95 02-SEP-95
TESTING        03-SEP-95 17-JAN-96
</FONT></PRE>
<P>Now you can see what parts of the project start after today.