SQL 3rd cover

SQL: Visual QuickStart Guide, Third Edition

by Chris Fehily
June 2008
Pages: 496
ISBN-10: 0321553578
ISBN-13: 978-0321553577

Read an excerpt (amazon.com).
Download this book’s companion files.

Buy Now

Paperback: Amazon.com, Amazon.ca, Amazon.co.uk

Amazon Kindle: Amazon.com

Description

SQL: Visual QuickStart Guide teaches SQL — the language of databases — to beginning and intermediate programmers. With SQL, you can create, alter, and drop tables, indexes, and views; insert, update, query, and delete data; and execute transactions to maintain the integrity of those data. This book covers the relational model, the core language for ANSI/ISO (standard) SQL:2003, and product-specific variations for Microsoft Access, Microsoft SQL Server, Oracle, IBM DB2, MySQL, and PostgreSQL. Hundreds of examples of varied difficulty encourage you to experiment and explore. The book’s split-page format separates examples from the flow of the text and its extensive cross references and index make it a good quick reference for experienced programmers.

This updated edition covers the most recent DBMS releases, adds a chapter on SQL tricks, and includes new programming tips, new sidebars on subtle or advanced topics, and other odds and ends.

Contents

Introduction
About SQL
About This Book
What You’ll Need
1. DBMS Specifics
Running SQL Programs
Microsoft Access
Microsoft SQL Server
Oracle
IBM DB2
MySQL
PostgreSQL
2. The Relational Model
Tables, Columns, and Rows
Primary Keys
Foreign Keys
Relationships
Normalization
The Sample Database
Creating the Sample Database
3. SQL Basics
SQL Syntax
SQL Standards and Conformance
Identifiers
Data Types
Character String Types
Binary Large Object Type
Exact Numeric Types
Approximate Numeric Types
Boolean Type
Datetime Types
Interval Types
Unique Identifiers
Other Data Types
Nulls
4. Retrieving Data from a Table
Retrieving Columns with SELECT and FROM
Creating Column Aliases with AS
Eliminating Duplicate Rows with DISTINCT
Sorting Rows with ORDER BY
Filtering Rows with WHERE
Combining and Negating Conditions with AND, OR, and NOT
Matching Patterns with LIKE
Range Filtering with BETWEEN
List Filtering with IN
Testing for Nulls with IS NULL
5. Operators and Functions
Creating Derived Columns
Performing Arithmetic Operations
Determining the Order of Evaluation
Concatenating Strings with ||
Extracting a Substring with SUBSTRING()
Changing String Case with UPPER() and LOWER()
Trimming Characters with TRIM()
Finding the Length of a String with CHARACTER_LENGTH()
Finding Substrings with POSITION()
Performing Datetime and Interval Arithmetic
Getting the Current Date and Time
Getting User Information
Converting Data Types with CAST()
Evaluating Conditional Values with CASE
Checking for Nulls with COALESCE()
Comparing Expressions with NULLIF()
6. Summarizing and Grouping Data
Using Aggregate Functions
Creating Aggregate Expressions
Finding a Minimum with MIN()
Finding a Maximum with MAX()
Calculating a Sum with SUM()
Calculating an Average with AVG()
Counting Rows with COUNT()
Aggregating Distinct Values with DISTINCT
Grouping Rows with GROUP BY
Filtering Groups with HAVING
7. Joins
Qualifying Column Names
Creating Table Aliases with AS
Using Joins
Creating Joins with JOIN or WHERE
Creating a Cross Join with CROSS JOIN
Creating a Natural Join with NATURAL JOIN
Creating an Inner Join with INNER JOIN
Creating Outer Joins with OUTER JOIN
Creating a Self-Join
8. Subqueries
Understanding Subqueries
Subquery Syntax
Subqueries vs. Joins
Simple and Correlated Subqueries
Qualifying Column Names in Subqueries
Nulls in Subqueries
Using Subqueries as Column Expressions
Comparing a Subquery Value by Using a Comparison Operator
Testing Set Membership with IN
Comparing All Subquery Values with ALL
Comparing Some Subquery Values with ANY
Testing Existence with EXISTS
Comparing Equivalent Queries
9. Set Operations
Combining Rows with UNION
Finding Common Rows with INTERSECT
Finding Different Rows with EXCEPT
10. Inserting, Updating, and Deleting Rows
Displaying Table Definitions
Inserting Rows with INSERT
Updating Rows with UPDATE
Deleting Rows with DELETE
11. Creating, Altering, and Dropping Tables
Creating Tables
Understanding Constraints
Creating a New Table with CREATE TABLE
Forbidding Nulls with NOT NULL
Specifying a Default Value with DEFAULT
Specifying a Primary Key with PRIMARY KEY
Specifying a Foreign Key with FOREIGN KEY
Forcing Unique Values with UNIQUE
Adding a Check Constraint with CHECK
Creating a Temporary Table with CREATE TEMPORARY TABLE
Creating a New Table from an Existing One with CREATE TABLE AS
Altering a Table with ALTER TABLE
Dropping a Table with DROP TABLE
12. Indexes
Creating an Index with CREATE INDEX
Dropping an Index with DROP INDEX
13. Views
Creating a View with CREATE VIEW
Retrieving Data Through a View
Updating Data Through a View
Dropping a View with DROP VIEW
14. Transactions
Executing a Transaction
15. SQL Tricks
Calculating Running Statistics
Generating Sequences
Finding Sequences, Runs, and Regions
Limiting the Number of Rows Returned
Assigning Ranks
Calculating a Trimmed Mean
Picking Random Rows
Handling Duplicates
Creating a Telephone List
Retrieving Metadata
Working with Dates
Calculating a Median
Finding Extreme Values
Changing Running Statistics Midstream
Pivoting Results
Working with Hierarchies
Index

Downloads

Most of this book’s examples use the sample database books, described in “The Sample Database” in Chapter 2. To create books, download the zip file sql_vqs3_files.zip, expand it, and follow the instructions for your DBMS, listed below. To expand the zip file, double-click it in Windows XP or later or in Mac OS X; in Linux or Unix, use the gunzip or unzip command. After expansion, read the file readme.txt for a description of the distribution.

In addition to creating the tables described in “The Sample Database” in Chapter 2, the SQL scripts create additional tables used in other examples (mostly those in Chapter 15, “SQL Tricks”). If you’re running a DBMS locally (that is, on your own computer), then you’re the database administrator (DBA) and have all the privileges you need. If you’re connecting to a DBMS on a network server, then ask your DBA for connection parameters and the privileges to create, query, update, and drop databases and tables.

The instructions for creating the sample database explain how to use simple tools and settings. As you gain experience, you might want to switch to using the statement CREATE DATABASE to create new databases. CREATE DATABASE is a powerful but nonstandard SQL command, so its syntax and capabilities vary by DBMS; see your DBMS’s documentation. (Microsoft Access doesn’t support CREATE DATABASE, but you can create Access databases programmatically by using Visual Basic for Applications or C#.)

Errata and Updates

Page 264 — In the left column, the inner query should be printed in red:
SELECT outer_columns
  FROM outer_table
  WHERE outer_column_value IN
    (SELECT inner_column
       FROM inner_table
       WHERE inner_column = outer_column)