Learning SQL, 2nd Edition

Index

A note on the digital index

A link in an index entry is displayed as the section title in which that entry appears. Because some sections have multiple index markers, it is not unusual for an entry to have several links to the same section. Clicking on any link will take you directly to the place in the text in which the marker appears.

Symbols

! (exclamation mark), != (not equal to) operator, Inequality conditions

% (percent sign), wildcard character in partial string matches, Using wildcards

' ' (quotes, single)

in strings, Including single quotes
surrounding strings, String Generation

' (apostrophe) in strings, Including single quotes

( ) (parentheses)

enclosing subqueries, What Is a Subquery?
ordering query combinations in compound queries, Set Operation Precedence
using with filter conditions, Using Parentheses

< (less than) operator

scalar subqueries and, Noncorrelated Subqueries
using with all operator, The all operator

<= (less than or equal to) operator, Noncorrelated Subqueries

<> (not equal to) operator

in inequality conditions, Inequality conditions
scalar subqueries and, Noncorrelated Subqueries
using with all operator, The all operator

= (equals sign)

= null, filtering for null values, Null: That Four-Letter Word

equal to operator

scalar subqueries and, Noncorrelated Subqueries
using with all operator, The all operator
using with any operator, The any operator

in equality conditions, Equality Conditions

> (greater than) operator

scalar subqueries and, Noncorrelated Subqueries
using with all operator, The all operator

>= (greater than or equal to) operator, Noncorrelated Subqueries

\ (backslash), escaping special characters in strings, Including single quotes

_ (underscore), wildcard character in partial string matches, Using wildcards

A

abs( ) function, Handling Signed Data

aggregate functions, Grouping Concepts, Aggregate Functions–How Nulls Are Handled

count( ) function, Counting Distinct Values
exercises with, Test Your Knowledge
handling null values, How Nulls Are Handled
in having clause, Group Filter Conditions
implicit versus explicit groups, Implicit Versus Explicit Groups
listing of common functions, Aggregate Functions
using expressions as arguments, Using Expressions
where clause and, Group Filter Conditions

aggregation

selective aggregation using case expressions, Selective Aggregation
using views for data aggregation, Data Aggregation

all operator, The all operator

<> all comparisons, null values and, The all operator

alter table statements

adding or removing constraints, Constraint Creation
adding or removing indexes, Index Creation
changing storage engine, Transaction Savepoints
modifying definition of existing table, Generating numeric key data

and operator

condition evaluation with, Condition Evaluation
three-condition evaluation using and, or, Using Parentheses
three-condition evaluation using and, or, and not, Using the not Operator
using in select statement where clause, The where Clause

ANSI mode, String Generation

ANSI SQL standard, join syntax, The ANSI Join Syntax

any operator, The any operator

arithmetic functions, Performing Arithmetic Functions

arithmetic operators in filter conditions, Building a Condition

as keyword

using with column aliases, Column Aliases
using with table aliases, Defining Table Aliases

asc and desc keywords, Ascending Versus Descending Sort Order

ASCII character set, Including special characters

ascii( ) function, Including special characters

atomicity, What Is a Transaction?

auto-commit mode, Starting a Transaction

auto-increment feature in MySQL, Generating numeric key data

avg( ) function, Aggregate Functions

B

B-tree (balanced-tree) indexes, B-tree indexes
begin transaction command, Starting a Transaction
between operator, The between operator
bitmap indexes, Bitmap indexes
branch nodes (B-tree indexes), B-tree indexes

C

C language, SQL integration toolkits, SQL: A Nonprocedural Language

C#, SQL integration toolkit, SQL: A Nonprocedural Language

C++, SQL integration toolkits, SQL: A Nonprocedural Language

Cartesian products, Cartesian Product, Cross Joins–Cross Joins

cascading deletes, Cascading Constraints

cascading updates, Cascading Constraints

case expressions, What Is Conditional Logic?

examples, Case Expression Examples–Handling Null Values

