- Shopping Bag ( 0 items )
Other sellers (Paperback)
-
All (16) from $5.76
-
New (9) from $41.24
-
Used (7) from $5.76
More About This Textbook
Overview
SQL was, is and always will be the database language for relational database systems such as Oracle, DB2, Sybase, Informix and Microsoft SQL Server. Introduction to SQL describes in depth the full capacity of SQL as it is implemented by the commercial databases, without neglecting the most recent changes to the standard, bringing the book up to date and fully compliant with SQL3. Unique in the extent of its coverage, this book takes you from the beginning to the end of SQL, the concepts to the practice, the apprentice to the master. Learn how to:
* Query data
* Update data
* Create tables and views
* Specify primary and foreign keys
* Use indexes
* Secure your data
* Develop stored procedures and triggers
* Develop applications with embedded SQL and ODBC
* Carry out transactions
* Optimize statements
* Deal with object-relational concepts such as subtables, references, sets and user-defined data types. NEW
section devoted to stored procedures and triggers NEW
coverage of object-oriented concepts NEW
information on application development with pre-programmed SQL NEW
chapter on using SQL in combination with ODBC The best way to learn a language is to work with it and the accompanying CD-ROM contains an SQL product called SOLID, chosen because it employs every aspect of SQL covered in the text. Experiment using the exercises in the book.
Fully updated to cover SQL2, this new edition is a complete introduction to SQL and includes a tutorial disk. The disk contains the database example described within the book and a brief version of Quadbase-SQL. Readers will benefit from working with a "real" SQL product and by building their own database with addresses.
Editorial Reviews
Booknews
The introductory guide describes the full capacity of SQL3 as it is implemented by commercial databases. The third edition adds sections on stored procedure and triggers, object-oriented concepts, and application development with pre-programmed SQL, and a chapter on using SQL in combination with ODBC. The CD-ROM contains SOLID server and SQL editor for experimenting with exercises in the book. Annotation c. Book News, Inc., Portland, OR (booknews.com)Product Details
Related Subjects
Meet the Author
Rick F. van der Lans is an independent consultant, author, and lecturer specializing in database technology, SQL, and data warehousing. He is managing director of R20/Consultancy. He has been a member of the Dutch ISO committee responsible for developing the SQL standard. For the past 20 years, he has taught SQL classes to thousands of attendees. His popular books, including Introduction to SQL and The SQL Guide to Oracle, have been translated into various languages and have sold more than 100,000 copies.
Rick is an internationally acclaimed lecturer. Throughout has career, he has lectured in many European countries, South America, USA, and Australia. You can contact Rick via email at sql@r20.nl.
Read an Excerpt
SQL was, is, and will stay for the foreseeable future the database language for relational database servers such as IBM DB2, Microsoft SQL Server, MySQL, Oracle, Progress, Sybase Adaptive Server, and dozens of others. This book contains a complete and detailed description of SQL (Structured Query Language). It should be seen primarily as a textbook in the active sense. After reading this book, you should be familiar with all the statements, the features, and some idiosyncrasies of SQL, and you should be able to use SQL efficiently and effectively.
SQL supports a small but very powerful set of statements for manipulating, managing, and protecting data stored in a database. This power has resulted in its tremendous popularity. In the early 1980s, there were only 10 to 20 SQL database servers, but today this number is at least multiplied by four. Almost every database server supports SQL or a dialect of the language. Currently, SQL products are available for every kind of computer, from a small handheld computer to a large server, and for every operating system, including Microsoft Windows and many UNIX variations. An official international standard for SQL was introduced in 1987. This has developed into what Michael Stonebraker, an authority in the field of databases, once expressed as intergalactic dataspeak.
Topics
This book is completely devoted to SQL. Every aspect of the language is discussed thoroughly and critically. These aspects, among others, include the following:
- Querying data (joins, functions, and subqueries)
- Updating data
- Creating tables and views
- Specifying primary and foreign keys and other integrity constraints
- Using indexes
- Considering data security
- Developing stored procedures and triggers
- Developing programs with embedded SQL and ODBC
- Working with transactions
- Optimizing statements
- Dealing with object relational concepts, such as subtables, references, sets, and user-defined data types
- Using the catalog
Which SQL Dialect?Many SQL products are available on the market today. All these implementations of SQL resemble each other closely, but, unfortunately, differences do exist between them. Some do not support all the SQL statements, and others do not have all the features of a specific SQL statement. In some cases, identical statements can even return different results by different products.
The question then becomes, which SQL dialect is described in this book? To make the book as practical as possible, we describe the SQL statements and features supported by most of the dominant SQL products. This increases the practical value of this book. After reading this book, you can work with any SQL product. In other words, the focus is not so much on DB2, Oracle, or MySQL, and not even on the international standards for SQL; instead, it is on common SQL—SQL as implemented by most products.
For Whom Is This Book Intended?
We recommend this SQL book to those who want to use the full power of SQL effectively and efficiently in practice. This book is, therefore, suitable for the following groups of people:
- Students in higher education, including those in technical colleges, polytechnics, universities, and sixth-form colleges
- Developers who develop or intend to develop applications with the help of an SQL product
- Designers, analysts, and consultants who have to deal, directly or indirectly, with SQL or another relational database language and want to know its features and limitations
- Home students who are interested in SQL in particular or relational databases in general
- Users who have the authority to use SQL to query the database of the company or institute for which they are working
A Practical BookThis book should be seen primarily as a textbook in the active sense, and less as a reference work. To this end, it contains many examples and exercises (with answers). Do not ignore the exercises. Experience shows that you will learn the language more thoroughly and more quickly by practicing often and doing many exercises.
Practicing with MySQL
One of the best-known SQL database servers is a product called MySQL. In this book, we assume that you will use MySQL to do the examples and the exercises. MySQL has been chosen because of its popularity and because its SQL dialect includes extensive functionality and shows much similarity to the international standard for SQL.
We advise you to install MySQL and practice as many exercises as possible with the help of MySQL. Executing SQL statements and studying the results is still the best way to master this powerful language. Later in this book, we describe how to install the product.
What Is on the CD-ROM?
The CD-ROM included in this book contains MySQL, of course. The version included on the CD-ROM has no functional limitations; it has the same functionality as the commercial version.
The version included is Version 5.0.7 for Windows. You can also download this product free of charge for many other platforms, including Linux, Sun Solaris, FreeBSD, MAC OS, HP-UX, IBM AIX, and Novell NetWare from the Web site http://www.mysql.com. You can even choose which version you would like to use.
A useful tool called WinSQL is also included on the CD-ROM, which makes working with MySQL easier. It is at least worth the effort to try these products.
This Book's Web Site
When you leaf through the book, you will find numerous SQL statements. Sometimes these are examples and sometimes they are answers to questions. After you install MySQL, you can run through these statements to see whether they work and see their effect. You could type in all the statements again like a real Spartan, but you can also make life easy for yourself by downloading all the statements from the Internet. A special Web site for this book, http://www.r20.nl, includes all the SQL statements.
The SQL statements have been placed deliberately on this Web site and not on the CD-ROM included in the book because it is easier to change them, if needed. It also makes it possible to add alternative solutions.
You can use the Web site for other aspects as well:
Therefore, keep an eye on this Web site.
Prerequisite Knowledge
Some general knowledge of programming languages and database servers is required.
The History of This Book
It was 1984, and the database world was under the spell of a revolution. SQL had started its triumphal procession. Vendors such as IBM and Oracle had introduced the commercial versions of their SQL database servers, and the marketing machine went at full speed. The market reacted positively to this rise of first-generation SQL database servers. Many organizations decided to buy such a database server and gradually phase out their existing products.
My employer at that time had decided to get involved in this tumult as well. It also wanted to make money with this new database language, and the plan was to start organizing SQL courses. Because of my background knowledge, I was charged with this task. That SQL would become such a success and that my agreement to present the courses would have far-reaching consequences, personally as well as professionally, was never in my mind.
After studying SQL closely, I started to develop the material for the course. After teaching SQL for two years with great pleasure, I got an idea to write a book about SQL. It would have to be a book that would be completely dedicated to this language, with its many possibilities and idiosyncrasies.
After producing gallons of blood, sweat, and tears, I completed the first Dutch edition in 1986, entitled Het SQL Leerboek. Barely before the book was published, I was asked to write an English version. That book was published in 1987, and in that language it was the first book completely devoted to SQL. After that, a German and Italian version appeared. Obviously, there was a need for information about SQL. Everyone wanted to learn about SQL, but there was not much information available.
Because SQL was still young, development was fast. Statements were added, extended, and improved. New implementations became available, new application areas were discovered, and new versions of the SQL standard appeared. Soon a new edition of the book had to be written. And there was more to come. The book you have in your hands right now is already the fourth edition of the English version. And it will not be the last because SQL has gloriously won the revolution in the database world, and there is no competition in sight on the horizon.
The Fourth Edition
This book is a completely revised fourth edition of Introduction to SQL. The previous edition was also seriously revised, so why a completely revised edition now? The explanation is simple: Once again, SQL has changed dramatically. However, not many statements have been added this time; the changes are more in the details. The language has been adjusted at several places, so the way in which certain statements are explained had to change.
And Finally . . .
Writing this book was not a solo project. Many people have contributed to this or previous editions of this book. I would like to use this preface to thank them for their help, contribution, ideas, comments on the contents, mental support, and patience.
I am grateful to the MySQL organization for providing the software. I think this product is invaluable for anyone who wants to learn SQL. The best way to learn a language is still to work with it!
It does not matter how many times a writer reads through his own work; editors remain indispensable. A writer does not read what he has written, but what he thinks he has written. In this respect, writing is like programming. That is why I owe a great deal to the following people for making critical comments and giving very helpful advice: Marc van Cappellen, Ian Cargill, Corine Cools, Richard van Dijk, Rose Endres, Wim Frederiks, Andrea Gray, Ed Jedeloo, Josien van der Laan, Oda van der Lans, Deborah Leendertse, Onno de Maar, Andrea Maurino, Sandor Nieuwenhuijs, Henk Schreij, Dave Slayton, Aad Speksnijder, Nok van Veen, and David van der Waaij. They all have read this manuscript (or parts of it), or the manuscript of a previous edition, a translation of it, or an adjusted version.
I would also like to thank the thousands of students across the world whom I have taught SQL over the past years. Their comments and recommendations have been invaluable in revising this book. In addition, a large number of readers of the previous edition responded to my request to send comments and suggestions. I want to thank them for the trouble they took to put these in writing.
For the first and second editions, Diane Cools did much of the typing and corrected many errors. I am still grateful for that because working with WordStar Version 1 on a PC/XT without a hard disk looked like a luxury then. I would like to thank her again for her work on this new edition. As an editor, she made this book readable to others. For a writer, it is also reassuring to know that there is someone who, especially in difficult times, keeps stimulating and motivating you. Thanks, Diane!
Finally, again I would like to ask readers to send comments, opinions, ideas, and suggestions concerning the contents of the book to sql@r20.nl stating "Introduction to SQL." Many thanks in anticipation of your cooperation.
Rick F. van der Lans Den Haag, The Netherlands, June 2006
© Copyright Pearson Education. All rights reserved.
Table of Contents
About the Author xix
Preface xxi
Part 1 Introduction 1
1 Introduction to SQL 3
2 The Tennis Club Sample Database 31
3 Installing the Software 41
4 SQL in a Nutshell 67
Part 2 Querying and Updating Data 95
5 SELECT Statement: Common Elements 97
6 SELECT Statements, Table Expressions, and Subqueries 155
7 SELECT Statement: The FROM Clause 185
8 SELECT Statement: The WHERE Clause 241
9 SELECT Statement: SELECT Clause and Aggregation Functions 339
10 SELECT Statement: The GROUP BY Clause 377
11 SELECT Statement: The HAVING Clause 421
12 SELECT Statement: The ORDER BY Clause 429
13 Combining Table Expressions 443
14 Updating Tables 469
Part 3 Creating Database Objects 485
15 Creating Tables 487
16 Specifying Integrity Constraints 515
17 Character Sets and Collating Sequences 535
18 Changing and Dropping Tables 553
19 Designing Tables 565
20 Using Indexes 579
21 Views 611
22 Creating Databases 635
23 Users and Data Security 641
24 Creating Sequences 665
25 Creating Schemas 681
Part 4 Programming with SQL 685
26 Introduction to Embedded SQL 687
27 Transactions and Multi-User Usage 725
28 Introduction to ODBC 745
29 Optimization of Statements 783
Part 5 Procedural Database Objects 803
30 Stored Procedures 805
31 Stored Functions 847
32 Triggers 853
Part 6 Object Relational Concepts 871
33 User-Defined Data Types, Functions, and Operators 873
34 Inheritance, References, and Collections 889
35 The Future of SQL 909
Appendix A Syntax of SQL 911
Appendix B Scalar Functions 953
Appendix C Bilbiography 999
Index 1003
Preface
Preface
Introduction
SQL was, is, and will stay for the foreseeable future the database language for relational database servers such as IBM DB2, Microsoft SQL Server, MySQL, Oracle, Progress, Sybase Adaptive Server, and dozens of others. This book contains a complete and detailed description of SQL (Structured Query Language). It should be seen primarily as a textbook in the active sense. After reading this book, you should be familiar with all the statements, the features, and some idiosyncrasies of SQL, and you should be able to use SQL efficiently and effectively.
SQL supports a small but very powerful set of statements for manipulating, managing, and protecting data stored in a database. This power has resulted in its tremendous popularity. In the early 1980s, there were only 10 to 20 SQL database servers, but today this number is at least multiplied by four. Almost every database server supports SQL or a dialect of the language. Currently, SQL products are available for every kind of computer, from a small handheld computer to a large server, and for every operating system, including Microsoft Windows and many UNIX variations. An official international standard for SQL was introduced in 1987. This has developed into what Michael Stonebraker, an authority in the field of databases, once expressed as intergalactic dataspeak.
Topics
This book is completely devoted to SQL. Every aspect of the language is discussed thoroughly and critically. These aspects, among others, include the following:
Which SQL Dialect?
Many SQL products are available on the market today. All these implementations of SQL resemble each other closely, but, unfortunately, differences do exist between them. Some do not support all the SQL statements, and others do not have all the features of a specific SQL statement. In some cases, identical statements can even return different results by different products.
The question then becomes, which SQL dialect is described in this book? To make the book as practical as possible, we describe the SQL statements and features supported by most of the dominant SQL products. This increases the practical value of this book. After reading this book, you can work with any SQL product. In other words, the focus is not so much on DB2, Oracle, or MySQL, and not even on the international standards for SQL; instead, it is on common SQL—SQL as implemented by most products.
For Whom Is This Book Intended?
We recommend this SQL book to those who want to use the full power of SQL effectively and efficiently in practice. This book is, therefore, suitable for the following groups of people:
A Practical Book
This book should be seen primarily as a textbook in the active sense, and less as a reference work. To this end, it contains many examples and exercises (with answers). Do not ignore the exercises. Experience shows that you will learn the language more thoroughly and more quickly by practicing often and doing many exercises.
Practicing with MySQL
One of the best-known SQL database servers is a product called MySQL. In this book, we assume that you will use MySQL to do the examples and the exercises. MySQL has been chosen because of its popularity and because its SQL dialect includes extensive functionality and shows much similarity to the international standard for SQL.
We advise you to install MySQL and practice as many exercises as possible with the help of MySQL. Executing SQL statements and studying the results is still the best way to master this powerful language. Later in this book, we describe how to install the product.
What Is on the CD-ROM?
The CD-ROM included in this book contains MySQL, of course. The version included on the CD-ROM has no functional limitations; it has the same functionality as the commercial version.
The version included is Version 5.0.7 for Windows. You can also download this product free of charge for many other platforms, including Linux, Sun Solaris, FreeBSD, MAC OS, HP-UX, IBM AIX, and Novell NetWare from the Web site http://www.mysql.com. You can even choose which version you would like to use.
A useful tool called WinSQL is also included on the CD-ROM, which makes working with MySQL easier. It is at least worth the effort to try these products.
This Book's Web Site
When you leaf through the book, you will find numerous SQL statements. Sometimes these are examples and sometimes they are answers to questions. After you install MySQL, you can run through these statements to see whether they work and see their effect. You could type in all the statements again like a real Spartan, but you can also make life easy for yourself by downloading all the statements from the Internet. A special Web site for this book, http://www.r20.nl, includes all the SQL statements.
The SQL statements have been placed deliberately on this Web site and not on the CD-ROM included in the book because it is easier to change them, if needed. It also makes it possible to add alternative solutions.
You can use the Web site for other aspects as well:
Therefore, keep an eye on this Web site.
Prerequisite Knowledge
Some general knowledge of programming languages and database servers is required.
The History of This Book
It was 1984, and the database world was under the spell of a revolution. SQL had started its triumphal procession. Vendors such as IBM and Oracle had introduced the commercial versions of their SQL database servers, and the marketing machine went at full speed. The market reacted positively to this rise of first-generation SQL database servers. Many organizations decided to buy such a database server and gradually phase out their existing products.
My employer at that time had decided to get involved in this tumult as well. It also wanted to make money with this new database language, and the plan was to start organizing SQL courses. Because of my background knowledge, I was charged with this task. That SQL would become such a success and that my agreement to present the courses would have far-reaching consequences, personally as well as professionally, was never in my mind.
After studying SQL closely, I started to develop the material for the course. After teaching SQL for two years with great pleasure, I got an idea to write a book about SQL. It would have to be a book that would be completely dedicated to this language, with its many possibilities and idiosyncrasies.
After producing gallons of blood, sweat, and tears, I completed the first Dutch edition in 1986, entitled Het SQL Leerboek. Barely before the book was published, I was asked to write an English version. That book was published in 1987, and in that language it was the first book completely devoted to SQL. After that, a German and Italian version appeared. Obviously, there was a need for information about SQL. Everyone wanted to learn about SQL, but there was not much information available.
Because SQL was still young, development was fast. Statements were added, extended, and improved. New implementations became available, new application areas were discovered, and new versions of the SQL standard appeared. Soon a new edition of the book had to be written. And there was more to come. The book you have in your hands right now is already the fourth edition of the English version. And it will not be the last because SQL has gloriously won the revolution in the database world, and there is no competition in sight on the horizon.
The Fourth Edition
This book is a completely revised fourth edition of Introduction to SQL. The previous edition was also seriously revised, so why a completely revised edition now? The explanation is simple: Once again, SQL has changed dramatically. However, not many statements have been added this time; the changes are more in the details. The language has been adjusted at several places, so the way in which certain statements are explained had to change.
And Finally . . .
Writing this book was not a solo project. Many people have contributed to this or previous editions of this book. I would like to use this preface to thank them for their help, contribution, ideas, comments on the contents, mental support, and patience.
I am grateful to the MySQL organization for providing the software. I think this product is invaluable for anyone who wants to learn SQL. The best way to learn a language is still to work with it!
It does not matter how many times a writer reads through his own work; editors remain indispensable. A writer does not read what he has written, but what he thinks he has written. In this respect, writing is like programming. That is why I owe a great deal to the following people for making critical comments and giving very helpful advice: Marc van Cappellen, Ian Cargill, Corine Cools, Richard van Dijk, Rose Endres, Wim Frederiks, Andrea Gray, Ed Jedeloo, Josien van der Laan, Oda van der Lans, Deborah Leendertse, Onno de Maar, Andrea Maurino, Sandor Nieuwenhuijs, Henk Schreij, Dave Slayton, Aad Speksnijder, Nok van Veen, and David van der Waaij. They all have read this manuscript (or parts of it), or the manuscript of a previous edition, a translation of it, or an adjusted version.
I would also like to thank the thousands of students across the world whom I have taught SQL over the past years. Their comments and recommendations have been invaluable in revising this book. In addition, a large number of readers of the previous edition responded to my request to send comments and suggestions. I want to thank them for the trouble they took to put these in writing.
For the first and second editions, Diane Cools did much of the typing and corrected many errors. I am still grateful for that because working with WordStar Version 1 on a PC/XT without a hard disk looked like a luxury then. I would like to thank her again for her work on this new edition. As an editor, she made this book readable to others. For a writer, it is also reassuring to know that there is someone who, especially in difficult times, keeps stimulating and motivating you. Thanks, Diane!
Finally, again I would like to ask readers to send comments, opinions, ideas, and suggestions concerning the contents of the book to sql@r20.nl stating "Introduction to SQL." Many thanks in anticipation of your cooperation.
Rick F. van der Lans
Den Haag, The Netherlands, June 2006
© Copyright Pearson Education. All rights reserved.