book

Learning SQL

Name: Learning SQL
Author: Alan Beaulieu
ISBN: 9780596007270

by Alan Beaulieu

August 2005

Beginner

312 pages

8h 23m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Learning SQL
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 BookConventions Used in This BookHow to Contact UsUsing Code ExamplesSafari EnabledAcknowledgments
1. A Little Background
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. Creating and Populating a Database
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. Query Primer
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. Filtering
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. Querying Multiple Tables
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. Working with Sets
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. Data Generation, Conversion, and Manipulation
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. Grouping and Aggregates
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. Subqueries
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. Joins Revisited
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. Conditional Logic
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. Transactions
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. Indexes and Constraints
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
A. ER Diagram for Example Database
B. MySQL Extensions to the SQL Language
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. Solutions to Exercises
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. Further Resources
D.1. Advanced SQLD.2. Database ProgrammingD.3. Database DesignD.4. Database TuningD.5. Database AdministrationD.6. Report Generation
About the Author
Colophon
SPECIAL OFFER: Upgrade this ebook with O’Reilly
Copyright

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596007272Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Learning SQL

by Alan Beaulieu

Chapter 6. Working with Sets

Set Theory Primer

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.