| |
| |
| 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 | |