Joe Celko's SQL for smarties: advanced SQL programming
Gespeichert in:
| 1. Verfasser: | |
|---|---|
| Format: | Buch |
| Sprache: | English |
| Veröffentlicht: |
San Francisco, Calif.
Kaufmann [u.a.]
2005
|
| Ausgabe: | 3. ed. |
| Schriftenreihe: | The Morgan Kaufmann series in data management systems
|
| Schlagworte: | |
| Online-Zugang: | Inhaltsverzeichnis |
| Beschreibung: | Literaturverz. S. [761] - 775 |
| Beschreibung: | XXVIII, 808 S. |
| ISBN: | 9780123693792 0123693799 |
Internformat
MARC
| LEADER | 00000nam a2200000zcb4500 | ||
|---|---|---|---|
| 001 | BV035186389 | ||
| 003 | DE-604 | ||
| 005 | 20081215 | ||
| 007 | t | ||
| 008 | 081128s2005 |||| 00||| eng d | ||
| 020 | |a 9780123693792 |9 978-0-12-369379-2 | ||
| 020 | |a 0123693799 |c (pbk) : £29.99 |9 0-12-369379-9 | ||
| 024 | 3 | |a 9780123693792 | |
| 035 | |a (OCoLC)61129114 | ||
| 035 | |a (DE-599)OBVAC05063429 | ||
| 040 | |a DE-604 |b ger |e rakwb | ||
| 041 | 0 | |a eng | |
| 049 | |a DE-859 | ||
| 050 | 0 | |a QA76.73.S67 | |
| 082 | 0 | |a 005.133 |2 22 | |
| 084 | |a ST 271 |0 (DE-625)143639: |2 rvk | ||
| 100 | 1 | |a Celko, Joe |e Verfasser |4 aut | |
| 245 | 1 | 0 | |a Joe Celko's SQL for smarties |b advanced SQL programming |c Joe Celko |
| 246 | 1 | 3 | |a SQL for smarties |
| 250 | |a 3. ed. | ||
| 264 | 1 | |a San Francisco, Calif. |b Kaufmann [u.a.] |c 2005 | |
| 300 | |a XXVIII, 808 S. | ||
| 336 | |b txt |2 rdacontent | ||
| 337 | |b n |2 rdamedia | ||
| 338 | |b nc |2 rdacarrier | ||
| 490 | 0 | |a The Morgan Kaufmann series in data management systems | |
| 500 | |a Literaturverz. S. [761] - 775 | ||
| 650 | 4 | |a SQL (computer program language) | |
| 650 | 0 | 7 | |a SQL |0 (DE-588)4134010-3 |2 gnd |9 rswk-swf |
| 650 | 0 | 7 | |a Programmierung |0 (DE-588)4076370-5 |2 gnd |9 rswk-swf |
| 689 | 0 | 0 | |a SQL |0 (DE-588)4134010-3 |D s |
| 689 | 0 | 1 | |a Programmierung |0 (DE-588)4076370-5 |D s |
| 689 | 0 | |8 1\p |5 DE-604 | |
| 856 | 4 | 2 | |m GBV Datenaustausch |q application/pdf |u http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=016993070&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |3 Inhaltsverzeichnis |
| 999 | |a oai:aleph.bib-bvb.de:BVB01-016993070 | ||
| 883 | 1 | |8 1\p |a cgwrk |d 20201028 |q DE-101 |u https://d-nb.info/provenance/plan#cgwrk | |
Datensatz im Suchindex
| _version_ | 1804138362268811264 |
|---|---|
| adam_text | C O N T E N T S INTRODUCTION TO THE THIRD EDITION XXV 1.1 WHAT CHANGED
IN TEN YEARS XXV 1.2 WHAT IS NEW IN THIS EDITION XXVII 1.3 CORRECTIONS
AND ADDITIONS XXVIII DATABASE DESIGN 1 1.1 SCHEMA AND TABLE CREATION 2
1.1.1 CREATE SCHEMA STATEMENT 3 1.1.2 MANIPULATING TABLES 5 1.1.3 COLUMN
CONSTRAINTS 10 1.1.4 UNIQUE CONSTRAINTS VERSUS UNIQUE INDEXES 17 1.1.5
NESTED UNIQUE CONSTRAINTS 18 1.1.6 OVERLAPPING KEYS 2 2 1.1.7 CREATE
ASSERTION CONSTRAINTS 2 5 1.1.8 USING VIEWS FOR SCHEMA LEVEL CONSTRAINTS
2 5 1.1.9 USING PRIMARY KEYS AND ASSERTIONS FOR CONSTRAINTS 29 1.1.10
AVOIDING ATTRIBUTE SPLITTING 31 1.1.11 MODELING CLASS HIERARCHIES IN DDL
34 1.2 GENERATING UNIQUE SEQUENTIAL NUMBERS FOR KEYS 36 1.2.1 IDENTITY
COLUMNS 3 7 1.2.2 ROWID AND PHYSICAL DISK ADDRESSES 39 1.2.3 SEQUENTIAL
NUMBERING IN PURE SQL 39 1.2.4 GUIDS 41 1.2.5 SEQUENCE GENERATOR
FUNCTIONS 42 1.2.6 UNIQUE VALUE GENERATORS 42 1.2.7 PREALLOCATED VALUES
44 1.2.8 RANDOM ORDER VALUES 45 1.3 A REMARK ON DUPLICATE ROWS 48 1.4
OTHER SCHEMA OBJECTS 50 1.4.1 SCHEMA TABLES 50 1.4.2 TEMPORARY TABLES 51
1.4.3 CREATE DOMAIN STATEMENT 51 CONTENTS (S^ 1.4.4 CREATE TRIGGER
STATEMENT 52 1.4.5 CREATE PROCEDURE STATEMENT 53 1.4.6 DECLARE CURSOR
STATEMENT 53 1 NORMALIZATION 61 2.1 FUNCTIONAL AND MULTIVALUED
DEPENDENCIES 64 2.2 FIRST NORMAL FORM (INF) 64 2.2.1 NOTE ON REPEATED
GROUPS 66 2.3 SECOND NORMAL FORM (2NF) 70 2.4 THIRD NORMAL FORM (3NF) 71
2.5 ELEMENTARY KEY NORMAL FORM (EKNF) 72 2.6 BOYCE-CODD NORMAL FORM
(BCNF) 73 2.7 FOURTH NORMAL FORM (4NF) 75 2.8 FIFTH NORMAL FORM (5NF) 76
2.9 DOMAIN-KEY NORMAL FORM (DKNF) 78 2.10 PRACTICAL HINTS FOR
NORMALIZATION 87 2.11 KEY TYPES 88 2.11.1 NATURAL KEYS 89 2.11.2
ARTIFICIAL KEYS 89 2.11.3 EXPOSED PHYSICAL LOCATORS 89 2.11.4 PRACTICAL
HINTS FOR DENORMALIZATION 91 2.11.5 ROWSORTING 93 3 NUMERIC DATA IN SQL
101 3.1 NUMERIC TYPES 101 3.1.1 BIT, BYTE, AND BOOLEAN DATA TYPES 104
3.2 NUMERIC TYPE CONVERSION 105 3.2.1 ROUNDING AND TRUNCATING 105 3.2.2
CAST() FUNCTION 107 3.3 FOUR-FUNCTION ARITHMETIC 108 3.4 ARITHMETIC AND
NULLS 109 3.5 CONVERTING VALUES TO AND FROM NULL 110 3.5.1 NULLIF0
FUNCTION 110 3.5.2 COALESCE0 FUNCTION 111 3.6 VENDOR MATH FUNCTIONS 113
CONTENTS 3.6.1 NUMBER THEORY OPERATORS 113 3.6.2 EXPONENTIAL FUNCTIONS
116 3.6.3 SCALING FUNCTIONS 116 3.6.4 CONVERTING NUMBERS TO WORDS 117
TEMPORAL DATA TYPES IN SQL 119 4.1 NOTES ON CALENDAR STANDARDS 119 4.2
SQL TEMPORAL DATA TYPES 123 4.2.1 TIPS FOR HANDLING DATES, TIMESTAMPS,
AND TIMES 124 4.2.2 DATE FORMAT STANDARDS 124 4.2.3 HANDLING TIMESTAMPS
125 4.2.4 HANDLING TIMES 127 4.3 QUERIES WITH DATE ARITHMETIC 128 4.4
THE NATURE OF TEMPORAL DATA MODELS 129 4.4.1 TEMPORAL DUPLICATES 129
4.4.2 TEMPORAL DATABASES 135 4.4.3 TEMPORAL PROJECTION AND SELECTION 137
4.4.4 TEMPORAL JOINS 139 4.4.5 MODIFYING VALID-TIME STATE TABLES 145
4.4.6 CURRENT MODIFICATIONS 146 4.4.7 SEQUENCED MODIFICATIONS 150 4.4.8
NONSEQUENCED MODIFICATIONS 155 4.4.9 TRANSACTION-TIME STATE TABLES 156
4.4.10 MAINTAINING THE AUDIT LOG 158 4.4.11 QUERYING THE AUDIT LOG 160
4.4.12 MODIFYING THE AUDIT LOG 164 4.4.13 BITEMPORAL TABLES 164 4.4.14
TEMPORAL SUPPORT IN STANDARD SQL 167 CHARACTER DATA TYPES IN SQL 169 5.1
PROBLEMS WITH SQL STRINGS 170 5.1.1 PROBLEMS OF STRING EQUALITY 170
5.1.2 PROBLEMS OF STRING ORDERING 171 5.1.3 PROBLEMS OF STRING GROUPING
172 5.2 STANDARD STRING FUNCTIONS 172 CONTENTS 5.3 COMMON VENDOR
EXTENSIONS 174 5.3.1 PHONETIC MATCHING 175 5.4 CUTTER TABLES 182 6
NULLS: MISSING DATA IN SQL 185 6.1 EMPTY AND MISSING TABLES 187 6.2
MISSING VALUES IN COLUMNS 187 6.3 CONTEXT AND MISSING VALUES 189 6.4
COMPARING NULLS 190 6.5 NULLS AND LOGIC 190 6.5.1 NULLS IN SUBQUERY
PREDICATES 191 6.5.2 STANDARD SQL SOLUTIONS 193 6.6 MATH AND NULLS 193
6.7 FUNCTIONS AND NULLS 193 6.8 NULLS AND HOST LANGUAGES 194 6.9 DESIGN
ADVICE FOR NULLS 195 6.9.1 AVOIDING NULLS FROM THE HOST PROGRAMS 197
6.10 A NOTE ON MULTIPLE NULL VALUES 198 7 MULTIPLE COLUMN DATA ELEMENTS
201 7.1 DISTANCE FUNCTIONS 201 7.2 STORING AN IP ADDRESS IN SQL 202
7.2.1 A SINGLE VARCHAR( 15) COLUMN 203 7.2.2 ONE INTEGER COLUMN 203
7.2.3 FOUR SMALLINT COLUMNS 205 7.3 CURRENCY AND OTHER UNIT CONVERSIONS
205 7.4 SOCIAL SECURITY NUMBERS 206 7.5 RATIONAL NUMBERS 209 8 TABLE
OPERATIONS 211 8.1 DELETE FROM STATEMENT 211 8.1.1 THE DELETE FROM
CLAUSE 212 8.1.2 THE WHERE CLAUSE 212 8.1.3 DELETING BASED ON DATA IN A
SECOND TABLE 216 8.1.4 DELETING WITHIN THE SAME TABLE 216 3) CONTENTS
8.2 8.3 8.4 8.5 8.1.5 DELETING IN MULTIPLE TABLES WITHOUT REFERENTIAL
INTEGRITY INSERT INTO STATEMENT 8.2.1 8.2.2 8.2.3 INSERT INTO CLAUSE THE
NATURE OF INSERTS BULK LOAD AND UNLOAD UTILITIES THE UPDATE STATEMENT
8.3.1 8.3.2 8.3.3 8.3.4 8.3.5 ANOTE THE UPDATE CLAUSE THE WHERE CLAUSE
THE SET CLAUSE UPDATING WITH A SECOND TABLE USING THE CASE EXPRESSION IN
UPDATES ON FLAWS IN A COMMON VENDOR EXTENSION MERGE STATEMENT 220 221
221 222 223 223 223 224 225 226 228 231 232 9 COMPARISON OR THETA
OPERATORS 235 9.1 CONVERTING DATA TYPES 236 9.2 ROW COMPARISONS IN SQL
238 10 VALUED PREDICATES 241 10.1 IS NULL PREDICATE 241 10.1.1
SOURCESOFNULLS 242 10.2 IS [NOTKTRUE I FALSE | UNKNOWN} PREDICATE 242
10.3 IS [NOT] NORMALIZED PREDICATE 244 11 CASE EXPRESSIONS 247 11.1 THE
CASE EXPRESSION 247 11.1.1 THE COALESCEO AND NULLIF() FUNCTIONS 2 51
11.1.2 CASE EXPRESSIONS WITH GROUP BY 252 11.1.3 CASE, CHECKO CLAUSES
AND LOGICAL IMPLICATION 253 11.1.4 SUBQUERY EXPRESSIONS AND CONSTANTS
257 11.2 ROZENSHTEIN CHARACTERISTIC FUNCTIONS 258 12 LIRE PREDICATE 261
12.1 TRICKS WITH PATTERNS 262 CONTENTS -*BP- 12.2 RESULTS WITH NULL
VALUES AND EMPTY STRINGS 264 12.3 LIKE IS NOT EQUALITY 264 12.4 AVOIDING
THE LIKE PREDICATE WITH A JOIN 264 12.5 CASE EXPRESSIONS AND LIKE
PREDICATES 266 12.6 SIMILARTO PREDICATES 267 12.7 TRICKS WITH STRINGS
269 12.7.1 STRING CHARACTER CONTENT 269 12.7.2 SEARCHING VERSUS
DECLARING A STRING 270 12.7.3 CREATING AN INDEX ON A STRING 270 13
BETWEEN AND OVERLAPS PREDICATES 273 13.1 THE BETWEEN PREDICATE 273
13.1.1 RESULTS WITH NULL VALUES 2 74 13.1.2 RESULTS WITH EMPTY SETS 2 74
13.1.3 PROGRAMMING TIPS 274 13.2 OVERLAPS PREDICATE 275 13.2.1 TIME
PERIODS AND OVERLAPS PREDICATE 275 14 THE [NOT] IN() PREDICATE 287 14.1
OPTIMIZING THE IN() PREDICATE 288 14.2 REPLACING ORS WITH THE IN()
PREDICATE 292 14.3 NULLS AND THE IN() PREDICATE 293 14.4 IN() PREDICATE
AND REFERENTIAL CONSTRAINTS 295 14.5 IN() PREDICATE AND SCALAR QUERIES
297 15 EXISTSO PREDICATE 299 15.1 EXISTS AND NULLS 300 15.2 EXISTS AND
INNER JOINS 302 15.3 NOT EXISTS AND OUTER JOINS 303 15.4 EXISTSO AND
QUANTIFIERS 304 15.5 EXISTSO AND REFERENTIAL CONSTRAINTS 305 15.6 EXISTS
AND THREE-VALUED LOGIC 306 CONTENTS 16 QUANTIFIED SUBQUERY PREDICATES
309 16.1 SCALAR SUBQUERY COMPARISONS 310 16.2 QUANTIFIERS AND MISSING
DATA 311 16.3 THE ALL PREDICATE AND EXTREMA FUNCTIONS 313 16.4 THE
UNIQUE PREDICATE 314 17 THE SELECT STATEMENT 317 17.1 SELECT AND JOINS
317 17.1.1 ONE-LEVEL SELECT STATEMENT 317 17.1.2 CORRELATED SUBQUERIES
IN A SELECT STATEMENT 324 17.1.3 SELECT STATEMENT SYNTAX 326 17.1.4 THE
ORDER BY CLAUSE 328 17.2 OUTERJOINS 336 17.2.1 SYNTAX FOR OUTERJOINS 337
17.2.2 NULLS AND OUTERJOINS 342 17.2.3 NATURAL VERSUS SEARCHED OUTER
JOINS 344 17.2.4 SEIF OUTER JOINS 345 17.2.5 TWO OR MORE OUTERJOINS 346
17.2.6 OUTERJOINS AND AGGREGATE FUNCTIONS 348 17.2.7 FUELL OUTER JOIN 349
17.2.8 WHERE CLAUSE OUTER JOIN OPERATORS 350 17.3 OLD VERSUS NEW JOIN
SYNTAX 351 17.4 SCOPE OF DERIVED TABLE NAMES 353 17.5 JOINS BY FUNCTION
CALLS 354 17.6 THE UNION JOIN 356 17.7 PACKINGJOINS 358 17.8 DR. CODD S
T-JOIN 359 17.8.1 THE CROATIAN SOLUTION 363 17.8.2 THE SWEDISH SOLUTION
364 17.8.3 THE COLOMBIAN SOLUTION 364 18 VIEWS, DERIVED TABLES,
MATERIALIZED TABLES, AND TEMPORARY TABLES 369 18.1 VIEWS IN QUERIES 370
18.2 UPDATABLE AND READ-ONLY VIEWS 371 CONTENTS DUER55E9HT 18.3 TYPESOF
VIEWS 373 18.3.1 SINGLE-TABLE PROJECTION AND RESTRICTION 373 18.3.2
CALCULATED COLUMNS 373 18.3.3 TRANSLATED COLUMNS 373 18.3.4 GROUPED
VIEWS 374 18.3.5 UNIONED VIEWS 375 18.3.6 JOINSINVIEWS 377 18.3.7 NESTED
VIEWS 377 18.4 HOW VIEWS ARE HANDLED IN THE DATABASE SYSTEM 379 18.4.1
VIEW COLUMN LIST 379 18.4.2 VIEW MATERIALIZATION 379 18.4.3 IN-LINE TEXT
EXPANSION 380 18.4.4 POINTER STRUCTURES 382 18.4.5 INDEXING AND VIEWS
383 18.5 WITH CHECK OPTION CLAUSE 383 18.5.1 WITH CHECK OPTION AS CHECKO
CLAUSE 388 18.6 DROPPING VIEWS 389 18.7 TEMPORARY TABLE DECLARATIONS 390
18.8 HINTS ON USING VIEWS AND TEMPORARY TABLES 391 18.8.1 USING VIEWS
392 18.8.2 USING TEMPORARY TABLES 392 18.8.3 FLATTENING A TABLE WITH A
VIEW 393 18.9 USING DERIVED TABLES 395 18.9.1 DERIVED TABLES IN THE FROM
CLAUSE 395 18.9.2 DERIVED TABLES WITH A VALUES CONSTRUCTOR 397 18.10
DERIVED TABLES IN THE WITH CLAUSE 397 19 PARTITIONING DATA IN QUERIES
401 19.1 COVERINGS AND PARTITIONS 401 19.1.1 PARTITIONING BY RANGES 402
19.1.2 PARTITION BY FUNCTIONS 403 19.1.3 PARTITION BY SEQUENCES 404 19.2
RELATIONAL DIVISION 406 19.2.1 DIVISION WITH A REMAINDER 408 19.2.2
EXACT DIVISION 409 -W 19.3 19.4 19.5 19.2.3 19.2.4 19.2.5 19.2.6
ROMLEY S BOOLEAN C O N T E N T S NOTE ON PERFORMANCE TODD S DIVISION
DIVISION WITH JOINS DIVISION WITH SET OPERATORS ; DIVISION EXPRESSIONS
IN AN RDBMS FIFO AND LIFO SUBSETS XVII 410 410 413 413 414 418 420 20
GROUPING OPERATIONS 425 20.1 GROUP BY CLAUSE 425 20.1.1 NULLS AND CROUPS
427 20.2 GROUP BY AND HAVING 427 20.2.1 GROUP CHARACTERISTICS AND THE
HAVING CLAUSE 429 20.3 MULTIPLE AGGREGATION LEVELS 431 20.3.1 GROUPED
VIEWS FOR MULTIPLE AGGREGATION LEVELS 432 20.3.2 SUBQUERY EXPRESSIONS
FOR MULTIPLE AGGREGATION LEVELS 433 20.3.3 CASE EXPRESSIONS FOR MULTIPLE
AGGREGATION LEVELS 434 20.4 GROUPING ON COMPUTED COLUMNS 435 20.5
GROUPING INTO PAIRS 436 20.6 SORTING AND GROUP BY 437 21 AGGREGATE
FUNCTIONS 439 21.1 COUNT() FUNCTIONS 440 21.2 SUM() FUNCTIONS 443 21.3
AVG() FUNCTIONS 444 21.3.1 AVERAGES WITH EMPTY CROUPS 446 21.3.2
AVERAGES ACROSS COLUMNS 448 21.4 EXTREMA FUNCTIONS 449 21.4.1 SIMPLE
EXTREMA FUNCTIONS 449 21.4.2 GENERALIZED EXTREMA FUNCTIONS 451 21.4.3
MULTIPLE CRITERIA EXTREMA FUNCTIONS 460 21.4.4 GREATESTO AND LEASTO
FUNCTIONS 462 21.5 THE LIST0 AGGREGATE FUNCTION 465 21.5.1 THE LIST0
FUNCTION WITH A PROCEDURE 466 CONTENTS 21.5.2 THE LIST() FUNCTION BY
CROSSTABS 467 21.6 THE PRD() AGGREGATE FUNCTION 468 21.6.1 PRD()
FUNCTION BY EXPRESSIONS 469 21.6.2 THE PRD() AGGREGATE FUNCTION BY
LOGARITHMS 470 21.7 BITWISE AGGREGATE FUNCTIONS 473 21.7.1 BITWISE OR
AGGREGATE FUNCTION 474 21.7.2 BITWISE AND AGGREGATE FUNCTION 475 22
AUXILIARY TABLES 477 22.1 THE SEQUENCE TABLE 477 22.1.1 ENUMERATING A
LIST 479 22.1.2 MAPPING A SEQUENCE INTO A CYCLE 481 22.1.3 REPLACING AN
ITERATIVE LOOP 483 22.2 LOOKUP AUXILIARY TABLES 485 22.2.1 SIMPLE
TRANSLATION AUXILIARY TABLES 487 22.2.2 MULTIPLE TRANSLATION AUXILIARY
TABLES 487 22.2.3 MULTIPLE PARAMETER AUXILIARY TABLES 488 22.2.4 RANGE
AUXILIARY TABLES 489 22.2.5 HIERARCHICAL AUXILIARY TABLES 490 22.2.6 ONE
TRUE LOOKUP TABLE 491 22.3 AUXILIARY FUNCTION TABLES 493 22.3.1 INVERSE
FUNCTIONS WITH AUXILIARY TABLES 495 22.3.2 INTERPOLATION WITH AUXILIARY
FUNCTION TABLES 504 22.4 GLOBAL CONSTANTS TABLES 506 23 STATISTICS IN
SQL 509 23.1 THE MODE 510 23.2 THE AVG() FUNCTION 512 23.3 THE MEDIAN
512 23.3.1 DATE S FIRST MEDIAN 513 23.3.2 CELKO S FIRST MEDIAN 514
23.3.3 DATE S SECOND MEDIAN 516 23.3.4 MURCHISON S MEDIAN 516 23.3.5
CELKO S SECOND MEDIAN 517 23.3.6 VAUGHAN S MEDIAN WITH VIEWS 519 ~^SI~
CONTENTS 23.3.7 MEDIAN WITH CHARACTERISTIC FUNCTION 520 23.3.8 CELKO S
THIRD MEDIAN 522 23.3.9 KEN HENDERSONS MEDIAN 526 23.4 VARIANCE AND
STANDARD DEVIATION 527 23.5 AVERAGE DEVIATION 528 23.6 CUMULATIVE
STATISTICS 528 23.6.1 RUNNING TOTAIS 529 23.6.2 RUNNING DIFFERENCES 530
23.6.3 CUMULATIVE PERCENTAGES 531 23.6.4 RANKINGS AND RELATED STATISTICS
533 23.6.5 QUINTILES AND RELATED STATISTICS 537 23.7 CROSS TABULATIONS
538 23.7.1 CROSSTABS BY CROSS JOIN 542 23.7.2 CROSSTABS BY OUTER JOINS
543 23.7.3 CROSSTABS BY SUBQUERY 544 23.7.4 CROSSTABS BY CASE EXPRESSION
545 23.8 HARMONIE MEAN AND GEOMETRIE MEAN 545 23.9 MULTIVARIABLE
DESCRIPTIVE STATISTICS IN SQL 546 23.9.1 COVARIANCE 546 23.9.2 PEARSON S
R 547 23.9.3 NULLS IN MULTIVARIABLE DESCRIPTIVE STATISTICS 548 24
REGIONS, RUNS, CAPS, SEQUENCES, AND SERIES 549 24.1 FINDING SUBREGIONS
OF SIZE (N) 550 NUMBERING REGIONS 551 FINDING REGIONS OF MAXIMUM SIZE
552 BOUND QUERIES 557 RUN AND SEQUENCE QUERIES 557 24.5.1 FILLING IN
SEQUENCE NUMBERS 560 SUMMATION OF A SERIES 562 SWAPPING AND SLIDING
VALUES IN A LIST 565 CONDENSING A LIST OF NUMBERS 567 FOLDING A LIST OF
NUMBERS 567 COVERINGS 568 24.2 24.3 24.4 24.5 24.6 24.7 24.8 24.9 24.10
CONTENTS 25 ARRAYS IN SQL 575 25.1 ARRAYS VIA NAMED COLUMNS 576 25.2
ARRAYS VIA SUBSCRIPT COLUMNS 580 25.3 MATRIX OPERATIONS IN SQL 581
25.3.1 MATRIX EQUALITY 582 25.3.2 MATRIX ADDITION 582 25.3.3 MATRIX
MULTIPLICATION 583 25.3.4 OTHER MATRIX OPERATIONS 585 25.4 FLATTENING A
TABLE INTO AN ARRAY 585 25.5 COMPARING ARRAYS IN TABLE FORMAT 587 26 SET
OPERATIONS 591 26.1 UNION AND UNION ALL 592 26.1.1 ORDER OL EXECUTION
594 26.1.2 MIXED UNION AND UNION ALL OPERATORS 595 26.1.3 UNION OF
COLUMNS FROM THE SAME TABLE 595 26.2 INTERSECT AND EXCEPT 596 26.2.1
INTERSECT AND EXCEPT WITHOUT NULLS AND DUPLICATES 599 26.2.2 INTERSECT
AND EXCEPT WITH NULLS AND DUPLICATES 600 26.3 A NOTE ON ALL AND SELECT
DISTINCT 601 26.4 EQUALITY AND PROPER SUBSETS 602 27 SUBSETS 605 27.1
EVERY NTH ITEM IN A TABLE 605 27.2 PICKING RANDOM ROWS FROM A TABLE 607
27.3 THE CONTAINS OPERATORS 612 27.3.1 PROPER SUBSET OPERATORS 612
27.3.2 TABLE EQUALITY 613 27.4 PICKING A REPRESENTATIVE SUBSET 618 28
TREES AND HIERARCHIES IN SQL 623 28.1 ADJACENCY LIST MODEL 624 CONTENTS
28.1.1 COMPLEX CONSTRAINTS 625 28.1.2 PROCEDURAL TRAVERSAL FOR QUERIES
627 28.1.3 ALTERING THE TABLE 628 28.2 THE PATH ENUMERATION MODEL 628
28.2.1 FINDING SUBTREES AND NODES 629 28.2.2 FINDING LEVELS AND
SUBORDINATES 630 28.2.3 DELETING NODES AND SUBTREES 630 28.2.4 INTEGRITY
CONSTRAINTS 631 28.3 NESTED SET MODEL OF HIERARCHIES 631 28.3.1 THE
COUNTING PROPERTY 633 28.3.2 THE CONTAINMENT PROPERTY 634 28.3.3
SUBORDINATES 635 28.3.4 HIERARCHICAL AGGREGATIONS 636 28.3.5 DELETING
NODES AND SUBTREES 636 28.3.6 CONVERTING ADJACENCY LIST TO NESTED SET
MODEL 637 28.4 OTHER MODELS FOR TREES AND HIERARCHIES 639 29 TEMPORAL
QUERIES 641 642 643 645 645 648 652 656 658 660 661 665 666 667 669 670
672 673 29.1 29.2 29.3 29.4 29.5 29.6 29.7 29.8 29.9 TEMPORAL MATH
PERSONAL CALENDARS TIME SERIES 29.3.1 29.3.2 29.3.3 29.3.4 29.3.5 29.3.6
GAPS IN A TIME SERIES CONTINUOUS TIME PERIODS MISSING TIMES IN
CONTIGUOUS EVENTS LOCATING DATES TEMPORAL STARTING AND ENDING POINTS
AVERAGE WAIT TIMES JULIAN DATES DATE AND TIME EXTRACTION FUNCTIONS OTHER
TEMPORAL FUNCTIONS WEEKS 29.7.1 MODELIN^ 29.8.1 CALENDAR SORTING BY
WEEKDAY NAMES ; TIME IN TABLES USING DURATION PAIRS AUXILIARY TABLE
CONTENTS 29.10 PROBLEMS WITHTHEYEAR 2000 675 29.10.1 THEZEROS 675
29.10.2 LEAPYEAR 676 29.10.3 THE MILLENNIUM 677 29.10.4 WEIRD DATES IN
LEGACY DATA 679 29.10.5 THE AFTERMATH 680 681 682 682 683 683 684 684
685 686 687 687 688 691 695 697 703 705 706 31 OLAP IN SQL 709 710 711
711 711 712 714 716 717 30 GRAPHS IN 30.1 30.2 30.3 30.4 30.5 30.6 SQL
BASIC GRAPH CHARACTERISTICS 30.1.1 30.1.2 30.1.3 30.1.4 30.1.5 30.1.6
ALL NODES IN THE GRAPH PATH ENDPOINTS REACHABLE NODES EDGES INDEGREE AND
OUTDEGREE SOURCE, SINK, ISOLATED, AND INTERNAL NODES PATHS IN A GRAPH
30.2.1 30.2.2 30.2.3 30.2.4 LENGTH OF PATHS SHORTEST PATH PATHS BY
ITERATION LISTING THE PATHS ACYCLIC GRAPHS AS NESTED SETS PATHS WITH CTE
30.4.1 NONACYCLIC GRAPHS ADJACENCY MATRIX MODEL POINTS INSIDE POLYGONS
1.1 1.2 STAR SCHEMA OLAP FUNCTIONALITY 31.2.1 31.2.2 31.2.3 31.2.4
31.2.5 31.2.6 RANK AND DENSE_RANK ROW NUMBERING GROUPING OPERATORS THE
WINDOW CLAUSE OLAP EXAMPLES OF SQL ENTERPRISE-WIDE DIMENSIONAL LAYER
CONTENTS 31.3 A BIT OF HISTORY 718 32 TRANSACTIONS AND CONCURRENCY
CONTROL 719 32.1 SESSIONS 719 32.2 TRANSACTIONS AND ACID 720 32.2.1
ATOMICITY 720 32.2.2 CONSISTENCY 721 32.2.3 ISOLATION 721 32.2.4
DURABILITY 722 32.3 CONCURRENCY CONTROL 722 32.3.1 THE FIVE PHENOMENA
722 32.3.2 THE ISOLATION LEVELS 724 32.3.3 CURSOR STABILITY ISOLATION
LEVEL 726 32.4 PESSIMISTIC CONCURRENCY CONTROL 726 32.5 SNAPSHOT
ISOLATION: OPTIMISTIC CONCURRENCY 727 32.6 LOGICAL CONCURRENCY CONTROL
729 32.7 DEADLOCK AND LIVELOCKS 730 33 OPTIMIZING SQL 731 33.1 ACCESS
METHODS 732 33.1.1 SEQUENTIAL ACCESS 732 33.1.2 INDEXED ACCESS 732
33.1.3 HASHED INDEXES 733 33.1.4 BIT VECTOR INDEXES 733 33.2 EXPRESSIONS
AND UNNESTED QUERIES 733 33.2.1 USE SIMPLE EXPRESSIONS 734 33.2.2 STRING
EXPRESSIONS 738 33.3 GIVE EXTRA JOIN INFORMATION IN QUERIES 738 33.4
INDEXTABLES CAREFULLY 740 33.5 WATCH THE IN PREDICATE 742 33.6 AVOID
UNIONS 744 33.7 PREFER JOINS OVER NESTED QUERIES 745 33.8 AVOID
EXPRESSIONS ON INDEXED COLUMNS 746 33.9 AVOID SORTING 746 33.10 AVOID
CROSSJOINS 750 XXIV 33.11 33.12 33.13 33.14 33.15 33.16 REFERENCES C O N
T E N T S LEARN TO USE INDEXES CAREFULLY ORDER INDEXES CAREFULLY KNOW
YOUR OPTIMIZER RECOMPILE STATIC SQL AFTER SCHEMA CHANGES TEMPORARY
TABLES ARE SOMETIMES HANDY UPDATE STATISTICS GENERAL REFERENCES LOGIC
MATHEMATICAL TECHNIQUES RANDOM NUMBERS SCALES AND MEASUREMENTS MISSING
VALUES REGULAER EXPRESSIONS GRAPH THEORY INTRODUCTORY SQL BOOKS
OPTIMIZING QUERIES TEMPORAL DATA AND THE YEAR 2000 PROBLEM SQL
PROGRAMMING TECHNIQUES CLASSICS FORUM UPDATABLE VIEWS THEORY,
NORMALIZATION, AND ADVANCED DATABASE TOPICS BOOKS ON SQL-92 AND SQL-99
STANDARDS AND RELATED CROUPS WEB SITES RELATED TO SQL STATISTICS
TEMPORAL DATABASES NEW CITATIONS 751 752 754 756 757 760 761 761 761 761
762 763 763 764 765 765 766 766 768 768 769 769 770 771 771 772 772 773
774 INDEX 777 ABOUT THE AUTHOR 810
|
| adam_txt |
C O N T E N T S INTRODUCTION TO THE THIRD EDITION XXV 1.1 WHAT CHANGED
IN TEN YEARS XXV 1.2 WHAT IS NEW IN THIS EDITION XXVII 1.3 CORRECTIONS
AND ADDITIONS XXVIII DATABASE DESIGN 1 1.1 SCHEMA AND TABLE CREATION 2
1.1.1 CREATE SCHEMA STATEMENT 3 1.1.2 MANIPULATING TABLES 5 1.1.3 COLUMN
CONSTRAINTS 10 1.1.4 UNIQUE CONSTRAINTS VERSUS UNIQUE INDEXES 17 1.1.5
NESTED UNIQUE CONSTRAINTS 18 1.1.6 OVERLAPPING KEYS 2 2 1.1.7 CREATE
ASSERTION CONSTRAINTS 2 5 1.1.8 USING VIEWS FOR SCHEMA LEVEL CONSTRAINTS
2 5 1.1.9 USING PRIMARY KEYS AND ASSERTIONS FOR CONSTRAINTS 29 1.1.10
AVOIDING ATTRIBUTE SPLITTING 31 1.1.11 MODELING CLASS HIERARCHIES IN DDL
34 1.2 GENERATING UNIQUE SEQUENTIAL NUMBERS FOR KEYS 36 1.2.1 IDENTITY
COLUMNS 3 7 1.2.2 ROWID AND PHYSICAL DISK ADDRESSES 39 1.2.3 SEQUENTIAL
NUMBERING IN PURE SQL 39 1.2.4 GUIDS 41 1.2.5 SEQUENCE GENERATOR
FUNCTIONS 42 1.2.6 UNIQUE VALUE GENERATORS 42 1.2.7 PREALLOCATED VALUES
44 1.2.8 RANDOM ORDER VALUES 45 1.3 A REMARK ON DUPLICATE ROWS 48 1.4
OTHER SCHEMA OBJECTS 50 1.4.1 SCHEMA TABLES 50 1.4.2 TEMPORARY TABLES 51
1.4.3 CREATE DOMAIN STATEMENT 51 CONTENTS (S^ 1.4.4 CREATE TRIGGER
STATEMENT 52 1.4.5 CREATE PROCEDURE STATEMENT 53 1.4.6 DECLARE CURSOR
STATEMENT 53 1 NORMALIZATION 61 2.1 FUNCTIONAL AND MULTIVALUED
DEPENDENCIES 64 2.2 FIRST NORMAL FORM (INF) 64 2.2.1 NOTE ON REPEATED
GROUPS 66 2.3 SECOND NORMAL FORM (2NF) 70 2.4 THIRD NORMAL FORM (3NF) 71
2.5 ELEMENTARY KEY NORMAL FORM (EKNF) 72 2.6 BOYCE-CODD NORMAL FORM
(BCNF) 73 2.7 FOURTH NORMAL FORM (4NF) 75 2.8 FIFTH NORMAL FORM (5NF) 76
2.9 DOMAIN-KEY NORMAL FORM (DKNF) 78 2.10 PRACTICAL HINTS FOR
NORMALIZATION 87 2.11 KEY TYPES 88 2.11.1 NATURAL KEYS 89 2.11.2
ARTIFICIAL KEYS 89 2.11.3 EXPOSED PHYSICAL LOCATORS 89 2.11.4 PRACTICAL
HINTS FOR DENORMALIZATION 91 2.11.5 ROWSORTING 93 3 NUMERIC DATA IN SQL
101 3.1 NUMERIC TYPES 101 3.1.1 BIT, BYTE, AND BOOLEAN DATA TYPES 104
3.2 NUMERIC TYPE CONVERSION 105 3.2.1 ROUNDING AND TRUNCATING 105 3.2.2
CAST() FUNCTION 107 3.3 FOUR-FUNCTION ARITHMETIC 108 3.4 ARITHMETIC AND
NULLS 109 3.5 CONVERTING VALUES TO AND FROM NULL 110 3.5.1 NULLIF0
FUNCTION 110 3.5.2 COALESCE0 FUNCTION 111 3.6 VENDOR MATH FUNCTIONS 113
CONTENTS 3.6.1 NUMBER THEORY OPERATORS 113 3.6.2 EXPONENTIAL FUNCTIONS
116 3.6.3 SCALING FUNCTIONS 116 3.6.4 CONVERTING NUMBERS TO WORDS 117
TEMPORAL DATA TYPES IN SQL 119 4.1 NOTES ON CALENDAR STANDARDS 119 4.2
SQL TEMPORAL DATA TYPES 123 4.2.1 TIPS FOR HANDLING DATES, TIMESTAMPS,
AND TIMES 124 4.2.2 DATE FORMAT STANDARDS 124 4.2.3 HANDLING TIMESTAMPS
125 4.2.4 HANDLING TIMES 127 4.3 QUERIES WITH DATE ARITHMETIC 128 4.4
THE NATURE OF TEMPORAL DATA MODELS 129 4.4.1 TEMPORAL DUPLICATES 129
4.4.2 TEMPORAL DATABASES 135 4.4.3 TEMPORAL PROJECTION AND SELECTION 137
4.4.4 TEMPORAL JOINS 139 4.4.5 MODIFYING VALID-TIME STATE TABLES 145
4.4.6 CURRENT MODIFICATIONS 146 4.4.7 SEQUENCED MODIFICATIONS 150 4.4.8
NONSEQUENCED MODIFICATIONS 155 4.4.9 TRANSACTION-TIME STATE TABLES 156
4.4.10 MAINTAINING THE AUDIT LOG 158 4.4.11 QUERYING THE AUDIT LOG 160
4.4.12 MODIFYING THE AUDIT LOG 164 4.4.13 BITEMPORAL TABLES 164 4.4.14
TEMPORAL SUPPORT IN STANDARD SQL 167 CHARACTER DATA TYPES IN SQL 169 5.1
PROBLEMS WITH SQL STRINGS 170 5.1.1 PROBLEMS OF STRING EQUALITY 170
5.1.2 PROBLEMS OF STRING ORDERING 171 5.1.3 PROBLEMS OF STRING GROUPING
172 5.2 STANDARD STRING FUNCTIONS 172 CONTENTS 5.3 COMMON VENDOR
EXTENSIONS 174 5.3.1 PHONETIC MATCHING 175 5.4 CUTTER TABLES 182 6
NULLS: MISSING DATA IN SQL 185 6.1 EMPTY AND MISSING TABLES 187 6.2
MISSING VALUES IN COLUMNS 187 6.3 CONTEXT AND MISSING VALUES 189 6.4
COMPARING NULLS 190 6.5 NULLS AND LOGIC 190 6.5.1 NULLS IN SUBQUERY
PREDICATES 191 6.5.2 STANDARD SQL SOLUTIONS 193 6.6 MATH AND NULLS 193
6.7 FUNCTIONS AND NULLS 193 6.8 NULLS AND HOST LANGUAGES 194 6.9 DESIGN
ADVICE FOR NULLS 195 6.9.1 AVOIDING NULLS FROM THE HOST PROGRAMS 197
6.10 A NOTE ON MULTIPLE NULL VALUES 198 7 MULTIPLE COLUMN DATA ELEMENTS
201 7.1 DISTANCE FUNCTIONS 201 7.2 STORING AN IP ADDRESS IN SQL 202
7.2.1 A SINGLE VARCHAR( 15) COLUMN 203 7.2.2 ONE INTEGER COLUMN 203
7.2.3 FOUR SMALLINT COLUMNS 205 7.3 CURRENCY AND OTHER UNIT CONVERSIONS
205 7.4 SOCIAL SECURITY NUMBERS 206 7.5 RATIONAL NUMBERS 209 8 TABLE
OPERATIONS 211 8.1 DELETE FROM STATEMENT 211 8.1.1 THE DELETE FROM
CLAUSE 212 8.1.2 THE WHERE CLAUSE 212 8.1.3 DELETING BASED ON DATA IN A
SECOND TABLE 216 8.1.4 DELETING WITHIN THE SAME TABLE 216 3) CONTENTS
8.2 8.3 8.4 8.5 8.1.5 DELETING IN MULTIPLE TABLES WITHOUT REFERENTIAL
INTEGRITY INSERT INTO STATEMENT 8.2.1 8.2.2 8.2.3 INSERT INTO CLAUSE THE
NATURE OF INSERTS BULK LOAD AND UNLOAD UTILITIES THE UPDATE STATEMENT
8.3.1 8.3.2 8.3.3 8.3.4 8.3.5 ANOTE THE UPDATE CLAUSE THE WHERE CLAUSE
THE SET CLAUSE UPDATING WITH A SECOND TABLE USING THE CASE EXPRESSION IN
UPDATES ON FLAWS IN A COMMON VENDOR EXTENSION MERGE STATEMENT 220 221
221 222 223 223 223 224 225 226 228 231 232 9 COMPARISON OR THETA
OPERATORS 235 9.1 CONVERTING DATA TYPES 236 9.2 ROW COMPARISONS IN SQL
238 10 VALUED PREDICATES 241 10.1 IS NULL PREDICATE 241 10.1.1
SOURCESOFNULLS 242 10.2 IS [NOTKTRUE I FALSE | UNKNOWN} PREDICATE 242
10.3 IS [NOT] NORMALIZED PREDICATE 244 11 CASE EXPRESSIONS 247 11.1 THE
CASE EXPRESSION 247 11.1.1 THE COALESCEO AND NULLIF() FUNCTIONS 2 51
11.1.2 CASE EXPRESSIONS WITH GROUP BY 252 11.1.3 CASE, CHECKO CLAUSES
AND LOGICAL IMPLICATION 253 11.1.4 SUBQUERY EXPRESSIONS AND CONSTANTS
257 11.2 ROZENSHTEIN CHARACTERISTIC FUNCTIONS 258 12 LIRE PREDICATE 261
12.1 TRICKS WITH PATTERNS 262 CONTENTS -*BP- 12.2 RESULTS WITH NULL
VALUES AND EMPTY STRINGS 264 12.3 LIKE IS NOT EQUALITY 264 12.4 AVOIDING
THE LIKE PREDICATE WITH A JOIN 264 12.5 CASE EXPRESSIONS AND LIKE
PREDICATES 266 12.6 SIMILARTO PREDICATES 267 12.7 TRICKS WITH STRINGS
269 12.7.1 STRING CHARACTER CONTENT 269 12.7.2 SEARCHING VERSUS
DECLARING A STRING 270 12.7.3 CREATING AN INDEX ON A STRING 270 13
BETWEEN AND OVERLAPS PREDICATES 273 13.1 THE BETWEEN PREDICATE 273
13.1.1 RESULTS WITH NULL VALUES 2 74 13.1.2 RESULTS WITH EMPTY SETS 2 74
13.1.3 PROGRAMMING TIPS 274 13.2 OVERLAPS PREDICATE 275 13.2.1 TIME
PERIODS AND OVERLAPS PREDICATE 275 14 THE [NOT] IN() PREDICATE 287 14.1
OPTIMIZING THE IN() PREDICATE 288 14.2 REPLACING ORS WITH THE IN()
PREDICATE 292 14.3 NULLS AND THE IN() PREDICATE 293 14.4 IN() PREDICATE
AND REFERENTIAL CONSTRAINTS 295 14.5 IN() PREDICATE AND SCALAR QUERIES
297 15 EXISTSO PREDICATE 299 15.1 EXISTS AND NULLS 300 15.2 EXISTS AND
INNER JOINS 302 15.3 NOT EXISTS AND OUTER JOINS 303 15.4 EXISTSO AND
QUANTIFIERS 304 15.5 EXISTSO AND REFERENTIAL CONSTRAINTS 305 15.6 EXISTS
AND THREE-VALUED LOGIC 306 CONTENTS 16 QUANTIFIED SUBQUERY PREDICATES
309 16.1 SCALAR SUBQUERY COMPARISONS 310 16.2 QUANTIFIERS AND MISSING
DATA 311 16.3 THE ALL PREDICATE AND EXTREMA FUNCTIONS 313 16.4 THE
UNIQUE PREDICATE 314 17 THE SELECT STATEMENT 317 17.1 SELECT AND JOINS
317 17.1.1 ONE-LEVEL SELECT STATEMENT 317 17.1.2 CORRELATED SUBQUERIES
IN A SELECT STATEMENT 324 17.1.3 SELECT STATEMENT SYNTAX 326 17.1.4 THE
ORDER BY CLAUSE 328 17.2 OUTERJOINS 336 17.2.1 SYNTAX FOR OUTERJOINS 337
17.2.2 NULLS AND OUTERJOINS 342 17.2.3 NATURAL VERSUS SEARCHED OUTER
JOINS 344 17.2.4 SEIF OUTER JOINS 345 17.2.5 TWO OR MORE OUTERJOINS 346
17.2.6 OUTERJOINS AND AGGREGATE FUNCTIONS 348 17.2.7 FUELL OUTER JOIN 349
17.2.8 WHERE CLAUSE OUTER JOIN OPERATORS 350 17.3 OLD VERSUS NEW JOIN
SYNTAX 351 17.4 SCOPE OF DERIVED TABLE NAMES 353 17.5 JOINS BY FUNCTION
CALLS 354 17.6 THE UNION JOIN 356 17.7 PACKINGJOINS 358 17.8 DR. CODD'S
T-JOIN 359 17.8.1 THE CROATIAN SOLUTION 363 17.8.2 THE SWEDISH SOLUTION
364 17.8.3 THE COLOMBIAN SOLUTION 364 18 VIEWS, DERIVED TABLES,
MATERIALIZED TABLES, AND TEMPORARY TABLES 369 18.1 VIEWS IN QUERIES 370
18.2 UPDATABLE AND READ-ONLY VIEWS 371 CONTENTS DUER55E9HT 18.3 TYPESOF
VIEWS 373 18.3.1 SINGLE-TABLE PROJECTION AND RESTRICTION 373 18.3.2
CALCULATED COLUMNS 373 18.3.3 TRANSLATED COLUMNS 373 18.3.4 GROUPED
VIEWS 374 18.3.5 UNIONED VIEWS 375 18.3.6 JOINSINVIEWS 377 18.3.7 NESTED
VIEWS 377 18.4 HOW VIEWS ARE HANDLED IN THE DATABASE SYSTEM 379 18.4.1
VIEW COLUMN LIST 379 18.4.2 VIEW MATERIALIZATION 379 18.4.3 IN-LINE TEXT
EXPANSION 380 18.4.4 POINTER STRUCTURES 382 18.4.5 INDEXING AND VIEWS
383 18.5 WITH CHECK OPTION CLAUSE 383 18.5.1 WITH CHECK OPTION AS CHECKO
CLAUSE 388 18.6 DROPPING VIEWS 389 18.7 TEMPORARY TABLE DECLARATIONS 390
18.8 HINTS ON USING VIEWS AND TEMPORARY TABLES 391 18.8.1 USING VIEWS
392 18.8.2 USING TEMPORARY TABLES 392 18.8.3 FLATTENING A TABLE WITH A
VIEW 393 18.9 USING DERIVED TABLES 395 18.9.1 DERIVED TABLES IN THE FROM
CLAUSE 395 18.9.2 DERIVED TABLES WITH A VALUES CONSTRUCTOR 397 18.10
DERIVED TABLES IN THE WITH CLAUSE 397 19 PARTITIONING DATA IN QUERIES
401 19.1 COVERINGS AND PARTITIONS 401 19.1.1 PARTITIONING BY RANGES 402
19.1.2 PARTITION BY FUNCTIONS 403 19.1.3 PARTITION BY SEQUENCES 404 19.2
RELATIONAL DIVISION 406 19.2.1 DIVISION WITH A REMAINDER 408 19.2.2
EXACT DIVISION 409 -W 19.3 19.4 19.5 19.2.3 19.2.4 19.2.5 19.2.6
ROMLEY'S BOOLEAN C O N T E N T S NOTE ON PERFORMANCE TODD'S DIVISION
DIVISION WITH JOINS DIVISION WITH SET OPERATORS ; DIVISION EXPRESSIONS
IN AN RDBMS FIFO AND LIFO SUBSETS XVII 410 410 413 413 414 418 420 20
GROUPING OPERATIONS 425 20.1 GROUP BY CLAUSE 425 20.1.1 NULLS AND CROUPS
427 20.2 GROUP BY AND HAVING 427 20.2.1 GROUP CHARACTERISTICS AND THE
HAVING CLAUSE 429 20.3 MULTIPLE AGGREGATION LEVELS 431 20.3.1 GROUPED
VIEWS FOR MULTIPLE AGGREGATION LEVELS 432 20.3.2 SUBQUERY EXPRESSIONS
FOR MULTIPLE AGGREGATION LEVELS 433 20.3.3 CASE EXPRESSIONS FOR MULTIPLE
AGGREGATION LEVELS 434 20.4 GROUPING ON COMPUTED COLUMNS 435 20.5
GROUPING INTO PAIRS 436 20.6 SORTING AND GROUP BY 437 21 AGGREGATE
FUNCTIONS 439 21.1 COUNT() FUNCTIONS 440 21.2 SUM() FUNCTIONS 443 21.3
AVG() FUNCTIONS 444 21.3.1 AVERAGES WITH EMPTY CROUPS 446 21.3.2
AVERAGES ACROSS COLUMNS 448 21.4 EXTREMA FUNCTIONS 449 21.4.1 SIMPLE
EXTREMA FUNCTIONS 449 21.4.2 GENERALIZED EXTREMA FUNCTIONS 451 21.4.3
MULTIPLE CRITERIA EXTREMA FUNCTIONS 460 21.4.4 GREATESTO AND LEASTO
FUNCTIONS 462 21.5 THE LIST0 AGGREGATE FUNCTION 465 21.5.1 THE LIST0
FUNCTION WITH A PROCEDURE 466 CONTENTS 21.5.2 THE LIST() FUNCTION BY
CROSSTABS 467 21.6 THE PRD() AGGREGATE FUNCTION 468 21.6.1 PRD()
FUNCTION BY EXPRESSIONS 469 21.6.2 THE PRD() AGGREGATE FUNCTION BY
LOGARITHMS 470 21.7 BITWISE AGGREGATE FUNCTIONS 473 21.7.1 BITWISE OR
AGGREGATE FUNCTION 474 21.7.2 BITWISE AND AGGREGATE FUNCTION 475 22
AUXILIARY TABLES 477 22.1 THE SEQUENCE TABLE 477 22.1.1 ENUMERATING A
LIST 479 22.1.2 MAPPING A SEQUENCE INTO A CYCLE 481 22.1.3 REPLACING AN
ITERATIVE LOOP 483 22.2 LOOKUP AUXILIARY TABLES 485 22.2.1 SIMPLE
TRANSLATION AUXILIARY TABLES 487 22.2.2 MULTIPLE TRANSLATION AUXILIARY
TABLES 487 22.2.3 MULTIPLE PARAMETER AUXILIARY TABLES 488 22.2.4 RANGE
AUXILIARY TABLES 489 22.2.5 HIERARCHICAL AUXILIARY TABLES 490 22.2.6 ONE
TRUE LOOKUP TABLE 491 22.3 AUXILIARY FUNCTION TABLES 493 22.3.1 INVERSE
FUNCTIONS WITH AUXILIARY TABLES 495 22.3.2 INTERPOLATION WITH AUXILIARY
FUNCTION TABLES 504 22.4 GLOBAL CONSTANTS TABLES 506 23 STATISTICS IN
SQL 509 23.1 THE MODE 510 23.2 THE AVG() FUNCTION 512 23.3 THE MEDIAN
512 23.3.1 DATE'S FIRST MEDIAN 513 23.3.2 CELKO'S FIRST MEDIAN 514
23.3.3 DATE'S SECOND MEDIAN 516 23.3.4 MURCHISON'S MEDIAN 516 23.3.5
CELKO'S SECOND MEDIAN 517 23.3.6 VAUGHAN'S MEDIAN WITH VIEWS 519 ~^SI~
CONTENTS 23.3.7 MEDIAN WITH CHARACTERISTIC FUNCTION 520 23.3.8 CELKO'S
THIRD MEDIAN 522 23.3.9 KEN HENDERSONS MEDIAN 526 23.4 VARIANCE AND
STANDARD DEVIATION 527 23.5 AVERAGE DEVIATION 528 23.6 CUMULATIVE
STATISTICS 528 23.6.1 RUNNING TOTAIS 529 23.6.2 RUNNING DIFFERENCES 530
23.6.3 CUMULATIVE PERCENTAGES 531 23.6.4 RANKINGS AND RELATED STATISTICS
533 23.6.5 QUINTILES AND RELATED STATISTICS 537 23.7 CROSS TABULATIONS
538 23.7.1 CROSSTABS BY CROSS JOIN 542 23.7.2 CROSSTABS BY OUTER JOINS
543 23.7.3 CROSSTABS BY SUBQUERY 544 23.7.4 CROSSTABS BY CASE EXPRESSION
545 23.8 HARMONIE MEAN AND GEOMETRIE MEAN 545 23.9 MULTIVARIABLE
DESCRIPTIVE STATISTICS IN SQL 546 23.9.1 COVARIANCE 546 23.9.2 PEARSON'S
R 547 23.9.3 NULLS IN MULTIVARIABLE DESCRIPTIVE STATISTICS 548 24
REGIONS, RUNS, CAPS, SEQUENCES, AND SERIES 549 24.1 FINDING SUBREGIONS
OF SIZE (N) 550 NUMBERING REGIONS 551 FINDING REGIONS OF MAXIMUM SIZE
552 BOUND QUERIES 557 RUN AND SEQUENCE QUERIES 557 24.5.1 FILLING IN
SEQUENCE NUMBERS 560 SUMMATION OF A SERIES 562 SWAPPING AND SLIDING
VALUES IN A LIST 565 CONDENSING A LIST OF NUMBERS 567 FOLDING A LIST OF
NUMBERS 567 COVERINGS 568 24.2 24.3 24.4 24.5 24.6 24.7 24.8 24.9 24.10
CONTENTS 25 ARRAYS IN SQL 575 25.1 ARRAYS VIA NAMED COLUMNS 576 25.2
ARRAYS VIA SUBSCRIPT COLUMNS 580 25.3 MATRIX OPERATIONS IN SQL 581
25.3.1 MATRIX EQUALITY 582 25.3.2 MATRIX ADDITION 582 25.3.3 MATRIX
MULTIPLICATION 583 25.3.4 OTHER MATRIX OPERATIONS 585 25.4 FLATTENING A
TABLE INTO AN ARRAY 585 25.5 COMPARING ARRAYS IN TABLE FORMAT 587 26 SET
OPERATIONS 591 26.1 UNION AND UNION ALL 592 26.1.1 ORDER OL EXECUTION
594 26.1.2 MIXED UNION AND UNION ALL OPERATORS 595 26.1.3 UNION OF
COLUMNS FROM THE SAME TABLE 595 26.2 INTERSECT AND EXCEPT 596 26.2.1
INTERSECT AND EXCEPT WITHOUT NULLS AND DUPLICATES 599 26.2.2 INTERSECT
AND EXCEPT WITH NULLS AND DUPLICATES 600 26.3 A NOTE ON ALL AND SELECT
DISTINCT 601 26.4 EQUALITY AND PROPER SUBSETS 602 27 SUBSETS 605 27.1
EVERY NTH ITEM IN A TABLE 605 27.2 PICKING RANDOM ROWS FROM A TABLE 607
27.3 THE CONTAINS OPERATORS 612 27.3.1 PROPER SUBSET OPERATORS 612
27.3.2 TABLE EQUALITY 613 27.4 PICKING A REPRESENTATIVE SUBSET 618 28
TREES AND HIERARCHIES IN SQL 623 28.1 ADJACENCY LIST MODEL 624 CONTENTS
28.1.1 COMPLEX CONSTRAINTS 625 28.1.2 PROCEDURAL TRAVERSAL FOR QUERIES
627 28.1.3 ALTERING THE TABLE 628 28.2 THE PATH ENUMERATION MODEL 628
28.2.1 FINDING SUBTREES AND NODES 629 28.2.2 FINDING LEVELS AND
SUBORDINATES 630 28.2.3 DELETING NODES AND SUBTREES 630 28.2.4 INTEGRITY
CONSTRAINTS 631 28.3 NESTED SET MODEL OF HIERARCHIES 631 28.3.1 THE
COUNTING PROPERTY 633 28.3.2 THE CONTAINMENT PROPERTY 634 28.3.3
SUBORDINATES 635 28.3.4 HIERARCHICAL AGGREGATIONS 636 28.3.5 DELETING
NODES AND SUBTREES 636 28.3.6 CONVERTING ADJACENCY LIST TO NESTED SET
MODEL 637 28.4 OTHER MODELS FOR TREES AND HIERARCHIES 639 29 TEMPORAL
QUERIES 641 642 643 645 645 648 652 656 658 660 661 665 666 667 669 670
672 673 29.1 29.2 29.3 29.4 29.5 29.6 29.7 29.8 29.9 TEMPORAL MATH
PERSONAL CALENDARS TIME SERIES 29.3.1 29.3.2 29.3.3 29.3.4 29.3.5 29.3.6
GAPS IN A TIME SERIES CONTINUOUS TIME PERIODS MISSING TIMES IN
CONTIGUOUS EVENTS LOCATING DATES TEMPORAL STARTING AND ENDING POINTS
AVERAGE WAIT TIMES JULIAN DATES DATE AND TIME EXTRACTION FUNCTIONS OTHER
TEMPORAL FUNCTIONS WEEKS 29.7.1 MODELIN^ 29.8.1 CALENDAR SORTING BY
WEEKDAY NAMES ; TIME IN TABLES USING DURATION PAIRS AUXILIARY TABLE
CONTENTS 29.10 PROBLEMS WITHTHEYEAR 2000 675 29.10.1 THEZEROS 675
29.10.2 LEAPYEAR 676 29.10.3 THE MILLENNIUM 677 29.10.4 WEIRD DATES IN
LEGACY DATA 679 29.10.5 THE AFTERMATH 680 681 682 682 683 683 684 684
685 686 687 687 688 691 695 697 703 705 706 31 OLAP IN SQL 709 710 711
711 711 712 714 716 717 30 GRAPHS IN 30.1 30.2 30.3 30.4 30.5 30.6 SQL
BASIC GRAPH CHARACTERISTICS 30.1.1 30.1.2 30.1.3 30.1.4 30.1.5 30.1.6
ALL NODES IN THE GRAPH PATH ENDPOINTS REACHABLE NODES EDGES INDEGREE AND
OUTDEGREE SOURCE, SINK, ISOLATED, AND INTERNAL NODES PATHS IN A GRAPH
30.2.1 30.2.2 30.2.3 30.2.4 LENGTH OF PATHS SHORTEST PATH PATHS BY
ITERATION LISTING THE PATHS ACYCLIC GRAPHS AS NESTED SETS PATHS WITH CTE
30.4.1 NONACYCLIC GRAPHS ADJACENCY MATRIX MODEL POINTS INSIDE POLYGONS
1.1 1.2 STAR SCHEMA OLAP FUNCTIONALITY 31.2.1 31.2.2 31.2.3 31.2.4
31.2.5 31.2.6 RANK AND DENSE_RANK ROW NUMBERING GROUPING OPERATORS THE
WINDOW CLAUSE OLAP EXAMPLES OF SQL ENTERPRISE-WIDE DIMENSIONAL LAYER
CONTENTS 31.3 A BIT OF HISTORY 718 32 TRANSACTIONS AND CONCURRENCY
CONTROL 719 32.1 SESSIONS 719 32.2 TRANSACTIONS AND ACID 720 32.2.1
ATOMICITY 720 32.2.2 CONSISTENCY 721 32.2.3 ISOLATION 721 32.2.4
DURABILITY 722 32.3 CONCURRENCY CONTROL 722 32.3.1 THE FIVE PHENOMENA
722 32.3.2 THE ISOLATION LEVELS 724 32.3.3 CURSOR STABILITY ISOLATION
LEVEL 726 32.4 PESSIMISTIC CONCURRENCY CONTROL 726 32.5 SNAPSHOT
ISOLATION: OPTIMISTIC CONCURRENCY 727 32.6 LOGICAL CONCURRENCY CONTROL
729 32.7 DEADLOCK AND LIVELOCKS 730 33 OPTIMIZING SQL 731 33.1 ACCESS
METHODS 732 33.1.1 SEQUENTIAL ACCESS 732 33.1.2 INDEXED ACCESS 732
33.1.3 HASHED INDEXES 733 33.1.4 BIT VECTOR INDEXES 733 33.2 EXPRESSIONS
AND UNNESTED QUERIES 733 33.2.1 USE SIMPLE EXPRESSIONS 734 33.2.2 STRING
EXPRESSIONS 738 33.3 GIVE EXTRA JOIN INFORMATION IN QUERIES 738 33.4
INDEXTABLES CAREFULLY 740 33.5 WATCH THE IN PREDICATE 742 33.6 AVOID
UNIONS 744 33.7 PREFER JOINS OVER NESTED QUERIES 745 33.8 AVOID
EXPRESSIONS ON INDEXED COLUMNS 746 33.9 AVOID SORTING 746 33.10 AVOID
CROSSJOINS 750 XXIV 33.11 33.12 33.13 33.14 33.15 33.16 REFERENCES C O N
T E N T S LEARN TO USE INDEXES CAREFULLY ORDER INDEXES CAREFULLY KNOW
YOUR OPTIMIZER RECOMPILE STATIC SQL AFTER SCHEMA CHANGES TEMPORARY
TABLES ARE SOMETIMES HANDY UPDATE STATISTICS GENERAL REFERENCES LOGIC
MATHEMATICAL TECHNIQUES RANDOM NUMBERS SCALES AND MEASUREMENTS MISSING
VALUES REGULAER EXPRESSIONS GRAPH THEORY INTRODUCTORY SQL BOOKS
OPTIMIZING QUERIES TEMPORAL DATA AND THE YEAR 2000 PROBLEM SQL
PROGRAMMING TECHNIQUES CLASSICS FORUM UPDATABLE VIEWS THEORY,
NORMALIZATION, AND ADVANCED DATABASE TOPICS BOOKS ON SQL-92 AND SQL-99
STANDARDS AND RELATED CROUPS WEB SITES RELATED TO SQL STATISTICS
TEMPORAL DATABASES NEW CITATIONS 751 752 754 756 757 760 761 761 761 761
762 763 763 764 765 765 766 766 768 768 769 769 770 771 771 772 772 773
774 INDEX 777 ABOUT THE AUTHOR 810 |
| any_adam_object | 1 |
| any_adam_object_boolean | 1 |
| author | Celko, Joe |
| author_facet | Celko, Joe |
| author_role | aut |
| author_sort | Celko, Joe |
| author_variant | j c jc |
| building | Verbundindex |
| bvnumber | BV035186389 |
| callnumber-first | Q - Science |
| callnumber-label | QA76 |
| callnumber-raw | QA76.73.S67 |
| callnumber-search | QA76.73.S67 |
| callnumber-sort | QA 276.73 S67 |
| callnumber-subject | QA - Mathematics |
| classification_rvk | ST 271 |
| ctrlnum | (OCoLC)61129114 (DE-599)OBVAC05063429 |
| dewey-full | 005.133 |
| dewey-hundreds | 000 - Computer science, information, general works |
| dewey-ones | 005 - Computer programming, programs, data, security |
| dewey-raw | 005.133 |
| dewey-search | 005.133 |
| dewey-sort | 15.133 |
| dewey-tens | 000 - Computer science, information, general works |
| discipline | Informatik |
| discipline_str_mv | Informatik |
| edition | 3. ed. |
| format | Book |
| fullrecord | <?xml version="1.0" encoding="UTF-8"?><collection xmlns="http://www.loc.gov/MARC21/slim"><record><leader>01710nam a2200457zcb4500</leader><controlfield tag="001">BV035186389</controlfield><controlfield tag="003">DE-604</controlfield><controlfield tag="005">20081215 </controlfield><controlfield tag="007">t</controlfield><controlfield tag="008">081128s2005 |||| 00||| eng d</controlfield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">9780123693792</subfield><subfield code="9">978-0-12-369379-2</subfield></datafield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">0123693799</subfield><subfield code="c">(pbk) : £29.99</subfield><subfield code="9">0-12-369379-9</subfield></datafield><datafield tag="024" ind1="3" ind2=" "><subfield code="a">9780123693792</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(OCoLC)61129114</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(DE-599)OBVAC05063429</subfield></datafield><datafield tag="040" ind1=" " ind2=" "><subfield code="a">DE-604</subfield><subfield code="b">ger</subfield><subfield code="e">rakwb</subfield></datafield><datafield tag="041" ind1="0" ind2=" "><subfield code="a">eng</subfield></datafield><datafield tag="049" ind1=" " ind2=" "><subfield code="a">DE-859</subfield></datafield><datafield tag="050" ind1=" " ind2="0"><subfield code="a">QA76.73.S67</subfield></datafield><datafield tag="082" ind1="0" ind2=" "><subfield code="a">005.133</subfield><subfield code="2">22</subfield></datafield><datafield tag="084" ind1=" " ind2=" "><subfield code="a">ST 271</subfield><subfield code="0">(DE-625)143639:</subfield><subfield code="2">rvk</subfield></datafield><datafield tag="100" ind1="1" ind2=" "><subfield code="a">Celko, Joe</subfield><subfield code="e">Verfasser</subfield><subfield code="4">aut</subfield></datafield><datafield tag="245" ind1="1" ind2="0"><subfield code="a">Joe Celko's SQL for smarties</subfield><subfield code="b">advanced SQL programming</subfield><subfield code="c">Joe Celko</subfield></datafield><datafield tag="246" ind1="1" ind2="3"><subfield code="a">SQL for smarties</subfield></datafield><datafield tag="250" ind1=" " ind2=" "><subfield code="a">3. ed.</subfield></datafield><datafield tag="264" ind1=" " ind2="1"><subfield code="a">San Francisco, Calif.</subfield><subfield code="b">Kaufmann [u.a.]</subfield><subfield code="c">2005</subfield></datafield><datafield tag="300" ind1=" " ind2=" "><subfield code="a">XXVIII, 808 S.</subfield></datafield><datafield tag="336" ind1=" " ind2=" "><subfield code="b">txt</subfield><subfield code="2">rdacontent</subfield></datafield><datafield tag="337" ind1=" " ind2=" "><subfield code="b">n</subfield><subfield code="2">rdamedia</subfield></datafield><datafield tag="338" ind1=" " ind2=" "><subfield code="b">nc</subfield><subfield code="2">rdacarrier</subfield></datafield><datafield tag="490" ind1="0" ind2=" "><subfield code="a">The Morgan Kaufmann series in data management systems</subfield></datafield><datafield tag="500" ind1=" " ind2=" "><subfield code="a">Literaturverz. S. [761] - 775</subfield></datafield><datafield tag="650" ind1=" " ind2="4"><subfield code="a">SQL (computer program language)</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">SQL</subfield><subfield code="0">(DE-588)4134010-3</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">Programmierung</subfield><subfield code="0">(DE-588)4076370-5</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="689" ind1="0" ind2="0"><subfield code="a">SQL</subfield><subfield code="0">(DE-588)4134010-3</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2="1"><subfield code="a">Programmierung</subfield><subfield code="0">(DE-588)4076370-5</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2=" "><subfield code="8">1\p</subfield><subfield code="5">DE-604</subfield></datafield><datafield tag="856" ind1="4" ind2="2"><subfield code="m">GBV Datenaustausch</subfield><subfield code="q">application/pdf</subfield><subfield code="u">http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=016993070&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA</subfield><subfield code="3">Inhaltsverzeichnis</subfield></datafield><datafield tag="999" ind1=" " ind2=" "><subfield code="a">oai:aleph.bib-bvb.de:BVB01-016993070</subfield></datafield><datafield tag="883" ind1="1" ind2=" "><subfield code="8">1\p</subfield><subfield code="a">cgwrk</subfield><subfield code="d">20201028</subfield><subfield code="q">DE-101</subfield><subfield code="u">https://d-nb.info/provenance/plan#cgwrk</subfield></datafield></record></collection> |
| id | DE-604.BV035186389 |
| illustrated | Not Illustrated |
| index_date | 2024-07-02T22:59:53Z |
| indexdate | 2024-07-09T21:26:59Z |
| institution | BVB |
| isbn | 9780123693792 0123693799 |
| language | English |
| oai_aleph_id | oai:aleph.bib-bvb.de:BVB01-016993070 |
| oclc_num | 61129114 |
| open_access_boolean | |
| owner | DE-859 |
| owner_facet | DE-859 |
| physical | XXVIII, 808 S. |
| publishDate | 2005 |
| publishDateSearch | 2005 |
| publishDateSort | 2005 |
| publisher | Kaufmann [u.a.] |
| record_format | marc |
| series2 | The Morgan Kaufmann series in data management systems |
| spelling | Celko, Joe Verfasser aut Joe Celko's SQL for smarties advanced SQL programming Joe Celko SQL for smarties 3. ed. San Francisco, Calif. Kaufmann [u.a.] 2005 XXVIII, 808 S. txt rdacontent n rdamedia nc rdacarrier The Morgan Kaufmann series in data management systems Literaturverz. S. [761] - 775 SQL (computer program language) SQL (DE-588)4134010-3 gnd rswk-swf Programmierung (DE-588)4076370-5 gnd rswk-swf SQL (DE-588)4134010-3 s Programmierung (DE-588)4076370-5 s 1\p DE-604 GBV Datenaustausch application/pdf http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=016993070&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA Inhaltsverzeichnis 1\p cgwrk 20201028 DE-101 https://d-nb.info/provenance/plan#cgwrk |
| spellingShingle | Celko, Joe Joe Celko's SQL for smarties advanced SQL programming SQL (computer program language) SQL (DE-588)4134010-3 gnd Programmierung (DE-588)4076370-5 gnd |
| subject_GND | (DE-588)4134010-3 (DE-588)4076370-5 |
| title | Joe Celko's SQL for smarties advanced SQL programming |
| title_alt | SQL for smarties |
| title_auth | Joe Celko's SQL for smarties advanced SQL programming |
| title_exact_search | Joe Celko's SQL for smarties advanced SQL programming |
| title_exact_search_txtP | Joe Celko's SQL for smarties advanced SQL programming |
| title_full | Joe Celko's SQL for smarties advanced SQL programming Joe Celko |
| title_fullStr | Joe Celko's SQL for smarties advanced SQL programming Joe Celko |
| title_full_unstemmed | Joe Celko's SQL for smarties advanced SQL programming Joe Celko |
| title_short | Joe Celko's SQL for smarties |
| title_sort | joe celko s sql for smarties advanced sql programming |
| title_sub | advanced SQL programming |
| topic | SQL (computer program language) SQL (DE-588)4134010-3 gnd Programmierung (DE-588)4076370-5 gnd |
| topic_facet | SQL (computer program language) SQL Programmierung |
| url | http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=016993070&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |
| work_keys_str_mv | AT celkojoe joecelkossqlforsmartiesadvancedsqlprogramming AT celkojoe sqlforsmarties |