checking for existence, Checking for Existence
conditional updates, Conditional Updates
division-by-zero errors, Division-by-Zero Errors
handling null values, Handling Null Values
result set transformations, Result Set Transformations
selective aggregation, Selective Aggregation

searched, Searched Case Expressions

simple, Simple Case Expressions

cast( ) function, Conversion Functions

converting strings to temporal data types, String-to-date conversions

ceil( ) function, Controlling Number Precision

char type, Character Data, Working with String Data

char( ) function, Including special characters

generating strings, Including special characters

character data types, Working with String Data

character data, MySQL database, Character Data

character sets

ASCII, Including special characters
latin1, Including special characters
sorting order or collation, String ranges

check constraints, Constraints

clauses

referencing multiple tables joined in a query, Defining Table Aliases

select statement, Query Clauses

select clause, The select Clause

select, from, and where, SQL Examples

subqueries in, Noncorrelated Subqueries

CLOB (Character Large Object) type, Working with String Data

code examples from this book, Using Code Examples

collation, String ranges

column aliases, Column Aliases

columns, The Relational Model

defined, Some Terminology
incorrect values for, Column Value Violations
multicolumn grouping, Multicolumn Grouping
query returning number of, Deployment Verification
single-column grouping, Single-Column Grouping
viewing for a table with describe command, The Bank Schema

columns view, Information_Schema

comments, SQL Examples

commit command, What Is a Transaction?

ending transactions, Ending a Transaction
issuing for savepoints, Transaction Savepoints

commits, auto-commit mode, Starting a Transaction

comparison operators

in filter conditions, Building a Condition
using scalar subqueries with, Noncorrelated Subqueries
using with all operator, The all operator
using with any operator, The any operator

complexity, hiding with use of views, Hiding Complexity

compound key, The Relational Model

compound queries, Set Theory in Practice

concat( ) function, Including special characters

appending characters to strings, String functions that return strings
building strings from individual pieces of data, String functions that return strings

conditional logic, Conditional Logic–Exercise 11-2

case expressions, What Is Conditional Logic?

examples, Case Expression Examples–Handling Null Values
searched, Searched Case Expressions
simple, Simple Case Expressions

defined, What Is Conditional Logic?

exercises in, Test Your Knowledge

conditions, filter (see filter conditions; filtering)

constraints, Constraints–Exercise 13-2

cascading, Cascading Constraints–Cascading Constraints
creating, Constraint Creation
exercises, Test Your Knowledge
getting information about primary key constraints, Schema Generation Scripts
getting information about using information_schema, Information_Schema
indexes and, Constraints and Indexes
query returning number of primary key constraints, Deployment Verification

containing statement, What Is a Subquery?

conversions

functions for, Conversion Functions
invalid date conversions causing statement errors, Invalid Date Conversions
string-to-date, String-to-date conversions

convert_tz( ) function, Temporal functions that return dates

Coordinated Universal Time (UTC), Dealing with Time Zones

correlated subqueries, Subquery Types, Correlated Subqueries

in case expressions checking existence, Checking for Existence
scalar subqueries example, Subqueries As Expression Generators
summing transactions for an account, Selective Aggregation
using exists operator, The exists Operator
using for data manipulation, Data Manipulation Using Correlated Subqueries
using with not exists operator, The exists Operator

count( ) function, Grouping Concepts, Aggregate Functions

counting banking transactions for a day (example), Cross Joins
counting distinct values, Counting Distinct Values

create index command, Index Creation

create table statements

building for MySQL table creation, Step 3: Building SQL Schema Statements
checking for well-formed, Schema Generation Scripts
creating constraints, Constraint Creation
index generated by, Index Creation
query generating, using information_schema, Schema Generation Scripts

create view statements, What Are Views?

cross joins, Cartesian Product, Cross Joins–Cross Joins

cube operations in grouping, Generating Rollups

current date or time, generating from strings, Functions for generating dates

D

