| |
| |
List of Figures | |
| |
| |
Foreword to the Fourth Edition | |
| |
| |
Preface | |
| |
| |
Why New Editions? | |
| |
| |
Changes for Recent Editions | |
| |
| |
Include More Real-World Examples | |
| |
| |
Emphasize SQL-92 | |
| |
| |
Provide Software for Hands-On Practice | |
| |
| |
The Fourth Edition | |
| |
| |
Test on More Systems | |
| |
| |
Show Examples of Vendor-Specific Differences | |
| |
| |
Acknowledgments | |
| |
| |
Introduction | |
| |
| |
The Beginnings of SQL | |
| |
| |
The Commercialization of SQL | |
| |
| |
Who Should Use This Book | |
| |
| |
The Focus of This Book | |
| |
| |
How to Learn SQL with This Book | |
| |
| |
The Structure of This Book | |
| |
| |
How to Use This Book | |
| |
| |
An Overview of the Book | |
| |
| |
| |
SQL and Relational Database Management | |
| |
| |
In This Chapter | |
| |
| |
Relational Database Management | |
| |
| |
The Relational Model: It's All Tables | |
| |
| |
Independence Forever | |
| |
| |
A High-Level Language | |
| |
| |
Data Manipulation | |
| |
| |
Data Definition | |
| |
| |
Data Administration | |
| |
| |
Relational Operations | |
| |
| |
Projection | |
| |
| |
Selection | |
| |
| |
Join | |
| |
| |
Alternatives for Viewing Data | |
| |
| |
NULLs | |
| |
| |
Security | |
| |
| |
Integrity | |
| |
| |
SQL Functions | |
| |
| |
Summary | |
| |
| |
| |
Designing Databases | |
| |
| |
In This Chapter | |
| |
| |
Design Considerations | |
| |
| |
Do You Need an Expert? | |
| |
| |
Data Definition and Customization | |
| |
| |
How to Approach Database Design | |
| |
| |
Introducing the Sample Database | |
| |
| |
Data Entities and Relationships | |
| |
| |
Entities: Things with an Independent Existence | |
| |
| |
One-to-Many Relationships | |
| |
| |
Many-to-Many Relationships | |
| |
| |
One-to-One Relationships | |
| |
| |
The Entity-Relationship Approach Summarized | |
| |
| |
The Normalization Guidelines | |
| |
| |
First Normal Form | |
| |
| |
Second Normal Form | |
| |
| |
Third Normal Form | |
| |
| |
Fourth Normal Form | |
| |
| |
Reviewing the Database Design | |
| |
| |
Summarizing the bookbiz Database | |
| |
| |
Testing Your Database Design | |
| |
| |
Other Database Definition Considerations | |
| |
| |
Summary | |
| |
| |
| |
Creating and Filling a Database | |
| |
| |
In This Chapter | |
| |
| |
From Theory to Practice: Installing bookbiz | |
| |
| |
Distinguishing SQL Examples and Syntax | |
| |
| |
Coping with Failure | |
| |
| |
Working with Databases | |
| |
| |
Getting Started | |
| |
| |
Recognizing Roles | |
| |
| |
Reserving Space for Database Objects | |
| |
| |
Choosing Databases | |
| |
| |
Creating Simple Tables | |
| |
| |
Choosing Datatypes | |
| |
| |
Assigning NULL and NOT NULL | |
| |
| |
Planning Tables | |
| |
| |
Defining the Tables in bookbiz | |
| |
| |
Creating Indexes | |
| |
| |
The CREATE INDEX Statement | |
| |
| |
How, What, and Why to Index | |
| |
| |
Creating Tables with SQL-92 Constraints | |
| |
| |
Sketching Constraints | |
| |
| |
Implementing Constraints on Individual Columns | |
| |
| |
Implementing Multicolumn Constraints | |
| |
| |
Changing and Deleting Databases and Objects | |
| |
| |
Changing Databases | |
| |
| |
Changing Table Definitions | |
| |
| |
Removing Databases | |
| |
| |
Removing Tables | |
| |
| |
Removing Indexes | |
| |
| |
Adding, Changing, and Removing Data | |
| |
| |
Adding New Rows: INSERT | |
| |
| |
Changing Existing Data: UPDATE | |
| |
| |
Removing Data: DELETE | |
| |
| |
Summary | |
| |
| |
| |
Selecting Data from the Database | |
| |
| |
In This Chapter | |
| |
| |
SELECT Overview and Syntax | |
| |
| |
Basic SELECT Syntax | |
| |
| |
Full SELECT Syntax | |
| |
| |
Choosing Columns: The SELECT Clause | |
| |
| |
Choosing All Columns: SELECT | |
| |
| |
Choosing Specific Columns | |
| |
| |
More Than Column Names | |
| |
| |
Specifying Tables: The FROM Clause Table List | |
| |
| |
Using Table Aliases | |
| |
| |
Skipping FROM | |
| |
| |
Selecting Rows: The WHERE Clause | |
| |
| |
Comparison Operators | |
| |
| |
Connecting Conditions with Logical Operators | |
| |
| |
Ranges (BETWEEN and NOT BETWEEN) | |
| |
| |
Lists (IN and NOT IN) | |
| |
| |
Selecting Null Values | |
| |
| |
Matching Character Strings: LIKE | |
| |
| |
Like, Is IN LIKE Equals...? | |
| |
| |
Summary | |
| |
| |
| |
Sorting Data and Other Selection Techniques | |
| |
| |
In This Chapter | |
| |
| |
A New Batch of SELECT Statement Clauses | |
| |
| |
Sorting Query Results: ORDER BY | |
| |
| |
ORDER BY Syntax | |
| |
| |
Character Sets and Sort Orders | |
| |
| |
Sorts Within Sorts | |
| |
| |
Sort Up, Sort Down | |
| |
| |
What About More Complex Expressions? | |
| |
| |
How Do You Sort Nulls? | |
| |
| |
Eliminating Duplicate Rows: DISTINCT and ALL | |
| |
| |
DISTINCT Syntax | |
| |
| |
DISTINCT with Multiple SELECT List Items | |
| |
| |
Aggregate Functions | |
| |
| |
Aggregate Syntax | |
| |
| |
Summary | |
| |
| |
| |
Grouping Data and Reporting from It | |
| |
| |
In This Chapter | |
| |
| |
Grouping and Aggregates | |
| |
| |
The GROUP BY Clause | |
| |
| |
GROUP BY Syntax | |
| |
| |
Cautions and Restrictions | |
| |
| |
NULLs and Groups | |
| |
| |
GROUP BY: Aggregate Interactions | |
| |
| |
GROUP BY with WHERE | |
| |
| |
GROUP BY and ORDER BY | |
| |
| |
The HAVING Clause | |
| |
| |
Garden-Variety HAVING | |
| |
| |
Where, Group by, Having, Order by | |
| |
| |
All About NULLs | |
| |
| |
NULLs and Database Design | |
| |
| |
Comparisons Involving NULLs | |
| |
| |
NULLs and Computations | |
| |
| |
Defaults as Alternatives to NULLs | |
| |
| |
Functions That Work with NULLs | |
| |
| |
NULL Functions and "What-If" Calculations | |
| |
| |
Summary | |
| |
| |
| |
Joining Tables for Comprehensive Data Analysis | |
| |
| |
In This Chapter | |
| |
| |
Defining Joins | |
| |
| |
Why Joins Are Necessary | |
| |
| |
Associating Data from Separate Tables | |
| |
| |
Providing Flexibility | |
| |
| |
Getting a Good Join | |
| |
| |
From/Where Join Syntax | |
| |
| |
SQL-92 Join Syntax | |
| |
| |
Analyzing a Join | |
| |
| |
Improving the Readability of Join Results | |
| |
| |
Avoiding Duplication | |
| |
| |
Limiting the Width of the Display | |
| |
| |
Using Aliases in the From Clause Table/View List | |
| |
| |
Specifying Join Conditions | |
| |
| |
Joins Based on Equality | |
| |
| |
Joins Not Based on Equality | |
| |
| |
Joining More Than Two Tables | |
| |
| |
Exploring Exotic Joins | |
| |
| |
Joining a Table with Itself: The Self-Join | |
| |
| |
Showing the Background: Outer Joins | |
| |
| |
Avoiding a Common Source of Errors | |
| |
| |
Understanding the Cartesian Product | |
| |
| |
Using the Cartesian Product | |
| |
| |
Constraining the Cartesian Product | |
| |
| |
Going Beyond Joins: Union, Intersect, Minus | |
| |
| |
Union | |
| |
| |
Intersect and Minus | |
| |
| |
Summary | |
| |
| |
| |
Structuring Queries with Subqueries | |
| |
| |
In This Chapter | |
| |
| |
What Is a Subquery? | |
| |
| |
Simplified Subquery Syntax | |
| |
| |
How Do Subqueries Work? | |
| |
| |
Simple Subquery Processing | |
| |
| |
Correlated Subquery Processing | |
| |
| |
Simple-Correlated Performance Issues | |
| |
| |
Joins or Subqueries? | |
| |
| |
Subqueries! | |
| |
| |
Joins! | |
| |
| |
Subqueries vs. Self-Joins? | |
| |
| |
Which Is Better? | |
| |
| |
Subquery Rules | |
| |
| |
Subqueries Returning Zero or More Values | |
| |
| |
Subqueries Introduced with In | |
| |
| |
Subqueries Introduced with not in | |
| |
| |
Correlated Subqueries Introduced with in | |
| |
| |
Subqueries Introduced with Comparison Operators and Any or All | |
| |
| |
Subqueries Returning a Single Value | |
| |
| |
Aggregate Functions Guarantee a Single Value | |
| |
| |
Group By and Having Must Return a Single Value | |
| |
| |
Correlated Subqueries with Comparison Operators | |
| |
| |
Subqueries Testing Existence | |
| |
| |
Not Exists Seeks the Empty Set | |
| |
| |
Using Exists to Find Intersection and Difference | |
| |
| |
Exists Alternatives | |
| |
| |
Subqueries in Multiple Levels of Nesting | |
| |
| |
Subqueries in Update, Delete, and Insert Statements | |
| |
| |
Subqueries in from and Select Clauses | |
| |
| |
Subqueries in the from Clause | |
| |
| |
Subqueries in the Select Clause | |
| |
| |
Summary | |
| |
| |
| |
Creating and Using Views | |
| |
| |
In This Chapter | |
| |
| |
With a View Toward Flexibility | |
| |
| |
View Commands | |
| |
| |
Creating Views | |
| |
| |
Displaying Data Through Views | |
| |
| |
Dropping Views | |
| |
| |
Advantages of Views | |
| |
| |
Focus, Simplification, and Customization | |
| |
| |
Security | |
| |
| |
Independence | |
| |
| |
How Views Work | |
| |
| |
Naming View Columns | |
| |
| |
Creating Views with Multiple Underlying Objects | |
| |
| |
Resolving Views | |
| |
| |
Data Modification Through Views | |
| |
| |
The Rules According to ANSI | |
| |
| |
Creating Copies of Data | |
| |
| |
Summary | |
| |
| |
| |
Security, Transactions, Performance, and Integrity | |
| |
| |
In This Chapter | |
| |
| |
Database Management in the Real World | |
| |
| |
Data Security | |
| |
| |
User Identification and Special Users | |
| |
| |
The Grant and Revoke Commands | |
| |
| |
Views as Security Mechanisms | |
| |
| |
Transactions | |
| |
| |
Transactions and Concurrency | |
| |
| |
Transactions and Recovery | |
| |
| |
User-Defined Transactions | |
| |
| |
Performance | |
| |
| |
Benchmarking | |
| |
| |
Design and Indexing | |
| |
| |
Optimizing Queries | |
| |
| |
Other Tools for Monitoring and Boosting Performance | |
| |
| |
Data Integrity | |
| |
| |
Domain Constraints | |
| |
| |
Entity Integrity | |
| |
| |
Referential Integrity | |
| |
| |
Stored Procedures and Triggers | |
| |
| |
Summary | |
| |
| |
| |
Solving Business Problems | |
| |
| |
In This Chapter | |
| |
| |
Using SQL on the Job | |
| |
| |
Thinking Conditionally | |
| |
| |
Case/Decode | |
| |
| |
Changing Null Displays | |
| |
| |
Formatting and Displaying Data | |
| |
| |
Displaying One Column as Two | |
| |
| |
Displaying Two Columns as One | |
| |
| |
Converting from One Datatype to Another | |
| |
| |
Playing with Patterns | |
| |
| |
Matching Uppercase and Lowercase Letters | |
| |
| |
Finding Data Within a Range When You Don't Know the Values | |
| |
| |
Locating Date Data | |
| |
| |
Displaying Data by Time Units | |
| |
| |
Avoiding Mistakes | |
| |
| |
Distinguishing Distincts | |
| |
| |
Removing Duplicates | |
| |
| |
Finding the "First" Entry | |
| |
| |
Summary | |
| |
| |
| |
Syntax Summary for the SQL Used in This Book | |
| |
| |
In This Appendix | |
| |
| |
Formatting | |
| |
| |
Syntax Conventions | |
| |
| |
Statement List | |
| |
| |
| |
Industry SQL Equivalents | |
| |
| |
In This Appendix | |
| |
| |
Comparisons | |
| |
| |
Naming Convention Comparison | |
| |
| |
Datatype Comparison | |
| |
| |
Function Comparison | |
| |
| |
Character Functions | |
| |
| |
Conditional Functions | |
| |
| |
Date and Time Functions | |
| |
| |
| |
Glossary | |
| |
| |
| |
The bookbiz Sample Database | |
| |
| |
In This Appendix | |
| |
| |
Database Details | |
| |
| |
Table Charts | |
| |
| |
publishers Table | |
| |
| |
authors Table | |
| |
| |
titles Table | |
| |
| |
titleauthors Table | |
| |
| |
sales Table | |
| |
| |
salesdetails Table | |
| |
| |
editors Table | |
| |
| |
titleditors Table | |
| |
| |
roysched Table | |
| |
| |
Create Statements for the bookbiz Database | |
| |
| |
Adaptive Server Anywhere Creates | |
| |
| |
Transact-SQL Creates | |
| |
| |
Oracle Creates | |
| |
| |
Informix Creates | |
| |
| |
Insert Statements | |
| |
| |
Create View Statements | |
| |
| |
| |
Resources | |
| |
| |
In This Appendix | |
| |
| |
Books | |
| |
| |
General | |
| |
| |
Informix | |
| |
| |
Microsoft SQL Server | |
| |
| |
mSQL/MySQL | |
| |
| |
Oracle | |
| |
| |
Sybase | |
| |
| |
Transact-SQL | |
| |
| |
Web Sites | |
| |
| |
Vendor Sites | |
| |
| |
Other Links | |
| |
| |
Newsgroups | |
| |
| |
Index | |