Index

Symbols

* (asterisk), 195

|| (concatenation operator), 138

> (greater than), 186–188

>= (greater than or equal to), 186

< (less than), 186–188

<= (less than or equal to), 186

() (parentheses), 144, 210–211, 216

CASE (conditional expressions), 684

% (percent sign), 195

? (question mark), 195

_ (underscore), 195

Numbers

2016 SQL Standard, 4

A

ABS, 142

Access, CASE (conditional expressions), 684

Actian, 74

adding sorting specifications to UNION, 382

aggregate expressions, 445

aggregate functions, 444–446, 798–799

AVG, calculating mean values, 451–452

Bowling League Database, 844–846

COUNT, 406–408, 446

COUNT (value expression), 448–449

COUNT(*), 446

counting all the rows, 446–448

in filters, 457–459

grouping data, 477–478

MAX, 406–408

finding largest values, 452–454

MIN, finding smallest values, 454–455

Null values, 445

OVER(), 803–805

Sales Orders Database, 847–850

sample statements, 459–466

Bowling League Database, 463–465

Entertainment Agency Database, 461–462

Recipes Database, 465–466

Sales Orders Database, 460–461

School Scheduling Database, 462–463

School Scheduling Database, 851–852

subqueries, 457–458

SUM, computing totals, 450–451

syntax diagrams, 444

using more than one function, 455–457

windows functions, 827–834

alias names, assigning to tables, INNER JOIN, 282–284

ALL, 371, 375, 417–420

all rows, deleting with DELETE statement, 605–607

alphabetical order, sorting by, 108

American National Standards Institute (ANSI), 2, 75

evolution of SQL/86, 76–79

analytical databases, 16

AND, 205–206

finding multiple matches in the same table

EXISTS, 646–648

GROUP BY, 648–652

HAVING, 648–652

IN, 644–646

INNER JOIN, 642–644

sample statements, 652–657

Bowling League Database, 663–668

Entertainment Agency Database, 657–659

Recipes Database, 668–671

Sales Orders Database, 653–657

School Scheduling Database, 659–663

sets with multiple AND criteria, 628–629

using with OR, 209–211

ANSI (American National Standards Institute), 2, 75

evolution of SQL/86, 76–79

ANSI NCITS-H2, 80

ANSI/ISO standards, 76–79

ANY, 417–420

APPROXIMATE NUMERIC, 129

arithmetic operations

IBM DB2, 890–908

Microsoft Access, 893

Microsoft SQL Server, 895

MySQL, 898

Oracle, 902

PostgreSQL, 904

artificial primary keys, 60

assigning correlation names to tables, INNER JOIN, 282–284

asterisks (*), 195

shortcuts, 102–103

ASYMMETRIC, 189–190

attributes. See columns

AVG, calculating mean values, 451–452

avoiding HAVING COUNT trap, 513–518

B

base tables, views (relational databases), 23

BETWEEN comparisons, 189–190

BETWEEN predicate, 178, 189–192

checking for overlapping ranges, 219–220

BETWEEN…AND, 189

BINARY, 128

blank spaces, 160

BOOLEAN, 129

Bowling League Database

aggregate functions, 463–465, 844–846

AND, 663–668

CASE (conditional expressions), 702–705

CUBE, 783–784

DELETE statement, 618–620

driver tables, 742–743

expressions, 169–171

GROUP BY, 496–499

GROUPING SETS, 786–787

HAVING clause, 525–526

INNER JOIN

looking for matching values, 311–314

more than two tables, 302–305

two tables, 298–299

INSERT statement, 596–597

NOT, 663–668

NTILE(), 842–843

OUTER JOIN, 359–361

search conditions, 231–232

SELECT statement, 119–120

subqueries

in expressions, 428–429

in filters, 434–435

UNION, 392–394

unlinked tables, 734–735

UPDATE statement, 565–568

C

calculated columns, 40

calculating

mean values with AVG, 451–452

row number with ROW_NUMBER, 814–818

totals on combinations using CUBE, 765–770

Call-Level Interface (CLI), 80

Cartesian product, 277–278, 325, 711–712

cascade deletion rule, 63

CASE (conditional expressions), 678

parentheses, 684

reasons for using, 678

sample statements, 692–693

Bowling League Database, 702–705

Entertainment Agency Database, 696–698

Sales Orders Database, 693–696

School Scheduling Database, 698–702

searched CASE, solving problems, 688–691

simple CASE, solving problems, 683–687

syntax, 678–682

WHERE clause, 691

CASE expression, syntax diagrams, 679

case sensitivity, 183

string comparison, 197

CAST function

changing data types, 130–132

concatenation expressions, 141

CEIL, 142

Chamberlin, Dr. Donald, 72

changing, data types, CAST function, 130–132

CHARACTER, 127

character string literals, 133–134

checking, relationships, INNER JOIN, 291–292

classic set operations versus SQL, 259–262

EXCEPT, 265–268

INTERSECT, 262–265

UNION, 268–271

clauses

FROM clause, 279–280, 343

embedding SELECT statements, 286

OUTER JOIN, 324–325, 327

CORRESPONDING clause, 373

HAVING. See HAVING clause

ORDER BY clause. See ORDER BY clause

SELECT statement, 91, 92–93

USING clause, 280–281

VALUES clause, INSERT statement, 575–578

WHERE clause. See WHERE clause

CLI (Call-Level Interface), 80

cloud servers, 83

Codd, Dr. Edgar F.16–17, 72

collating sequences, 107

column expressions, subqueries, 402–405, 422–423

column references, 275–276

syntax diagrams, 276

column restrictions, GROUP BY, 486–488

columns

calculated columns, 40

counting values in, 448–449

fine-tuning, 35

naming conventions, 35–38

structure of columns, 38–40

mixing with expressions, 481–483

multipart columns, 39

resolving, 40–43

multiple columns, updating, 541–543

multivalued columns, 39

resolving, 43–45

relational databases, 21

requesting all columns with SELECT statement, 101–103

resolving duplicate columns, 50–56

retrieving multiple columns with SELECT statement, 100–101

combining

