Introduction to SQL Mastering the Relational Database Language

by van der Lans, Rick F.

• ISBN13: 9780321305961
• ISBN10: 0321305965
• Edition: 4th
• Format: Paperback
• Copyright: 2006-09-26
• Publisher: Addison-Wesley Professional

Summary

The Classic SQL Tutorial: Fully Updated for Today#x19;s Standards and Today#x19;s Top Databases For twenty years, van der Lans#x19; Introduction to SQLhas been the definitive SQL tutorial for database professionals everywhere, regardless of experience or platform. Now van der Lans has systematically updated this classic guide to reflect the latest SQL standards and the newest versions of today#x19;s leading RDBMSs: Oracle, Microsoft SQL Server, DB2, and MySQL. #xA0; Using case study examples and hands-on exercises, van der Lans illuminates every key SQL concept, technique, and statement. Drawing on decades of experience as an SQL standards team member and enterprise consultant, he reveals exactly why SQL works as it does#x13;and how to get the most out of it. You#x19;ll gain powerful insight into everything from basic queries to stored procedures, transactions to data security. Whether you#x19;re a programmer or DBA, a student or veteran, this book will take you from #x1C;apprentice#x1D; to true SQL master. #xA0; Writing queries and updating data: all you need to know about SELECT #xA0; Working with joins, functions, and subqueries #xA0; Creating database objects: tables, indexes, views, and more #xA0; Specifying keys and other integrity constraints #xA0; Using indexes to improve efficiency #xA0; Enforcing security via passwords and privileges #xA0; Building stored procedures and triggers #xA0; Developing with embedded SQL and ODBC #xA0; Working with transactions, including rollbacks, savepoints, isolation levels, and more #xA0; Optimizing performance by reformulating SQL statements #xA0; Using object-relational features: subtables, references, sets, and user-defined data types #xA0; Reference section: SQL statement definitions and SQL function lists

Author Biography

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 [e-mail].

Table of Contents

