Learning SQL: Generate, Manipulate, and Retrieve Data

✍ Scribed by Alan Beaulieu

• Publisher: O'Reilly Media
• Year: 2020
• Tongue: English
• Leaves: 380
• Edition: 3
• Category: Library

✦ Synopsis

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

✦ Table of Contents

Cover
Copyright
Table of Contents
Preface
    Why Learn SQL?
    Why Use This Book to Do It?
    Structure of This Book
    Conventions Used in This Book
    Using the Examples in This Book
    O’Reilly Online Learning
    How to Contact Us
    Acknowledgments
Chapter 1. A Little Background
    Introduction to Databases
        Nonrelational Database Systems
        The Relational Model
        Some Terminology
    What Is SQL?
        SQL Statement Classes
        SQL: A Nonprocedural Language
        SQL Examples
    What Is MySQL?
    SQL Unplugged
    What’s in Store
Chapter 2. Creating and Populating a Database
    Creating a MySQL Database
    Using the mysql Command-Line Tool
    MySQL Data Types
        Character Data
        Numeric Data
        Temporal Data
    Table Creation
        Step 1: Design
        Step 2: Refinement
        Step 3: Building SQL Schema Statements
    Populating and Modifying Tables
        Inserting Data
        Updating Data
        Deleting Data
    When Good Statements Go Bad
        Nonunique Primary Key
        Nonexistent Foreign Key
        Column Value Violations
        Invalid Date Conversions
    The Sakila Database
Chapter 3. Query Primer
    Query Mechanics
    Query Clauses
    The select Clause
        Column Aliases
        Removing Duplicates
    The from Clause
        Tables
        Table Links
        Defining Table Aliases
    The where Clause
    The group by and having Clauses
    The order by Clause
        Ascending Versus Descending Sort Order
        Sorting via Numeric Placeholders
    Test Your Knowledge
        Exercise 3-1
        Exercise 3-2
        Exercise 3-3
        Exercise 3-4
Chapter 4. Filtering
    Condition Evaluation
        Using Parentheses
        Using the not Operator
    Building a Condition
    Condition Types
        Equality Conditions
        Range Conditions
        Membership Conditions
        Matching Conditions
    Null: That Four-Letter Word
    Test Your Knowledge
        Exercise 4-1
        Exercise 4-2
        Exercise 4-3
        Exercise 4-4
Chapter 5. Querying Multiple Tables
    What Is a Join?
        Cartesian Product
        Inner Joins
        The ANSI Join Syntax
    Joining Three or More Tables
        Using Subqueries as Tables
        Using the Same Table Twice
    Self-Joins
    Test Your Knowledge
        Exercise 5-1
        Exercise 5-2
        Exercise 5-3
Chapter 6. Working with Sets
    Set Theory Primer
    Set Theory in Practice
    Set Operators
        The union Operator
        The intersect Operator
        The except Operator
    Set Operation Rules
        Sorting Compound Query Results
        Set Operation Precedence
    Test Your Knowledge
        Exercise 6-1
        Exercise 6-2
        Exercise 6-3
Chapter 7. Data Generation, Manipulation, and Conversion
    Working with String Data
        String Generation
        String Manipulation
    Working with Numeric Data
        Performing Arithmetic Functions
        Controlling Number Precision
        Handling Signed Data
    Working with Temporal Data
        Dealing with Time Zones
        Generating Temporal Data
        Manipulating Temporal Data
    Conversion Functions
    Test Your Knowledge
        Exercise 7-1
        Exercise 7-2
        Exercise 7-3
Chapter 8. Grouping and Aggregates
    Grouping Concepts
    Aggregate Functions
        Implicit Versus Explicit Groups
        Counting Distinct Values
        Using Expressions
        How Nulls Are Handled
    Generating Groups
        Single-Column Grouping
        Multicolumn Grouping
        Grouping via Expressions
        Generating Rollups
    Group Filter Conditions
    Test Your Knowledge
        Exercise 8-1
        Exercise 8-2
        Exercise 8-3
Chapter 9. Subqueries
    What Is a Subquery?
    Subquery Types
    Noncorrelated Subqueries
        Multiple-Row, Single-Column Subqueries
        Multicolumn Subqueries
    Correlated Subqueries
        The exists Operator
        Data Manipulation Using Correlated Subqueries
    When to Use Subqueries
        Subqueries as Data Sources
        Subqueries as Expression Generators
    Subquery Wrap-Up
    Test Your Knowledge
        Exercise 9-1
        Exercise 9-2
        Exercise 9-3