sets, UNION, 257–259, 268–271

UNION. See UNION

commercial implementations of SQL, 83

COMMIT, 540

common values, finding with INTERSECT, 262–265

comparing string values, 181–184

comparison predicate, 178, 181

aggregate functions as filters, 457–458

comparing string values, 181–184

equality and inequality, 185–186

less than and greater than, 186–188

composite primary keys, 57

Computer Associates International, Inc., 74

computing totals with SUM, 450–451

concatenation expressions, 138–141

CAST function, 141

syntax diagrams, 138

conditional expressions. See CASE (conditional expressions)

conditions

expressing, 225–226

multiple conditions, 204–205

converting values into data types, CAST function, 130–132

correlation names, assigning to tables (INNER JOIN), 282–284

CORRESPONDING clause, 373

COUNT, 406–408, 445–446

counting all the rows, 446–448

counting values in columns or expressions, 448–449

HAVING clause, 513–518

COUNT (value expression), 446, 448–449

COUNT(*), 827–828

counting all the rows, 446–448

counting

all the rows, COUNT(*), 446–448

values in columns or expressions, 448–449

CROSS JOIN, 713

deciding when to use, 713–714

sample statements, 725–726

unlinked tables, 711–712, 715–716

CUBE, 778–779

Bowling League Database, 783–784

calculating totals on combinations, 765–770

Sales Orders Database, 784–786

D

data

fetching from two tables

with JOIN, 370

with UNION, 371

grouping, 472–474

with aggregate functions, 477–478

with GROUP BY. See GROUP BY

mixing columns and expressions, 481–483

versus information, 93–95

inserting

with INSERT statements. See INSERT statement

with SELECT expressions, 581–587

ranking, with RANK(), 818–824

splitting into quintiles, 824–827

unlinked data. See unlinked data

data types

APPROXIMATE NUMERIC, 129

BINARY, 128

BOOLEAN, 129

changing with CAST function, 130–132

CHARACTER, 127

DATETIME, 129

EXACT NUMERIC, 128

extended data types, 130

for IBM DB2, 857, 889

INTERVAL, 129, 147–148

JOIN eligible data types, 275

for Microsoft Access, 893

for Microsoft SQL Server, 895

MySQL, 897

NATIONAL CHARACTER, 128

Oracle, 901

PostgreSQL, 904

data warehouses, 19

database design, 30–31, 34–35

database structures, 34–35

database theory, 30–31

databases

analytical databases, 16

operational databases, 16

relational database model, 16–17

relational databases. See relational databases

sample databases, 9–11

types of, 15–16

DATE, 136, 138

Date, C. J., 76

date and time arithmetic expressions, 138, 146

date expressions, 147–148

time expressions, 149–150

date expressions, 147–148, 156–157

SELECT statement, time expressions, 156

syntax diagrams, 147

DATETIME, 129

datetime literals, 135–138

DB2, 74

DEFAULT keyword, 576

default pad character, 184

degree of participation, relationships, 66–68

DELETE statement, 604–605

deleting

all rows, 605–607

some rows, 607–611

sample statements, 612–613

Bowling League Database, 618–620

Entertainment Agency Database, 614–617

Sales Orders Database, 613–614

School Scheduling Database, 617–618

syntax diagrams, 604

uses for, 611–612

VALUES clause, 603–604

WHERE clause, 603–604

deleting. See also resolving

all rows with DELETE statement, 605–607

duplicate rows with SELECT statement, 50–56

some rows with DELETE statement, 607–611

deletion rule, relationships, 63–64

delimited identifiers, 37, 48

DENSE_RANK(), 820, 823–824

derived tables, 284

descending order, sorting by, 109

design, database design, 30–31, 34–35

diagrams. See also Appendix A; syntax diagrams

predicates, 681–682

Search Condition, 680

SELECT queries, 106

SELECT statement, 92

utilizing, 5–9

difference, 243, 250

problems you can solve with, 256–257

result sets, 252–256

set theory, 250–252

DISTINCT, 103–105, 415, 576

GROUP BY, 485

MAX, 454

MIN, 455

UNION, 389

driver tables, 717

Bowling League Database, 742–743

Entertainment Agency Database, 737–739

Sales Orders Database, 736–737

School Scheduling Database, 739–742

setting up, 717–720

using, 720–725

dynamic data, 16

E

eliminating. See deleting

embedding

INNER JOIN in SELECT statements, 284–286

JOINs within JOINs, 286–291

OUTER JOIN, 333–344

SELECT statements in OUTER JOINs, 330–333

Entertainment Agency Database

aggregate functions, 461–462

AND, 657–659

CASE (conditional expressions), 696–698

DELETE statement, 614–617

driver tables, 737–739

expressions, 165–167

GROUP BY, 493–494

GROUPING SETS, 787–788

HAVING clause, 521–522

INNER JOIN

looking for matching values, 309–311

more than two tables, 301–302

two tables, 296–297

INSERT statement, 593–595

NOT, 657–659

NTILE(), 843–844

OUTER JOIN, 354–355

ROW_NUMBER(), 835–837

search conditions, 228–230

SELECT statement, 116–117

subqueries

in expressions, 426–427

in filters, 432–433

UNION, 389–390

unlinked tables, 728–731

UPDATE statement, 558–562

Entry SQL, 78

equality, comparison predicate, 185–186

ESCAPE option, LIKE predicate, 198–199

Euler, Leonard, 246–247

Euler diagram, 246–247

events, relational databases, 20

evolution of SQL/86, 76–79

EXACT NUMERIC, 128

EXCEPT, 265–268

excluding rows with NOT, 201–204, 211–214

executing queries, 112

EXISTS, 420–422

AND, 654–655, 657–658

finding multiple matches in the same table, 646–648

EXP, 142

explicit values, specifying, 132–133

expressing conditions, 225–226

expressions, 126

aggregate expressions, 445

counting values in, 448–449

grouping with GROUP BY, 488–490

mixing with columns, 481–483

sample statements, 163

Bowling League Database, 169–171

Entertainment Agency Database, 165–167

Sales Orders Database, 164–165