About the Author	xix
Preface	xxi
I. Introduction	1	(94)
Introduction to SQL	3	(28)
Introduction	3	(1)
Database, Database Server, and Database Language	3	(2)
The Relational Model	5	(4)
Table, Column, and Row	6	(1)
Constraints	7	(1)
Primary Key	7	(1)
Candidate Key	8	(1)
Alternate Key	8	(1)
Foreign Key	8	(1)
What Is SQL?	9	(4)
Several Categories of SQL Applications	13	(4)
The History of SQL	17	(1)
From Monolithic via Client/Server to the Internet	18	(3)
Transaction Databases and Data Warehouses	21	(1)
Standardization of SQL	22	(4)
The Market of SQL Database Servers	26	(2)
Which SQL Dialect?	28	(1)
The Structure of the Book	28	(3)
The Tennis Club Sample Database	31	(10)
Introduction	31	(1)
Description of the Tennis Club	31	(3)
The Contents of the Tables	34	(3)
Integrity Constraints	37	(4)
Installing the Software	41	(26)
Introduction	41	(1)
Installation of MySQL	41	(10)
Installation of ODBC	51	(4)
Installation of WinSQL	55	(9)
Downloading SQL Statements from the Web Site	64	(1)
Ready?	65	(2)
SQL in a Nutshell	67	(28)
Introduction	67	(1)
Logging On to the MySQL Database Server	67	(1)
Creating New SQL Users	68	(3)
Creating Databases	71	(1)
Selecting the Current Database	71	(1)
Creating Tables	72	(2)
The NULL Value	74	(1)
Populating Tables with Data	75	(1)
Querying Tables	76	(4)
Updating and Deleting Rows	80	(2)
Optimizing Query Processing with Indexes	82	(1)
Views	83	(1)
Users and Data Security	84	(1)
Deleting Database Objects	85	(1)
System Parameters	86	(1)
Grouping of SQL Statements	87	(1)
The Catalog Tables	88	(5)
Definitions of SQL Statements	93	(2)
II. Querying and Updating Data	95	(390)
Select Statement: Common Elements	97	(58)
Introduction	97	(1)
Literals and Their Data Types	97	(8)
The Integer Literal	99	(1)
The Decimal Literal	100	(1)
Float, Real, and Double Literals	100	(1)
The Alphanumeric Literal	101	(1)
The Date Literal	102	(1)
The Time Literal	102	(1)
The Timestamp Literal	103	(1)
The Boolean Literal	103	(1)
The Hexadecimal Literal	104	(1)
Expressions	105	(3)
Assigning Names to Result Columns	108	(3)
The Column Specification	111	(1)
The User Variable and the Set Statement	112	(2)
The System Variable	114	(1)
The Case Expression	115	(6)
The Scalar Expression Between Brackets	121	(1)
The Scalar Function	122	(5)
Casting of Expressions	127	(3)
The Null Value as an Expression	130	(1)
The Compound Scalar Expression	131	(14)
The Compound Numeric Expression	132	(3)
The Compound Alphanumeric Expression	135	(2)
The Compound Date Expression	137	(4)
The Compound Time Expression	141	(2)
The Compound Timestamp Expression	143	(2)
The Aggregation Function and the Scalar Subquery	145	(1)
The Row Expression	146	(2)
The Table Expression	148	(1)
Answers	149	(6)
Select Statements, Table Expressions, and Subqueries	155	(30)
Introduction	155	(1)
The Definition of the Select Statement	155	(5)
Processing the Clauses in a Select Block	160	(7)
Example 1	160	(4)
Example 2	164	(2)
Example 3	166	(1)
Possible Forms of a Table Expression	167	(4)
What Is a Select Statement?	171	(1)
What Is a Subquery?	171	(8)
Answers	179	(6)
Select Statement: The From Clause	185	(56)
Introduction	185	(1)
Table Specifications in the From Clause	185	(3)
Again, the Column Specification	188	(1)
Multiple Table Specifications in the From Clause	189	(5)
Pseudonyms for Table Names	194	(1)
Various Examples of Joins	195	(4)
Mandatory Use of Pseudonyms	199	(3)
Explicit Joins in the From Clause	202	(4)
Joins with Using	206	(1)
Outer Joins	206	(10)
The Left Outer Join	207	(4)
The Right Outer Join	211	(1)
The Full Outer Join	212	(4)
Additional Conditions in the Join Condition	216	(3)
The Cross Join	219	(1)
The Union Join and the Natural Join	220	(1)
Equi Joins and Theta Joins	221	(1)
The From Clause with Table Expressions	222	(9)
Answers	231	(10)
Select Statement: The Where Clause	241	(98)
Introduction	241	(1)
Conditions Using Comparison Operators	242	(8)
Comparison Operators with Subqueries	250	(6)
Comparison Operators with Correlated Subqueries	256	(3)
Conditions Coupled with And, Or, and Not	259	(4)
The IN Operator with Expression List	263	(6)
The In Operator with Subquery	269	(12)
The Between Operator	281	(3)
The Like Operator	284	(3)
The Is Null Operator	287	(3)
The Exists Operator	290	(3)
The All and ANY Operators	293	(10)
Scope of Columns in Subqueries	303	(4)
More Examples with Correlated Subqueries	307	(7)
Conditions with Negation	314	(4)
Future Conditions	318	(3)
Answers	321	(18)
Select Statement: Select Clause and Aggregation Functions	339	(38)
Introduction	339	(1)
Selecting All Columns (*)	340	(1)
Expressions in the Select Clause	341	(1)
Removing Duplicate Rows with Distinct	342	(4)
When Are Two Rows Equal?	346	(3)
An Introduction to Aggregation Functions	349	(2)
The Count Function	351	(5)
The Max and Min Functions	356	(7)
The Sum and AVG Functions	363	(5)
The Variance and Stddev Functions	368	(2)
Answers	370	(7)
Select Statement: The Group By Clause	377	(44)
Introduction	377	(1)
Grouping on One Column	378	(5)
Grouping on Two or More Columns	383	(3)
Grouping on Expressions	386	(2)
Grouping of Null Values	388	(1)
General Rules for the Group By Clause	389	(3)
Complex Examples with Group By	392	(7)
Grouping with With Rollup	399	(2)
Grouping with With Cube	401	(2)
Grouping Sets	403	(5)
Grouping with Rollup and Cube	408	(5)
Combining Grouping Sets	413	(1)
Answers	414	(7)
Select Statement: The Having Clause	421	(8)
Introduction	421	(1)
Examples of the Having Clause	422	(3)
General Rule for the Having Clause	425	(1)
Answers	426	(3)
Select Statement: The Order By Clause	429	(14)
Introduction	429	(1)
Sorting on Column Names	429	(3)
Sorting on Expressions	432	(2)
Sorting with Sequence Numbers and Column Headings	434	(2)
Sorting in Ascending and Descending Order	436	(3)
Sorting of Null Values	439	(2)
Answers	441	(2)
Combining Table Expressions	443	(26)
Introduction	443	(1)
Combining with Union	444	(3)
Rules for Using Union	447	(4)
Combining with Intersect	451	(4)
Combining with Except	455	(4)
Keeping Duplicate Rows	459	(2)
Set Operators and the Null Value	461	(1)
Combining Multiple Set Operators	461	(2)
Set Operators and the Theory	463	(2)
Answers	465	(4)
Updating Tables	469	(16)
Introduction	469	(1)
Inserting New Rows	469	(4)
Populating a Table with Rows from Another Table	473	(2)
Updating Values in Rows	475	(5)
Deleting Rows from a Table	480	(1)
Answers	481	(4)
III. Creating Database Objects	485	(200)
Creating Tables	487	(28)
Introduction	487	(1)
Creating New Tables	487	(3)
Data Types of Columns	490	(8)
The Integer Data Types	492	(1)
The Decimal Data Types	492	(1)
The Float Data Types	493	(2)
The Alphanumeric Data Types	495	(1)
The Temporal Data Types	496	(1)
The Boolean Data Type	496	(1)
The Blob Data Types	497	(1)
Creating Temporary Tables	498	(2)
Copying Tables	500	(4)
Naming Tables and Columns	504	(1)
Column Options: Default and Comment	505	(3)
Derived Columns	508	(1)
Tables and the Catalog	509	(4)
Answers	513	(2)
Specifying Integrity Contraints	515	(20)
Introduction	515	(2)
Primary Keys	517	(2)
Alternate Keys	519	(2)
Foreign Keys	521	(4)
The Referencing Action	525	(3)
Check Integrity Constraints	528	(3)
Naming Integrity Constraints	531	(1)
Deleting Integrity Constraints	532	(1)
Integrity Constraints and the Catalog	532	(1)
Answers	532	(3)
Character Sets and Collating Sequences	535	(18)
Introduction	535	(1)
Available Character Sets and Collating Sequences	536	(3)
Assigning Character Sets to Columns	539	(2)
Assigning Collating Sequences to Columns	541	(2)
Expressions with Character Sets and Collating Sequences	543	(3)
Sorting and Grouping with Collating Sequences	546	(2)
The Coercibility of Expressions	548	(2)
Related System Variables	550	(2)
Answers	552	(1)
Changing and Dropping Tables	553	(12)
Introduction	553	(1)
Deleting Entire Tables	553	(1)
Renaming Tables	554	(1)
Changing the Table Structure	555	(1)
Changing Columns	556	(4)
Changing Integrity Constraints	560	(3)
Answers	563	(2)
Designing Tables	565	(14)
Introduction	565	(1)
Which Tables and Columns?	566	(5)
Guideline 1: Define a Primary Key for Each Table	566	(1)
Guideline 2: Each Determinant in a Table Must Be a Candidate Key of That Table	566	(1)
Guideline 3: Do Not Use Repeating Groups in a Table	567	(3)
Guideline 4: Do Not Concatenate Columns	570	(1)
Adding Redundant Data	571	(3)
Guideline 5: Add Redundant Data When the Processing Time of Select Statements Is Not Acceptable	573	(1)
Choosing a Data Type for a Column	574	(2)
Guideline 6: Use the Same Data Types for Columns That Will Be Compared with One Another	574	(1)
Guideline 7: Assign a Column a Numeric Data Type Only if It Will Be Used in Calculations	575	(1)
Guideline 8: Do Not Skimp on the Length of Columns	575	(1)
Guideline 9: Do Not Use the Data Type Varchar for All Alphanumeric Columns	575	(1)
When Should You Use Not Null?	576	(1)
Guideline 10: Use Not Null When a Column Must Contain a Value for Every Row	576	(1)
Answers	576	(3)
Using Indexes	579	(32)
Introduction	579	(1)
Rows, Tables, and Files	580	(1)
How Does an Index Work?	581	(4)
Processing a Select Statement: The Steps	585	(5)
Creating Indexes	590	(3)
Dropping Indexes	593	(1)
Indexes and Primary Keys	594	(1)
The Big Players_XXL Table	595	(2)
Choosing Columns for Indexes	597	(5)
A Unique Index on Candidate Keys	597	(1)
An Index on Foreign Keys	597	(1)
An Index on Columns Included in Selection Criteria	597	(3)
An Index on a Combination of Columns	600	(1)
An Index on Columns Used for Sorting	601	(1)
Special Index Forms	602	(4)
The Multitable Index	602	(1)
The Virtual Column Index	602	(1)
The Selective Index	603	(1)
The Hash Index	604	(2)
The Bitmap Index	606	(1)
Indexes and the Catalog	606	(3)
Answers	609	(2)
Views	611	(24)
Introduction	611	(1)
Creating Views	611	(5)
The Column Names of Views	616	(1)
Updating Views: With Check Option	617	(2)
Deleting Views	619	(1)
Views and the Catalog	619	(1)
Restrictions on Querying Views	620	(2)
Restrictions on Updating Views	622	(1)
Processing View Statements	623	(3)
Application Areas for Views	626	(6)
Simplification of Routine Statements	626	(2)
Reorganizing Tables	628	(2)
Stepwise Development of Select Statements	630	(1)
Specifying Integrity Constraints	631	(1)
Data Security	632	(1)
Answers	632	(3)
Creating Databases	635	(6)
Introduction	635	(1)
Databases and the Catalog	635	(2)
Creating Databases	637	(1)
Updating Databases	638	(1)
Dropping Databases	639	(2)
Users and Data Security	641	(24)
Introduction	641	(1)
Adding and Removing Users	642	(2)
Granting Table and Column Privileges	644	(3)
Granting Database Privileges	647	(3)
Granting User Privileges	650	(2)
Passing on Privileges: With Grant Option	652	(1)
Working with Roles	653	(2)
Recording Privileges in the Catalog	655	(3)
Revoking Privileges	658	(4)
Security of and Through Views	662	(1)
Answers	663	(2)
Creating Sequences	665	(16)
Introduction	665	(1)
Why Do We Need Sequences?	665	(3)
Options of the Sequences	668	(6)
Retrieving the Last Generated Number	674	(1)
Altering and Deleting Sequences	675	(2)
Privileges for Sequences	677	(1)
Answers	678	(3)
Creating Schemas	681	(4)
What Is a Schema?	681	(1)
Creating a Schema	682	(1)
Removing a Schema	683	(1)
Schema Versus SQL User	684	(1)
IV. Programming with SQL	685	(118)
Introduction to Embedded SQL	687	(38)
Introduction	687	(1)
The Pseudo Programming Language	687	(1)
DDL and DCL Statements and Embedded SQL	688	(1)
Processing Programs	689	(3)
Using Host Variables in SQL Statements	692	(2)
The SQLCODE Host Variable	694	(1)
Executable Versus Nonexecutable SQL Statements	695	(1)
The Whenever Statement	696	(4)
Logging On to SQL	700	(1)
Select Statements Returning One Row	701	(4)
Null Values and the Null Indicator	705	(1)
Cursors for Querying Multiple Rows	706	(6)
The Direction for Browsing Through a Cursor	712	(1)
Processing Cursors	713	(2)
The For Clause	715	(2)
Deleting Rows via Cursors	717	(2)
Dynamic SQL	719	(3)
Example of a C Program	722	(3)
Transactions and Multi-User Usage	725	(20)
Introduction	725	(1)
What Is a Transaction?	725	(5)
Starting Transactions	730	(1)
Embedded SQL and Transactions	731	(2)
Savepoints	733	(1)
Problems with Multi-User Usage	734	(4)
Dirty Read or Uncommitted Read	735	(1)
Nonrepeatable Read or Nonreproducible Read	735	(1)
Phantom Read	736	(1)
Lost Update	737	(1)
Locking	738	(1)
Deadlocks	739	(1)
The Granularity of Locked Data	739	(1)
The Lock Table Statement	740	(1)
The Isolation Level	741	(2)
Answers	743	(2)
Introduction to ODBC	745	(38)
Introduction	745	(1)
The History of ODBC	745	(1)
How Does ODBC Work?	746	(3)
A Simple Example of ODBC	749	(4)
Return Codes	753	(1)
Retrieving Data About SQL	753	(1)
DDL Statements and ODBC	754	(2)
DML Statements and ODBC	756	(1)
Using Host Variables in SQL Statements	757	(4)
Settings for a Statement Handle	761	(1)
Select Statements	762	(9)
Retrieving Data Value by Value	762	(2)
Retrieving Data Row by Row	764	(5)
Retrieving Data in Groups of Rows	769	(2)
Asynchronous Processing of SQL Statements	771	(5)
The For Clause	776	(2)
Accessing Catalog Tables with ODBC	778	(1)
Levels and Support	779	(1)
The Competitors of ODBC	780	(3)
Optimization of Statements	783	(20)
Introduction	783	(1)
Avoid the OR Operator	784	(2)
Avoid Unnecessary Use of the Union Operator	786	(1)
Avoid the Not Operator	787	(1)
Isolate Columns in Conditions	788	(1)
Use the Between Operator	788	(1)
Avoid Particular Forms of the Like Operator	789	(1)
Add Redundant Conditions to Joins	789	(1)
Avoid the Having Clause	790	(1)
Make the Select Clause as Small as Possible	791	(1)
Avoid Distinct	791	(1)
Use the All Option with Set Operators	792	(1)
Prefer Outer Joins to Union Operators	792	(1)
Avoid Data Type Conversions	793	(1)
The Largest Table Last	794	(1)
Avoid the Any and All Operators	794	(4)
The Future of the Optimizer	798	(1)
Answers	799	(4)
V. Procedural Database Objects	803	(68)
Stored Procedures	805	(42)
Introduction	805	(1)
An Example of a Stored Procedure	806	(3)
The Parameters of a Stored Procedure	809	(1)
The Body of a Stored Procedure	810	(2)
Local Variables	812	(3)
The Set Statement	815	(1)
Flow-Control Statements	816	(8)
Calling Stored Procedures	824	(3)
Stored Procedures with Select Into	827	(4)
Error Messages, Handlers, and Conditions	831	(5)
Stored Procedures with a Cursor	836	(3)
Stored Procedures and Transactions	839	(1)
Stored Procedures and the Catalog	840	(1)
Removing Stored Procedures	841	(1)
Compiling and Recompiling	842	(1)
Security with Stored Procedures	843	(1)
Advantages of Stored Procedures	844	(3)
Stored Functions	847	(6)
Introduction	847	(1)
Examples of Stored Functions	848	(4)
Removing Stored Functions	852	(1)
Triggers	853	(18)
Introduction	853	(1)
An Example of a Trigger	854	(3)
More Complex Examples	857	(6)
Triggers as Integrity Constraints	863	(3)
Removing Triggers	866	(1)
Differences Between SQL Products	866	(1)
Answers	867	(4)
VI. Object Relational Concepts	871	(40)
User-Defined Data Types, Functions, and Operators	873	(16)
Introduction	873	(1)
Creating User-Defined Data Types	873	(2)
Access to Data Types	875	(1)
Casting of Values	876	(2)
Creating User-Defined Operators	878	(1)
Opaque Data Type	879	(1)
Named Row Data Type	880	(5)
The Typed Table	885	(1)
Integrity Constraints on Data Types	886	(1)
Keys and Indexes	887	(1)
Answers	887	(2)
Inheritance, References, and Collections	889	(20)
Inheritance of Data Types	889	(2)
Linking Tables via Row Identifications	891	(7)
Collections	898	(7)
Inheritance of Tables	905	(4)
The Future of SQL	909	(2)
A. Syntax of SQL	911	(42)
Introduction	911	(1)
The BNF Notation	911	(3)
The Symbols < and >	912	(1)
The ::= Symbol	912	(1)
The | Symbol	912	(1)
The Symbols [ and ]	913	(1)
The ... Symbol	913	(1)
The Symbols { and }	913	(1)
The ; Symbol	913	(1)
The `` Symbol	914	(1)
Additional Remarks	914	(1)
Reserved Words in SQL3	914	(3)
Syntax Definitions of SQL Statements	917	(36)
Groups of SQL Statements	917	(3)
Definitions of SQL Statements	920	(17)
Common Elements	937	(16)
B. Scalar Functions	953	(46)
C. Bilbiography	999	(4)
Index	1003

Excerpts

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 asintergalactic dataspeak. Topics This book is completely devoted to SQL. Every aspect of the language is discussed thoroughly and critically. These aspects of SQL, among others, covered: 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 should be able to 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 Th