apf.htm

Learn SQL in an easy way...

<DL>
	<DD>No, the syntax is incorrect. The GROUP BY must come before the <TT>ORDER BY</TT>.
	Also, all the selected columns must be listed in the <TT>GROUP BY</TT>.
	<P><B>4. </B>When using the <TT>HAVING</TT> clause, do you always have to use a <TT>GROUP
	BY</TT> also?<BR>
	Yes.</P>
	<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?</P>
	<P>Yes, it is not necessary to use the <TT>SELECT</TT> statement on a column that
	you put in the <TT>ORDER BY</TT> clause.
</DL>

<H3><FONT COLOR="#000077">Exercise Answers</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>Here is your baseline that shows how many folks are on each team.</P>
	<H5>INPUT:</H5>
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, COUNT(TEAM)
FROM ORGCHART
GROUP BY TEAM;</B></FONT></PRE>
	<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
	<PRE><FONT COLOR="#0066FF">TEAM                  COUNT
=============== ===========

COLLECTIONS               2
MARKETING                 3
PR                        1
RESEARCH                  2</FONT></PRE>

</BLOCKQUOTE>

<PRE><FONT COLOR="#0066FF"></FONT></PRE>

<DL>
	<DD>Compare it to the query that solves the question:
	<H5>INPUT:</H5>
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF"><B>SELECT TEAM, COUNT(TEAM)
FROM ORGCHART
WHERE SICKLEAVE &gt;=30
GROUP BY TEAM;</B></FONT></PRE>
	<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
	<PRE><FONT COLOR="#0066FF">TEAM                  COUNT
=============== ===========

COLLECTIONS               1
MARKETING                 1
RESEARCH                  1</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>The output shows the number of people on each <TT>team</TT> with a <TT>SICKLEAVE</TT>
	balance of <TT>30</TT> days or more.
	<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>
	<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT CHECK#, PAYEE, AMOUNT
     FROM CHECKS
     WHERE CHECK# = 1;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>You can get the same results in several ways. Can you think of some more?
</DL>

<H2><FONT COLOR="#000077">Day 6, &quot;Joining Tables&quot;</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>

<DL>
	<DD><B>1.</B> How many rows would a two-table join produce if one table had 50,000
	rows and the other had 100,000?
	<P>5,000,000,000 rows.</P>
	<P><B>2.</B> What type of join appears in the following select statement?
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF"> select e.name, e.employee_id, ep.salary
 from employee_tbl e,
      employee_pay_tbl ep
 where e.employee_id = ep.employee_id;</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>The preceding join is an equi-join. You are matching all the <TT>employee_id</TT>s
	in the two tables.
	<P><B>3. </B>Will the following <TT>SELECT</TT> statements work?
</DL>

<PRE><FONT COLOR="#0066FF">              select name, employee_id, salary
              from employee_tbl e,
                   employee_pay_tbl ep
              where employee_id = employee_id
                and name like '%MITH';
</FONT></PRE>

<DL>
	<DD>No. The columns and tables are not properly named. Remember column and table
	aliases.
</DL>

<PRE><FONT COLOR="#0066FF">                 select e.name, e.employee_id, ep.salary
                 from employee_tbl e,
                      employee_pay_tbl ep
                 where name like '%MITH';
</FONT></PRE>

<DL>
	<DD>No. The <TT>join</TT> command is missing in the <TT>where</TT> clause.
</DL>

<PRE><FONT COLOR="#0066FF">                  select e.name, e.employee_id, ep.salary
                  from employee_tbl e,
                       employee_pay_tbl ep
                  where e.employee_id = ep.employee_id
                   and e.name like '%MITH';
</FONT></PRE>

<DL>
	<DD>Yes. The syntax is correct.
	<P><B>4. </B>In the <TT>WHERE</TT> clause, when joining the tables, should you do
	the join first or the conditions?</P>
	<P>The joins should go before the conditions.</P>
	<P><B>5. </B>In joining tables are you limited to one-column joins, or can you join
	on more than one column?</P>
	<P>You can join on more than one column. You may be forced to join on multiple columns
	depending on what makes a row of data unique or the specific conditions you want
	to place on the data to be retrieved.
