📄 ch03.htm
字号:
GOOBER
</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 are
on both lists (including duplicates)?</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT NAME FROM SOFTBALL
</B> 2<B> UNION ALL
</B> 3 <B> SELECT NAME FROM FOOTBALL;</B>
NAME
--------------------
ABLE
BAKER
CHARLIE
DEAN
EXITOR
FALCONER
GOOBER
ABLE
BRAVO
CHARLIE
DECON
EXITOR
FUBAR
GOOBER
</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>--you
need 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> <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
--------------------
ABLE
CHARLIE
EXITOR
GOOBER
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>In this example <TT>INTERSECT</TT> finds the short list of players who are on
both 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 in
the second. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <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
--------------------
BRAVO
DECON
FUBAR
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The preceding query shows the three football players who are not on the softball
team. If you reverse the order, you get the three softball players who aren't on
the football team:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <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
--------------------
BAKER
DEAN
FALCONER
</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 functions
you 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> <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 80212
MAST JD 381 555-6767 LA 23456
PERKINS ALTON 911 555-3116 CA 95633
</FONT></PRE>
<P>Or you could type this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <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 80212
MAST JD 381 555-6767 LA 23456
PERKINS 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 know
when 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> <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 22333
MAST JD 381 555-6767 LA 23456
BOSS SIR 204 555-2345 CT 95633
</FONT></PRE>
<P>If you needed a range of things from the <TT>PRICE</TT> table, you could write
the following:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B> 2<B> FROM PRICE
</B> 3<B> WHERE WHOLESALE > 0.25
</B> 4 <B> AND
</B> 5<B> WHOLESALE < 0.75;</B>
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .45
</FONT></PRE>
<P>Or using <TT>BETWEEN</TT>, you would write this:</P>
<H5>INPUT/OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT *
</B> 2 <B> FROM PRICE
</B> 3<B> WHERE WHOLESALE BETWEEN 0.25 AND 0.75;
</B>
ITEM WHOLESALE
-------------- ---------
TOMATOES .34
POTATOES .51
BANANAS .67
TURNIPS .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-tune
your requests to the database. You learned how to use arithmetic, comparison, character,
logical, and set operators. This powerful set of tools provides the cornerstone of
your SQL knowledge.
<H2><FONT COLOR="#000077">Q&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 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, "Answers to Quizzes and Exercises."
<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 22333
MEZA AL 200 555-2222 UK
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
PERKINS ALTON 911 555-3116 CA 95633
BOSS 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 >= 10 AND a <=30</TT>?</P>
<P><B>5. </B>What will this query return?
</DL>
<BLOCKQUOTE>
<PRE><FONT COLOR="#0066FF"><B>SELECT FIRSTNAME
FROM FRIENDS
WHERE 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> <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-6666
MAST, JD 381-555-6767
BULHER, FERRIS 345-555-3223</FONT></PRE>
</BLOCKQUOTE>
<CENTER>
<P>
<HR>
<A HREF="ch02.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch02.htm"><IMG SRC="previous.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="ch04.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch04.htm"><IMG
SRC="next.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="index-1.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/index-1.htm"><IMG SRC="contents.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A><BR>
<BR>
<BR>
<IMG SRC="corp.gif" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>
<P>© <A HREF="copy.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>
</BODY>
</HTML>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -