SQL: Visual QuickStart Guide, Third Edition

SQL: Visual QuickStart Guide, Third Edition

Released June 2008
Publisher(s): Peachpit Press
ISBN: None

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

SQL is a standard interactive and programming language for querying and modifying data and managing databases. This task-based tutorial and reference guide takes the mystery out learning and applying SQL. After going over the relational database model and SQL syntax in the first few chapters, veteran author Chris Fehily immediately launches into the tasks that will get readers comfortable with SQL. In addition to covering all the SQL basics, this thoroughly updated reference contains a wealth of in-depth SQL knowledge and serves as an excellent reference for more experienced users.

Table of contents

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

Product information

  • Title: SQL: Visual QuickStart Guide, Third Edition
  • Author(s):
  • Release date: June 2008
  • Publisher(s): Peachpit Press
  • ISBN: None