<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 ORGCHARTGROUP BY TEAM;</B></FONT></PRE>	<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>	<PRE><FONT COLOR="#0066FF">TEAM                  COUNT=============== ===========COLLECTIONS               2MARKETING                 3PR                        1RESEARCH                  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 ORGCHARTWHERE SICKLEAVE &gt;=30GROUP BY TEAM;</B></FONT></PRE>	<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>	<PRE><FONT COLOR="#0066FF">TEAM                  COUNT=============== ===========COLLECTIONS               1MARKETING                 1RESEARCH                  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.DESCRIPTIONFROM PART F, PART SWHERE F.PARTNUM = S.PARTNUMAND F.DESCRIPTION &lt;&gt; S.DESCRIPTIONAND 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 SHOPPEBIKE SPECLE SHOPPEBIKE SPECJACKS 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.ZIPFROM CUSTOMER CWHERE 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.006 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  PAID13 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>sand <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, ORDEREDONFROM ORDERSWHERE NAME &gt; (SELECT NAMEFROM ORDERSWHERE NAME ='JACKS BIKE')</B>NAME         ORDEREDON========== ===========TRUE WHEEL 15-MAY-1996TRUE WHEEL 19-MAY-1996TRUE WHEEL  2-SEP-1996TRUE WHEEL 30-JUN-1996LE SHOPPE  17-JAN-1996LE 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>