School Scheduling Database, 167–169

SELECT clause, 150–151

concatenation expressions, 151–152

naming, 152–154

SELECT expression, 372

SELECT statement

date expressions, 156–157

mathematical expressions, 154–156

subqueries, sample statements, 425–430

types of

data, 127–130

concatenation expressions, 138–141

date and time arithmetic expressions, 138, 146–150

mathematical expressions, 138, 142–146

UPDATE expressions, subqueries, 548–551

value expressions, 157–159

syntax diagrams, 549

extended data types, 130

eXtensible Markup Language (XML), 19

extensions, SQL/92, 79

F

Federal Information Processing Standard (FIPS), 80

fetching data

from two tables with JOIN, 370

from two tables with UNION, 371

fields. See columns

filter rows, subqueries, 543–546

filtering

deciding between WHERE and HAVING, 510–513

grouped data, 506–510

filters

aggregate functions, 457–459

subqueries, 408–411

subqueries as, 423–424

sample statements, 430–437

finding

common values, INTERSECT, 262–265

largest values with MAX, 452–454

matching values with INNER JOINs, 293–294

sample statements, 306–316

missing values

EXCEPT, 265–268

OUTER JOIN, 349

multiple matches in the same table

EXISTS, 646–648

GROUP BY, 648–652

HAVING, 648–652

IN, 644–646

INNER JOIN, 642–644

partially matched information, OUTER JOIN, 349–350

related rows, INNER JOIN, 293

smallest values with MIN, 454–455

fine-tuning

columns, 35

naming conventions, 35–38

resolving multipart columns, 40–43

resolving multivalued columns, 43–45

structure of columns, 38–40

tables, 46

naming conventions, 46–48

relationships, 60–63

relationships, deletion rule, 63–64

relationships, participation, 64–68

resolving duplicate columns, 50–56

structure of tables, 48–50

FIPS (Federal Information Processing Standard), 80

FLOOR, 142

foreign keys, relational databases, 22–23

FROM clause, 279–280, 343

embedding, SELECT statements, 286

INNER JOIN, 277

OUTER JOIN, 324–325, 327

SELECT statement, 92, 331

FULL OUTER JOIN, 344–347

non-key values, 347–348

syntax, 344–347

syntax diagrams, 344

Full SQL, 79

functions

aggregate functions. See aggregate functions

CAST function, 130–132

IBM DB2, 890–892

Microsoft Access, 893–894

Microsoft SQL Server, 896–897

MySQL, 898–901

Oracle, 902–903

future of SQL (Structured Query Language), 83–84

G

generating primary key values with INSERT statements, 578–580

greater than (>), 186–188

greater than or equal to (>=), 186

GROUP BY, 475, 692, 725, 757, 761

column restrictions, 486–488

finding multiple matches in the same table, 648–652

grouping expressions, 488–490

mixing columns and expressions, 481–483

versus OVER(), 800

sample statements, 491–501

Bowling League Database, 496–499

Entertainment Agency Database, 493–494

Recipes Database, 499–500

Sales Orders Database, 491–492

School Scheduling Database, 495–496

SELECT statement, 93

sets with multiple NOT criteria, 638–641

simulating SELECT DISTINCT statements, 484–485

in subqueries in WHERE clauses, 483–484

syntax, 475–481, 753

syntax diagrams, 475

uses for, 490–491

grouped data, filtering, 506–510

grouping

calculating totals on combinations with CUBE, 765–770

creating a union of totals with GROUPING SETS, 771–775

CUBE, 778–779

data, 472–474

aggregate functions, 477–478

GROUP BY. See GROUP BY

mixing columns and expressions, 481–483

expressions, with GROUP BY, 488–490

GROUPING SETS, 775–776

ROLLUP, 777–778

sample statements, 780–781

CUBE, 783–786

GROUPING SETS, 786–788

ROLLUP, 781–783

in sub-groups, 750–753

totals in hierarchies, ROLLUP, 754–765

variations on techniques, 775–780

GROUPING function, 758

GROUPING SETS, 775–776

Bowling League Database, 786–787

creating union of totals, 771–775

Entertainment Agency Database, 787–788

H

HAVING

filtering, 510–513

grouped data, 508–510

finding multiple matches in the same table, 648–652

sample statements, 519–527

Bowling League Database, 525–526

Entertainment Agency Database, 521–522

Recipes Database, 526–527

Sales Orders Database, 520–521

School Scheduling Database, 522–525

SELECT statement, 93

sets with multiple NOT criteria, 638–641

uses for, 518–519

HAVING COUNT trap, avoiding, 513–518

Hernandez, Mike, 17

hierarchies, totals, ROLLUP, 754–765

history of SQL (Structured Query Language)

commercial implementations, 83

early vendor implementations, 73–74

origins of, 72–73

standards, 75–76

I

IBM, 18, 19, 74

early vendor implementations, 73–74

System R, 72, 73

IBM DB2

arithmetic operations, 890–908

data types, 857, 889

functions, 890–892

Identity data type, 579

identifiers

delimited identifiers, 37, 48

regular identifiers, 37, 48

Identity data type, 579

IN, finding multiple matches in the same table, 644–646

IN predicate, 178

membership condition, 192–194

for subqueries, 411–417

INCITS DM32.2, 80

inequality, comparison predicate, 185–186

information

versus data, 93–95

sorting with SELECT statement, 105–107

Ingres, 74

INGRES (Interactive Graphics Retrieval System), 18

Inmon, William H., 19

INNER JOIN, 274, 713

assigning correlation names to tables, 282–284

FROM clause, 277

embedding

JOINs within JOINs, 286–291

SELECT statements, 284–286

finding

matching values, 293–294

multiple matches in the same table, 642–644

related rows, 293

relationships, checking, 291–292

sample statements, 295

looking for matching values, 306–316

more than two tables, 300–306

two tables, 295–300

syntax, 276

syntax diagrams, 277

tables, 277–282

INSERT INTO, 576

INSERT statement, 573–575

generating primary key values, 578–580

inserting values, 575–578

sample statements, 588–589

Bowling League Database, 596–597

Entertainment Agency Database, 593–595

Sales Orders Database, 589–592

School Scheduling Database, 595–596

SELECT expression, 581

syntax diagrams with VALUES clause, 575

uses for, 587–588

inserting

data

with INSERT statement. See INSERT statement

with SELECT expressions, 581–587

values, with INNER JOINs, 575–578

Interactive Graphics Retrieval System (INGRES), 18

Intermediate SQL, 78

International Organization for Standardization. See ISO (International Organization for Standardization)

INTERSECT, 262–265

intersection, 243, 244

problems you can solve with, 249–250

result sets, 246–249

set theory, 244–245

INTERVAL, 129, 147–148

IS NULL, 634–635

IS NULL predicate, 178

ISO (International Organization for Standardization), 2, 76

evolution of SQL/86, 76–79

J

JOIN, 273–274

determining what is legal to join, 275

embedding within JOINs, 286–291

OUTER JOIN, 333–344

fetching data from two tables, 370

INNER JOIN. See INNER JOIN

NATURAL JOIN, 329–330

OUTER JOIN. See OUTER JOIN

UNION JOIN, 348

UPDATE clause, 546–548

JOIN eligible data types, 275

K

keys

primary keys, tables, 56–60

relational databases, 22–23

keywords

DATE, 138

DEFAULT keyword, 576

DISTINCT, 103–105, 576

GROUP BY, 93

HAVING, 93

INSERT INTO, 576

special predicate keywords

ALL, 417–420

ANY, 417–420

EXISTS, 420–422

IN, 411–417

SOME, 417–420

TIME, 138

TOP, 111

VALUES, 576

WHERE, 93

L

largest values, finding with MAX, 452–454

LEFT OUTER JOIN, 323–324, 332

less than (<), 186–188

less than or equal to (<=), 186

LIKE predicate, 178

ESCAPE option, 198–199

pattern match condition, 194–199

linking

multiple tables, embedding JOINs within JOINs, 286–291

SELECT statements with INTERSECT, 265

linking tables, 44–45

literal values, 132–133

character string literals, 133–134

datetime literals, 135–138

numeric literals, 135

LN, 142

M

mandatory participation, 64

many-to-many relationships, 61–62

relational databases, 27–29

matches, finding multiple matches in the same table

EXISTS, 646–648

GROUP BY, 648–652

HAVING, 648–652

IN, 644–646

INNER JOIN, 642–644

matching values, finding with INNER JOINs, 293–294

sample statements, 306–316

mathematical expressions, 138, 142–146

Nulls, 163

parentheses, 144

SELECT statement, 154–156

syntax diagrams, 143

MAX, 406–408

finding largest values, 452–454

mean values, calculating with AVG, 451–452

membership condition, IN predicate, 192–194

mere mortals, 1–2

Microsoft Access, 19, 195

arithmetic operations, 893

data types, 893

functions, 893–894

Microsoft Office Access, CASE (conditional expressions), 684

Microsoft SQL Server

arithmetic operations, 895

data types, 895

functions, 896–897

Identity data type, 579

ORDER BY clause, 653, 757

MIN, finding smallest values, 454–455

missing values, 161

finding with OUTER JOIN, 349

MOD, 142

multicolumn sorts, 109–110

multipart columns, 39

resolving, 40–43

multiple columns, updating, 541–543

multiple conditions, 204–205

multiple tables, linking (embedding JOINs within JOINs), 286–291

multivalued columns, 39

resolving, 43–45

MySQL

arithmetic operations, 898

data types, 897

functions, 898–901

ROLLUP, 765

N

names, qualifying in ON clause, 279–280

naming conventions

for columns, 35–38

for tables, 46–48

naming expressions (SELECT clause), 152–154

NATIONAL CHARACTER, 128

National Committee for Information Technology Standards (NCITS), 80

National Institute of Standards and Technology (NIST), 80

NATURAL JOIN, 281, 329–330

NCITS (National Committee for Information Technology Standards), 80

nested parenthetical operations, 144

NEXTVAL property, Oracle, 579

NIST (National Institute of Standards and Technology), 80

non-key values, FULL OUTER JOIN, 347–348

NOT

excluding rows, 201–204, 211–214

sample statements, 652–657

Bowling League Database, 663–668

Entertainment Agency Database, 657–659

Recipes Database, 668–671

Sales Orders Database, 653–657

School Scheduling Database, 659–663

sets with multiple NOT criteria, 629–630

GROUP BY, 638–641

HAVING, 638–641

NOT EXISTS, 637–638

NOT IN, 635–637

OUTER JOIN, 632–635

syntax diagrams, 202

NOT EXISTS, sets with multiple NOT criteria, 637–638

NOT IN, sets with multiple NOT criteria, 635–637

NTILE(), 824–827

Bowling League Database, 842–843

Entertainment Agency Database, 843–844

Null, 159–162, 199–201, 221–225

aggregate functions, 445

problems with, 162–163

Null condition, syntax diagrams, 199

numeric literals, 135

O

objects, relational databases, 20

ODBC (Open Database Connectivity), 80

ON clause

qualifying names, 279–280

search conditions, 278

one-to-many relationships, 61

relational databases, 26

one-to-one relationships, 60–61

relational databases, 25–26

Open Database Connectivity (ODBC), 80

operational databases, 16

operations, set operations, 243–244

operators

AND, 205–206

using with OR, 209–211

NOT operator, excluding rows, 211–214

OR, 206–209

determining result sets, 224

using with AND, 209–211

optional participation, 64

OR, 205, 206–209

determining result sets, 224

using with AND, 209–211

Oracle

arithmetic operations, 902

data types, 901

functions, 902–903

NEXTVAL property, 579

Oracle Corporation, 74

ORDER BY, 106–107, 474, 652–653, 692, 725, 757

predicates, 805–810

sorting SELECT queries, 108–111

UNION, 382

order of precedence, 143

search conditions, 214–215

less is more, 217–218

prioritizing, 215–217

origins of SQL (Structured Query Language), 72–73

orphaned rows, 63

OUTER JOIN, 321–323, 349, 713

embedding

JOINs within JOINs, 333–344

SELECT statements, 330–333

finding

missing values, 349

partially matched information, 349–350

FULL OUTER JOIN. See FULL OUTER JOIN

grouping data types, 478

LEFT OUTER JOIN, 323–324

RIGHT OUTER JOIN, 323–324

sample statements, 350–365

Bowling League Database, 359–361

Entertainment Agency Database, 354–355

Recipes Database, 362–364

Sales Orders Database, 351–353

School Scheduling Database, 356–359

sets with multiple NOT criteria, 632–635

syntax, 324–330

tables, 324–330

OVER(), 799, 811

aggregate functions, 803–805

versus GROUP BY, 800

predicates

ORDER BY, 805–810

PARTITION BY, 800–801

ROWS (or RANGE), 811–814

overlapping ranges, checking for, 219–221

P

parentheses, 210–211, 216

CASE expression, 684

mathematical expressions, 144

partially matched information, finding with OUTER JOIN, 349–350

participation, relationships, 64–68

PARTITION BY, 800–801

pattern match condition, 194–199

syntax diagrams, 195

pattern strings, samples, 195–196

percent sign (%), 195

PERCENT_RANK(), 820–821, 824

PostgreSQL, 904–905

arithmetic operations, 904

data types, 904

functions, 904–905

POWER, 142

predicates, 93, 177

BETWEEN … AND, 189

BETWEEN, 178

range condition, 189–192

combining with AND, 205–206

comparison, 178

comparison predicate, aggregate functions as filters, 457–458

diagrams, 681–682

IN, 178

membership condition, 192–194

IS NULL, 178

unknown values, 199–201

LIKE, 178

ESCAPE option, 198–199

pattern match condition, 194–199

OR, 206–209

ORDER BY, 805–810

PARTITION BY, 800–801

ROWS (or RANGE), 811–814

primary key values, generating with INSERT statements, 578–580

primary keys, 20

relational databases, 22–23

tables, 56–60

primary tables, one-to-one relationships, 25

prioritizing search conditions, order of precedence, 215–217

problems, solving

with searched CASE, 688–691

with simple CASE, 683–687

with unlinked data, 714–716

Q

qualifying names, ON clause, 279–280

quantified predicates, (SOME, ANY, ALL), 417–420

QUEL (Query Language), 74

queries

executing, 112

RDBMS (relational database management system), 24

SELECT queries. See SELECT query

query expression, 106–107

Query Language (QUEL), 74

query optimizers, 218

query specification, 106–107

question mark (?), 195

quintiles, splitting, data, 824–827

quotes, single quote, 133

R

RANGE, 811–814, 827–828

range condition, 189–192

ranges, overlapping ranges (checking for), 219–221

RANK(), 818–824

ranking data, with RANK(), 818–824

RDBMS (relational database management system), 17–19, 74

REAL, 146

reasons for learning SQL (Structured Query Language), 84

Recipes Database

aggregate functions, 465–466

AND, 668–671

GROUP BY, 499–501

HAVING clause, 526–527

INNER JOIN

looking for matching values, 314–315

more than two tables, 305–306

two tables, 299–300

NOT, 668–671

OUTER JOIN, 362–364

ROW_NUMBER(), 837–838

search conditions, 232–233

SELECT statement, 120–121

subqueries

in expressions, 429–430

in filters, 435–437

UNION, 394–395

records. See rows

referential integrity, 62–63

refining searches, 176

regular identifiers, 37, 48

related rows, finding with INNER JOINs, 293

relational database management system (RDBMS), 17–19, 74

relational database model, 16–17

relational databases

columns, 21

keys, 22–23

reasons for learning, 29–30

relations, 19

relationships, 25

many-to-many, 27–29

one-to-many, 26

one-to-one, 25–26

rows, 21–22

tables, 20–21

views, 23–24

Relational Software, Inc., 74

Relational Technology, Inc., 74

relations. See tables

relationships

checking with INNER JOIN, 291–292

deletion rule, 63–64

many-to-many relationships, 61–62

one-to-many relationships, 61

one-to-one relationships, 60–61

participation, 64–68

relational databases, 25

many-to-many, 27–29

one-to-many, 26

one-to-one, 25–26

tables, 60–63

requests

translating into SQL, SELECT statement, 95–99

writing with UNION, 372

combining complex SELECT statements, 375–379

using simple SELECT statements, 372–375

resolving. See also deleting

duplicate columns, 50–56

multipart columns, 40–43

multivalued columns, 43–45

restrict deletion rule, 63

restrictions, column restrictions (GROUP BY), 486–488

result sets

determining with OR, 224

difference, 252–256

intersection, 246–249

union, combining, 259–261

retrieving multiple columns, with SELECT statement, 100–101

RIGHT OUTER JOIN, 323–324

ROLLBACK, 540

ROLLUP, 777–778

MySQL, 765

Sales Orders Database, 781–782

sample statements, 781–782

School Scheduling Database, 782–783

totals in hierarchies, 754–765

row numbers, calculating with ROW_NUMBER, 814–818

row subqueries, 400–402

row value constructors, 401

ROW_NUMBER(), 814–818, 827–828, 829

Entertainment Agency Database, 835–837

Recipes Database, 837–838

rows

counting with COUNT(*), 446–448

deleting all rows with DELETE statement, 605–607

deleting some rows with DELETE statement, 607–611

eliminating duplicates with SELECT statement, 103–105

excluding, with NOT, 201–204, 211–214

filter rows, subqueries, 543–546

related rows, finding with INNER JOIN, 293

relational databases, 21–22

selected rows, updating, 631–633

ROWS (or RANGE), 811–814, 827–828

S

SAA (Systems Application Architecture), 80

Sales Orders Database

aggregate functions, 460–461, 847–850

AND, 653–657

CASE (conditional expressions), 693–696

CUBE, 784–786

DELETE statement, 613–614

driver tables, 736–737

expressions, 164–165

GROUP BY, 491–492

HAVING clause, 520–521

INNER JOIN

looking for matching values, 306–309

more than two tables, 300–301

