Cover......Page 1
Contents......Page 7
Introduction......Page 12
About SQL......Page 13
About This Book......Page 17
What You’ll Need......Page 21
Chapter 1: DBMS Specifics......Page 22
Running SQL Programs......Page 23
Microsoft Access......Page 26
Microsoft SQL Server......Page 31
Oracle......Page 38
IBM DB2......Page 41
MySQL......Page 48
PostgreSQL......Page 51
Chapter 2: The Relational Model......Page 54
Tables, Columns, and Rows......Page 55
Primary Keys......Page 59
Foreign Keys......Page 61
Relationships......Page 63
Normalization......Page 66
The Sample Database......Page 72
Creating the Sample Database......Page 78
Chapter 3: SQL Basics......Page 82
SQL Syntax......Page 83
SQL Standards and Conformance......Page 86
Identifiers......Page 87
Data Types......Page 89
Character String Types......Page 91
Binary Large Object Type......Page 93
Exact Numeric Types......Page 94
Approximate Numeric Types......Page 96
Boolean Type......Page 97
Datetime Types......Page 98
Interval Types......Page 101
Unique Identifiers......Page 103
Other Data Types......Page 104
Nulls......Page 105
Chapter 4: Retrieving Data from a Table......Page 108
Retrieving Columns with SELECT and FROM......Page 109
Creating Column Aliases with AS......Page 112
Eliminating Duplicate Rows with DISTINCT......Page 114
Sorting Rows with ORDER BY......Page 116
Filtering Rows with WHERE......Page 122
Combining and Negating Conditions with AND, OR, and NOT......Page 126
Matching Patterns with LIKE......Page 135
Range Filtering with BETWEEN......Page 139
List Filtering with IN......Page 142
Testing for Nulls with IS NULL......Page 145
Chapter 5: Operators and Functions......Page 148
Creating Derived Columns......Page 149
Performing Arithmetic Operations......Page 151
Determining the Order of Evaluation......Page 154
Concatenating Strings with ||......Page 155
Extracting a Substring with SUBSTRING()......Page 158
Changing String Case with UPPER() and LOWER()......Page 161
Trimming Characters with TRIM()......Page 163
Finding the Length of a String with CHARACTER_LENGTH()......Page 168
Finding Substrings with POSITION()......Page 170
Performing Datetime and Interval Arithmetic......Page 173
Getting the Current Date and Time......Page 175
Getting User Information......Page 177
Converting Data Types with CAST()......Page 178
Evaluating Conditional Values with CASE......Page 182
Checking for Nulls with COALESCE()......Page 186
Comparing Expressions with NULLIF()......Page 187
Chapter 6: Summarizing and Grouping Data......Page 190
Using Aggregate Functions......Page 191
Creating Aggregate Expressions......Page 192
Finding a Minimum with MIN()......Page 193
Finding a Maximum with MAX()......Page 194
Calculating a Sum with SUM()......Page 195
Calculating an Average with AVG()......Page 196
Counting Rows with COUNT()......Page 199
Aggregating Distinct Values with DISTINCT......Page 200
Grouping Rows with GROUP BY......Page 204
Filtering Groups with HAVING......Page 211
Chapter 7: Joins......Page 214
Qualifying Column Names......Page 215
Creating Table Aliases with AS......Page 217
Using Joins......Page 219
Creating Joins with JOIN or WHERE......Page 221
Creating a Cross Join with CROSS JOIN......Page 225
Creating a Natural Join with NATURAL JOIN......Page 227
Creating an Inner Join with INNER JOIN......Page 231
Creating Outer Joins with OUTER JOIN......Page 256
Creating a Self-Join......Page 268
Chapter 8: Subqueries......Page 274
Understanding Subqueries......Page 275
Subquery Syntax......Page 277
Subqueries vs. Joins......Page 278
Simple and Correlated Subqueries......Page 283
Qualifying Column Names in Subqueries......Page 288
Nulls in Subqueries......Page 289
Using Subqueries as Column Expressions......Page 291
Comparing a Subquery Value by Using a Comparison Operator......Page 296
Testing Set Membership with IN......Page 302
Comparing All Subquery Values with ALL......Page 309
Comparing Some Subquery Values with ANY......Page 312
Testing Existence with EXISTS......Page 315
Comparing Equivalent Queries......Page 322
Chapter 9: Set Operations......Page 324
Combining Rows with UNION......Page 325
Finding Common Rows with INTERSECT......Page 331
Finding Different Rows with EXCEPT......Page 333
Chapter 10: Inserting, Updating, and Deleting Rows......Page 336
Displaying Table Definitions......Page 337
Inserting Rows with INSERT......Page 340
Updating Rows with UPDATE......Page 348
Deleting Rows with DELETE......Page 354
Chapter 11: Creating, Altering, and Dropping Tables......Page 358
Creating Tables......Page 359
Understanding Constraints......Page 360
Creating a New Table with CREATE TABLE......Page 362
Forbidding Nulls with NOT NULL......Page 364
Specifying a Default Value with DEFAULT......Page 367
Specifying a Primary Key with PRIMARY KEY......Page 371
Specifying a Foreign Key with FOREIGN KEY......Page 374
Forcing Unique Values with UNIQUE......Page 380
Adding a Check Constraint with CHECK......Page 384
Creating a Temporary Table with CREATE TEMPORARY TABLE......Page 387
Creating a New Table from an Existing One with CREATE TABLE AS......Page 390
Altering a Table with ALTER TABLE......Page 394
Dropping a Table with DROP TABLE......Page 397
Chapter 12: Indexes......Page 398
Creating an Index with CREATE INDEX......Page 399
Dropping an Index with DROP INDEX......Page 404
Chapter 13: Views......Page 406
Creating a View with CREATE VIEW......Page 407
Retrieving Data Through a View......Page 412
Updating Data Through a View......Page 415
Dropping a View with DROP VIEW......Page 419
Chapter 14: Transactions......Page 420
Executing a Transaction......Page 421
Chapter 15: SQL Tricks......Page 426
Calculating Running Statistics......Page 427
Generating Sequences......Page 430
Finding Sequences, Runs, and Regions......Page 436
Limiting the Number of Rows Returned......Page 442
Assigning Ranks......Page 451
Calculating a Trimmed Mean......Page 453
Picking Random Rows......Page 454
Handling Duplicates......Page 456
Creating a Telephone List......Page 459
Retrieving Metadata......Page 460
Working with Dates......Page 466
Calculating a Median......Page 472
Finding Extreme Values......Page 474
Changing Running Statistics Midstream......Page 475
Pivoting Results......Page 477
Working with Hierarchies......Page 479
A......Page 486
B......Page 487
C......Page 488
D......Page 490
E......Page 492
I......Page 493
J......Page 494
M......Page 495
O......Page 496
P......Page 497
Q......Page 498
R......Page 499
S......Page 500
T......Page 502
W......Page 503