ch05.htm

Learn SQL in an easy way...

<H5>ANALYSIS:</H5>
<P>Note the use of <TT>LIKE</TT> to find the <TT>REMARKS</TT> that started with <TT>Ga</TT>.
With the use of <TT>OR</TT>, data was returned if the <TT>WHERE</TT> clause met either
one of the two conditions.</P>
<P>What if you asked for the same information and group it by <TT>PAYEE</TT>? The
query would look something like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, REMARKS
FROM CHECKS
WHERE PAYEE = 'Cash'
OR REMARKS LIKE'Ga%'
GROUP BY PAYEE
ORDER BY REMARKS;</B>
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This query would not work because the SQL engine would not know what to do with
the remarks. Remember that whatever columns you put in the <TT>SELECT</TT> clause
must also be in the <TT>GROUP BY</TT> clause--unless you don't specify any columns
in the <TT>SELECT</TT> clause.
<H4><FONT COLOR="#000077">Example 5.2</FONT></H4>
<P>Using the table <TT>ORGCHART</TT>, find the salary of everyone with less than
<TT>25</TT> days of sick leave. Order the results by <TT>NAME</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, SALARY
FROM ORGCHART
WHERE SICKLEAVE &lt; 25
ORDER BY NAME;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME                 SALARY
=============== ===========
FURY               35000.00
MERRICK            45000.00
PRECOURT           37500.00
STOKES             36000.00
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This query is straightforward and enables you to use your new-found skills with
<TT>WHERE</TT> and <TT>ORDER BY</TT>.
<H4><FONT COLOR="#000077">Example 5.3</FONT></H4>
<P>Again, using <TT>ORGCHART</TT>, display <TT>TEAM</TT>, <TT>AVG(SALARY</TT>), <TT>AVG(SICKLEAVE</TT>),
and <TT>AVG(ANNUALLEAVE</TT>) on each team:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM,
AVG(SALARY),
AVG(SICKLEAVE),
AVG(ANNUALLEAVE)
FROM ORGCHART
GROUP BY TEAM;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">TEAM                    AVG         AVG         AVG
=============== =========== =========== ===========
COLLECTIONS        37500.00          26          21
MARKETING          36333.33          28          15
PR                 37500.00          24          24
RESEARCH           39500.00          26          15
</FONT></PRE>
<P>An interesting variation on this query follows. See if you can figure out what
happened:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM,
AVG(SALARY),
AVG(SICKLEAVE),
AVG(ANNUALLEAVE)
FROM ORGCHART
GROUP BY TEAM
ORDER BY NAME;</B>

TEAM                    AVG         AVG         AVG
=============== =========== =========== ===========
RESEARCH           39500.00          27          15
COLLECTIONS        37500.00          26          21
PR                 37500.00          24          24
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">
MARKETING          36333.33          28          15
</FONT></PRE>
<P>A simpler query using <TT>ORDER BY</TT> might offer a clue:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, TEAM
FROM ORGCHART
ORDER BY NAME, TEAM;
</B>
NAME            TEAM
=============== ===========
ADAMS           RESEARCH
FURY            COLLECTIONS
MERRICK         RESEARCH
MEZA            COLLECTIONS
PRECOURT        PR
RICHARDSON      MARKETING
STOKES          MARKETING
WILKES          MARKETING
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>When the SQL engine got around to ordering the results of the query, it used the
<TT>NAME</TT> column (remember, it is perfectly legal to use a column not specified
in the <TT>SELECT</TT> statement), ignored duplicate <TT>TEAM</TT> entries, and came
up with the order <TT>RESEARCH</TT>, <TT>COLLECTIONS</TT>, <TT>PR</TT>, and <TT>MARKETING</TT>.
Including <TT>TEAM</TT> in the <TT>ORDER BY</TT> clause is unnecessary, because you
have unique values in the <TT>NAME</TT> column. You can get the same result by typing
this statement:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, TEAM
FROM ORGCHART
ORDER BY NAME;</B>

NAME            TEAM
=============== ============
ADAMS           RESEARCH
FURY            COLLECTIONS
MERRICK         RESEARCH
MEZA            COLLECTIONS
PRECOURT        PR
RICHARDSON      MARKETING
STOKES          MARKETING
WILKES          MARKETING
</FONT></PRE>
<P>While you are looking at variations, don't forget you can also reverse the order:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, TEAM
FROM ORGCHART
ORDER BY NAME DESC;</B>