data dictionary, SQL Statement Classes, Data About Data

data statements, Why Use This Book to Do It?, SQL Statement Classes

errors in table data insertion and modification, When Good Statements Go Bad

data types, character, Working with String Data

data types, MySQL, MySQL Data Types

character data, Character Data

character sets, Character sets
text types, Text data

numeric data, Numeric Data, Numeric Data

floating-point types, Numeric Data
integer types, Numeric Data
temporal types, Temporal Data

database connections, Query Mechanics

database systems, Introduction to Databases

databases, A Little Background–Some Terminology

constraints, Constraints–Exercise 13-2

creating MySQL database, Creating a MySQL Database

defined, Introduction to Databases

indexes, Indexes and Constraints–The Downside of Indexes

multiuser, Multiuser Databases

locking, Locking

nonrelational, Nonrelational Database Systems

optimizer, SQL: A Nonprocedural Language

relational model, The Relational Model

specifying database for mysql tool, Using the mysql Command-Line Tool

SQL92 join syntax, The ANSI Join Syntax

terms and definitions, Some Terminology

time zones and, Dealing with Time Zones

tools for SQL commands, SQL: A Nonprocedural Language

date type, Temporal Data

datediff( ) function, Temporal functions that return numbers

dates and times

date format components, MySQL temporal types, Temporal Data
functions generating dates from strings, Functions for generating dates
invalid date conversions, Invalid Date Conversions
MySQL temporal types, Temporal Data
string-to-date conversions, String-to-date conversions
temporal functions returning dates, Temporal functions that return dates
time zones, Dealing with Time Zones

datetime type, Temporal Data, Temporal Data

strings representing datetime values, String representations of temporal data

date_add( ) function, Temporal functions that return dates, Cross Joins

dayname( ) function, Temporal functions that return strings

DB2 Universal Database, What Is MySQL?

deadlocks, Ending a Transaction

deallocate statements, Dynamic SQL Generation

delete statements

deleting data from MySQL table, Deleting Data
using correlated subqueries, Data Manipulation Using Correlated Subqueries

deletes, cascading, Cascading Constraints

deployment verification for schema objects, Deployment Verification

desc and asc keywords, Ascending Versus Descending Sort Order

describe (desc) command, Step 3: Building SQL Schema Statements

columns in table, describing, The Bank Schema
examining views, What Are Views?

distinct keyword, Removing Duplicates, The in and not in operators

division-by-zero errors, Division-by-Zero Errors

driving table, Joining Three or More Tables

duplicates

excluding using union operator, The union Operator
removal by except and except all operators, The except Operator
removal by intersect operator, The intersect Operator
removing from query returns, Removing Duplicates
union all operator and, The union Operator

durability, What Is a Transaction?

dynamic SQL execution, Dynamic SQL Generation

E

Eastern Standard Time, Dealing with Time Zones

entities, The Relational Model

defined, Some Terminology

enum data type, MySQL, Step 3: Building SQL Schema Statements

equality conditions, Equality Conditions

case expressions and, Simple Case Expressions
correlated subqueries in, Correlated Subqueries
data modification using, Data modification using equality conditions
error from subquery returning more than one row, Noncorrelated Subqueries
subquery in, What Is a Subquery?

equi-joins, Equi-Joins Versus Non-Equi-Joins

escaping single quotes in strings, Including single quotes

except all operator, The except Operator

except operation (sets), Set Theory Primer

except operator, The except Operator

execute statements, Dynamic SQL Generation

existence, checking for, Checking for Existence

exists operator, The exists Operator

explicit groups, Implicit Versus Explicit Groups

expressions, What Is Conditional Logic?

(see also case expressions)
in filter conditions, Building a Condition
generating with subqueries, Subqueries As Expression Generators
grouping via, Grouping via Expressions
included in select clause (example), The select Clause
sorting data in select statement order by clause, Sorting via Expressions
using as arguments for aggregate functions, Using Expressions

extract( ) function, Temporal functions that return strings

