This second installment covers procedural versus declarative languages, SQL's place in the data center and obtaining SQL.
Procedural Versus Declarative Languages
We mentioned at the beginning of the chapter, that SQL is not a procedural language. Before we move
on to look at how we can actually implement SQL, we'll first see exactly what we mean by a declarative
rather than procedural language. Many readers will have experience in languages like Visual Basic or
C++ in which they mastered how to write a series of statements in order to achieve a goal. You have
probably even used these commands to "walk through" a set of data checking to see if each item is the
item of interest. If you wanted to change data you wrote a series of steps that somehow opened up the
datastore, then put a pointer in the correct location, modified the data, moved to the next bit of data (all
the while checking if you were at the end of the data yet), modified the next record and finished by
closing the datastore. In a procedural language we tell the computer each step to perform. The
computer performs those steps and, if they are well written, we will end up with our desired result.
SQL is not a procedural language but a declarative language. You write a single SQL declaration and
hand it to the DBMS. The DBMS then executes internal code, which is hidden from us. The DBMS
returns a set, which is a group of data that is somehow defined. For example, we write a SQL statement
that would translate as something like Give me the last names of all the employees. The DBMS will
work away for a few milliseconds and then produce a set that contains Adams, Barrett, Cao, etc. In a
declarative language, we carefully phrase what we want and then let the DBMS get it for us. If we have
written a good SQL statement then the resulting set of data will be correct.
Procedural languages result in many lines of code.
Declarative languages result in one statement of the desired result.
The distinction between these classes of languages drives the entire way that we think about database
programming. Many of the mistakes that I see are a result of thinking in a procedural way when writing
SQL statements. In this book we will show you hundreds of examples of good SQL statements that will
help you on your way to thinking declaratively.
Languages in a Taxi Ride
A good analogy exists between these types of languages and different types of taxi rides. When I get in a
taxi I can give a direction to the driver in one of two ways. If the driver looks and sounds like he knows
what he is doing then I just give him the address. If the driver looks like he just moved to town
yesterday, then I talk him through the directions to get to my destination.
If I give the driver an address I am communicating like a SQL statement, it is a clear description of the
result and the driver carries it out as he or she sees fit. We may go south then east or we may go east
then south. The driver is free to adapt to local conditions like a closed road or a traffic jam. It doesn't
really matter to me; a good taxi driver will get me to my stated destination in the best way the driver
knows. During the trip I just sit still and wait for the result. If the address is written on a piece of paper
then I can show that same card to any good driver.
If I give the driver exact directions then I am communicating in a procedural language. I tell the driver
to head south four blocks then turn left at the Luck Hunan Restaurant. Procedural languages work fine
when the taxi driver is not too experienced and when the situation is very uniform. But I must
constantly interact with the driver. If I get involved in a book we are likely to overshoot our destination.
Likewise, if I tell the driver to turn left at the Luck Hunan Restaurant and the name has changed to the
Lucky Duck Bar, then the communication will fail.
In this analogy we see a number of important points that apply to SQL. First, a declarative language
only works when you have a strong, SQL-enabled DBMS (taxi driver). Second, procedural languages
can fail when there are unexpected changes in the situation (changes in the city's landmarks). Third,
procedural languages require interaction during the task (like me instructing the driver at every turn)
whereas declarative languages only require that the requester wait for the process to finish. And fourth,
a well-written SQL statement will work with any SQL-enabled DBMS (like an address on a paper will
work with any good taxi driver).
Comparing Procedural and Declarative Languages
|Procedural (Basic, C++, Cobol, etc.)
|Most work done by interpreter of the languages
||Most work done by Data Engine within the DBMS|
|Many lines of code to perform a task
||One SQL statement to perform task|
|Programmer must be skilled in translating the objective into lines of procedural code
||Programmer must be skilled in clearly stating the objective as a SQL statement|
|Requires minimum of management around the actual data
||Relies on sophisticated, SQL-enabled DBMS to hold the data and execute the SQL statement against the data|
|Programmer understands and has access to each step of the code
||Programmer has no interaction with the execution of the SQL statement|
|Data exposed to programmer during execution of the code
||Programmer receives data at end as an entire set|
|More susceptible to failure due to changes in the data structure
||More resistant to changes in the data structure|
|Traditionally faster, but that is changing
||Originally slower, but now setting speed records|
|Code of procedure tightly linked to front end
||Same SQL statements will work with most front ends|
Code loosely linked to front end.
|Code tightly integrated with structure of the datastore
||Code loosely linked to structure of data; DBMS handles structural issues|
|Programmer works with a pointer or cursor
||Programmer not concerned with positioning|
|Knowledge of coding tricks applies only to one language
||Knowledge of SQL tricks applies to any language using SQL|
To summarize this table, declarative languages are quite different from the procedural languages you
may be using now. Procedural languages like C++ place an emphasis on the programmer writing many
lines of code to describe the exact steps of obtaining a result. Declarative languages like SQL place an
emphasis on the programmer writing an exact description of the desired result. The DBMS then handles
the task of obtaining the result. A good SQL programmer becomes very skilled at carefully describing
the result but remains blissfully ignorant of the internal code of the DBMS that executes the result.
SQL's Place in the Data Center
So how does SQL actually fit into the solution to my data management problems? We discussed earlier
that SQL did not have a front end (user interface) or a back end (DBMS). So in the next few sections we
will discuss some "ends" that we can use as well as the protocols that create a bridge across which the
SQL statements flow.
"How Do I Get SQL? "
You need a front end that can connect to a SQL-enabled DBMS and can pass a SQL statement. We will
talk more about connections in Chapter 2, but for now consider a connection to be a conduit for
carrying SQL statements between the front and back ends. A connection is like the apparatus of the
telephone company that connects your handset to your friend's handset it allows a language to be used
to communicate. Third, you need a SQL-enabled DBMS. You probably already have a SQL-enabled
front end and a DBMS in one form or another (see the sections below). And if you don't you can get an
inexpensive version or a trial version that will allow you to get started learning SQL for free.
Keep in mind that since SQL is a standard in the public domain, you do not need software or a license
to use the language. You can acquire SQL by simply learning the syntax of how to write the SQL
statements. But in order to practice creating and running SQL statements you will need three parts:
- a front end
- a connection
- a back end
We will look in detail at various options for each of these, but let us start with a quick overview.
The front end provides a means to send a SQL statement. Usually the front end includes an interface to
users with a form to gather data and buttons to carry out tasks. The front end may contain a fixed SQL
statement or it may use code to create a statement on the fly using the data provided by the user. The
most common type of front end is the web browser.
The connection provides a conduit to get the SQL statement to the back end and then to return results
to the front end. The connection is like a telephone connection over which a spoken language travels,
although the language itself is free. A common connection today is ADO (Activex Data Objects) but
there are many other options.
The back end is a DBMS that accepts and can act on SQL statements. Usually the back end has two
parts, the actual store of data and a database engine, which can carry out the SQL statement. The most
common back ends are the major database products such as Oracle and Microsoft SQL Server.
The included CD contains a front end, connection and back end, but in a less
conventional arrangement than the one we have just described. The CD is a 120-day
trial version of Microsoft SQL Server 2000. SQL Server provides a very strong back
end, but it does not come with front end or connection software. However, there is a
utility called Query Analyzer which comes with SQL Server. This tool provides a front
end optimized for developers that want to test SQL statements. It is a very simple
front end that allows you to type a SQL Statement, run it and then view the results. A
normal front end would have tools for creating a polished interface for users, but
Query Analyzer is aimed at developers that are testing. Built into Query Analyzer is
connection software that automatically creates a connection when the developer
selects which database to work with.
In this book we will use different front ends in chapters in order to help you become more versatile. The
differences are not very great; the SQL statements remain the same regardless of the front end.
In Chapter 2 we will talk you through setting up various combination of front ends and back ends as
well as showing samples of code.
The syntax of how each front end sends those SQL statements may vary occasionally. Likewise, each
front end has its own syntax or tools for handling the data that is returned when a SQL statement
asks for information. We will point such differences out where they may occur.
Microsoft's desktop database uses SQL in many places. You can write and store queries as SQL
statements. You can use SQL to define the source of a report or form. One option puts the actual SQL
statement into the report as the value of the Record Source property. A second option stores the
statement in a query and uses the query name as the value for the Record Source. SQL statements are
also valid sources for list boxes on Access forms. Although Access never requires a user to employ SQL,
in many places SQL can be used instead of Access object names.
The query grid contains a very powerful feature for students of SQL with Access experience. You can
use the drag and drop interface of the grid to create a query, the same as you have already done many
times in the past. But you can then click on View/SQL and see that visual interface expressed as a SQL
statement, ready to cut and paste. Similarly a query can be created as a SQL statement and then viewed
as a grid. At any time the query can be run to check the results.
You may be familiar with the Access Query Grid view:
(Click picture for full size view)
We can click on Menu:View | SQL View to see the same request written as a SQL statement:
(Click picture for full size view)
Active Server Pages, Microsoft's technology for server-side building of dynamic web pages, has the
ability to use the ADO component (more on ADO in Chapter 2). The ADO Object allows a
programmer to connect an ASP page to a datastore and then send a SQL command to that datastore
and receive information back. For example, a web page visitor could enter her membership number
into an ASP page and then click Submit. The responding ASP page could use that membership number
to look up her account in the corporate database and return appropriate information in a table, text, or
list box. ASP is not limited to reading data; it can use SQL statements to instruct the data store to add,
change or delete data as per the web visitor's instructions.
For further information regarding Active Server Pages, you can visit
You will find an
introductory tutorial in ASP in Appendix E at the back of this book.
Visual Basic, from Microsoft, offers an easy-to-learn front end for Windows applications. It comes with a
rich, graphical developer's environment, which allows quick prototyping and easy revisions. The
finished product can then be compiled and deployed to any PC running Windows. The chief
disadvantage of Visual Basic is that the features that speed up development also cause the applications
to be slightly slower and less robust when deployed. The disadvantages will be largely eliminated with
the .NET release of Visual Basic. Even though Visual Basic clocks a few percent slower than C++, it is
still much faster than scripting languages used in ASP.
Visual Basic includes a DataGrid, which can be filled by the results of a SQL statement; other data
linked interfaces, like list boxes and text boxes, can also be filled from a datastore using SQL. Changes
made to data on forms can be written to the database, either automatically or through specific SQL
statements. Several third parties offer even more sophisticated controls that produce slick data-linked
interfaces with minimal coding.
See Chapter 2 for code specifics of working with VB and SQL and www.vbextras.com for listings
of third party products.
In addition to using the controls, you can build and use SQL statements in any VB code. For example, a
button on a form might fire an event containing code to send a SQL statement that will change data in
certain records. Likewise, data returning from a SQL statement can be accepted into VB code and then
For further information regarding Visual Basic & Visual Studio visit
Like ASP and VB, C++ can use ADO to connect to a DBMS and pass a SQL statement. The results are
returned and can be used in an array, as variables or in expressions. Code that uses ADO, particularly
the recent versions, is both efficient and flexible.
Besides ADO, C++ Programmers have an additional option of using connection technologies such as
ODBC or OLEDB (covered in Chapter 2) to pass SQL statements to the DBMS. This procedure
involves creating and managing Data Classes. Although more intensive, you can exert a finer level of
control than using ADO.
Other Front Ends
Almost every other front end can process a SQL statement to a DBMS. For example, using Visual Basic
for Applications (VBA) you can have Microsoft Word (including Mail Merge) use SQL to access data.
Likewise, Excel VBA can connect your spreadsheet to a datastore and pass SQL statements to it.
PowerBuilder and Delphi are tools similar to Visual Basic that can create a user interface. That interface
can also communicate with data stores via SQL statements.
Visit http://www.sybase.com/products/powerbuilder/ to learn more about PowerBuilder.
Cold Fusion can use proprietary tags to send SQL statements to a DBMS. DBMS vendors such as
Oracle offer other packages for front-end development.
Both Java and JDBC can also create front ends that will both send and receive the results of SQL
The front end will contain or create a SQL statement and have a way in which to connect to a back end,
as we discussed in the sections above. We noted that virtually every front end can send a SQL
statement. Likewise, virtually every DBMS sold in the last ten years can receive and process SQL
statements. The heavy-duty products designed to support enterprises generally have the term "Server"
in their product title in some way. Lightweight back ends are typically desktop applications, which also
contain code to support receiving SQL Statements. They can all be used as back ends to receive and
process a SQL statement.
The flagship database management system from Microsoft is SQL Server (versions 7 and 2000). The
product is optimized to run on Windows NT (or Windows 2000) and has done well in speed test against
competing products. Microsoft SQL Server is often less expensive to deploy than competing DBMS.
Furthermore, an aggressive training and certification program is increasing the number of professionals
qualified to program and run Microsoft SQL Server.
For product information on Microsoft SQL Server 7 or 2000, visit
Although SQL Server is Microsoft's enterprise scale DBMS product (it falls under the umbrella of their
.Net enterprises servers), there are many other Microsoft products that can use SQL.
Two DBMS products have similar names: Microsoft SQL Server and Sybase SQL
Server are both specific DBMS products. These are two of many products that can
receive SQL statements and thus function as back ends.
Oracle offers one of the world's most widely deployed DBMS, currently named Oracle9i. The Oracle
product has proven itself in many of the largest datastores supporting entire corporations. Oracle has
established itself as a leader for web sites with very high traffic. The explosion of data access requests
from the web has pushed Oracle to further increase speed, capacity and reliability.
To check out Oracle DBMS products see
Sybase currently offers three DBMS products. Adaptive Server Enterprise is optimized for enterprise
computing and web site support. SQL Anywhere Studio provides a suite of tools for rapid development
and deployment of business solutions. Adaptive Server IQ provides improved support for the analysis
of data and ad hoc queries.
Information about Sybase products can be found at
IBM offers the DB2 family of products, which run on the hardware and operating systems of both IBM
and non-IBM machines, including a developers kit for their products on Linux platforms. The DB2
Universal Version 7 includes features that move more operations to memory and thus improve
http://www-4.ibm.com/software/data/db2/ for information on IBM's DB2.
You do not need to buy and maintain a Data Server in order to have a back end. Several desktop
applications can support SQL requests and will run in memory space on your laptop.
Oracle offers an 8i Personal Edition that can be installed on a desktop. This version is fully capable of
storing data and then accepting SQL statements against that data. However, it is not intended to scale to
many simultaneous users. As of early 2001, a free trial download is available from www.oracle.com.
Microsoft also offers products for the desktop. First is Access, a DBMS that is easy to learn and powerful
but was not designed to scale to more than a few users at once. Access has a drag and drop interface
which translates to SQL, and displays both translations on the monitor for most points in the design
screens. Note that Access can function as both a front end with the forms and reports objects and as a
back end with its table and query objects.
Note that we have mentioned Access under both a front and back end. Some of Access's tools such as
forms and reports function as a front end. Other tools such as queries and Tables function as back ends.
For people using Access as an entire desktop database solution the distinction is not important. But the
front and back end tools can be used separately in conjunction with products from other vendors. For
example, an Access front end can connect to an IBM DB2 datastore. Or to switch hands, an ASP page
can connect to Access queries and Tables offered as backends. In the case of these cross-software
connections, SQL is the language for communication.
There are several places within Access where you can use SQL statements instead of using the names
of objects like tables or queries. We discuss these in Chapter 2.
Microsoft also offers the MSDE (Microsoft Data Engine) which is essentially the Microsoft SQL Server
data engine without the rest of the DBMS features (such as Query Analyzer), and without the ability to
scale beyond a few users. You can use MSDE to manage your data and to run SQL statements against
that data, but you do not have the tools for performance monitoring, replication, security and other
features found in SQL Server. MSDE is, at the time of this writing, a free download from
www.microsoft.com. It also ships with editions of Microsoft Office 2000 which include Access
(Microsoft Office 2000 Premium or developer editions).