two tables, 295–296

INSERT statement, 589–592

NOT, 653–657

OUTER JOIN, 351–353

ROLLUP, 781–782

search conditions, 227–228

SELECT statement, 114–115

subqueries

in expressions, 425–426

in filters, 430–431

UNION, 385–389

unlinked tables, 726–728

UPDATE statement, 554–557

windows functions, 838–839

sample databases, 9–11

sample statements

aggregate functions, 459–466

Bowling League Database, 463–465

Entertainment Agency Database, 461–462

Recipes Database, 465–466

Sales Orders Database, 460–461

School Scheduling Database, 462–463

AND, 652–657

Bowling League Database, 663–668

Entertainment Agency Database, 657–659

Recipes Database, 668–671

Sales Orders Database, 653–657

School Scheduling Database, 659–663

CASE (conditional expressions), 692–693

Bowling League Database, 702–705

Entertainment Agency Database, 696–698

Sales Orders Database, 693–696

School Scheduling Database, 698–702

CROSS JOIN, 725–726

DELETE statement, 612–613

Bowling League Database, 618–620

Entertainment Agency Database, 614–617

Sales Orders Database, 613–614

School Scheduling Database, 617–618

expressions, 163–171

Bowling League Database, 169–171

Entertainment Agency Database, 165–167

Sales Orders Database, 164–165

School Scheduling Database, 167–169

GROUP BY, 491–501

Bowling League Database, 496–499

Entertainment Agency Database, 493–494

Recipes Database, 499–500

Sales Orders Database, 491–492

School Scheduling Database, 495–496

grouping, 780–781

CUBE, 783–786

GROUPING SETS, 786–788

ROLLUP, 781–783

HAVING clause, 519–527

Bowling League Database, 525–526

Entertainment Agency Database, 521–522

Recipes Database, 526–527

Sales Orders Database, 520–521

School Scheduling Database, 522–525

INNER JOIN, 295

looking for matching values, 306–316

more than two tables, 300–306

two tables, 295–300

INSERT statement, 588–589

Bowling League Database, 596–597

Entertainment Agency Database, 593–595

Sales Orders Database, 589–592

School Scheduling Database, 595–596

NOT, 652–657

Bowling League Database, 663–668

Entertainment Agency Database, 657–659

Recipes Database, 668–671

Sales Orders Database, 653–657

School Scheduling Database, 659–663

OUTER JOIN, 350–365

Bowling League Database, 359–361

Entertainment Agency Database, 354–355

Recipes Database, 362–364

Sales Orders Database, 351–353

School Scheduling Database, 356–359

search conditions, 226–227

Bowling League Database, 231–232

Entertainment Agency Database, 228–229

Recipes Database, 232–233

Sales Orders Database, 227–228

School Scheduling Database, 230–231

SELECT queries, 113–121

SELECT statement, 113–121

Bowling League Database, 119–120

Entertainment Agency Database, 116–117

Recipes Database, 120–121

Sales Orders Database, 114–115

School Scheduling Database, 117–118

subqueries, 424–425

Bowling League Database, 428–429, 434–435

Entertainment Agency Database, 426–427, 432–433

in expressions, 425–430

in filters, 430–437

Recipes Database, 429–430, 435–437

Sales Orders Database, 425–426, 430–431

School Scheduling Database, 427–428, 433–434

UNION, 385–395

Bowling League Database, 392–394

Entertainment Agency Database, 389–390

Recipes Database, 394–395

Sales Orders Database, 385–389

School Scheduling Database, 390–392

unlinked data

driver tables, 736–743

unlinked tables, 726–736

UPDATE statement, 552–553

Bowling League Database, 565–568

Entertainment Agency Database, 558–562

Sales Orders Database, 554–557

School Scheduling Database, 562–565

windows functions, 834–835

aggregate functions, 844–852

NTILE(), 842–844

RANK(), DENSE_RANK, and PERCENT_RANK, 838–841

ROW_NUMBER(), 835–838

samples, defined pattern strings, 195–196

saved queries, RDBMS (relational database management system), 24

saving SELECT statement, 111–112

scalar subqueries, 400, 402

schema for sample databases. See Appendix B

School Scheduling Database

aggregate functions, 462–463, 851–852

AND, 659–663

CASE (conditional expressions), 698–702

DELETE statement, 617–618

driver tables, 739–742

expressions, 167–169

GROUP BY, 495–496

HAVING clause, 522–525

INNER JOIN

looking for matching values, 311

two tables, 297–298

INSERT statement, 595–596

NOT, 659–663

OUTER JOIN, 356–359

ROLLUP, 782–783

search conditions, 230–231

SELECT statement, 117–118

subqueries

in expressions, 427–428

in filters, 433–434

UNION, 390–392

unlinked tables, 731–734

UPDATE statement, 562–565

windows functions, 840–841

search conditions, 177

AND, 205–206

OR, 206–209

order of precedence, 214–215

less is more, 217–218

prioritizing, 215–217

sample statements, 226–233

Bowling League Database, 231–232

Entertainment Agency Database, 228–229

Recipes Database, 232–233

Sales Orders Database, 227–228

School Scheduling Database, 230–231

syntax diagrams, 205, 680

searched CASE, solving problems, 688–691

searching, for unknown values, 199–201

secondary tables, one-to-one relationships, 25

SELECT clause, expressions, 150–151

concatenation expressions, 151–152

naming, 152–154

SELECT DISTINCT statements, simulating, 484–485

SELECT expression, 372

inserting data, 581–587

SELECT queries, 106

diagrams, 106

sample statements, 113–121

sorting information, 108–111

SELECT statement, 90, 91–93

FROM clause, 331

clauses, 91, 92–93

complex SELECT statements, combining, 375–379

data versus information, 93–95

date expressions, time expressions, 156

diagrams, 92

embedding

INNER JOIN, 284–286

OUTER JOINs, 330–333

expressions

date expressions, 156–157

mathematical expressions, 154–156

GROUP BY, syntax diagrams, 475

requesting all columns, 101–103

retrieving multiple columns, 100–101

rows, eliminating duplicates, 103–105