returning only year portion of a date, Grouping via Expressions

F

Falcon storage engine, Transaction Savepoints

filter conditions

ANSI join syntax and, The ANSI Join Syntax
group, Grouping Concepts, Group Filter Conditions
join conditions versus, Join Conditions Versus Filter Conditions
in select statement where clause, The where Clause
subqueries in, Subqueries in Filter Conditions
in where clauses, Filtering

filtering, Filtering–Exercise 4-4

building conditions, Building a Condition

condition types, Condition Types

equality conditions, Equality Conditions
inequality conditions, Inequality conditions
matching conditions, Matching Conditions
membership conditions, Membership Conditions
modifying data with equality conditions, Data modification using equality conditions
range conditions, Range Conditions

evaluation of conditions, Condition Evaluation

using not operator, Using the not Operator
using parentheses, Using Parentheses

exercises, Test Your Knowledge

null values, Null: That Four-Letter Word

floating-point numbers, controlling precision of, Controlling Number Precision

floating-point types, MySQL, Numeric Data

floor( ) function, Controlling Number Precision

foreign key constraints, Constraints

cascading, Cascading Constraints–Cascading Constraints

foreign keys, The Relational Model

defined, Some Terminology
nonexistent key causing statement errors, Nonexistent Foreign Key
self-referencing, Self-Joins
using in joins, What Is a Join?

from clauses, SQL Examples

join order and, Joining Three or More Tables

joining three or more tables, Joining Three or More Tables

joining two tables using inner join, Inner Joins

missing, Using the mysql Command-Line Tool

on subclause, Inner Joins

ANSI join syntax in, The ANSI Join Syntax

in select statement, The from Clause–Defining Table Aliases

in select statements

table aliases, Defining Table Aliases
table links, Table Links
tables defined by, Tables

select statements for updatable views, Updatable Views

subqueries in, Subqueries As Data Sources, Subqueries As Data Sources

using in joins, Cartesian Product

using subclause, Inner Joins

using with select clause in select statement, The select Clause

full-text indexes, Text indexes

functions

advantages of case expressions over for conditional logic, The Case Expression

aggregate, Aggregate Functions–How Nulls Are Handled

built-in function in select clause (example), The select Clause

conversion, Conversion Functions

generating dates from strings, Functions for generating dates

numeric

controlling number precision, Controlling Number Precision
handling signed data, Handling Signed Data
single-argument, Performing Arithmetic Functions

string functions returning numbers, String functions that return numbers

string functions returning strings, String functions that return strings–String functions that return strings

temporal functions returning dates, Temporal functions that return dates

temporal functions returning numbers, Temporal functions that return numbers

temporal functions returning strings, Temporal functions that return strings

G

GMT (Greenwich Mean Time), Dealing with Time Zones

group by clauses, Grouping Concepts

explicit groups in, Implicit Versus Explicit Groups
in select statements, The group by and having Clauses
where clause and, Grouping Concepts

grouping, Grouping and Aggregates–Grouping Concepts, Generating Groups–Generating Rollups

exercises in, Test Your Knowledge
generating rollups, Generating Rollups, Data fabrication
group filter conditions, Group Filter Conditions
implicit versus explicit groups, Implicit Versus Explicit Groups
multicolumn groups, Multicolumn Grouping
single-column groups, Single-Column Grouping
using expressions, Grouping via Expressions
using subqueries, Data fabrication, Task-oriented subqueries
with cube option, Generating Rollups

H

having clauses

aggregate functions in, Group Filter Conditions
group filter conditions in, Grouping Concepts, Group Filter Conditions
in select statements, The group by and having Clauses
subqueries in filter conditions, Subqueries in Filter Conditions

hierarchical database systems, Nonrelational Database Systems

high-cardinality data, Bitmap indexes

I

if-then-else statements, The Case Expression

implicit groups, Implicit Versus Explicit Groups

in operator, Membership Conditions

