Learning SQL, 3rd Edition

Learning SQL, 3rd Edition

by Alan Beaulieu
Released March 2020
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781492057611

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 Buy on ebooks.com
Start your free trial

Book description

As data floods into your company, you need to put it to work right away—and SQL is the best tool for the job. With the latest edition of this introductory guide, author Alan Beaulieu helps developers get up to speed with SQL fundamentals for writing database applications, performing administrative tasks, and generating reports. You’ll find new chapters on SQL and big data, analytic functions, and working with very large databases.

Each chapter presents a self-contained lesson on a key SQL concept or technique using numerous illustrations and annotated examples. Exercises let you practice the skills you learn. Knowledge of SQL is a must for interacting with data. With Learning SQL, you’ll quickly discover how to put the power and flexibility of this language to work.

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

Publisher resources

View/Submit Errata

Table of contents

  1. 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. Using the Examples in This Book
    6. O’Reilly Online Learning
    7. How to Contact Us
    8. Acknowledgments
  2. 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. SQL Unplugged
    5. What’s in Store
  3. 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
      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
      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 Sakila Database
  4. 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
      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 Numeric Placeholders
    8. Test Your Knowledge
      1. Exercise 3-1
      2. Exercise 3-2
      3. Exercise 3-3
      4. Exercise 3-4
  5. 4. Filtering
    1. Condition Evaluation
      1. Using Parentheses
      2. Using the not Operator
    2. Building a Condition
    3. Condition Types
      1. Equality Conditions
      2. Range Conditions
      3. Membership Conditions
      4. Matching Conditions
    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
  6. 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. Test Your Knowledge
      1. Exercise 5-1
      2. Exercise 5-2
      3. Exercise 5-3
  7. 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
  8. 7. Data Generation, Manipulation, and Conversion
    1. Working with String Data
      1. String Generation
      2. String Manipulation
    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
      3. Manipulating Temporal Data
    4. Conversion Functions
    5. Test Your Knowledge
      1. Exercise 7-1
      2. Exercise 7-2
      3. Exercise 7-3
  9. 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
  10. 9. Subqueries
    1. What Is a Subquery?
    2. Subquery Types
    3. Noncorrelated Subqueries
      1. Multiple-Row, Single-Column Subqueries
      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
      2. Subqueries as Expression Generators
    6. Subquery Wrap-Up
    7. Test Your Knowledge
      1. Exercise 9-1
      2. Exercise 9-2
      3. Exercise 9-3
  11. 10. Joins Revisited
    1. Outer Joins
      1. Left Versus Right Outer Joins
      2. Three-Way Outer Joins
    2. Cross Joins
    3. Natural Joins
    4. Test Your Knowledge
      1. Exercise 10-1
      2. Exercise 10-2
      3. Exercise 10-3 (Extra Credit)
  12. 11. Conditional Logic
    1. What Is Conditional Logic?
    2. The case Expression
      1. Searched case Expressions
      2. Simple case Expressions
    3. Examples of case Expressions
      1. Result Set Transformations
      2. Checking for Existence
      3. Division-by-Zero Errors
      4. Conditional Updates
      5. Handling Null Values
    4. Test Your Knowledge
      1. Exercise 11-1
      2. Exercise 11-2
  13. 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
  14. 13. Indexes and Constraints
    1. Indexes
      1. Index Creation
      2. Types of Indexes
      3. How Indexes Are Used
      4. The Downside of Indexes
    2. Constraints
      1. Constraint Creation
    3. Test Your Knowledge
      1. Exercise 13-1
      2. Exercise 13-2
  15. 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
  16. 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
  17. 16. Analytic Functions
    1. Analytic Function Concepts
      1. Data Windows
      2. Localized Sorting
    2. Ranking
      1. Ranking Functions
      2. Generating Multiple Rankings
    3. Reporting Functions
      1. Window Frames
      2. Lag and Lead
      3. Column Value Concatenation
    4. Test Your Knowledge
      1. Exercise 16-1
      2. Exercise 16-2
      3. Exercise 16-3
  18. 17. Working with Large Databases
    1. Partitioning
      1. Partitioning Concepts
      2. Table Partitioning
      3. Index Partitioning
      4. Partitioning Methods
      5. Partitioning Benefits
    2. Clustering
    3. Sharding
    4. Big Data
      1. Hadoop
      2. NoSQL and Document Databases
      3. Cloud Computing
      4. Conclusion
  19. 18. SQL and Big Data
    1. Introduction to Apache Drill
    2. Querying Files Using Drill
    3. Querying MySQL Using Drill
    4. Querying MongoDB Using Drill
    5. Drill with Multiple Data Sources
    6. Future of SQL
  20. A. ER Diagram for Example Database
  21. B. Solutions to Exercises
    1. Chapter 3
      1. Exercise 3-1
      2. Exercise 3-2
      3. Exercise 3-3
      4. Exercise 3-4
    2. Chapter 4
      1. Exercise 4-1
      2. Exercise 4-2
      3. Exercise 4-3
      4. Exercise 4-4
    3. Chapter 5
      1. Exercise 5-1
      2. Exercise 5-2
      3. Exercise 5-3
    4. Chapter 6
      1. Exercise 6-1
      2. Exercise 6-2
      3. Exercise 6-3
    5. Chapter 7
      1. Exercise 7-1
      2. Exercise 7-2
      3. Exercise 7-3
    6. Chapter 8
      1. Exercise 8-1
      2. Exercise 8-2
      3. Exercise 8-3
    7. Chapter 9
      1. Exercise 9-1
      2. Exercise 9-2
      3. Exercise 9-3
    8. Chapter 10
      1. Exercise 10-1
      2. Exercise 10-2
      3. Exercise 10-3 (Extra Credit)
    9. Chapter 11
      1. Exercise 11-1
      2. Exercise 11-2
    10. Chapter 12
      1. Exercise 12-1
    11. Chapter 13
      1. Exercise 13-1
      2. Exercise 13-2
    12. Chapter 14
      1. Exercise 14-1
      2. Exercise 14-2
    13. Chapter 15
      1. Exercise 15-1
      2. Exercise 15-2
    14. Chapter 16
      1. Exercise 16-1
      2. Exercise 16-2
      3. Exercise 16-3
  22. Index
  23. About the Author

Product information

  • Title: Learning SQL, 3rd Edition
  • Author(s): Alan Beaulieu
  • Release date: March 2020
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781492057611