</DL>

<H3><FONT COLOR="#000077">Exercise Answers</FONT></H3>

<DL>
	<DD><B>1. </B>In the section on joining tables to themselves, the last example returned
	two combinations. Rewrite the query so only one entry comes up for each redundant
	part number.
	<H5>INPUT/OUTPUT:</H5>
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF"><B>SELECT F.PARTNUM, F.DESCRIPTION,
S.PARTNUM,S.DESCRIPTION
FROM PART F, PART S
WHERE F.PARTNUM = S.PARTNUM
AND F.DESCRIPTION &lt;&gt; S.DESCRIPTION
AND F.DESCRIPTION &gt; S.DESCRIPTION</B>

   PARTNUM DESCRIPTION          PARTNUM DESCRIPTION
========== ================ =========== ====================
</FONT></PRE>
	<PRE><FONT COLOR="#0066FF">         76 ROAD BIKE                     76 CLIPPLESS SHOE</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>2. </B>Rewrite the following query to make it more readable and shorter.
	<H5>INPUT:</H5>
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">      <B>select orders.orderedon, orders.name, part.partnum,
               part.price, part.description from orders, part
               where orders.partnum = part.partnum and orders.orderedon
              between '1-SEP-96' and '30-SEP-96'
              order by part.partnum;</B></FONT></PRE>
	<P><FONT COLOR="#000000"><I>Answer:</I></FONT></P>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select o.orderedon ORDER_DATE, o.name NAME, p.partnum PART#,
            p.price PRICE, p.description DESCRIPTION
     from orders o,
          part p
     where o.partnum = p.partnum
       and o.orderedon like '%SEP%'
     order by ORDER_DATE;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>3. </B>From the <TT>PART</TT> table and the <TT>ORDERS</TT> table, make up
	a query that will return the following:
	<H5>OUTPUT:</H5>
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">ORDEREDON             NAME               PARTNUM         QUANTITY
==================    ================== =======         ========
2-SEP-96              TRUE WHEEL              10               1</FONT></PRE>
	<P><FONT COLOR="#000000"><I>Answer:</I></FONT></P>
	<PRE><FONT COLOR="#0066FF">   <B>select o.orderedon ORDEREDON, o.name NAME, p.partnum PARTNUM, o.quanity QUANITY
           from orders o,
                part p
           where o.partnum = p.partnum
             and o.orderedon like '%SEP%';</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>Many other queries will also work.
</DL>

<H2><FONT COLOR="#000077">Day 7, &quot;Subqueries: The Embedded SELECT Statement&quot;</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>

<DL>
	<DD><B>1. </B>In the section on nested subqueries, the sample subquery returned several
	values:
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>Some of these are duplicates. Why aren't these duplicates in the final result
	set?<BR>
	The result set has no duplicates because the query that called the subquery
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SELECT ALL C.NAME, C.ADDRESS, C.STATE,C.ZIP
FROM CUSTOMER C
WHERE C.NAME IN</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>returned only the rows where <TT>NAME</TT> was in the list examined by the statement
	<TT>IN</TT>. Don't confuse this simple <TT>IN</TT> statement with the more complex
	join.
	<P><B>2. </B>Are the following statements true or false?</P>
	<P>The aggregate functions <TT>SUM</TT>, <TT>COUNT</TT>, <TT>MIN</TT>, <TT>MAX</TT>,
	and <TT>AVG</TT> all return multiple values.<BR>
	False. They all return a single value.</P>
	<P>The maximum number of subqueries that can be nested is two.<BR>
	False. The limit is a function of your implementation.<BR>
	Correlated subqueries are completely self-contained.<BR>
	False. Correlated subqueries enable you to use an outside reference.</P>
	<P><B>3. </B>Will the following subqueries work using the <TT>ORDERS</TT> table and
	the <TT>PART</TT> table?</P>
	<H5>INPUT/OUTPUT:</H5>
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">   SQL&gt; <B>SELECT *
        FROM PART;</B>

        PARTNUM  DESCRIPTION     PRICE
             54  PEDALS          54.25
             42  SEATS           24.50
             46  TIRES           15.25
             23  MOUNTAIN BIKE  350.45
             76  ROAD BIKE      530.00
             10  TANDEM        1200.00
