book

Learning SQL

by Alan Beaulieu

August 2005

Beginner

312 pages

8h 23m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Why Learn SQL?Why Use This Book to Do It?Structure of This BookConventions Used in This BookHow to Contact UsUsing Code ExamplesSafari EnabledAcknowledgments
1.1. Introduction to Databases1.1.1. Nonrelational Database Systems1.1.2. The Relational Model1.1.3. Some Terminology1.2. What Is SQL?1.2.1. SQL Statement Classes1.2.2. SQL: A Nonprocedural Language1.2.3. SQL Examples1.3. What Is MySQL?1.4. What's in Store
2.1. Creating a MySQL Database2.2. Using the mysql Command-Line Tool2.3. MySQL Data Types2.3.1. Character Data2.3.1.1. Character sets2.3.1.2. Text data2.3.2. Numeric Data2.3.3. Temporal Data2.4. Table Creation2.4.1. Step 1: Design2.4.2. Step 2: Refinement2.4.3. Step 3: Building SQL Schema Statements2.5. Populating and Modifying Tables2.5.1. Inserting Data2.5.1.1. Generating numeric key data2.5.1.2. The insert statement2.5.2. Updating Data2.5.3. Deleting Data2.6. When Good Statements Go Bad2.6.1. Nonunique Primary Key2.6.2. Nonexistent Foreign Key2.6.3. Column Value Violations2.6.4. Invalid Date Conversions2.7. The Bank Schema
3.1. Query Mechanics3.2. Query Clauses3.3. The select Clause3.3.1. Column Aliases3.3.2. Removing Duplicates3.4. The from Clause3.4.1. Tables3.4.1.1. Subquery-generated tables3.4.1.2. Views3.4.2. Table Links3.4.3. Defining Table Aliases3.5. The where Clause3.6. The group by and having Clauses3.7. The order by Clause3.7.1. Ascending Versus Descending Sort Order3.7.2. Sorting via Expressions3.7.3. Sorting via Numeric Placeholders3.8. Exercises3.8.1. 3-13.8.2. 3-23.8.3. 3-33.8.4. 3-4
4.1. Condition Evaluation4.1.1. Using Parentheses4.1.2. Using the not Operator4.2. Building a Condition4.3. Condition Types4.3.1. Equality Conditions4.3.1.1. Inequality conditions4.3.1.2. Data modification using equality conditions4.3.2. Range Conditions4.3.2.1. The between operator4.3.2.2. String ranges4.3.3. Membership Conditions4.3.3.1. Using subqueries4.3.3.2. Using not in4.3.4. Matching Conditions4.3.4.1. Using wildcards4.3.4.2. Using regular expressions4.4. NULL: That Four-Letter Word4.5. Exercises4.5.1. 4-14.5.2. 4-24.5.3. 4-34.5.4. 4-4
5.1. What Is a Join?5.1.1. Cartesian Product5.1.2. Inner Joins5.1.3. The ANSI Join Syntax5.2. Joining Three or More Tables5.2.1. Using Subqueries as Tables5.2.2. Using the Same Table Twice5.3. Self-Joins5.4. Equi-Joins Versus Non-Equi-Joins5.5. Join Conditions Versus Filter Conditions5.6. Exercises5.6.1. 5-15.6.2. 5-25.6.3. 5-3
6.1. Set Theory Primer6.2. Set Theory in Practice6.3. Set Operators6.3.1. The union Operator6.3.2. The intersect Operator6.3.3. The except Operator6.4. Set Operation Rules6.4.1. Sorting Compound Query Results6.4.2. Set Operation Precedence6.5. Exercises6.5.1. 6-16.5.2. 6-26.5.3. 6-3