Chapter 10. Joins Revisited
    Outer Joins
        Left Versus Right Outer Joins
        Three-Way Outer Joins
    Cross Joins
    Natural Joins
    Test Your Knowledge
        Exercise 10-1
        Exercise 10-2
        Exercise 10-3 (Extra Credit)
Chapter 11. Conditional Logic
    What Is Conditional Logic?
    The case Expression
        Searched case Expressions
        Simple case Expressions
    Examples of case Expressions
        Result Set Transformations
        Checking for Existence
        Division-by-Zero Errors
        Conditional Updates
        Handling Null Values
    Test Your Knowledge
        Exercise 11-1
        Exercise 11-2
Chapter 12. Transactions
    Multiuser Databases
        Locking
        Lock Granularities
    What Is a Transaction?
        Starting a Transaction
        Ending a Transaction
        Transaction Savepoints
    Test Your Knowledge
        Exercise 12-1
Chapter 13. Indexes and Constraints
    Indexes
        Index Creation
        Types of Indexes
        How Indexes Are Used
        The Downside of Indexes
    Constraints
        Constraint Creation
    Test Your Knowledge
        Exercise 13-1
        Exercise 13-2
Chapter 14. Views
    What Are Views?
    Why Use Views?
        Data Security
        Data Aggregation
        Hiding Complexity
        Joining Partitioned Data
    Updatable Views
        Updating Simple Views
        Updating Complex Views
    Test Your Knowledge
        Exercise 14-1
        Exercise 14-2
Chapter 15. Metadata
    Data About Data
    information_schema
    Working with Metadata
        Schema Generation Scripts
        Deployment Verification
        Dynamic SQL Generation
    Test Your Knowledge
        Exercise 15-1
        Exercise 15-2
Chapter 16. Analytic Functions
    Analytic Function Concepts
        Data Windows
        Localized Sorting
    Ranking
        Ranking Functions
        Generating Multiple Rankings
    Reporting Functions
        Window Frames
        Lag and Lead
        Column Value Concatenation
    Test Your Knowledge
        Exercise 16-1
        Exercise 16-2
        Exercise 16-3
Chapter 17. Working with Large Databases
    Partitioning
        Partitioning Concepts
        Table Partitioning
        Index Partitioning
        Partitioning Methods
        Partitioning Benefits
    Clustering
    Sharding
    Big Data
        Hadoop
        NoSQL and Document Databases
        Cloud Computing
Chapter 18. SQL and Big Data
    Introduction to Apache Drill
    Querying Files Using Drill
    Querying MySQL Using Drill
    Querying MongoDB Using Drill
    Drill with Multiple Data Sources
    Future of SQL
Appendix A. ER Diagram for Example Database
Appendix B. Solutions to Exercises
    Chapter 3
        Exercise 3-1
        Exercise 3-2
        Exercise 3-3
        Exercise 3-4
    Chapter 4
        Exercise 4-1
        Exercise 4-2
        Exercise 4-3
        Exercise 4-4
    Chapter 5
        Exercise 5-1
        Exercise 5-2
        Exercise 5-3
    Chapter 6
        Exercise 6-1
        Exercise 6-2
        Exercise 6-3
    Chapter 7
        Exercise 7-1
        Exercise 7-2
        Exercise 7-3
    Chapter 8
        Exercise 8-1
        Exercise 8-2
        Exercise 8-3
    Chapter 9
        Exercise 9-1
        Exercise 9-2
        Exercise 9-3
    Chapter 10
        Exercise 10-1
        Exercise 10-2
        Exercise 10-3 (Extra Credit)
    Chapter 11
        Exercise 11-1
        Exercise 11-2
    Chapter 12
        Exercise 12-1
    Chapter 13
        Exercise 13-1
        Exercise 13-2
    Chapter 14
        Exercise 14-1
        Exercise 14-2
    Chapter 15
        Exercise 15-1
        Exercise 15-2
    Chapter 16
        Exercise 16-1
        Exercise 16-2
        Exercise 16-3
Index
About the Author
Colophon