NAME            TEAM
=============== ============
WILKES          MARKETING
STOKES          MARKETING
RICHARDSON      MARKETING
PRECOURT        PR
MEZA            COLLECTIONS
MERRICK         RESEARCH
FURY            COLLECTIONS
ADAMS           RESEARCH
</FONT></PRE>
<H4><FONT COLOR="#000077">Example 5.4: The Big Finale</FONT></H4>
<P>Is it possible to use everything you have learned in one query? It is, but the
results will be convoluted because in many ways you are working with apples and oranges--or
aggregates and nonaggregates. For example, <TT>WHERE</TT> and <TT>ORDER BY</TT> are
usually found in queries that act on single rows, such as this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT *
FROM ORGCHART
ORDER BY NAME DESC;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">NAME            TEAM          SALARY   SICKLEAVE ANNUALLEAVE
=============== ======== =========== =========== ===========
WILKES          MARKETING   31000.00          40           9
STOKES          MARKETING   36000.00          20          19
RICHARDSON      MARKETING   42000.00          25          18
PRECOURT        PR          37500.00          24          24
MEZA            COLLECTIONS 40000.00          30          27
MERRICK         RESEARCH    45000.00          20          17
FURY            COLLECTIONS 35000.00          22          14
ADAMS           RESEARCH    34000.00          34          12
</FONT></PRE>
<P><TT>GROUP BY</TT> and <TT>HAVING</TT> are normally seen in the company of aggregates:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE,
SUM(AMOUNT) TOTAL,
COUNT(PAYEE) NUMBER_WRITTEN
FROM CHECKS
GROUP BY PAYEE
HAVING SUM(AMOUNT) &gt; 50;</B>

PAYEE                          TOTAL NUMBER_WRITTEN
==================== =============== ==============
Cash                             119              3
Joes Stale $ Dent                150              1
Local Utilities                219.5              3
Ma Bell                    350.33002              2
Reading R.R.                  245.34              1
</FONT></PRE>
<P>You have seen that combining these two groups of clauses can have unexpected results,
including the following:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE,
SUM(AMOUNT) TOTAL,
COUNT(PAYEE) NUMBER_WRITTEN
FROM CHECKS
WHERE AMOUNT &gt;= 100
GROUP BY PAYEE
HAVING SUM(AMOUNT) &gt; 50;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">PAYEE                          TOTAL NUMBER_WRITTEN
==================== =============== ==============
Joes Stale $ Dent                150              1
Ma Bell                    350.33002              2
Reading R.R.                  245.34              1
</FONT></PRE>
<P>Compare these two result sets and examine the raw data:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT PAYEE, AMOUNT
FROM CHECKS
ORDER BY PAYEE;
</B>
PAYEE                         AMOUNT
==================== ===============
Abes Cleaners                   10.5
Abes Cleaners                  24.35
Cash                              25
Cash                              34
Cash                              60
Joans Gas                      15.75
Joans Gas                       25.1
Joes Stale $ Dent                150
Local Utilities                   34
Local Utilities                 87.5
Local Utilities                   98
Ma Bell                          150
Ma Bell                       200.33
Reading R.R.                  245.34
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You see how the <TT>WHERE</TT> clause filtered out all the checks less than <TT>100</TT>
dollars before the <TT>GROUP BY</TT> was performed on the query. We are not trying
to tell you not to mix these groups--you may have a requirement that this sort of
construction will meet. However, you should not casually mix aggregate and nonaggregate
functions. The previous examples have been tables with only a handful of rows. (Otherwise,
you would need a cart to carry this book.) In the real world you will be working
with thousands and thousands (or billions and billions) of rows, and the subtle changes
caused by mixing these clauses might not be so apparent.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>Today you learned all the clauses you need to exploit the power of a <TT>SELECT</TT>
statement. Remember to be careful what you ask for because you just might get it.
Your basic SQL education is complete. You already know enough to work effectively
with single tables. Tomorrow (Day 6, &quot;Joining Tables&quot;) you will have the
opportunity to work with multiple tables.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q I thought we covered some of these functions earlier this week? If so, why
	are we covering them again?</B>
	<P><B>A</B> We did indeed cover <TT>WHERE</TT> on Day 3. You needed a knowledge of
	<TT>WHERE</TT> to understand how certain operators worked. <TT>WHERE</TT> appears
	again today because it is a clause, and today's topic is clauses.
</DL>

<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077">Quiz</FONT></H3>

<DL>
	<DD><B>1.</B> Which clause works just like <TT>LIKE(&lt;exp&gt;%)</TT>?
	<P><B>2. </B>What is the function of the <TT>GROUP BY</TT> clause, and what other
	clause does it act like?</P>
	<P><B>3. </B>Will this <TT>SELECT</TT> work?</P>
	<H5>INPUT:</H5>
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT NAME, AVG(SALARY), DEPARTMENT
     FROM PAY_TBL
     WHERE DEPARTMENT = 'ACCOUNTING'
     ORDER BY NAME
     GROUP BY DEPARTMENT, SALARY;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>4.</B> When using the <TT>HAVING</TT> clause, do you always have to use a
	<TT>GROUP BY</TT> also?
	<P><B>5. </B>Can you use <TT>ORDER BY</TT> on a column that is not one of the columns
	in the <TT>SELECT</TT> statement?
</DL>

<H3><FONT COLOR="#000077">Exercises</FONT></H3>

<DL>
	<DD><B>1.</B> Using the <TT>ORGCHART</TT> table from the preceding examples, find
	out how many people on each team have <TT>30</TT> or more days of sick leave.
	<P><B>2. </B>Using the <TT>CHECKS</TT> table, write a <TT>SELECT</TT> that will return
	the following:</P>
	<H5>OUTPUT:</H5>
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">CHECK#_____PAYEE_______AMOUNT

   1     MA BELL         150</FONT></PRE>

</BLOCKQUOTE>

<CENTER>
<P>
<HR>
<A HREF="../ch04/ch04.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch06/ch06.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A><BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>