7.1. Working with String Data7.1.1. String Generation7.1.1.1. Including single quotes7.1.1.2. Including special characters7.1.2. String Manipulation7.1.2.1. String functions that return numbers7.1.2.2. String functions that return strings7.2. Working with Numeric Data7.2.1. Performing Arithmetic Functions7.2.2. Controlling Number Precision7.2.3. Handling Signed Data7.3. Working with Temporal Data7.3.1. Dealing with Time Zones7.3.2. Temporal Data Generation7.3.2.1. String representations of temporal data7.3.2.2. String-to-date conversions7.3.2.3. Functions for generating dates7.3.3. Temporal Data Manipulation7.3.3.1. Temporal functions that return dates7.3.3.2. Temporal functions that return strings7.3.3.3. Temporal functions that return numbers7.4. Conversion Functions7.5. Exercises7.5.1. 7-17.5.2. 7-27.5.3. 7-3
8.1. Grouping Concepts8.2. Aggregate Functions8.2.1. Implicit Versus Explicit Groups8.2.2. Counting Distinct Values8.2.3. Using Expressions8.2.4. How Nulls Are Handled8.3. Generating Groups8.3.1. Single-Column Grouping8.3.2. Multi-Column Grouping8.3.3. Grouping via Expressions8.3.4. Generating Rollups8.4. Group Filter Conditions8.5. Exercises8.5.1. 8-18.5.2. 8-28.5.3. 8-38.6. 8-4 (Extra Credit)
9.1. What Is a Subquery?9.2. Subquery Types9.3. Noncorrelated Subqueries9.3.1. Multiple-Row, Single-Column Subqueries9.3.1.1. The in operator9.3.1.2. The all operator9.3.1.3. The any operator9.3.2. Multicolumn Subqueries9.4. Correlated Subqueries9.4.1. The Exists Operator9.4.2. Data Manipulation Using Correlated Subqueries9.5. When to Use Subqueries9.5.1. Subqueries as Data Sources9.5.1.1. Table fabrication9.5.1.2. Task-oriented subqueries9.5.2. Subqueries in Filter Conditions9.5.3. Subqueries as Expression Generators9.6. Subquery Wrap-up9.7. Exercises9.7.1. 9-19.7.2. 9-29.7.3. 9-39.7.4. 9-4
10.1. Outer Joins10.1.1. Left Versus Right Outer Joins10.1.2. Three-Way Outer Joins10.1.3. Self Outer Joins10.2. Cross Joins10.3. Natural Joins10.4. Exercises10.4.1. 10-110.4.2. 10-210.4.3. 10-310.4.4. 10-4 (Extra Credit)
11.1. What Is Conditional Logic?11.2. The Case Expression11.2.1. Searched Case Expressions11.2.2. Simple Case Expressions11.3. Case Expression Examples11.3.1. Result Set Transformations11.3.2. Selective Aggregation11.3.3. Checking for Existence11.3.4. Division by Zero Errors11.3.5. Conditional Updates11.3.6. Handling Null Values11.4. Exercises11.4.1. 11-111.4.2. 11-2
12.1. Multiuser Databases12.2. What Is a Transaction?12.2.1. Starting a Transaction12.2.2. Ending a Transaction12.2.3. Transaction Savepoints
13.1. Indexes13.1.1. Index Creation13.1.1.1. Unique indexes13.1.1.2. Multi-column indexes13.1.2. Types of Indexes13.1.2.1. B-tree indexes13.1.2.2. Bitmap indexes13.1.2.3. Text indexes13.1.3. How Indexes Are Used13.1.4. The Downside of Indexes13.2. Constraints13.2.1. Constraint Creation13.2.2. Constraints and Indexes13.2.3. Cascading Constraints
B.1. Select ExtensionsB.1.1. The limit ClauseB.1.1.1. Combining the limit clause with the order by clauseB.1.1.2. limit's optional second parameterB.1.1.3. Ranking queriesB.1.2. The into outfile ClauseB.2. Combination Insert/Update StatementsB.3. Ordered Updates and DeletesB.4. Multitable Updates and Deletes
C.1. Chapter 3C.1.1. 3-1C.1.2. 3-2C.1.3. 3-3C.1.4. 3-4C.2. Chapter 4C.2.1. 4-1C.2.2. 4-2C.2.3. 4-3C.2.4. 4-4C.3. Chapter 5C.3.1. 5-1C.3.2. 5-2C.3.3. 5-3C.4. Chapter 6C.4.1. 6-1C.4.2. 6-2C.4.3. 6-3C.5. Chapter 7C.5.1. 7-1C.5.2. 7-2C.5.3. 7-3C.6. Chapter 8C.6.1. 8-1C.6.2. 8-2C.6.3. 8-3C.7. 8-4 (Extra Credit)C.8. Chapter 9C.8.1. 9-1C.8.2. 9-2C.8.3. 9-3C.8.4. 9-4C.9. Chapter 10C.9.1. 10-1C.9.2. 10-2C.9.3. 10-3C.9.4. 10-4 (Extra Credit)C.10. Chapter 11C.10.1. 11-1C.10.2. 11-2
D.1. Advanced SQLD.2. Database ProgrammingD.3. Database DesignD.4. Database TuningD.5. Database AdministrationD.6. Report Generation

Content preview from Learning SQL

Chapter 6. Working with Sets

Although you can interact with the data in a database one row at a time, relational databases are really all about sets. You have seen how tables can be created via queries or subqueries, made persistent via insert statements, and brought together via joins; this chapter will explore how multiple tables can be combined using various set operators.

Set Theory Primer

In many parts of the world, basic set theory is included in elementary-level math curriculums. Perhaps you recall looking at something like what is shown in Figure 6-1.

Figure 6-1. The union operation

The shaded area in Figure 6-1 represents the union of sets A and B, which is the combination of the two sets (with any overlapping regions included only once). Is this starting to look familiar? If so, then you'll finally get a chance to put that knowledge to use; if not, don't worry, because it's easy to visualize using a couple of diagrams.

Using circles to represent two data sets (A and B), imagine a subset of data that is common to both sets; this common data is represented by the overlapping area shown in Figure 6-1. Since set theory is rather uninteresting without an overlap between data sets, I will use the same diagram to illustrate each set operation. There is another set operation that is concerned only with the overlap between two data sets; this operation is known as the intersection and ...