sample statements, 113–121

Bowling League Database, 119–120

Entertainment Agency Database, 116–117

Recipes Database, 120–121

Sales Orders Database, 114–115

School Scheduling Database, 117–118

saving, 111–112

simple SELECT statements

syntax diagrams, 403

writing requests with UNION, 372–375

sorting information, 105–107

translating requests into SQL, 95–99

WHERE clause, 176–178

using, 179–181

selected rows, updating, 631–633

SEQUEL (Structured English Query Language), 72

SEQUEL-XRM, 72

set diagrams, 246–247

set membership, IN predicate, 411–417

set operations, 243–244

classic set operations versus SQL

EXCEPT, 265–268

INTERSECT, 262–265

UNION, 268–271

classic versus SQL, 259–262

difference, 243, 250

problems you can solve with, 256–257

result sets, 252–256

set theory, 250–252

intersection, 243, 244

problems you can solve with, 249–250

result sets, 246–249

set theory, 244–245

union, 243

combining result sets, 259–261

problems you can solve with, 261–262

set theory, 257–259

set theory

difference, 250–252

intersection, 244–245

union, 257–259

sets, 242–243, 628

AND, multiple AND criteria, 628–629

combining

with UNION, 268–271

with union, 257–259

finding multiple matches in the same table

EXISTS, 646–648

GROUP BY, 648–652

HAVING, 648–652

IN, 644–646

INNER JOIN, 642–644

including some criteria but excluding others, 630–631

NOT

GROUP BY, 638–641

HAVING, 638–641

multiple NOT criteria, 629–630

NOT EXISTS, 637–638

NOT IN, 635–637

OUTER JOIN, 632–635

shortcuts, requesting all columns with SELECT statement, 101–103

simple CASE, solving problems, 683–687

simple primary keys, 57

simple SELECT statements, syntax diagrams, 403

simple UPDATE expression, 537–538

simple WHERE clause, deleting some rows, 607

simulating SELECT DISTINCT statements, GROUP BY, 484–485

single quote, 133

smallest values, finding, with MIN, 454–455

solving problems

with searched CASE, 688–691

with simple CASE, 683–687

with unlinked data, 714–716

SOME, 417–420

some rows, deleting with DELETE statement, 607–611

sort order, 108–109

sorting

by alphabetical order, 108

information

ORDER BY clause, SELECT queries, 108–111

SELECT statement, 105–107

UNION, 381–383

special predicate keywords, subqueries

ALL, 417–420

ANY, 417–420

EXISTS, 420–422

IN predicate, 411–417

SOME, 417–420

specifying, explicit values, 132–133

Specifying Queries As Relational Expressions (SQUARE), 73

splitting data into quintiles, 824–827

SQL (Structured Query Language)

commercial implementations, 83

future of, 83–84

history of SQL (Structured Query Language)

early vendor implementations, 73–74

evolution of SQL/86, 76–79

standards, 75–76

origins of, 72–73

reasons for learning, 84

set operations

classic versus SQL, 259–262

EXCEPT, 265–268

INTERSECT, 262–265

UNION, 268–271

standards. See standards

SQL Standard, structure of, 81–82

SQL/86, 76

evolution of, 76–79

SQL/89, 76–77

SQL/92, 78–79

SQL:2016, 83–84

SQL/Data System (SQL/DS), 74

SQL/DS (SQL/Data System), 74

SQRT, 142

SQUARE (Specifying Queries As Relational Expressions), 73

standards

evolution of SQL/86, 76–79

FIPS (Federal Information Processing Standard), 80

history of SQL (Structured Query Language), 75–76

ODBC (Open Database Connectivity), 80

SAA (Systems Application Architecture), 80

SQL Standard, structure of, 81–82

SQL/89, 76–77

SQL/92, 78–79

X/OPEN, 79

START TRANSACTION, 540

static data, 16

Stonebraker, Michael, 74

string comparison, case sensitivity, 197

string values, comparing, 181–184

structure of

columns, fine-tuning, 38–40

databases, 34–35

SQL Standard, 81–82

tables, 48–50

Structured English Query Language (SEQUEL), 72

sub-groups, grouping, 750–753

subqueries, 400, 422

aggregate functions, 457–458

COUNT, 406–408

MAX, 406–408

as column expressions, 402–405, 422–423

deleting some rows, DELETE statement, 609–611

in filter rows, 543–546

as filters, 408–411, 423–424

row subqueries, 400–402

sample statements, 424–425

Bowling League Database, 428–429, 434–435

Entertainment Agency Database, 426–427, 432–433

in expressions, 425–430

in filters, 430–437

Recipes Database, 429–430, 435–437

Sales Orders Database, 425–426, 430–431

School Scheduling Database, 427–428, 433–434

scalar subqueries, 400, 402

special predicate keywords

ALL, 417–420

ANY, 417–420

EXISTS, 420–422

IN predicate, 411–417

SOME, 417–420

table subqueries, 400, 402

UPDATE expressions, 548–551

in WHERE clauses, GROUP BY, 483–484

subtotal combinations, calculating with CUBE, 765–770

SUM, computing totals, 450–451

SYMMETRIC, 189–190

syntax

CASE (conditional expressions), 678–682

FULL OUTER JOIN, 344–347

GROUP BY, 475–481, 753

INNER JOIN, 276

OUTER JOIN, 324–330

subqueries as column expressions, 402–405

subqueries as filters, 408–411

windows functions, 798–800

syntax diagrams

aggregate functions, 444

CASE expression, 679

column references, 276

comparison condition, 181

concatenation expressions, 138

date and time literals, 136

date expressions, 147

DELETE statement, 604

FULL OUTER JOIN, 344

INSERT statement with SELECT expressions, 581

INSERT statement with VALUES clause, 575

mathematical expressions, 143

naming expressions, 153

NOT operator, 202

Null condition, 199

numeric literals, 135

pattern match condition, 195

predicates, 681–682

query using INNER JOIN on two tables, 277

range condition, 189

search conditions, 205

SELECT statement that includes value expression, 159

SELECT statement with GROUP BY clause, 475

