| |
| |
| |
| Databasics | |
| |
| |
| |
| Tables | |
| |
| |
| |
| Data Types | |
| |
| |
| |
| Character String | |
| |
| |
| |
| Numeric | |
| |
| |
| |
| Temporal | |
| |
| |
| |
| Binary | |
| |
| |
| |
| Boolean | |
| |
| |
| |
| Other Data Types | |
| |
| |
| |
| NULL | |
| |
| |
| |
| Primary Key | |
| |
| |
| |
| Table Relationships | |
| |
| |
| |
| Foreign Key | |
| |
| |
| |
| Relationship Types | |
| |
| |
| |
| Schemas | |
| |
| |
| |
| Restaurant Database | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Retrieval: Basic SELECTion | |
| |
| |
| |
| Simple SELECT | |
| |
| |
| |
| Selecting Rows | |
| |
| |
| |
| Standard Comparison Operators | |
| |
| |
| |
| String Comparisons | |
| |
| |
| |
| Matching String Patterns with LIKE | |
| |
| |
| |
| Getting What We Haven't Got with NOT | |
| |
| |
| |
| Combining Conditions with AND and OR | |
| |
| |
| |
| Selecting a Range of Values with BETWEEN | |
| |
| |
| |
| Selecting a Set of Values Using IN | |
| |
| |
| |
| IS NULL: Exploring the Unknown | |
| |
| |
| |
| ANDs, ORs, NOTs with NULLs: Three-Valued Logic | |
| |
| |
| |
| Three-Valued Logic and the IN Operator | |
| |
| |
| |
| How WHERE Determines What's In and Out | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Reshaping Results | |
| |
| |
| |
| AS: Naming Result Table Columns | |
| |
| |
| |
| DISTINCT and ALL: Dealing with Duplicates | |
| |
| |
| |
| Derived Attributes | |
| |
| |
| |
| Numeric | |
| |
| |
| |
| Character String | |
| |
| |
| |
| Temporal | |
| |
| |
| |
| Binary | |
| |
| |
| |
| Computation in the WHERE Clause | |
| |
| |
| |
| ORDER BY: Ordering Result Table Traversal | |
| |
| |
| |
| CAST: Data Type Conversion | |
| |
| |
| |
| CASE, COALESCE, and NULLIF: Conditional Expressions | |
| |
| |
| |
| CASE: Value List | |
| |
| |
| |
| CASE: Conditional List | |
| |
| |
| |
| NULLIF | |
| |
| |
| |
| COALESCE | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Aggregating Results | |
| |
| |
| |
| Aggregation Functions | |
| |
| |
| |
| AVG and SUM | |
| |
| |
| |
| MIN and MAX | |
| |
| |
| |
| COUNT | |
| |
| |
| |
| Removing Rows before Aggregation with WHERE | |
| |
| |
| |
| Removing Repeating Data with DISTINCT before Aggregation | |
| |
| |
| |
| Mixing Attributes, Aggregates, and Literals | |
| |
| |
| |
| Group Aggregation Using GROUP BY | |
| |
| |
| |
| Removing Rows before Grouping with WHERE | |
| |
| |
| |
| Sorting Groups with ORDER BY | |
| |
| |
| |
| Removing Groups with HAVING | |
| |
| |
| |
| Aggregates over Expressions | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Joins | |
| |
| |
| |
| Two Table Joins with WHERE | |
| |
| |
| |
| Table Aliases | |
| |
| |
| |
| Joins Needing More Than Two Tables | |
| |
| |
| |
| Self-Join: Joining a Table with Itself | |
| |
| |
| |
| Example Joins | |
| |
| |
| |
| How Does a Join Really Work? | |
| |
| |
| |
| Theta Joins: Generalizing Join Predicates | |
| |
| |
| |
| JOIN Operator | |
| |
| |
| |
| Inner Join | |
| |
| |
| |
| Outer Join | |
| |
| |
| |
| Natural Join | |
| |
| |
| |
| Cross Join | |
| |
| |
| |
| Join Strategies | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Set Queries: UNION, INTERSECT, and EXCEPT | |
| |
| |
| |
| Union | |
| |
| |
| |
| Intersect | |
| |
| |
| |
| Except | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Subqueries | |
| |
| |
| |
| What Are Subqueries? | |
| |
| |
| |
| Multilevel Subquery Nesting | |
| |
| |
| |
| Subqueries Using NOT IN | |
| |
| |
| |
| Subqueries with Empty Results | |
| |
| |
| |
| Combining JOIN and Subqueries | |
| |
| |
| |
| Standard Comparison Operators with Lists Using ANY, SOME, or ALL | |
| |
| |
| |
| ANY or SOME | |
| |
| |
| |
| All | |
| |
| |
| |
| Correlated Subqueries | |
| |
| |
| |
| Exists | |
| |
| |
| |
| Derived Relations-Subqueries in the FROM Clause | |
| |
| |
| |
| Subqueries in the HAVING Clause | |
| |
| |
| |
| Subset Queries | |
| |
| |
| |
| Subset Using EXISTS and EXCEPT | |
| |
| |
| |
| Subset Using Set Cardinality | |
| |
| |
| |
| Comparing Set Cardinality and Subsets | |
| |
| |
| |
| Subqueries in the SELECT Clause | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Modifying Data | |
| |
| |
| |
| INSERT: Adding New Rows | |
| |
| |
| |
| INSERTing a Row with VALUES | |
| |
| |
| |
| INSERTing Multiple Rows with SELECT | |
| |
| |
| |
| DELETE: Removing Rows | |
| |
| |
| |
| UPDATE: Changing Row Values | |
| |
| |
| |
| Testing Your DELETE and UPDATE WHERE Conditions | |
| |
| |
| |
| Living within Constraints | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Creating, Deleting, and Altering Tables | |
| |
| |
| |
| Creating Simple Tables | |
| |
| |
| |
| DEFAULT Values | |
| |
| |
| |
| Constraints | |
| |
| |
| |
| Not Null | |
| |
| |
| |
| Unique | |
| |
| |
| |
| Primary Key | |
| |
| |
| |
| Foreign Key | |
| |
| |
| |
| Check | |
| |
| |
| |
| Naming Constraints | |
| |
| |
| |
| Creating a Table from Tables | |
| |
| |
| |
| Create Domain | |
| |
| |
| |
| Referential Actions: The Autopilot of Foreign Key Maintenance | |
| |
| |
| |
| Indexes | |
| |
| |
| |
| Create Index | |
| |
| |
| |
| Drop Index | |
| |
| |
| |
| Indexes Are NOT in SQL 2003 | |
| |
| |
| |
| Drop Table | |
| |
| |
| |
| Alter Table | |
| |
| |
| |
| Generated Values | |
| |
| |
| |
| Sequences | |
| |
| |
| |
| Global and Local Temporary Tables | |
| |
| |
| |
| Creating a Database | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Views | |
| |
| |
| |
| Why Views? | |
| |
| |
| |
| Querying Views | |
| |
| |
| |
| Updating Views | |
| |
| |
| |
| Drop View | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Transactions | |
| |
| |
| |
| Ending a Transaction-COMMIT and ROLLBACK | |
| |
| |
| |
| Starting a Transaction-START TRANSACTION | |
| |
| |
| |
| Auto-Commit | |
| |
| |
| |
| SAVEPOINTs | |
| |
| |
| |
| Immediate or Deferred Constraints | |
| |
| |
| |
| Testing Changes with Transactions | |
| |
| |
| |
| Transaction Characteristics | |
| |
| |
| |
| Locking Issues | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Database Privileges | |
| |
| |
| |
| Grant | |
| |
| |
| |
| Revoke | |
| |
| |
| |
| Public | |
| |
| |
| |
| Creating a Set of Privileges Using ROLEs | |
| |
| |
| |
| Using Privileges and Views | |
| |
| |
| |
| Wrap Up | |
| |
| |
| |
| Introduction to Cursors, Embedded SQL, Stored Procedures, and Triggers | |
| |
| |
| |
| CURSORs | |
| |
| |
| |
| Programming with SQL | |
| |
| |
| |
| Stored Procedures | |
| |
| |
| |
| Executing SQL in Other Programming Languages | |
| |
| |
| |
| Triggers | |
| |
| |
| |
| Create Trigger | |
| |
| |
| |
| Trigger Firing Rules | |
| |
| |
| |
| Drop Trigger | |
| |
| |
| |
| Using Triggers | |
| |
| |
| |
| Wrap Up | |
| |
| |
| Index | |