- Shopping Bag ( 0 items )
Ships from: STERLING HEIGHTS, MI
Usually ships in 1-2 business days
Ships from: acton, MA
Usually ships in 1-2 business days
A practical tutorial on the Structured Query Language (SQL). Emphasizes business problem solutions with SQL in the relational database environment. Discuses DB design relationships and guidelines, data input and output, selection, sorting, grouping, and joining. Explains query structuring and security, transaction, and performance issues. Good chapters on problem solution and avoidance of common errors. Treatment follows the ANSI SQL '92 standard. The accompanying CD-ROM includes a Windows 3.1/95/NT version of Sybase SQL Anywhere Runtime, a full-featured SQL database that very nicely and conveniently teaches you SQL on your PC. After you finish with this tutorial, we recommend Joe Celko's SQL for Smarties : Advanced SQL Programming for a more in-depth look at this material.
Indexes speed the retrieval of data. An index on a column can often make the difference between a nearly immediate response to a query and a long wait.
So why not index every column? The most significant reason is that building and maintaining an index takes time and storage space on the database device.
A second reason is that inserting, deleting, or updating data in indexed columns takes a little longer than in unindexed columns, because of the time it takes the system to maintain the index when key values are changed. But this cost is usually outweighed by the extent to which indexes improve retrieval performance.
In general, it's usually appropriate to put indexes on columns you frequently use in retrievals, especially primary key columns and columns used in joins and sorts. Here are some more precise guidelines:
These are some cases in which indexes are not useful:
Indexes are a very complex issue. To use them well, you need to understand how your system's query optimizer works, and what performance issues your application has-topics not covered here. Generally speaking, you should start out by picking what seem to be reasonable indexes, and then look for performance problems during your prototyping, benchmarking, and testing phases. When you have a clear idea of your needs, you can adjust the indexes to meet them.
CREATING TABLES WITH SQL-92 CONSTRAINTS
Many commercial systems have adopted the SQL-92 CREATE TABLE options for PRIMARY KEY, UNIQUE, DEFAULT, CHECK, REFERENCES, and FOREIGN KEY. These elements provide some important data integrity protections.
PRIMARY KEY marks the column (which cannot allow nulls) as the primary key of the table. Every value entered must be unique or else the input is rejected. The handling of this constraint internally varies from system to system, but it is often equivalent to an index. if the primary key includes multiple columns, PRIMARY KEY is defined on the table level.
Before you create a table with defaults and constraints, sketch your requirements. Figure 3-6 shows defaults and constraints you might use for the titles table.
Syntax for these features varies from vendor to vendor, so check your system manuals for specifics. Usually, it looks something like the following syntax summary, with column-level constraints optionally following each column definition. The constraint clauses always include the constraint keyword (DEFAULT, CHECK, PRIMARY KEY, UNIQUE, FOREIGN KEY, REFERENCES) and optionally the word CONSTRAINT and a name for the constraint. (Naming constraints may simplify the process of dropping or changing them. Although most systems generate constraint labels you can...
|Preface to the Second and Third Editions|
|Ch. 1||SQL and Relational Database Management||1|
|Ch. 2||Designing Databases||17|
|Ch. 3||Creating and Filling a Database||47|
|Ch. 4||Selecting Data from the Database||87|
|Ch. 5||Sorting Data and Other Selection Techniques||131|
|Ch. 6||Grouping Data and Reporting From It||163|
|Ch. 7||Joining Tables for Comprehensive Data Analysis||193|
|Ch. 8||Structuring Queries with Subqueries||223|
|Ch. 9||Creating and Using Views||259|
|Ch. 10||Security, Transactions, Performance, and Integrity||285|
|Ch. 11||Solving Business Problems||315|
|Ch. 12||Mistakes and How to Avoid Them||353|
|App. A||Syntax Summary for the SQL Used in This Book||377|
|App. B||Industry SQL Equivalents||379|
|App. D||The Bookbiz Sample Database||407|
These forces are pushing commercial implementations closer together. After all, both commercial interests and the standards committees are defining a SQL that
* Implements an increasingly complete relational model;
* Minimizes incompatibility and implementation dependence; and
* Adds language for new functions carefully, in order to maintain compatibility with early versions of SQL.
This book (and the CD containing a run-time version of SQL Anywhere) can help you teach yourself basic SQL. Then you can apply what you've leaned to other SQL dialects you have access to. Once you understand the fundamentals, you'll find it easy to make adjustments for different versions.
WHO SHOULD USE THIS BOOK
The Practical SQL Handbook is meant for you if you use a relational database system -- whether you're sharing that system with other users or going it alone on a single-user personal computer. It assumes that you're an intelligent amateur -- whether you're an end user in a large company, government office, or nonprofit agency; the owner of a small business; the manager of a small organization; a home computer user working on a personal project; or a student learning about database technology. You may be moving to a relational database system from a PC file manager, making the transition to SQL from a non-SQL-based database management system, or taking up database management for the first time.
We do take for granted that you have a nodding acquaintance with computers and computer tools. Of course, some degree of familiarity with database systems will help.
If you're planning to develop a sophisticated database application, you'll need to embed SQL in a programming language or use it with a "fourth-generation application language" (4GL). But you need never have written a single line of programming code in order to use The Practical SQL Handbook successfully to learn both the basics of SQL and to get a good grasp on a variety of more advanced topics.
Theoreticians are not our intended audience; we assume that the fine points of relational theory and the intricacies of the ISO-ANSI debates are not of primary interest to our readers. On the other hand, we think you should be aware of the major SQL controversies -- at least in their broad outlines -- so that you'll be alert to the tricky areas of SQL use. In short, we think you want to know what really works, or at least the fastest way to find that out.
Accordingly, this book concentrates on teaching you to use SQL interactively -- typing the commands and receiving results directly on a screen, as opposed to embedding them in a programming language. Every commercial implementation of SQL has an interactive interface for use in learning the language initially and writing ad hoc queries. Many provide report writers or 4GLs that can be used in association with SQL to develop applications of moderate complexity.
THE FOCUS OF THIS BOOK
In spite of the messiness involved, this book focuses on the real world of commercial SQLs -- which we also refer to as "industry SQLs" -- rather than on the current ANSI standard version of SQL. We made this choice because the industry SQLs provide a better teaching tool in the areas of notation, target audience, and overall functionality:
* The syntax notation used to document most industry SQLs is reasonably clear and intuitively understandable. The ISO-ANSI publications, on the other hand, use BNF 1Backus Naur Forml notation, which is very precise but difficult to read and understand.
* All the industry SQLs support an interactive interface for the beginning or casual user; the ISO-ANSI standard is largely concerned with the embedded SQL interface for programmers and applications developers.
* Vendors of relational database systems implement the features their customers ask for, including some originally offered only by competitors. Adoption of ANSI SQL features varies from SQL to SQL. By taking the industry standard (rather than the ANSI standard) as the basis for this book, we can focus on features available today.
The ANSI standard is a forbidding document, bristling with clauses, caveats, and footnotes; its BNF notation is best suited for capturing the function of each language element, rather than its exact syntax. If you want to undertake your own investigations, we recommend that you take a guide along: C. J. Date's A Guide to the SQL Standard. Date is one of the major theoreticians of the relational model and one of the most prolific writers on relational matters. His book explains how to read BNF and serves as a useful collection of his opinions on the merits and deficiencies of the standard and of SQL in general.
After ruminating on the ISO-ANSI document for a while, even with the assistance of Date's Guide, you're likely to turn for enlightenment to the closest relational database management system's user's manual. But user's manuals have their own drawbacks and limitations.
While some user's manuals are adequate for learning the basics of SQL, a great many are either overly simplistic or overly obscure. Perhaps more problematic, user's manuals (including several written by the authors of this book) unavoidably focus on the details of syntax and the eccentricities of one particular dialect -- often at the expense of the conceptual picture.
The Practical SQL Handbook is meant to take up where even the best SQL user's manual leaves off. It takes you step by step through the basics of SQL and then introduces you to the issues involved in designing SQL-based database applications. The Practical SQL Handbook covers topics that are usually neglected or given short shrift in user's manuals: database design indexes, nulls, joins, subqueries, views, performance, and data integrity.
HOW TO LEARN SQL WITH THIS BOOK
Let's begin with a few expectations. First of all, we expect you to read large parts of this book while sitting at your terminal. Second, we expect you to study and reproduce the examples. Third, we expect you to practice, test, and explore. There's no substitute for interactive practice, even if your ultimate intention may be to program a highly sophisticated application.
From there, a lot is up to you. Learning styles differ: some people absorb new material through carefully considering prose explanations; others take in concepts just by looking at pictures. We expect this book to be equally helpful whether you choose to read every word of it or to briefly scan the text and rely mainly on the examples. Words and phrases in boldface indicate the first mention of an entry that is defined in the glossary.
Some of the hundreds of examples in The Practical SQL Handbook are deliberately simple in order to illustrate basic concepts. Others are more difficult. At the most complex end of the range you'll find SQL statements that may serve as models for your own applications. Wherever their complexity warrants it, examples are dissected and explained in detail.
The fact that every version of SQL is different from every other version, at least in some details, means that no general-purpose book on SQL can guarantee all of its examples to work exactly as presented. The good news is that our decision to base The Practical SQL Handbook on the industry SQLs rather than on the official SQL standard will make the "translation" process more straightforward. With the reference materials supplied with your SQL product, the lists of cross-system analogies in keywords and operators in Appendix B. and a little detective work, you'll be able to test most of our examples on whatever SQL or SQL-like system you're using.
All the examples in The Practical SQL Handbook are guaranteed to work in the language with which they were developed -- Sybase SQL Server Transact-SQL -- and the language of the relational database system shipped with the book: Sybase SQL Anywhere. We don't claim to represent in depth any implementation of SQL, or to cover any other dialect's strengths or limitations. We do cover fin broad strokes) the major areas of commonality among industry SQLs.
With very few exceptions, all examples are derived from our sample database, called the bookbiz database. Chapters 2 and 3 explain the sample database. You don't have to use the same sample data we do, but that's the best approach -- it will help you see more quickly whether your results are correct.
The bookbiz database is very small. Its size and simplicity will allow you to become comfortable with the copy provided on the SQL Anywhere CD as you work through the examples in the book, and later re-create it on your own system. On the other hand, it is complex enough to illustrate important points about the significant relational issues.
The Structure of This Book
Each chapter of The Practical SQL Handbook presents one SQL feature or cluster of related features. The discussion follows a pattern, beginning with the following:
* Definition -- what does it do
* Minimal syntax -- a simple "vanilla" version of the command (one that is stripped of most optional clauses and extensions, which tend to vary from SQL to SQL)
* A simple example
Following this initial description of syntax and usage, we elaborate on the role of this feature in the relational model, and its possible use in data base applications.
If necessary, we then provide additional syntax -- optional clauses offering additional functions or fine-tuning capabilities -- and more-complex examples. In this way, each new feature has a complete description and example.
Where possible, each example builds on previous ones. However, the examples in each chapter stand alone, so that you can complete a chapter at one sitting. Learning SQL is like learning any other foreign language: the learning process begins with imitation, proceeds through comprehension, and should end with fluency. At each of these stages, the key to success is practice.
Interactive practice with SQL will be more pleasant and efficient if you follow some simple time-saving procedures:
Save your practice SQL statements in operating system files. (Your system should provide a method for doing this.) If you are not certain that a SQL query (data retrieval operation) is producing the desired results, save the results for off-line examination. Keep some log or record of what worked and what didn't, and save the error message, too, if you can.
Save your successes. Keep elegant SQL solutions on file: you may want to imitate them for other purposes later on.
Structure an application's queries into separate modules or subroutines. Like modern structured programming, good SQL applications should be made of many subroutines and be open to constant reapplication and recycling.
Make yourself a crib sheet. Even if your system has provided you with a quick reference card, make your own list and quick sketches of favorite commands. This will reinforce learning. You will soon find that you use some SQL commands much more often than others.
Improve on our solutions. In our experience, the more you work at expressing yourself in SQL, the simpler and more elegant your statements become.
The point of learning SQL and practicing it interactively is to be able to express any desired operation properly, so that you get the results you want. In order to achieve this level of proficiency, you'll have to explore and test-drive your relational system's SQL until you're sure you can trust it. You don't want to find out when you're running at fifty transactions per second that your results have been invalidated by a SQL error (or a logic error).
SQL requires practice because it's a foreign language, and the entity that speaks it best -- your system's parser -- isn't human. Although SQL has a limited number of keywords and operators and is relatively easy to read, there are areas that are tricky. Like many other high-level languages for computers, SQL has a definite grammar and structure and a fair number of specific syntax rules. SQL may be English-like, but it's still far from being a natural language. Sooner or later, you'll come across some operations that SQL simply cannot perform.
The Practical SQL Handbook will help you understand SQL's strengths and limits. It will assist in heading off potential disasters caused by poor database design or unwieldy and unmaintainable SQL-based applications, and make the learning of SQL as quick and painless as possible.
AN OVERVIEW OF THE BOOK
Chapter 1: SQL and Relational Database Management. This chapter briefly defines and informally illustrates the relational model, and presents the chief features of the SQL language as the voice of the relational model.
Chapter 2: Designing Databases. Database design is often an intimidating prospect. This chapter surveys the most helpful techniques, using the sample database to illustrate the analysis of data and the decision making involved in database design. It discusses primary and foreign keys, entity-relationship modeling, and the normalization rules, which can act as guidelines for good database design.
Chapter 3: Creating and Filling a Database. The design proposed in the previous chapter becomes a reality here, as the SQL commands for creating databases, tables, and indexes, and for adding, changing, and deleting data, are examined in detail. An explanation of our SQL syntax conventions accompanies this initiation into hands-on use of the SQL language.
Chapter 4: Selecting Data from the Database. This chapter, with which you can start using the SQL Anywhere CD to run examples, presents the basic elements of the SELECT command. It explains how to retrieve particular rows and columns from a single table, and covers computed values; comparison operators, and logical operators.
Chapter 5: Sorting Data and Other Selection Techniques. Other clauses in the SELECT statement allow you to sort your data, eliminate duplicates from the results, or use aggregate functions to report averages, sums, or counts.
Chapter 6: Grouping Data and Reporting from It. The SELECT statement also includes language for grouping data and reporting from it using the aggregate functions described in the previous chapter. This chapter covers these SQL features and also returns to the controversial topic of how a relational database management system should handle null values (missing information).
Chapter 7: Joining Tables for Comprehensive Data Analysis. The join operation is one of the hallmarks of the relational model. This chapter explains how to use the join operation to retrieve data from one or more tables. A complex variant on simple selection, joins confront users with significant issues in analyzing and verifying the results of data retrieval.
Chapter 8: Structuring Queries with Subqueries. This chapter deals with the proper use and application of nested queries, or subqueries. The corre fated subquery (notorious for causing confusion) is explained, with many examples.
Chapter 9: Creating and Using Views. This chapter discusses views (virtual tables) and their use in providing customized access to data. Views can also provide data security, since you can grant other users access to specified portions of a table for specified operations. The thorny issue of updating views is described in some detail.
Chapter 10: Security, lYansactions, Performance, and integrity. This chapter is devoted to considerations encountered in real-world database managemeet. It explains the SQL commands for specifying permissions, returns to the subject of indexing to discuss its use in boosting performance, and covers mechanisms for transaction management. It also describes extensions to the SQL language that provide database consistency and referential integrity. Some of the features discussed here are specific to Sybase's implementation of SQL.
Chapter 11: Solving Business Problems. Here's where you can practice the skills you reamed in earlier chapters, with SQL code samples based on questions and answers found on the Internet, reproduced in terms of the bookbiz database. Here you'll find examples of real-world problems, including formatting results, finding data, working with multitable queries, using the GROUP BY clause, and creating sequential numbers. Chapter 11 is a code cookbook, full of recipes you can use (with your own modifications} on the job.
Chapter 12: Mistakes and Now to Avoid Them. This chapter also contains code recipes taken from the Internet and translated into bookbiz terms, but with a different flavor: they aren't solutions but examples of common errors. Look here for mistakes with GROUP BY, HAVING and WHERE interactions, DISTINCT, as well as for fundamental misunderstandings of what. SQL can do. Used wisely, this chapter can help you avoid some classic wrong code.
Appendix A: Syntax Summary for the SQL Used in This Book
Appendix B: Industry SQL Equivalents.
Appendix C: Glossary.
Appendix D: The bookbiz Sample Database. (This appendix includes a chart of tables with data, data structure diagram, and CREATE and INSERT statements.)
Appendix E: Bibliography
Posted October 23, 2000
This was one of the best written INTRO books I have seen. I say INTRO, because it does not cover everything I wanted, but I don't mind because I also liked the fact that it wasn't 600+ pages. SQL is too dry to spend that much time reading about it. The comment the last reader had about the readonly software is true, but there are ways around that. They cannot give away a full RDBMS software & book for $40. Just download an evaluation of SQL Server 7 and the CD the book comes with has a script to create the database. Just copy/paste the script into Query Analyzer and you're done(you may have to troubleshoot a little). Or you can enter it by hand which I found to be much more useful for me to learn SQL. Anyway, GOOD INTRO BOOK. BUY IT.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.
Posted March 23, 2000
Not a bad text if you're just being introduced to SQL *and* you have access to a SQL DBMS. The included software -- SQL Server Anywhere -- is a read only version. This means that you cannot create, add, or modify any data! Consequently, the chapter about Views is useless if you use the included software. The chapter about creating tables and entering data is also useless. If all you want to do is practice really simple SQL queries, then this book is for you. If not, look elsewhere.Was this review helpful? Yes NoThank you for your feedback. Report this reviewThank you, this review has been flagged.