Learning SQL: Generate, Manipulate, and Retrieve Data 3rd Edition by Alan Beaulieu, ISBN-13: 978-1492057611

Description

Learning SQL: Generate, Manipulate, and Retrieve Data 3rd Edition by Alan Beaulieu, ISBN-13: 978-1492057611

[PDF eBook eTextbook] – Available Instantly

• Publisher: ‎ O’Reilly Media; 3rd edition (April 21, 2020)
• Language: ‎ English
• 377 pages
• ISBN-10: ‎ 1492057614
• ISBN-13: ‎ 978-1492057611

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:

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

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

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

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

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

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

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

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

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

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

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)

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

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

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

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

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

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

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

Conclusion

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

A. ER Diagram for Example Database

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

Alan Beaulieu has been designing, building, and implementing custom database applications for over 25 years. He is the author of Learning SQL and Mastering Oracle SQL (O’Reilly), and has written an online course on SQL for the University of California. He currently runs his own consulting company that specializes in database design and development in the fields of Financial Services and Telecommunications. Alan has a Bachelor of Science degree in Operations Research from the Cornell University School of Engineering. He lives in Massachusetts with his wife and two daughters.

What makes us different?

• Instant Download

• Always Competitive Pricing

• 100% Privacy

• FREE Sample Available

• 24-7 LIVE Customer Support