About 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.
Excerpt. © Reprinted by permission. All rights reserved.
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:
- 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 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:
- For MySQL, an installation process and instructions are included. When you install MySQL under Windows, you will find useful tips on the Web site. The installation process of the example database is also explained there.
- If an error is found in the book, a rectification will be placed on the Web site.
- Reader comments that could be of interest to others will also be placed on the Web site.
- We even will consider making additional chapters available on the Web site in the future.
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.
Excerpt. © Reprinted by permission. All rights reserved.
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:
- 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 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:
- For MySQL, an installation process and instructions are included. When you install MySQL under Windows, you will find useful tips on the Web site. The installation process of the example database is also explained there.
- If an error is found in the book, a rectification will be placed on the Web site.
- Reader comments that could be of interest to others will also be placed on the Web site.
- We even will consider making additional chapters available on the Web site in the future.
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.
![]({"https://images-na.ssl-images-amazon.com/images/I/41QrQ03cxRL._SX258_BO1,204,203,200_.jpg":[260,343],"https://images-na.ssl-images-amazon.com/images/I/41QrQ03cxRL._SX376_BO1,204,203,200_.jpg":[378,499]})
