Learning SQL by Beaulieu, Alan -- Read -- Imperial Library of Trantor

Index

Learning SQL

Alan Beaulieu

Editor

Mary Treseler

SPECIAL OFFER: Upgrade this ebook with O’Reilly A Note Regarding Supplemental Files Preface Why Learn SQL? Why Use This Book to Do It? Structure of This Book Conventions Used in This Book

Note Warning

How to Contact Us Using Code Examples Safari® Books Online

Note

Acknowledgments Chapter 1. A Little Background Introduction to Databases

Nonrelational Database Systems

Note

The Relational Model

Note Note

Some Terminology

What Is SQL?

SQL Statement Classes SQL: A Nonprocedural Language

Note

SQL Examples

Note

What Is MySQL? What’s in Store Chapter 2. Creating and Populating a Database Creating a MySQL Database

Warning

Using the mysql Command-Line Tool MySQL Data Types

Character Data

Note Character sets Text data Note

Numeric Data Temporal Data

Note

Table Creation

Step 1: Design Step 2: Refinement Step 3: Building SQL Schema Statements

Note

Populating and Modifying Tables

Inserting Data

Generating numeric key data The insert statement

Updating Data Deleting Data

When Good Statements Go Bad

Nonunique Primary Key Nonexistent Foreign Key

Note

Column Value Violations Invalid Date Conversions

Note

The Bank Schema Chapter 3. Query Primer Query Mechanics

Note

Query Clauses The select Clause

Column Aliases Removing Duplicates

Warning

The from Clause

Tables

Subquery-generated tables Views

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 Expressions 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

Inequality conditions Data modification using equality conditions Note

Range Conditions

The between operator String ranges

Membership Conditions

Using subqueries Using not in

Matching Conditions

Using wildcards Using regular expressions

Null: That Four-Letter Word

Note

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 Equi-Joins Versus Non-Equi-Joins Join Conditions Versus Filter Conditions 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

Note

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, Conversion, and Manipulation Working with String Data

String Generation

Including single quotes Note Including special characters Note Note

String Manipulation

String functions that return numbers Warning Note Note String functions that return strings

Working with Numeric Data

Performing Arithmetic Functions

Note

Controlling Number Precision

Note

Handling Signed Data

Working with Temporal Data

Dealing with Time Zones

Note

Generating Temporal Data

String representations of temporal data String-to-date conversions Functions for generating dates Note

Manipulating Temporal Data

Temporal functions that return dates Note Temporal functions that return strings Note Temporal functions that return numbers Note

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

Note Note

Group Filter Conditions

Warning

Test Your Knowledge

Exercise 8-1 Exercise 8-2 Exercise 8-3 Exercise 8-4 (Extra Credit)

Chapter 9. Subqueries What Is a Subquery? Subquery Types Noncorrelated Subqueries

Multiple-Row, Single-Column Subqueries

The in and not in operators The all operator Note The any operator Note

Multicolumn Subqueries

Correlated Subqueries

Note The exists Operator Data Manipulation Using Correlated Subqueries

When to Use Subqueries

Subqueries As Data Sources

Data fabrication Task-oriented subqueries

Subqueries in Filter Conditions Subqueries As Expression Generators

Subquery Wrap-up Test Your Knowledge

Exercise 9-1 Exercise 9-2 Exercise 9-3 Exercise 9-4

Chapter 10. Joins Revisited Outer Joins

Left Versus Right Outer Joins Three-Way Outer Joins Self Outer Joins

Cross Joins Natural Joins Test Your Knowledge

Exercise 10-1 Exercise 10-2 Exercise 10-3 Exercise 10-4 (Extra Credit)

Chapter 11. Conditional Logic What Is Conditional Logic? The Case Expression

Searched Case Expressions Simple Case Expressions

Case Expression Examples

Result Set Transformations

Note

Selective Aggregation 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?

Note Starting a Transaction

Note

Ending a Transaction Transaction Savepoints

Test Your Knowledge

Exercise 12-1

Chapter 13. Indexes and Constraints Indexes

Index Creation

Note Note Unique indexes Note Multicolumn indexes

Types of Indexes

B-tree indexes Bitmap indexes Text indexes

How Indexes Are Used

Note Note

The Downside of Indexes

Constraints

Note Constraint Creation Constraints and Indexes Cascading Constraints

Test Your Knowledge

Exercise 13-1 Exercise 13-2

Chapter 14. Views What Are Views? Why Use Views?

Data Security

Note

Data Aggregation Hiding Complexity Joining Partitioned Data

Updatable Views

Updating Simple Views Updating Complex Views

Note

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

Note

Test Your Knowledge

Exercise 15-1 Exercise 15-2

Appendix A. ER Diagram for Example Database Appendix B. MySQL Extensions to the SQL Language Extensions to the select Statement

The limit Clause

Combining the limit clause with the order by clause Note The limit clause’s optional second parameter Ranking queries

The into outfile Clause

Note Note

Combination Insert/Update Statements Ordered Updates and Deletes

Note

Multitable Updates and Deletes

Note Note

Appendix C. Solutions to Exercises Chapter 3

3-1 3-2 3-3 3-4

Chapter 4

4-1 4-2 4-3 4-4

Chapter 5

5-1 5-2 5-3

Chapter 6

6-1 6-2 6-3

Chapter 7

7-1 7-2 7-3

Chapter 8

8-1 8-2 8-3 8-4 (Extra Credit)

Chapter 9

9-1 9-2 9-3 9-4

Chapter 10

10-1 10-2 10-3 10-4 (Extra Credit)

Chapter 11

11-1 11-2

Chapter 12

12-1

Chapter 13

13-1 13-2

Chapter 14

14-1 14-2

Chapter 15

15-1 15-2

Index

A note on the digital index Symbols A B C D E F G H I J L M N O P Q R S T U V W X Y

About the Author Colophon SPECIAL OFFER: Upgrade this ebook with O’Reilly

← Prev
Back
Next →

← Prev
Back
Next →