Table of contents for SQL / Chris Fehily.


Bibliographic record and links to related information available from the Library of Congress catalog
Note: Electronic data is machine generated. May be incomplete or contain other coding.


Counter
Introduction                               xA
About SQL...............................xii
About This Book......................... xvi
W hat You'll Need  ........................... x
Chapter 1:    DBMS Specifics
Running SQL Programs ....................... 2
Microsoft Access ..........................5
Microsoft SQL Server ........................10
Oracle .................................17
IBM DB2................................20
MySQL ..............................        27
PostgreSQL ............. ................30
Chapter 2:     The Relational Model                       33
Tables, Columns, and Rows ................... 34
Primary Keys .....   .............    .......
Foreign  Keys  ................................
Relationships  ...... .........................42
Norm alization  ........ ....................... 45
The Sample Database ................... 51
Creating the Sample Database ................ 57
Chapter 3:     SQL Basics                                 61
SQL  Syntax  .............    .............. 62
SQL Standards and Conformance ............. 65
Identifiers.................  .  ..........66
D ata  Types  ........ ..........................68
Character String Types ....................... 70
Binary Large Object Type .................... 72
Exact Numeric Types  ........................ 73
Approximate Numeric Types ................ 75
Boolean  Type  ...... .........................76
Datetim e Types  ............................. 77
Interval Types  ........ ....................... 80
Unique Identifiers  ........................... 82
Other Data Types  ....... ..................... 83
N ulls  ....................................... 84
Chapter 4:    Retrieving Data from a Table             87
Retrieving Columns with SELECT and FROM ..... 88
Creating Column Aliases with AS ........... ... 91
Eliminating Duplicate Rows with DISTINCT . . .93
Sorting Rows with ORDER BY ....... .....  . . 95
Filtering Rows with WHERE ................... 101
Combining and Negating Conditions with
AND, OR, and NOT ............. .....  105
Matching Patterns with LIKE ............ .. 114
Range Filtering with BETWEEN ................ 118
List Filtering with IN ...  ............. 121
Testing for Nulls with IS NULL ................ 124
Chapter 5:    Operators and Functions                 127
Creating Derived Columns ........... .... 128
Performing Arithmetic Operations ........... 130
Determining the Order of Evaluation ......... 133
Concatenating Strings with I I .............. 134
Extracting a Substring with SUBSTRINGO  ..... 137
Changing String Case with UPPER()
and  LOWERO  ........................... 140
Trimming Characters with TRIMO  .......... 142
Finding the Length of a String with
CHARACTERJLENGTHO  ..... ............. -  147
Finding Substrings with POSITION(O ........ .. 149
Performing Datetime and Interval
Arithmetic . .................   .. 152
Getting the-Current Date and Time ........ . 154
Getting User Information ................1. 56
Converting Data Types with CASTO .........157
Evaluating Conditional Values with CASE ...... 161
Checking for Nulls with COALESCEO ......... 165
Comparing Expressions with NULLIFO  ..- -.... 166
Chapter 6:    Summarizing and Grouping Data           169
Using Aggregate Functions ................. 170
Creating Aggregate Expressions ............. 171
Finding a Minimum with MINO ............ . 172
Finding a Maximum with MAXO  .... ....... 173
Calculating a Sum with SUMO ....... ...... -  174
Calculating an Average with AVGO  .. ........ 175
Counting Rows with COUNTO  ................ 178
Aggregating Distinct Values with DISTINCT . .. 179
Grouping Rows with GROUP BY ................ 183
Filtering Groups with HAVING ................ 190
Qualifying Column Names . .... ..... .. .... 194
Creating Table Aliases with AS............... 196
UsingJoins.................................198
Creating Joins with JOIN or WHERE ............ 200
Creating a Cross Join with CROSS JOIN ........ 204
Creating a Natural Join with NATURAL JOIN .. . . 206
Creating an Inner Join with INNER JOIN .:.. .....210
Creating Outer Joins with OUTER 0OIN ......... 235
Creating a Self-Join  ......................... 247
Chapter 8:     Subqueries                              253
Understanding Subqueries ................ 254
Subquery Syntax  .........................256
Subqueries vs. Joins ......................... 257
Simple and Correlated Subqueries . .... ...... 262
Qualifying Column Names in Subqueries . .... 267
Nulls in  Subqueries  ......................... 268
Using Subqueries as Column Expressions ..... 270
Comparing a Subquery Value by Using a
Comparison Operator ............... ... 275
Testing Set Membership with IN ...... .. . 281
Comparing All Subquery Values with ALL ..... 288
Comparing Some Subquery Values with ANY .. 291
Testing Existence with EXISTS ............... 294
Comparing Equivalent Queries .............. 301
Chapter 9:    Set Operations                           303
Combining Rows with UNION ................. 304
Finding Common Rows with INTERSECT ...... 310
Finding Different Rows with EXCEPT . ....... 312
Chapter lo:     Inserting, Updating, and
Deleting Rows                            315
Displaying Table Definitions ................. 316
Inserting Rows with INSERT .................. 319
Updating Rows with UPDATE ................. 327
Deleting Rows with DELETE ..................333
Chapter 1i:    Creating, Altering, and
Dropping Tables                          337
Creating Tables ............................... 338
Understanding Constraints ................... 339
Creating a New Table with CREATE TABLE ...... 341
Forbidding Nulls with NOT NULL ..... .......... 343
Specifying a Default Value with DEFAULT .. .... 346
Specifying a Primary Key with
PRIMARY KEY ........................350
Specifying a Foreign Key with
FOREIGN KEY ..........................353
Forcing Unique Values with UNIQUE ........... 359
Adding a Check Constraint with CHECK ....... 363
Creating a Temporary Table with
CREATE TEMPORARY TABLE ................. 366
Creating a New Table from an Existing One
with CREATE TABLE AS.................... 369
Altering a Table with ALTER TABLE ..... .... . 373
Dropping a Table with DROP TABLE ..... ... .... 376
Chapter 12:    Indexes                                 377
Creating an Index with CREATE INDEX ......... 378
Dropping an Index with DROP INDEX ..... .... .383
Chapter 13:    Views                                  385
Creating a View with CREATE VIEW ........... 386
Retrieving Data Through a View ............... 391
Updating Data Through a View ............... 394
Dropping a View with DROP VIEW .............. 398
Chapter 14:    Transactions                           399
Executing a Transaction .................... 400
Chapter 15:    SQL Tricks                             405
Calculating Running Statistics .............. .  406
Generating Sequences ...................409
Finding Sequences, Runs, and Regions ....... 415
Limiting the Number of Rows Returned ..... 421
Assigning Ranks  .......................... 430
Calculating a Trimmed Mean ............... 432
Picking Random Rows ..................... 433
Handling Duplicates ........................ 435
Creating a Telephone List ................... 438
Retrieving Metadata .................... 439
Working with Dates ...................... 445
Calculating a Median ...................... 451
Finding Extreme Values ................. .... 453
Changing Running Statistics Midstream ...... 454
Pivoting Results ........................... 456
Working with Hierarchies ................... 458
Index                                  465



Library of Congress subject headings for this publication: SQL (Computer program language)