searching for value within set of values, The in and not in operators
using with subquery, The in and not in operators

indexes, Indexes and Constraints–The Downside of Indexes

B-tree (balanced-tree), B-tree indexes
bitmap, Bitmap indexes
constraints and, Constraints and Indexes
creating, Index Creation
exercises, Test Your Knowledge
multicolumn, Multicolumn indexes
overhead of, strategy for minimizing, The Downside of Indexes
query returning number of, Deployment Verification
removing via alter table command, Index Creation
retrieving information about using information_schema, Information_Schema
text, Text indexes
unique, Unique indexes
uses of, How Indexes Are Used
viewing for a table, Index Creation

inequality conditions, Inequality conditions

scalar subquery in, Noncorrelated Subqueries

information_schema objects, Information_Schema–Information_Schema

columns view, Dynamic SQL Generation
information about constraints, Information_Schema
information about table indexes, Information_Schema
views available in MySQL 6.0, Information_Schema
views in, Information_Schema

inner joins, Inner Joins

exercises in, Test Your Knowledge

INNER keyword, Inner Joins

InnoDB storage engine, Transaction Savepoints

insert statements, Inserting Data, The insert statement–The insert statement

errors from nonexistent foreign key, Nonexistent Foreign Key
errors from nonunique primary key, Nonunique Primary Key
example, SQL Examples
important considerations, The insert statement
inserting data through views, Updating Complex Views
noncorrelated scalar subqueries generating values for, Subqueries As Expression Generators

insert( ) function, String functions that return strings

integer types, MySQL, Numeric Data

integration toolkits for SQL, SQL: A Nonprocedural Language

intermediate result sets, Joining Three or More Tables

intersect all operator, The intersect Operator

intersect operator, The intersect Operator

precedence of, Set Operation Precedence

intersection operation (sets), Set Theory Primer

intervals

adding to dates, Temporal functions that return dates
common interval types, Temporal functions that return dates
determing number between two dates, Temporal functions that return numbers
using with extract( ) function, Temporal functions that return strings

is not null operator, Null: That Four-Letter Word

is null operator, Null: That Four-Letter Word

J

Java

SQL integration toolkits, SQL: A Nonprocedural Language
SQL statements and, Dynamic SQL Generation

join conditions

ANSI join syntax and, The ANSI Join Syntax
filter conditions versus, Join Conditions Versus Filter Conditions

join keyword, Cartesian Product

joins, Querying Multiple Tables, Joins Revisited–Exercise 10-4 (Extra Credit)

ANSI syntax for, The ANSI Join Syntax

cross joins, Cartesian Product, Cross Joins–Cross Joins

defined, What Is a Join?

equi- versus non-equi-joins, Equi-Joins Versus Non-Equi-Joins–Equi-Joins Versus Non-Equi-Joins

exercises, Test Your Knowledge

exercises in, Test Your Knowledge

inner joins, Inner Joins

join versus filter conditions, Join Conditions Versus Filter Conditions

joining three or more tables, Joining Three or More Tables–Using the Same Table Twice

order of joins, Joining Three or More Tables
specifying join order, Joining Three or More Tables
using same table twice, Using the Same Table Twice
using subqueries as tables, Using Subqueries As Tables

joining views to other tables or views, What Are Views?

natural joins, Natural Joins

outer joins, Outer Joins–Self Outer Joins

left versus right, Left Versus Right Outer Joins
self, Self Outer Joins
three-way, Three-Way Outer Joins

self-joins, Self-Joins

specifying type of join, Inner Joins

L

last_day( ) function, Temporal functions that return dates

latin1 character set, Character sets

leaf nodes (B-tree indexes), B-tree indexes

left outer joins, Left Versus Right Outer Joins

left( ) function, Matching Conditions

length( ) function, String functions that return numbers

like operator, Using wildcards

comparing strings, String functions that return numbers
regular expressions and, Using regular expressions

limit clauses, Ascending Versus Descending Sort Order

links, table, Table Links