SELECT statement with WHERE clause, 177

simple SELECT statements, 403

time expressions, 149

UNION JOIN, 348

UNION statement, 370

using UNION to combine two simple SELECT statements, 372

utilizing, 5–9

Value Expression, 679

value expressions, 157, 549

System R, 18, 72, 73

T

table references, 345

table subqueries, 400, 402

tables

assigning correlation names to, INNER JOIN, 282–284

derived tables, 284

driver tables, 717

Bowling League Database, 742–743

Entertainment Agency Database, 737–739

Sales Orders Database, 736–737

School Scheduling Database, 739–742

setting up, 717–720

using, 720–725

fine-tuning, 46

naming conventions, 46–48

relationships, 60–63

relationships, deletion rule, 63–64

relationships, participation, 64–68

resolving duplicate columns, 50–56

structure of tables, 48–50

INNER JOIN, 277–282

linking, embedding JOINs within JOINs, 286–291

linking tables, 44–45

naming conventions, 46–48

OUTER JOIN, 324–330

primary keys, 56–60

relational databases, 20–21

unlinked tables, 713

creating, 710–712

techniques for grouping, variations on, 775–780

TIME, 136

time expressions, 149–150

SELECT statement, 156

syntax diagrams, 149

TIMESTAMP, 137, 138

TOP, 111

totals

calculating totals on combinations, using CUBE, 765–770

computing with SUM, 450–451

creating a union of totals, with GROUPING SETS, 771–775

in hierarchies, ROLLUP, 754–765

transactions, 540–541

translating requests into SQL, SELECT statement, 95–99

translation statements, 108

triggers, 549, 551

tuple. See rows

type of participation, relationships, 64–68

types of data, expressions, 127–130

types of expressions

concatenation expressions, 138–141

date and time arithmetic expressions, 138, 146

date expressions, 147–148

time expressions, 149–150

mathematical expressions, 138, 142–146

U

underscore (_), 195

UNION, 268–271, 369–372

combining three tables, 380

DISTINCT, 389

fetching data from two tables, 371

sample statements, 385–395

Bowling League Database, 392–394

Entertainment Agency Database, 389–390

Recipes Database, 394–395

Sales Orders Database, 385–389

School Scheduling Database, 390–392

sorting, 381–383

syntax diagrams, 370

combining two simple SELECT statements, 372

uses for, 383–384

using more than once, 379–381

writing requests, 372

combining complex SELECT statements, 375–379

using simple SELECT statements, 372–375

union, 243

problems you can solve with, 261–262

result sets, combining, 259–261

set theory, 257–259

UNION ALL, 371

UNION JOIN, 348

union of totals, creating with GROUPING SETS, 771–775

unknown values. See Null, 161

unlinked data, 710–713

driver tables, 717

setting up, 717–720

using, 720–725

sample statements

driver tables, 736–743

unlinked tables, 726–736

solving problems, 714–716

unlinked tables, 713

Bowling League Database, 734–735

creating, 710–712

Entertainment Agency Database, 728–731

Sales Orders Database, 726–728

School Scheduling Database, 731–734

UPDATE clause, JOIN, 546–548

UPDATE expressions

simple UPDATE expression, 537–538

subqueries, 548–551

UPDATE statement, 536–537

sample statements, 552–553

Bowling League Database, 565–568

Entertainment Agency Database, 558–562

Sales Orders Database, 554–557

School Scheduling Database, 562–565

subqueries in filter rows, 543–546

updating

multiple columns, 541–543

selected rows, 631–633

uses for, 551–552

updating

multiple columns, 541–543

selected rows, 631–633

USING clause, 280–281

USING syntax, OUTER JOIN, 329

V

Value Expression, 679–680

syntax diagram, 679

value expressions, 157–159

syntax diagrams, 157, 549

values

converting into data types, CAST function, 130–132

finding largest values, with MAX, 452–454

inserting with INSERT statements, 575–578

literal values, 132–133

matching values, finding with INNER JOIN, 293–294

mean values, calculating with AVG, 451–452

missing values, 161

finding with OUTER JOIN, 349

non-key values, FULL OUTER JOIN, 347–348

Null, 159–162

smallest values, finding with MIN, 454–455

unknown values, 161

VALUES clause

DELETE statement, 603–604

INSERT statement, 575–578

syntax diagrams, INSERT statement, 575

VALUES keyword, 576

Venn, John, 246–247

Venn diagram, 246–247

verifying you’re updating the correct rows, 632–633

DELETE statement, 607–608

views, relational databases, 23–24

VM/CMS operating system, 74

W

WHERE clause, 176–178

CASE (conditional expressions), 691

comparison predicate, 181

comparing string values, 181–184

equality and inequality, 185–186

less than and greater than, 186–188

DELETE statement, 603–604

deleting, some rows, 607

filtering, 510–513

IS NULL, unknown values, 199–201

LIKE predicate, pattern match condition, 194–199

IN predicate, membership condition, 192–194

range condition, 189–192

row subqueries, 401–402

SELECT statement, 93

subqueries

as filters, 423–424

GROUP BY, 483–484

using, 179–181

WIDTH_BUCKET, 142

windows functions, 794–798

aggregate functions, 827–834

calculating row number, 814–818

OVER()

ORDER BY clause, 805–810

PARTITION BY, 800–801

ROWS (or RANGE), 811–814

ranking data with RANK(), 818–824

Sales Orders Database, 838–839

sample statements, 834–835

aggregate functions, 844–852

NTILE(), 842–844

RANK(), DENSE_RANK, and PERCENT_RANK, 838–841

ROW_NUMBER(), 835–838

School Scheduling Database, 840–841

splitting data into quintiles with NTILE(), 824–827

syntax, 798–800

WITH RECURSIVE, 717

Wong, Eugene, 74

writing requests with UNION, 372

combining complex SELECT statements, 375–379

using simple SELECT statements, 372–375

X–Y

X3, 75

X3H2, 75–76

evolution of SQL/86, 76–79

XML (eXtensible Markup Language), 19

X/OPEN, 79

Z

zero, 160

zero-length string, 160