SQL Visual QuickStart Guide

by Fehily, Chris

• ISBN13: 9780321553577
• ISBN10: 0321553578
• Edition: 3rd
• Format: Paperback
• Copyright: 2008-06-04
• Publisher: Peachpit Pr

Summary

Task-based guide delivers the SQL know-how to employ and maintain any SQL-based database.

Author Biography

Chris Fehily is a writer and consultant living in San Francisco. His other books for Peachpit Press include Visual QuickStart Guides to the Python programming language and Windows XP and Vista operating systems

Table of Contents

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