TURNIPS              .45CHEESE               .89APPLES               .23ORANGES</FONT></PRE><PRE><FONT COLOR="#0066FF">7 rows selected.</FONT></PRE><P>To find the non-<TT>NULL</TT> items, type this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *  </B>2<B>  FROM PRICE</B>  3<B>  WHERE WHOLESALE IS NOT NULL;</B>ITEM           WHOLESALE-------------- ---------TOMATOES             .34POTATOES             .51BANANAS              .67TURNIPS              .45CHEESE               .89APPLES               .23</FONT></PRE><PRE><FONT COLOR="#0066FF">6 rows selected.</FONT></PRE><H3><FONT COLOR="#000077">Set Operators</FONT></H3><P>On Day 1, &quot;Introduction to SQL,&quot; you learned that SQL is based on thetheory of sets. The following sections examine set operators.<H4><FONT COLOR="#000077">UNION and UNION ALL</FONT></H4><P><TT>UNION</TT> returns the results of two queries minus the duplicate rows. Thefollowing two tables represent the rosters of teams:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM FOOTBALL;</B></FONT></PRE><H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5><PRE><FONT COLOR="#0066FF">NAME--------------------ABLEBRAVOCHARLIEDECONEXITORFUBARGOOBER</FONT></PRE><PRE><FONT COLOR="#0066FF">7 rows selected.</FONT></PRE><H5><FONT COLOR="#000000">INPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM SOFTBALL;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">NAME--------------------ABLEBAKERCHARLIEDEANEXITORFALCONERGOOBER</FONT></PRE><PRE><FONT COLOR="#0066FF">7 rows selected.</FONT></PRE><P>How many different people play on one team or another?</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT NAME FROM SOFTBALL  </B>2<B>  UNION</B>  3<B>  SELECT NAME FROM FOOTBALL;</B>NAME--------------------ABLEBAKERBRAVOCHARLIEDEANDECONEXITORFALCONERFUBARGOOBER</FONT></PRE><PRE><FONT COLOR="#0066FF">10 rows selected.</FONT></PRE><P><TT>UNION</TT> returns 10 distinct names from the two lists. How many names areon both lists (including duplicates)?</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT NAME FROM SOFTBALL </B> 2<B>  UNION ALL </B> 3 <B> SELECT NAME FROM FOOTBALL;</B>NAME--------------------ABLEBAKERCHARLIEDEANEXITORFALCONERGOOBERABLEBRAVOCHARLIEDECONEXITORFUBARGOOBER</FONT></PRE><PRE><FONT COLOR="#0066FF">14 rows selected.</FONT></PRE><H5>ANALYSIS:</H5><P>The combined list--courtesy of the <TT>UNION ALL</TT> statement--has 14 names.<TT>UNION ALL</TT> works just like <TT>UNION</TT> except it does not eliminate duplicates.Now show me a list of players who are on both teams. You can't do that with <TT>UNION</TT>--youneed to learn <TT>INTERSECT</TT>.<H4><FONT COLOR="#000077">INTERSECT</FONT></H4><P><TT>INTERSECT</TT> returns only the rows found by both queries. The next <TT>SELECT</TT>statement shows the list of players who play on both teams:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM FOOTBALL</B>  2 <B> INTERSECT</B>  3<B>  SELECT * FROM SOFTBALL;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#000000"></FONT></PRE><PRE><FONT COLOR="#0066FF">NAME--------------------ABLECHARLIEEXITORGOOBER</FONT></PRE><H5>ANALYSIS:</H5><P>In this example <TT>INTERSECT</TT> finds the short list of players who are onboth teams by combining the results of the two <TT>SELECT</TT> statements.<H4><FONT COLOR="#000077">MINUS (Difference)</FONT></H4><P><TT>Minus</TT> returns the rows from the first query that were not present inthe second. For example:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM FOOTBALL </B> 2 <B> MINUS</B>  3 <B> SELECT * FROM SOFTBALL;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">NAME--------------------BRAVODECONFUBAR</FONT></PRE><H5>ANALYSIS:</H5><P>The preceding query shows the three football players who are not on the softballteam. If you reverse the order, you get the three softball players who aren't onthe football team:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM SOFTBALL</B>  2 <B> MINUS </B> 3 <B> SELECT * FROM FOOTBALL;</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">NAME--------------------BAKERDEANFALCONER</FONT></PRE><H3><FONT COLOR="#000077">Miscellaneous Operators: IN and BETWEEN</FONT></H3><P>The two operators <TT>IN</TT> and <TT>BETWEEN</TT> provide a shorthand for functionsyou already know how to do. If you wanted to find friends in Colorado, California,and Louisiana, you could type the following:</P><H5>INPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *  </B>2<B>  FROM FRIENDS </B> 3 <B> WHERE STATE= 'CA'  </B>4 <B> OR  </B>5 <B> STATE ='CO' </B> 6<B>  OR </B> 7 <B> STATE = 'LA';</B></FONT></PRE><H5><FONT COLOR="#000000">OUTPUT:</FONT></H5><PRE><FONT COLOR="#0066FF">LASTNAME       FIRSTNAME       AREACODE PHONE    ST ZIP-------------- -------------- --------- -------- -- -----MERRICK        BUD                  300 555-6666 CO 80212MAST           JD                   381 555-6767 LA 23456PERKINS        ALTON                911 555-3116 CA 95633</FONT></PRE><P>Or you could type this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * </B> 2<B>  FROM FRIENDS  </B>3<B>  WHERE STATE IN('CA','CO','LA');</B></FONT></PRE><PRE><FONT COLOR="#0066FF">LASTNAME       FIRSTNAME       AREACODE PHONE    ST ZIP-------------- -------------- --------- -------- -- -----MERRICK        BUD                  300 555-6666 CO 80212MAST           JD                   381 555-6767 LA 23456PERKINS        ALTON                911 555-3116 CA 95633</FONT></PRE><H5>ANALYSIS:</H5><P>The second example is shorter and more readable than the first. You never knowwhen you might have to go back and work on something you wrote months ago. <TT>IN</TT>also works with numbers. Consider the following, where the column <TT>AREACODE</TT>is a number:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * </B> 2 <B> </B>FROM FRIENDS<B></B>  3 <B> WHERE AREACODE IN(100,381,204);</B>LASTNAME       FIRSTNAME       AREACODE PHONE    ST ZIP-------------- -------------- --------- -------- -- -----BUNDY          AL                   100 555-1111 IL 22333MAST           JD                   381 555-6767 LA 23456BOSS           SIR                  204 555-2345 CT 95633</FONT></PRE><P>If you needed a range of things from the <TT>PRICE</TT> table, you could writethe following:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt;  <B>SELECT * </B> 2<B>  FROM PRICE </B> 3<B>  WHERE WHOLESALE &gt; 0.25</B>  4 <B> AND</B>  5<B>  WHOLESALE &lt; 0.75;</B>ITEM           WHOLESALE-------------- ---------TOMATOES             .34POTATOES             .51BANANAS              .67TURNIPS              .45</FONT></PRE><P>Or using <TT>BETWEEN</TT>, you would write this:</P><H5>INPUT/OUTPUT:</H5><PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT *</B>  2 <B> FROM PRICE </B> 3<B>  WHERE WHOLESALE BETWEEN 0.25 AND 0.75;</B>ITEM            WHOLESALE-------------- ---------TOMATOES              .34POTATOES              .51BANANAS               .67TURNIPS               .45</FONT></PRE><P>Again, the second example is a cleaner, more readable solution than the first.<BLOCKQUOTE>	<P><HR><FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>If a <TT>WHOLESALE</TT> value of	<TT>0.25</TT> existed in the <TT>PRICE</TT> table, that record would have been retrieved	also. Parameters used in the <TT>BETWEEN</TT> operator are inclusive parametersinclusive.	<HR></BLOCKQUOTE><H2><FONT COLOR="#000077">Summary</FONT></H2><P>At the beginning of Day 3, you knew how to use the basic <TT>SELECT</TT> and <TT>FROM</TT>clauses. Now you know how to use a host of operators that enable you to fine-tuneyour requests to the database. You learned how to use arithmetic, comparison, character,logical, and set operators. This powerful set of tools provides the cornerstone ofyour SQL knowledge.<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2><DL>	<DD><B>Q How does all of this information apply to me if I am not using SQL from	the command line as depicted in the examples?</B>	<P><B>A</B> Whether you use SQL in COBOL as Embedded SQL or in Microsoft's Open Database	Connectivity (ODBC), you use the same basic constructions. You will use what you	learned today and yesterday repeatedly as you work with SQL.</P>	<P><B>Q</B> <B>Why are you constantly telling me to check my implementation? I thought	there was a standard!</B></P>	<P><B>A</B> There is an ANSI standard (the most recent version is 1992); however,	most vendors modify it somewhat to suit their databases. The basics are similar if	not identical, and each instance has extensions that other vendors copy and improve.	We have chosen to use ANSI as a starting point but point out the differences as we	go along.</DL><H2><FONT COLOR="#000077">Workshop</FONT></H2><P>The Workshop provides quiz questions to help solidify your understanding of thematerial covered, as well as exercises to provide you with experience in using whatyou have learned. Try to answer the quiz and exercise questions before checking theanswers in Appendix F, &quot;Answers to Quizzes and Exercises.&quot;<H3><FONT COLOR="#000077">Quiz</FONT></H3><P>Use the <TT>FRIENDS</TT> table to answer the following questions.</P><PRE><FONT COLOR="#0066FF">LASTNAME       FIRSTNAME       AREACODE PHONE    ST ZIP-------------- -------------- --------- -------- -- -----BUNDY          AL                   100 555-1111 IL 22333MEZA           AL                   200 555-2222 UKMERRICK        BUD                  300 555-6666 CO 80212MAST           JD                   381 555-6767 LA 23456BULHER         FERRIS               345 555-3223 IL 23332PERKINS        ALTON                911 555-3116 CA 95633BOSS           SIR                  204 555-2345 CT 95633</FONT></PRE><DL>	<DD><B>1.</B> Write a query that returns everyone in the database whose last name	begins with <TT>M</TT>.	<P><B>2.</B> Write a query that returns everyone who lives in Illinois with a first	name of <TT>AL</TT>.</P>	<P><B>3. </B>Given two tables (<TT>PART1</TT> and <TT>PART2</TT>) containing columns	named <TT>PARTNO</TT>, how would you find out which part numbers are in both tables?	Write the query.</P>	<P><B>4. </B>What shorthand could you use instead of <TT>WHERE a &gt;= 10 AND a &lt;=30</TT>?</P>	<P><B>5. </B>What will this query return?</DL><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF"><B>SELECT FIRSTNAMEFROM FRIENDSWHERE FIRSTNAME = 'AL'  AND LASTNAME = 'BULHER';</B></FONT></PRE></BLOCKQUOTE><H3><FONT COLOR="#000077">Exercises</FONT></H3><DL>	<DD><B>1.</B> Using the <TT>FRIENDS</TT> table, write a query that returns the following:</DL><PRE></PRE><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">NAME                ST------------------- --AL             FROM IL</FONT></PRE>	<H5><FONT COLOR="#000000">INPUT:</FONT></H5>	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT (FIRSTNAME || 'FROM') NAME, STATE  </B>2 <B> FROM FRIENDS  </B>3<B>  WHERE STATE = 'IL'  </B>4<B>  AND</B>  5  <B>LASTNAME = 'BUNDY';</B></FONT></PRE>	<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>	<PRE><FONT COLOR="#0066FF">NAME                ST------------------- --AL             FROM IL</FONT></PRE></BLOCKQUOTE><PRE><FONT COLOR="#0066FF"></FONT></PRE><DL>	<DD><B>2. </B>Using the <TT>FRIENDS</TT> table, write a query that returns the following:</DL><PRE></PRE><BLOCKQUOTE>	<PRE><FONT COLOR="#0066FF">NAME                       PHONE-------------------------- -------------MERRICK, BUD               300-555-6666MAST, JD                   381-555-6767BULHER, FERRIS             345-555-3223</FONT></PRE></BLOCKQUOTE><CENTER><P><HR><A HREF="../ch02/ch02.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch04/ch04.htm"><IMGSRC="../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. Allrights reserved.</CENTER></BODY></HTML>