ch02.htm

21精通SQL

look like this:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">PAYEE                REMARKS                AMOUNT      CHECK#
-------------------- ------------------  ---------   ---------
Ma Bell              Have sons next time       150           1
Reading R.R.         Train to Chicago       245.34           2
Ma Bell              Cellular Phone         200.32           3
Local Utilities      Gas                        98           4
Joes Stale $ Dent    Groceries                 150           5
Cash                 Wild Night Out             25           6
Joans Gas            Gas                      25.1           7

7 rows selected.
</FONT></PRE>
<P>Another way to write the same statement follows.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF"><B>SELECT payee, remarks, amount, check#
FROM checks;</B>
</FONT></PRE>
<P>Notice that the <TT>FROM</TT> clause has been carried over to the second line.
This convention is a matter of personal taste when writing SQL code. The output would
look like this:</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">PAYEE                REMARKS                 AMOUNT   CHECK#
-------------------- -------------------- --------- --------
Ma Bell              Have sons next time        150        1
Reading R.R.         Train to Chicago        245.34        2
Ma Bell              Cellular Phone          200.32        3
Local Utilities      Gas                         98        4
Joes Stale $ Dent    Groceries                  150        5
Cash                 Wild Night Out              25        6
Joans Gas            Gas                       25.1        7

7 rows selected.</FONT></PRE>
<H5><FONT COLOR="#000000">ANALYSIS:</FONT></H5>
<PRE><FONT COLOR="#000000"></FONT></PRE>
<P>The output is identical because only the format of the statement changed. Now
that you have established control over the order of the columns, you will be able
to specify which columns you want to see.
<H3><FONT COLOR="#000077">Selecting Individual Columns</FONT></H3>
<P>Suppose you do not want to see every column in the database. You used <TT>SELECT
*</TT> to find out what information was available, and now you want to concentrate
on the check number and the amount. You type</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT CHECK#, amount from checks;
</B></FONT></PRE>
<P>which returns</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">   CHECK#    AMOUNT
--------- ---------
        1       150
        2    245.34
        3    200.32
        4        98
        5       150
        6        25
        7      25.1

7 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Now you have the columns you want to see. Notice the use of upper- and lowercase
in the query. It did not affect the result.</P>
<P>What if you need information from a different table?
<H3><FONT COLOR="#000077">Selecting Different Tables</FONT></H3>
<P>Suppose you had a table called <TT>DEPOSITS</TT> with this structure:</P>
<PRE><FONT COLOR="#0066FF">DEPOSIT# WHOPAID                AMOUNT REMARKS
-------- ---------------------- ------ -------------------
       1 Rich Uncle                200 Take off Xmas list
       2 Employer                 1000 15 June Payday
       3 Credit Union              500 Loan
</FONT></PRE>
<P>You would simply change the <TT>FROM</TT> clause to the desired table and type
the following statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select * from deposits
</B></FONT></PRE>
<P>The result is</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF"> DEPOSIT# WHOPAID               AMOUNT REMARKS
-------- ---------------------- ------ -------------------
       1 Rich Uncle                200 Take off Xmas list
              2 	Employer                 1000 15 June Payday
              3 	Credit Union              500 Loan
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>With a single change you have a new data source.
<H2><FONT COLOR="#000077">Queries with Distinction</FONT></H2>
<P>If you look at the original table, <TT>CHECKS</TT>, you see that some of the data
repeats. For example, if you looked at the <TT>AMOUNT</TT> column using</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select amount from checks;
</B></FONT></PRE>
<P>you would see</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">   AMOUNT
---------
      150
   245.34
   200.32
       98
      150
       25
     25.1
</FONT></PRE>
<P>Notice that the amount <TT>150</TT> is repeated. What if you wanted to see how
may different amounts were in this column? Try this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>select DISTINCT amount from checks;</B>
</FONT></PRE>
<P>The result would be</P>
<H5>OUTPUT:</H5>
<PRE><FONT COLOR="#0066FF">   AMOUNT
---------
       25
     25.1
       98
      150
   200.32
   245.34