6 rows selected.
</FONT></PRE>
	<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
	<PRE><FONT COLOR="#0066FF">      SQL&gt; <B>SELECT *
           FROM ORDERS;</B>

           ORDEREDON   NAME          PARTNUM   QUANITY  REMARKS
             15-MAY-96 TRUE WHEEL         23         6  PAID
             19-MAY-96 TRUE WHEEL         76         3  PAID
              2-SEP-96 TRUE WHEEL         10         1  PAID
             30-JUN-96 BIKE SPEC          54        10  PAID
             30-MAY-96 BIKE SPEC          10         2  PAID
             30-MAY-96 BIKE SPEC          23         8  PAID
             17-JAN-96 BIKE SPEC          76        11  PAID
             17-JAN-96 LE SHOPPE          76         5  PAID
              1-JUN-96 LE SHOPPE          10         3  PAID
              1-JUN-96 AAA BIKE           10         1  PAID
              1-JUN-96 AAA BIKE           76         4  PAID
              1-JUN-96 AAA BIKE           46        14  PAID
             11-JUL-96 JACKS BIKE         76        14  PAID
13 rows selected.
</FONT></PRE>
	<P><FONT COLOR="#000000">a. </FONT><FONT COLOR="#0066FF"><TT>SQL&gt; <B>SELECT *
	FROM ORDERS<BR>
	       WHERE PARTNUM =<BR>
	       SELECT PARTNUM FROM PART<BR>
	       WHERE DESCRIPTION = 'TRUE WHEEL';</B></TT></FONT></P>

</BLOCKQUOTE>


<DL>
	<DD>No. Missing the parenthesis around the subquery.
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<P><FONT COLOR="#000000">b. </FONT><FONT COLOR="#0066FF"><TT>SQL&gt; <B>SELECT PARTNUM<BR>
	       FROM ORDERS<BR>
	       WHERE PARTNUM =<BR>
	       (SELECT * FROM PART<BR>
	       WHERE DESCRIPTION = 'LE SHOPPE');</B></TT></FONT></P>

</BLOCKQUOTE>


<DL>
	<DD>No. The SQL engine cannot correlate all the columns in the <TT>part</TT> table
	with the operator <TT>=</TT>.
</DL>

<PRE></PRE>


<BLOCKQUOTE>
	<P><FONT COLOR="#000000">c. </FONT><FONT COLOR="#0066FF"><TT>SQL&gt; <B>SELECT NAME,
	PARTNUM<BR>
	       FROM ORDERS<BR>
	       WHERE EXISTS<BR>
	       (SELECT * FROM ORDERS<BR>
	       WHERE NAME = 'TRUE WHEEL');</B></TT></FONT></P>

</BLOCKQUOTE>


<DL>
	<DD>Yes. This subquery is correct.
</DL>

<H3><FONT COLOR="#000077">Exercise Answer</FONT></H3>
<P>Write a query using the table <TT>ORDERS</TT> to return all the <TT>NAME</TT>s
and <TT>ORDEREDON</TT> dates for every store that comes after <TT>JACKS BIKE</TT>
in the alphabet.</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME &gt; 
(SELECT NAME
FROM ORDERS
WHERE NAME ='JACKS BIKE')</B>

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
LE SHOPPE  17-JAN-1996
LE SHOPPE   1-JUN-1996
</FONT></PRE>
<H2><FONT COLOR="#000077">Day 8, &quot;Manipulating Data&quot;</FONT></H2>
<H3><FONT COLOR="#000077">Quiz Answers</FONT></H3>

<DL>
	<DD><B>1. </B>What is wrong with the following statement?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">DELETE COLLECTION;</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD>If you want to delete all records from the <TT>COLLECTION</TT> table, you must
	use the following syntax:
</DL>