locate( ) function, String functions that return numbers

locking, Locking

granularities of locks, Lock Granularities
storage engines and, Transaction Savepoints

low-cardinality data, Bitmap indexes

M

matching conditions, Matching Conditions

using multiple search expressions, Using wildcards

using regular expressions, Using regular expressions

using wildcards, Using wildcards

example search expressions, Using wildcards

max( ) function, Aggregate Functions

membership conditions, Membership Conditions

generating using subqueries, Using subqueries
using not in operator, Using not in

metadata, SQL Statement Classes, Metadata–Exercise 15-2

exercises in, Test Your Knowledge
information included in, Data About Data
information_schema objects, Information_Schema–Information_Schema
publishing by database servers, Data About Data
using in deployment verification, Deployment Verification
using in dynamic SQL generation, Dynamic SQL Generation–Exercise 15-2
using in schema generation scripts, Working with Metadata–Schema Generation Scripts

min( ) function, Aggregate Functions

mod( ) function, Performing Arithmetic Functions

mode, checking and changing for MySQL, String Generation

modulo operator, Performing Arithmetic Functions

multibyte character sets, Character sets

multiparent hierarchy, Nonrelational Database Systems

MySQL, Why Use This Book to Do It?

bank schema (example), The Bank Schema–The Bank Schema

constraint generation, indexes and, Constraints and Indexes

creating a sample database, Creating a MySQL Database

data types, MySQL Data Types

character, Character Data
numeric, Numeric Data

downloading and installing MySQL 6.0 server, Creating a MySQL Database

dynamic SQL execution, Dynamic SQL Generation

except operator and, The except Operator

grouping, with cube option not supported, Generating Rollups

if( ) function, The Case Expression

indexes, Index Creation

information_schema database, Data About Data

intersect operator and, The intersect Operator

loading time zone data, String representations of temporal data

locking, Locking

mysql command-line tool, SQL: A Nonprocedural Language

overview of, What Is MySQL?

populating and modifying tables, Populating and Modifying Tables–Deleting Data

deleting data, Deleting Data
inserting data, Inserting Data–Updating Data
updating data, Updating Data

set operation precedence, Set Operation Precedence

specifying join order, Joining Three or More Tables

storage engines, Transaction Savepoints

table creation, Table Creation–Step 3: Building SQL Schema Statements

building SQL schema statements, Step 3: Building SQL Schema Statements

time zone settings, Dealing with Time Zones

transactions

disabling auto-commit mode, Starting a Transaction
error message for deadlock, Ending a Transaction
starting, Starting a Transaction

updatable views, Updatable Views

mysql command-line tool

--xml option, The insert statement
result sets returned by, Query Mechanics
running create table statement, Step 3: Building SQL Schema Statements
using, Using the mysql Command-Line Tool

N

natural joins, Natural Joins

natural key, The Relational Model

network database systems, Nonrelational Database Systems

non-equi-joins, Equi-Joins Versus Non-Equi-Joins

noncorrelated subqueries, Subquery Types–Multicolumn Subqueries

multiple-column, Multicolumn Subqueries

multiple-row, single-column, Multiple-Row, Single-Column Subqueries–The any operator

using all operator, The all operator
using any operator, The any operator

nonprocedural languages, SQL: A Nonprocedural Language

normalization

defined, The Relational Model
table design in MySQL and, Step 2: Refinement

not exists operator, The exists Operator, Data Manipulation Using Correlated Subqueries

not in operator, Using not in

<> all versus, The all operator
using in subquery, The in and not in operators

not operator, using with filter conditions, Using the not Operator

null values, Step 3: Building SQL Schema Statements

comparisons with not in and <> all operators, The all operator
filtering, Null: That Four-Letter Word
handling using case expressions, Handling Null Values
handling when performing aggregations, How Nulls Are Handled
subqueries generating data for columns allowing null values, Subqueries As Expression Generators

numeric data, Working with Numeric Data–Handling Signed Data