6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice that only six rows are selected. Because you specified <TT>DISTINCT</TT>,
only one instance of the duplicated data is shown, which means that one less row
is returned. <TT>ALL</TT> is a keyword that is implied in the basic <TT>SELECT</TT>
statement. You almost never see <TT>ALL</TT> because <TT>SELECT &lt;Table&gt;</TT>
and <TT>SELECT ALL &lt;Table&gt;</TT> have the same result.</P>
<P>Try this example--for the first (and only!) time in your SQL career:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT ALL AMOUNT</B>
  2  <B>FROM CHECKS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">   AMOUNT
---------
      150
   245.34
   200.32
       98
      150
       25
     25.1

7 rows selected.
</FONT></PRE>
<P>It is the same as a <TT>SELECT &lt;Column&gt;</TT>. Who needs the extra keystrokes?
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>The keywords <TT>SELECT</TT> and <TT>FROM</TT> enable the query to retrieve data.
You can make a broad statement and include all tables with a <TT>SELECT *</TT> statement,
or you can rearrange or retrieve specific tables. The keyword <TT>DISTINCT </TT>limits
the output so that you do not see duplicate values in a column. Tomorrow you learn
how to make your queries even more selective.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q Where did this data come from and how do I connect to it?</B>
	<P><B>A</B> The data was created using the methods described on Day 8. The database
	connection depends on how you are using SQL. The method shown is the traditional
	command-line method used on commercial-quality databases. These databases have traditionally
	been the domain of the mainframe or the workstation, but recently they have migrated
	to the PC.</P>
	<P><B>Q</B> <B>OK, but if I don't use one of these databases, how will I use SQL?</B></P>
	<P><B>A</B> You can also use SQL from within a programming language. Embedded SQLEmbedded
	SQL is normally a language extension, most commonly seen in COBOL, in which SQL is
	written inside of and compiled with the program. Microsoft has created an entire
	Application Programming Interface (API) that enables programmers to use SQL from
	inside Visual Basic, C, or C++. Libraries available from Sybase and Oracle also enable
	you to put SQL in your programs. Borland has encapsulated SQL into database objects
	in Delphi. The concepts in this book apply in all these languages.
</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; and make sure
you understand the answers before starting tomorrow's work.
<H3><FONT COLOR="#000077">Quiz</FONT></H3>

<DL>
	<DD><B>1.</B> Do the following statements return the same or different output:
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SELECT * FROM CHECKS;
select * from checks;?</FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>2. </B>The following queries do not work. Why not?
	<DL>
		<P><B>a. </B><TT>Select *</TT></P>
		<P><B>b.</B> <TT>Select * from checks</TT></P>
		<P><B>c.</B> <TT>Select amount name payee FROM checks;</TT></P>
	</DL>
	<P><B>3.</B> Which of the following SQL statements will work?</P>
	<DL>
		<P><B>a</B>. <TT>select *</TT></P>
		<P><TT>  from checks;</TT></P>
		<P><B>b.</B> <TT>select * from checks;</TT></P>

		<P><B>c. </B><TT>select * from checks</TT><BR>
		<TT></TT></P>

		<P><TT>  /</TT>
	</DL>
</DL>

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

<DL>
	<DD><B>1.</B> Using the <TT>CHECKS</TT> table from earlier today, write a query to
	return just the check numbers and the remarks.
	<P><B>2.</B> Rewrite the query from exercise 1 so that the remarks will appear as
	the first column in your query results.</P>
	<P><B>3. </B>Using the <TT>CHECKS</TT> table, write a query to return all the unique
	remarks.
</DL>

<CENTER>
<P>
<HR>
<A HREF="ch01.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch01.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="ch03.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch03.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>&#169; <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>