ch01.htm

21精通SQL

SQL*Plus tool. This tool then returns data to the screen for the user to see, or
it performs the appropriate action on the database.</P>
<P>Most examples are directed toward the beginning programmer or first-time user
of SQL. We begin with the simplest of SQL statements and advance to the topics of
transaction management and stored procedure programming. The Oracle RDBMS is distributed
with a full complement of development tools. It includes a C++ and Visual Basic language
library (Oracle Objects for OLE) that can link an application to a Personal Oracle
database. It also comes with graphical tools for database, user, and object administration,
as well as the SQL*Loader utility, which is used to import and export data to and
from Oracle.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>Personal Oracle7 is a scaled-down
	version of the full-blown Oracle7 server product. Personal Oracle7 allows only single-user
	connections (as the name implies). However, the SQL syntax used on this product is
	identical to that used on the larger, more expensive versions of Oracle. In addition,
	the tools used in Personal Oracle7 have much in common with the Oracle7 product.
	
<HR>


</BLOCKQUOTE>

<P>We chose the Personal Oracle7 RDBMS for several reasons:

<UL>
	<LI>It includes nearly all the tools needed to demonstrate the topics discussed in
	this book.<BR>
	<BR>
	
	<LI>It is available on virtually every platform in use today and is one of the most
	popular RDBMS products worldwide.<BR>
	<BR>
	
	<LI>A 90-day trial copy can be downloaded from Oracle Corporation's World Wide Web
	server (<A HREF="tppmsgs/msgs0.htm#40" tppabs="http://www.oracle.com/"><B>http://www.oracle.com</B></A>).
</UL>

<P>Figure 1.4 shows SQL*Plus from this suite of tools.</P>
<P><A NAME="04"></A><A HREF="04.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/04.htm"><B>Figure 1.4.</B></A><B><BR>
</B><I>Oracle's SQL*Plus.</I></P>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>Keep in mind that nearly all the
	SQL code given in this book is portable to other database management systems. In
	cases where syntax differs greatly among different vendors' products, examples are
	given to illustrate these differences. 
<HR>


</BLOCKQUOTE>

<H3><FONT COLOR="#000077"><B>Microsoft Query</B></FONT></H3>
<P>Microsoft Query (see Figure 1.5) is a useful query tool that comes packaged with
Microsoft's Windows development tools, Visual C++, and Visual Basic. It uses the
ODBC standard to communicate with underlying databases. Microsoft Query passes SQL
statements to a driver, which processes the statements before passing them to a database
system.</P>
<P><A NAME="05"></A><A HREF="05.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/05.htm"><B>Figure 1.5.</B></A><B><BR>
</B><I>Microsoft Query.</I></P>
<H2><FONT COLOR="#000077"><B>Open Database Connectivity (ODBC)</B></FONT></H2>
<P>ODBC is a functional library designed to provide a common Application Programming
Interface (API) to underlying database systems. It communicates with the database
through a library driver, just as Windows communicates with a printer via a printer
driver. Depending on the database being used, a networking driver may be required
to connect to a remote database. The architecture of ODBC is illustrated in Figure
1.6.</P>
<P><A NAME="06"></A><A HREF="06.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/06.htm"><B>Figure 1.6.</B></A><B><BR>
</B><I>ODBC structure.</I></P>
<P>The unique feature of ODBC (as compared to the Oracle or Sybase libraries) is
that none of its functions are database-vendor specific. For instance, you can use
the same code to perform queries against a Microsoft Access table or an Informix
database with little or no modification. Once again, it should be noted that most
vendors add some proprietary extensions to the SQL standard, such as Microsoft's
and Sybase's Transact-SQL and Oracle's PL/SQL.</P>
<P>You should always consult the documentation before beginning to work with a new
data source. ODBC has developed into a standard adopted into many products, including
Visual Basic, Visual C++, FoxPro, Borland Delphi, and PowerBuilder. As always, application
developers need to weigh the benefit of using the emerging ODBC standard, which enables
you to design code without regard for a specific database, versus the speed gained
by using a database specific function library. In other words, using ODBC will be
more portable but slower than using the Oracle7 or Sybase libraries.
<H2><FONT COLOR="#000077"><B>SQL in Application Programming</B></FONT></H2>
<P>SQL was originally made an ANSI standard in 1986. The ANSI 1989 standard (often
called SQL-89) defines three types of interfacing to SQL within an application program:

<UL>
	<LI>Module Language-- Uses procedures within programs. These procedures can be called
	by the application program and can return values to the program via parameter passing.<BR>
	<BR>
	
	<LI>Embedded SQL--Uses SQL statements embedded with actual program code. This method
	often requires the use of a precompiler to process the SQL statements. The standard
	defines statements for Pascal, FORTRAN, COBOL, and PL/1.<BR>
	<BR>
	
	<LI>Direct Invocation--Left up to the implementor.
</UL>

<P>Before the concept of dynamic SQL evolved, embedded SQL was the most popular way
to use SQL within a program. Embedded SQL, which is still used, uses <I>static</I>
SQL--meaning that the SQL statement is compiled into the application and cannot be
changed at runtime. The principle is much the same as a compiler versus an interpreter.
The performance for this type of SQL is good; however, it is not flexible--and cannot
always meet the needs of today's changing business environments. Dynamic SQL is discussed
shortly.</P>
<P>The ANSI 1992 standard (SQL-92) extended the language and became an international
standard. It defines three levels of SQL compliance: entry, intermediate, and full.
The new features introduced include the following:

<UL>
	<LI>Connections to databases<BR>
	<BR>
	
	<LI>Scrollable cursors<BR>
	<BR>
	
	<LI>Dynamic SQL<BR>
	<BR>
	
	<LI>Outer joins
</UL>

<P>This book covers not only all these extensions but also some proprietary extensions
used by RDBMS vendors. Dynamic SQL allows you to prepare the SQL statement at runtime.
Although the performance for this type of SQL is not as good as that of embedded
SQL, it provides the application developer (and user) with a great degree of flexibility.
A call-level interface, such as ODBC or Sybase's DB-Library, is an example of dynamic
SQL.</P>
<P>Call-level interfaces should not be a new concept to application programmers.
When using ODBC, for instance, you simply fill a variable with your SQL statement
and call the function to send the SQL statement to the database. Errors or results
can be returned to the program through the use of other function calls designed for
those purposes. Results are returned through a process known as the <I>binding</I>
<I>of variables</I>.
<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>Day 1 covers some of the history and structure behind SQL. Because SQL and relational
databases are so closely linked, Day 1 also covers (albeit briefly) the history and
function of relational databases. Tomorrow is devoted to the most important component
of SQL: the query.
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q Why should I be concerned about SQL?</B><BR>
	<BR>
	<B>A</B> Until recently, if you weren't working on a large database system, you probably
	had only a passing knowledge of SQL. With the advent of client/server development
	tools (such as Visual Basic, Visual C++, ODBC, Borland's Delphi, and Powersoft's
	PowerBuilder) and the movement of several large databases (Oracle and Sybase) to
	the PC platform, most business applications being developed today require a working
	knowledge of SQL.<BR>
	<BR>
	<B>Q Why do I need to know anything about relational database theory to use SQL?</B><BR>
	<BR>
	<B>A</B> SQL was developed to service relational databases. Without a minimal understanding
	of relational database theory, you will not be able to use SQL effectively except
	in the most trivial cases.<BR>
	<BR>
	<B>Q All the new GUI tools enable me to click a button to write SQL. Why should I
	spend time learning to write SQL manually?</B><BR>
	<BR>
	<B>A </B>GUI tools have their place, and manually writing SQL has its place. Manually
	written SQL is generally more efficient than GUI-written SQL. Also, a GUI SQL statement
	is not as easy to read as a manually written SQL statement. Finally, knowing what
	is going on behind the scenes when you use GUI tools will help you get the most out
	of them.<BR>
	<BR>
	<B>Q So, if SQL is standardized, should I be able to program with SQL on any databases?</B><BR>
	<BR>
	<B>A </B>No, you will be able to program with SQL only on RDBMS databases that support
	SQL, such as MS-Access, Oracle, Sybase, and Informix. Although each vendor's implementation
	will differ slightly from the others, you should be able to use SQL with very few
	adjustments.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></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"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1. </B>What makes SQL a nonprocedural language?<BR>
	<BR>
	<B>2.</B> How can you tell whether a database is truly relational?<BR>
	<BR>
	<B>3.</B> What can you do with SQL?<BR>
	<BR>
	<B>4. </B>Name the process that separates data into distinct, unique sets.
</DL>

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

<DL>
	<DD>Determine whether the database you use at work or at home is truly relational.
</DL>

<H1></H1>
<CENTER>
<P>
<HR>
<A HREF="wk1ag.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/wk1ag.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="ch02.htm" tppabs="http://202.113.16.101/%7eeb%7e/Teach%20Yourself%20MS%20SQL%20Server%206.5%20in%2021%20Days/ch02.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>