SQL: A Beginner's Guide

ISBN: 0072130962

Here are a couple of common problems that readers have encountered with SQL: A Beginner’s Guide:

  • The server HenryS2000 does not reside on the Internet and is not available for readers to connect to. You should set up your own database for use with the book, and replace the name HenryS2000 with the name of the computer where your database resides.
  • Code samples available from the McGraw-Hill web site are zipped to save space and reduce transmission time. You need to download a shareware program called WinZip to unzip them. (Go to to download the evaluation version, which will get you through your work with the book.)
  • The zip file that contains the code is a container for the zip files for the individual chapters. You need to unzip the file you downloaded from McGraw-Hill, and then you need to unzip the file for the chapter you are working on to get the files that software like Visual Basic can open.
  • You need to use Visual Basic to open the Visual Basic projects. Other software will not work.
  • Microsoft does not provide Enterprise Manager and Query Analyzer any longer with MSDE. You need to work around this problem by acquiring free tools to replace them.
  • MSDE is no longer available from Write Environment, Inc., ceased operations on December 31, 2001. Alas, the site is no longer there.
  • MSDE has changed considerably since the book was written, and may not be the best choice for you to use in working with this book at this time.

OK, so that’s the quick tour of several issues associated with using SQL: A Beginner’s Guide at this point in its history. What follows are some suggestions about how to work around the issues. Like it or not, the software industry has changed faster than the book has.

Your first issue in working with the book is acquiring a database to use with the book. Attached to this message is one solution, an Excel file that can be used as a database. To use this option, you need to take these steps:

  1. Go to your Windows Control Panel.
  2. Open the Data Sources (ODBC) icon. The icon may have a slightly different name, and you Windows XP users will need to open the Administrative Tools icon before you even see it.
  3. Follow the directions in the book to set up an ODBC Datasource using the Microsoft Excel driver. Some of you may be forced to set up a User datasource, because some versions of Windows won’t let you set up a System datasource. (Keep remembering that Windows has moved on in the last couple of years.)
  4. Reference the datasource you set up in your connection string, and you will be able to work with this Excel file as a database. YOU WILL NOT BE ABLE TO PERFORM EACH AND EVERY SQL OPERATION ON THIS FILE, HOWEVER. Excel is not that SQL smart. So when it complains back at you, bear in mind that you’ve gone as far as you can go.

An Excel file is not a hot shot database, obviously, so you will probably want to opt for another solution. Here are the other options:

  • Go to the Oracle Technology Network ( join, and download a copy of Oracle. You can use the personal (Oracle9iLite) version if you want. Be prepared, however. Oracle is a complex system to set up. It’s setup operation can fail inexplicably. And removing it from your system requires that you follow the instructions available at asktom.oracle.com, and even then it is not all gone from your registry. But you get a free copy of an industrial strength database that you can use. It comes with a lousy query analyzer called SQL*Plus, so you will want to find a replacement. DDL statements in Oracle are unique to the vendor when it comes to creating tables and similar operations. So you will need to consult the books online for Oracle. There are lots of them, and the fastest way to find what you want is to use the Master Index.
  • Go to Microsoft and download the updated (service pack 3) 120 day trial version of SQL Server ( It comes with all the tools, but it times out eventually. You can work for four months before it gives out. You can always shift to MSDE at that time if you need to.
  • Go to Microsoft and download the current version of MSDE ( You need to download the file Sql2kdesksp3.exe. This file will install MSDE from scratch if you do not have a file to update. You need to use the service pack 3 version to avoid the SQL Slammer worm, which preys on all versions of SQL Server. Keep in mind that no tools come with this software package, so you will need to get a Query Analyzer replacement. Installation is tricky. To install MSDE, run the file you download. It will unpack a bunch of files into a directory that you provide. Run setup using this command line: setup sapwd=”password” securitymode=”sql”. If you don’t, you may find yourself using Windows security only with no way to change the situation except to uninstall and reinstall, or the get a copy of Enterprise
  • Go to Borland and download the trial version of InterBase ( This product does come with the required tools. The product comes in a desktop edition. You will need to review the documentation to find out the details of how to use it.
  • Go to MySQL and download the MySQL database for your platform. This is Open Source software, and I really don’t have much experience with it. But it’s freely available and free to use.
  • Get a copy of Microsoft Access, import the Excel file into tables, and work with the Access .mdb file to do the exercises. This option costs money, but not a lot. You will need to get a query tool to practice writing queries, because the tools in Access just aren’t like the tools that you use with any other database.

Also attached to this message is the SQL script for creating the Pubs database. Once you have your database up and running, you can run the script using a query tool to build the database on your database server.

OK, so you have some database options. Tools are another problem. You may not like the ones available with your database (as in the case of my feelings about Oracle’s SQL*Plus), or your database may not provide them (as with MSDE, and to a certain extent with Access).

If you need a query tool, the a variety of them in all price ranges from free to commercial are available at:

One question is how you can get the Pubs data stored in the Excel file attached to this message into a SQL database. One method is to practice writing INSERT statements, and insert the data into the database. If you have Data Transformation Services available, you can use DTS to copy the data from the spreadsheet into the Pubs tables. You can also use Excel to save the data to comma-separated ASCII files, and you can use the following command as a template to load the data into the tables:

BULK INSERT TableNameInPubs

FROM 'c:\CSVFileName'

WITH

(

FIELDTERMINATOR = ',',

ROWTERMINATOR = '\n'

)

You may need to experiment with the row terminator, since I have not verified this load in advance.

Or you can use the Excel files as a ODBC data source, and use a freeware query tool to access the database from the Excel file itself, as described earlier in this message.

As I noted earlier, much has changed since we originally did the book. Software has a faster change rate than book publishing. Viruses appear, web sites change locations of information, and database development brings new features to us. I hope this message helps out. And I hope you enjoy working with the databases.

Forrest Houlette