ch07.htm

Learn SQL in an easy way...

AAA BIKE    1-JUN-1996
AAA BIKE    1-JUL-1996
AAA BIKE    1-JUL-1996
JACKS BIKE 11-JUL-1996
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Not what you might expect. The subquery inside <TT>EXISTS</TT> is evaluated only
once in this uncorrelated example. Because the return from the subquery has at least
one row, <TT>EXISTS</TT> evaluates to <TT>TRUE</TT> and all the rows in the query
are printed. If you change the subquery as shown next, you don't get back any results.</P>
<PRE><FONT COLOR="#0066FF">SELECT NAME, ORDEREDON
FROM ORDERS
WHERE EXISTS
(SELECT *
FROM ORDERS
WHERE NAME ='MOSTLY HARMLESS')
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P><TT>EXISTS</TT> evaluates to <TT>FALSE</TT>. The subquery does not generate a
result set because <TT>MOSTLY HARMLESS</TT> is not one of your names.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Notice the use of <TT>SELECT *</TT>
	in the subquery inside the <TT>EXISTS</TT>. <TT>EXISTS</TT> does not care how many
	columns are returned. 
<HR>


</BLOCKQUOTE>

<P>You could use <TT>EXISTS</TT> in this way to check on the existence of certain
rows and control the output of your query based on whether they exist.</P>
<P>If you use <TT>EXISTS</TT> in a correlated subquery, it is evaluated for every
case implied by the correlation you set up. For example:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, ORDEREDON
FROM ORDERS O
WHERE EXISTS
(SELECT *
FROM CUSTOMER C
WHERE STATE ='NE'
AND C.NAME = O.NAME)</B>

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

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
AAA BIKE    1-JUN-1996
AAA BIKE    1-JUL-1996
AAA BIKE    1-JUL-1996
</FONT></PRE>
<P>This slight modification of your first, uncorrelated query returns all the bike
shops from Nebraska that made orders. The following subquery is run for every row
in the query correlated on the <TT>CUSTOMER</TT> name and <TT>ORDERS</TT> name:</P>
<PRE><FONT COLOR="#0066FF">(SELECT *
FROM CUSTOMER C
WHERE STATE ='NE'
AND C.NAME = O.NAME)
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P><TT>EXISTS</TT> is <TT>TRUE</TT> for those rows that have corresponding names
in <TT>CUSTOMER</TT> located in <TT>NE</TT>. Otherwise, it returns <TT>FALSE</TT>.</P>
<P>Closely related to <TT>EXISTS</TT> are the keywords <TT>ANY</TT>, <TT>ALL</TT>,
and <TT>SOME</TT>. <TT>ANY</TT> and <TT>SOME</TT> are identical in function. An optimist
would say this feature provides the user with a choice. A pessimist would see this
condition as one more complication. Look at this query:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME = ANY
(SELECT NAME
FROM ORDERS
WHERE NAME ='TRUE WHEEL')</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<PRE><FONT COLOR="#0066FF">NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P><TT>ANY</TT> compared the output of the following subquery to each row in the
query, returning <TT>TRUE</TT> for each row of the query that has a result from the
subquery.</P>
<PRE><FONT COLOR="#0066FF">(SELECT NAME
FROM ORDERS
WHERE NAME ='TRUE WHEEL')
</FONT></PRE>
<P>Replacing <TT>ANY</TT> with <TT>SOME</TT> produces an identical result:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME = SOME
(SELECT NAME
FROM ORDERS
WHERE NAME ='TRUE WHEEL')</B>

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

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>You may have already noticed the similarity to <TT>IN</TT>. The same query using
<TT>IN</TT> is as follows:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME IN
(SELECT NAME
FROM ORDERS
WHERE NAME ='TRUE WHEEL')</B>

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

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>As you can see, <TT>IN</TT> returns the same result as <TT>ANY</TT> and <TT>SOME</TT>.
Has the world gone mad? Not yet. Can <TT>IN</TT> do this?</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME &gt; ANY
(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>
<P>The answer is no. <TT>IN</TT> works like multiple equals. <TT>ANY</TT> and <TT>SOME</TT>
can be used with other relational operators such as greater than or less than. Add
this tool to your kit.</P>
<P><TT>ALL</TT> returns <TT>TRUE</TT> only if all the results of a subquery meet
the condition. Oddly enough, <TT>ALL</TT> is used most commonly as a double negative,
as in this query:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME &lt;&gt; ALL
(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
BIKE SPEC  30-JUN-1996
BIKE SPEC  30-MAY-1996
BIKE SPEC  30-MAY-1996
BIKE SPEC  17-JAN-1996
LE SHOPPE  17-JAN-1996
LE SHOPPE   1-JUN-1996
AAA BIKE    1-JUN-1996
AAA BIKE    1-JUL-1996
AAA BIKE    1-JUL-1996
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This statement returns everybody except <TT>JACKS BIKE</TT>. <TT>&lt;&gt;ALL</TT>
evaluates to <TT>TRUE</TT> only if the result set does not contain what is on the
left of the <TT>&lt;&gt;</TT>.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>Today you performed dozens of exercises involving subqueries. You learned how
to use one of the most important parts of SQL. You also tackled one of the most difficult
parts of SQL: a correlated subquery. The correlated subquery creates a relationship
between the query and the subquery that is evaluated for every instance of that relationship.
Don't be intimidated by the length of the queries. You can easily examine them one
subquery at a time.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q In some cases SQL offers several ways to get the same result. Isn't this
	flexibility confusing?</B>
	<P><B>A</B> No, not really. Having so many ways to achieve the same result enables
	you to create some really neat statements. Flexibility is the virtue of SQL.
</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>In the section on nested subqueries, the sample subquery returned several
	values:
</DL>



<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?
	<P><B>2.</B> Are the following statements true or false?<BR>
	The aggregate functions <TT>SUM</TT>, <TT>COUNT</TT>, <TT>MIN</TT>, <TT>MAX</TT>,
	and <TT>AVG</TT> all return multiple values.<BR>
	The maximum number of subqueries that can be nested is two.<BR>
	Correlated subqueries are completely self-contained.</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>



<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
</FONT></PRE>
	<PRE><FONT COLOR="#0066FF">   6 rows selected.</FONT></PRE>
	<H5><FONT COLOR="#000000">INPUT/OUTPUT:</FONT></H5>
	<PRE><FONT COLOR="#000000"></FONT></PRE>

</BLOCKQUOTE>

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


<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">      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>

</BLOCKQUOTE>

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

<DL>
	<DD><B>a.</B> <FONT COLOR="#0066FF"><TT>SQL&gt; <B>SELECT * FROM ORDERS</B></TT></FONT>
	<DL>
		<DD><FONT COLOR="#0066FF"><TT><B>WHERE PARTNUM =<BR>
		SELECT PARTNUM FROM PART<BR>
		WHERE DESCRIPTION = 'TRUE WHEEL';</B></TT></FONT>
	</DL>
	<P><B>b.</B> <FONT COLOR="#0066FF"><TT>SQL&gt; <B>SELECT PARTNUM</B></TT></FONT>
	<DL>
		<P><FONT COLOR="#0066FF"><TT><B>FROM ORDERS<BR>
		WHERE PARTNUM = <BR>
		(SELECT * FROM PART<BR>
		WHERE DESCRIPTION = 'LE SHOPPE');</B></TT></FONT></P>
	</DL>
	<P><B>c. </B><FONT COLOR="#0066FF"><TT>SQL&gt; <B>SELECT NAME, PARTNUM</B></TT></FONT>
	<DL>
		<P><FONT COLOR="#0066FF"><TT><B>FROM ORDERS<BR>
		WHERE EXISTS<BR>
		(SELECT * FROM ORDERS<BR>
		WHERE NAME = 'TRUE WHEEL');</B></TT></FONT>
	</DL>
</DL>

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

<DL>
	<DD>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.
</DL>

<H1></H1>
<CENTER>
<P>
<HR>
<A HREF="../ch06/ch06.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../wk1rev/wk1rev.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>