Learning SQL, 2nd Edition

Learning SQL, 2nd Edition

by Alan Beaulieu
Released April 2009
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596520830

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.

Buy on Amazon
Start your free trial

Book description

Updated for the latest database management systems -- including MySQL 6.0, Oracle 11g, and Microsoft's SQL Server 2008 -- this introductory guide will get you up and running with SQL quickly. Whether you need to write database applications, perform administrative tasks, or generate reports, Learning SQL, Second Edition, will help you easily master all the SQL fundamentals.

Each chapter presents a self-contained lesson on a key SQL concept or technique, with numerous illustrations and annotated examples. Exercises at the end of each chapter let you practice the skills you learn. With this book, you will:

  • Move quickly through SQL basics and learn several advanced features
  • Use SQL data statements to generate, manipulate, and retrieve data
  • Create database objects, such as tables, indexes, and constraints, using SQL schema statements
  • Learn how data sets interact with queries, and understand the importance of subqueries
  • Convert and manipulate data with SQL's built-in functions, and use conditional logic in data statements

Knowledge of SQL is a must for interacting with data. With Learning SQL, you'll quickly learn how to put the power and flexibility of this language to work.

Publisher resources

View/Submit Errata

Table of contents

  1. A Note Regarding Supplemental Files
  2. Preface
    1. Why Learn SQL?
    2. Why Use This Book to Do It?
    3. Structure of This Book
    4. Conventions Used in This Book
    5. How to Contact Us
    6. Using Code Examples
    7. Safari® Books Online
    8. Acknowledgments
  3. 1. A Little Background
    1. Introduction to Databases
      1. Nonrelational Database Systems
      2. The Relational Model
      3. Some Terminology
    2. What Is SQL?
      1. SQL Statement Classes
      2. SQL: A Nonprocedural Language
      3. SQL Examples
    3. What Is MySQL?
    4. What’s in Store
  4. 2. Creating and Populating a Database
    1. Creating a MySQL Database
    2. Using the mysql Command-Line Tool
    3. MySQL Data Types
      1. Character Data
        1. Character sets
        2. Text data
      2. Numeric Data
      3. Temporal Data
    4. Table Creation
      1. Step 1: Design
      2. Step 2: Refinement
      3. Step 3: Building SQL Schema Statements
    5. Populating and Modifying Tables
      1. Inserting Data
        1. Generating numeric key data
        2. The insert statement
      2. Updating Data
      3. Deleting Data
    6. When Good Statements Go Bad
      1. Nonunique Primary Key
      2. Nonexistent Foreign Key
      3. Column Value Violations
      4. Invalid Date Conversions
    7. The Bank Schema
  5. 3. Query Primer
    1. Query Mechanics
    2. Query Clauses
    3. The select Clause
      1. Column Aliases
      2. Removing Duplicates
    4. The from Clause
      1. Tables
        1. Subquery-generated tables
        2. Views
      2. Table Links
      3. Defining Table Aliases
    5. The where Clause
    6. The group by and having Clauses
    7. The order by Clause
      1. Ascending Versus Descending Sort Order
      2. Sorting via Expressions
      3. Sorting via Numeric Placeholders
    8. Test Your Knowledge
      1. Exercise 3-1
      2. Exercise 3-2
      3. Exercise 3-3
      4. Exercise 3-4
  6. 4. Filtering
    1. Condition Evaluation
      1. Using Parentheses
      2. Using the not Operator
    2. Building a Condition
    3. Condition Types
      1. Equality Conditions
        1. Inequality conditions
        2. Data modification using equality conditions
      2. Range Conditions
        1. The between operator
        2. String ranges
      3. Membership Conditions
        1. Using subqueries
        2. Using not in
      4. Matching Conditions
        1. Using wildcards
        2. Using regular expressions
    4. Null: That Four-Letter Word
    5. Test Your Knowledge
      1. Exercise 4-1
      2. Exercise 4-2
      3. Exercise 4-3
      4. Exercise 4-4
  7. 5. Querying Multiple Tables
    1. What Is a Join?
      1. Cartesian Product
      2. Inner Joins
      3. The ANSI Join Syntax
    2. Joining Three or More Tables
      1. Using Subqueries As Tables
      2. Using the Same Table Twice
    3. Self-Joins
    4. Equi-Joins Versus Non-Equi-Joins
    5. Join Conditions Versus Filter Conditions
    6. Test Your Knowledge
      1. Exercise 5-1
      2. Exercise 5-2
      3. Exercise 5-3
  8. 6. Working with Sets
    1. Set Theory Primer
    2. Set Theory in Practice
    3. Set Operators
      1. The union Operator
      2. The intersect Operator
      3. The except Operator
    4. Set Operation Rules
      1. Sorting Compound Query Results
      2. Set Operation Precedence
    5. Test Your Knowledge
      1. Exercise 6-1
      2. Exercise 6-2
      3. Exercise 6-3
  9. 7. Data Generation, Conversion, and Manipulation
    1. Working with String Data
      1. String Generation
        1. Including single quotes
        2. Including special characters
      2. String Manipulation
        1. String functions that return numbers
        2. String functions that return strings
    2. Working with Numeric Data
      1. Performing Arithmetic Functions
      2. Controlling Number Precision
      3. Handling Signed Data
    3. Working with Temporal Data
      1. Dealing with Time Zones
      2. Generating Temporal Data
        1. String representations of temporal data
        2. String-to-date conversions
        3. Functions for generating dates
      3. Manipulating Temporal Data
        1. Temporal functions that return dates
        2. Temporal functions that return strings
        3. Temporal functions that return numbers
    4. Conversion Functions
    5. Test Your Knowledge
      1. Exercise 7-1
      2. Exercise 7-2
      3. Exercise 7-3
  10. 8. Grouping and Aggregates
    1. Grouping Concepts
    2. Aggregate Functions
      1. Implicit Versus Explicit Groups
      2. Counting Distinct Values
      3. Using Expressions
      4. How Nulls Are Handled
    3. Generating Groups
      1. Single-Column Grouping
      2. Multicolumn Grouping
      3. Grouping via Expressions
      4. Generating Rollups
    4. Group Filter Conditions
    5. Test Your Knowledge
      1. Exercise 8-1
      2. Exercise 8-2
      3. Exercise 8-3
      4. Exercise 8-4 (Extra Credit)
  11. 9. Subqueries
    1. What Is a Subquery?
    2. Subquery Types
    3. Noncorrelated Subqueries
      1. Multiple-Row, Single-Column Subqueries
        1. The in and not in operators
        2. The all operator
        3. The any operator
      2. Multicolumn Subqueries
    4. Correlated Subqueries
      1. The exists Operator
      2. Data Manipulation Using Correlated Subqueries
    5. When to Use Subqueries
      1. Subqueries As Data Sources
        1. Data fabrication
        2. Task-oriented subqueries
      2. Subqueries in Filter Conditions
      3. Subqueries As Expression Generators
    6. Subquery Wrap-up
    7. Test Your Knowledge
      1. Exercise 9-1
      2. Exercise 9-2
      3. Exercise 9-3
      4. Exercise 9-4
  12. 10. Joins Revisited
    1. Outer Joins
      1. Left Versus Right Outer Joins
      2. Three-Way Outer Joins
      3. Self Outer Joins
    2. Cross Joins
    3. Natural Joins
    4. Test Your Knowledge
      1. Exercise 10-1
      2. Exercise 10-2
      3. Exercise 10-3
      4. Exercise 10-4 (Extra Credit)
  13. 11. Conditional Logic
    1. What Is Conditional Logic?
    2. The Case Expression
      1. Searched Case Expressions
      2. Simple Case Expressions
    3. Case Expression Examples
      1. Result Set Transformations
      2. Selective Aggregation
      3. Checking for Existence
      4. Division-by-Zero Errors
      5. Conditional Updates
      6. Handling Null Values
    4. Test Your Knowledge
      1. Exercise 11-1
      2. Exercise 11-2
  14. 12. Transactions
    1. Multiuser Databases
      1. Locking
      2. Lock Granularities
    2. What Is a Transaction?
      1. Starting a Transaction
      2. Ending a Transaction
      3. Transaction Savepoints
    3. Test Your Knowledge
      1. Exercise 12-1
  15. 13. Indexes and Constraints
    1. Indexes
      1. Index Creation
        1. Unique indexes
        2. Multicolumn indexes
      2. Types of Indexes
        1. B-tree indexes
        2. Bitmap indexes
        3. Text indexes
      3. How Indexes Are Used
      4. The Downside of Indexes
    2. Constraints
      1. Constraint Creation
      2. Constraints and Indexes
      3. Cascading Constraints
    3. Test Your Knowledge
      1. Exercise 13-1
      2. Exercise 13-2
  16. 14. Views
    1. What Are Views?
    2. Why Use Views?
      1. Data Security
      2. Data Aggregation
      3. Hiding Complexity
      4. Joining Partitioned Data
    3. Updatable Views
      1. Updating Simple Views
      2. Updating Complex Views
    4. Test Your Knowledge
      1. Exercise 14-1
      2. Exercise 14-2
  17. 15. Metadata
    1. Data About Data
    2. Information_Schema
    3. Working with Metadata
      1. Schema Generation Scripts
      2. Deployment Verification
      3. Dynamic SQL Generation
    4. Test Your Knowledge
      1. Exercise 15-1
      2. Exercise 15-2
  18. A. ER Diagram for Example Database
  19. B. MySQL Extensions to the SQL Language
    1. Extensions to the select Statement
      1. The limit Clause
        1. Combining the limit clause with the order by clause
        2. The limit clause’s optional second parameter
        3. Ranking queries
      2. The into outfile Clause
    2. Combination Insert/Update Statements
    3. Ordered Updates and Deletes
    4. Multitable Updates and Deletes
  20. C. Solutions to Exercises
    1. Chapter 3
      1. 3-1
      2. 3-2
      3. 3-3
      4. 3-4
    2. Chapter 4
      1. 4-1
      2. 4-2
      3. 4-3
      4. 4-4
    3. Chapter 5
      1. 5-1
      2. 5-2
      3. 5-3
    4. Chapter 6
      1. 6-1
      2. 6-2
      3. 6-3
    5. Chapter 7
      1. 7-1
      2. 7-2
      3. 7-3
    6. Chapter 8
      1. 8-1
      2. 8-2
      3. 8-3
      4. 8-4 (Extra Credit)
    7. Chapter 9
      1. 9-1
      2. 9-2
      3. 9-3
      4. 9-4
    8. Chapter 10
      1. 10-1
      2. 10-2
      3. 10-3
      4. 10-4 (Extra Credit)
    9. Chapter 11
      1. 11-1
      2. 11-2
    10. Chapter 12
      1. 12-1
    11. Chapter 13
      1. 13-1
      2. 13-2
    12. Chapter 14
      1. 14-1
      2. 14-2
    13. Chapter 15
      1. 15-1
      2. 15-2
  21. Index
  22. About the Author
  23. Colophon
  24. Copyright

Product information

  • Title: Learning SQL, 2nd Edition
  • Author(s): Alan Beaulieu
  • Release date: April 2009
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596520830