skip to content
Joe Celko's thinking in sets : auxiliary, temporal, and virtual tables in SQL Preview this item
ClosePreview this item
Checking...

Joe Celko's thinking in sets : auxiliary, temporal, and virtual tables in SQL

Author: Joe Celko
Publisher: Amsterdam ; Boston : Elsevier / Morgan Kaufmann, ©2008.
Series: Morgan Kaufmann series in data management systems.
Edition/Format:   eBook : Document : EnglishView all editions and formats
Summary:
Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity. This book will change the way you think about the problems you solve with SQL programs.  Read more...
Rating:

(not yet rated) 0 with reviews - Be the first.

Subjects
More like this

 

Find a copy online

Links to this item

Find a copy in the library

&AllPage.SpinnerRetrieving; Finding libraries that hold this item...

Details

Genre/Form: Electronic books
Additional Physical Format: Print version:
Celko, Joe.
Joe Celko's thinking in sets.
Amsterdam ; Boston : Elsevier / Morgan Kaufmann, ©2008
(DLC) 2007043898
(OCoLC)176861723
Material Type: Document, Internet resource
Document Type: Internet Resource, Computer File
All Authors / Contributors: Joe Celko
ISBN: 9780123741370 0123741378 9780080557526 008055752X 128117212X 9781281172129
OCLC Number: 228148104
Description: 1 online resource (xv, 362 pages) : illustrations.
Contents: Table of Contents --
Preface xvii --
1 SQL Is Declarative, Not Procedural --
1.1 Different Programming Models --
1.2 Different Data Models --
1.2.1 Columns Are Not Fields --
1.2.2 Rows Are Not Records --
1.2.3 Tables Are Not Files --
1.2.4 Relational Keys Are Not Record Locators --
1.2.5 Kinds of Keys --
1.2.6 Desirable Properties of Relational Keys --
1.2.7 Unique But Not Invariant --
1.3 Tables as Entities --
1.4 Tables as Relationships --
1.5 Statements Are Not Procedures --
1.6 Molecular, Atomic, and Subatomic Data Elements --
1.6.1 Table Splitting --
1.6.2 Column Splitting --
1.6.3 Temporal Splitting --
1.6.4 Faking Non-1NF Data --
1.6.5 Molecular Data Elements --
1.6.6 Isomer Data Elements --
1.6.7 Validating a Molecule --
2 Hardware, Data Volume, and Maintaining Databases --
2.1 Parallelism --
2.2 Cheap Main Storage --
2.3 Solid-State Disk --
2.4 Cheaper Secondary and Tertiary Storage --
2.5 The Data Changed --
2.6 The Mindset Has Not Changed --
3 Data Access and Records --
3.1 Sequential Access --
3.1.1 Tape-Searching Algorithms --
3.2 Indexes --
3.2.1 Single-Table Indexes --
3.2.2 Multiple-Table Indexes --
3.2.3 Type of Indexes --
3.3 Hashing --
3.3.1 Digit Selection --
3.3.2 Division Hashing --
3.3.3 Multiplication Hashing --
3.3.4 Folding --
3.3.5 Table Lookups --
3.3.6 Collisions --
3.4 Bit Vector Indexes --
3.5 Parallel Access --
3.6 Row and Column Storage --
3.6.1 Row-Based Storage --
3.6.2 Column-Based Storage --
3.7 JOIN Algorithms --
3.7.1 Nested-Loop Join Algorithm --
3.7.2 Sort-Merge Join Method --
3.7.3 Hash Join Method --
3.7.4 Shins Algorithm --
4 Lookup Tables --
4.1 Data Element Names --
4.2 Multiparameter Lookup Tables --
4.3 Constants Table --
4.4 OTLT or MUCK Table Problems --
4.5 Defi nition of a Proper Table --
--
5 Auxiliary Tables --
5.1 Sequence Table --
5.1.1 Creating a Sequence Table --
5.1.2 Sequence Constructor --
5.1.3 Replacing an Iterative Loop --
5.2 Permutations --
5.2.1 Permutations via Recursion --
5.2.2 Permutations via CROSS JOIN --
5.3 Functions --
5.3.1 Functions without a Simple Formula --
5.4 Encryption via Tables --
5.5 Random Numbers --
5.6 Interpolation --
6 Views --
6.1 Mullins VIEW Usage Rules --
6.1.1 Effi cient Access and Computations --
6.1.2 Column Renaming --
6.1.3 Proliferation Avoidance --
6.1.4 The VIEW Synchronization Rule --
6.2 Updatable and Read-Only VIEWs --
6.3 Types of VIEWs --
6.3.1 Single-Table Projection and Restriction --
6.3.2 Calculated Columns --
6.3.3 Translated Columns --
6.3.4 Grouped VIEWs --
6.3.5 UNIONed VIEWs --
6.3.6 JOINs in VIEWs --
6.3.7 Nested VIEWs --
6.4 Modeling Classes with Tables --
6.4.1 Class Hierarchies in SQL --
6.4.2 Subclasses via ASSERTIONs and TRIGGERs --
6.5 How VIEWs Are Handled in the Database System --
6.5.1 VIEW Column List --
6.5.2 VIEW Materialization --
6.6 In-Line Text Expansion --
6.7 WITH CHECK OPTION Clause --
6.7.1 WITH CHECK OPTION as CHECK() Clause --
6.8 Dropping VIEWs --
6.9 Outdated Uses for VIEWs --
6.9.1 Domain Support --
6.9.2 Table Expression VIEWs --
6.9.3 VIEWs for Table Level CHECK() Constraints --
6.9.4 One VIEW per Base Table --
7 Virtual Tables --
7.1 Derived Tables --
7.1.1 Column Naming Rules --
7.1.2 Scoping Rules --
7.1.3 Exposed Table Names --
7.1.4 LATERAL() Clause --
7.2 Common Table Expressions --
7.2.1 Nonrecursive CTEs --
7.2.2 Recursive CTEs --
7.3 Temporary Tables --
7.3.1 ANSI/ISO Standards --
7.3.2 Vendors Models --
7.4 The Information Schema --
7.4.1 The INFORMATION_SCHEMA Declarations --
7.4.2 A Quick List of VIEWS and Their Purposes --
7.4.3 DOMAIN Declarations --
7.4.4 Defi nition Schema --
7.4.5 INFORMATION_SCHEMA Assertions --
8 Complicated Functions via Tables --
8.1 Functions without a Simple Formula --
8.1.1 Encryption via Tables --
8.2 Check Digits via Tables --
8.2.1 Check Digits Defi ned --
8.2.2 Error Detection versus Error Correction --
8.3 Classes of Algorithms --
8.3.1 Weighted-Sum Algorithms --
8.3.2 Power-Sum Check Digits --
8.3.3 Luhn Algorithm --
8.3.4 Dihedral Five Check Digit --
8.4 Declarations, Not Functions, Not Procedures --
8.5 Data Mining for Auxiliary Tables --
9 Temporal Tables --
9.1 The Nature of Time --
9.1.1 Durations, Not Chronons --
9.1.2 Granularity --
9.2 The ISO Half-Open Interval Model --
9.2.1 Use of NULL for Eternity --
9.2.2 Single Timestamp Tables --
9.2.3 Overlapping Intervals --
9.3 State Transition Tables --
9.4 Consolidating Intervals --
9.4.1 Cursors and Triggers --
9.4.2 OLAP Function Solution --
9.4.3 CTE Solution --
9.5 Calendar Tables --
9.5.1 Day of Week via Tables --
9.5.2 Holiday Lists --
9.5.3 Report Periods --
9.5.4 Self-Updating Views --
9.6 History Tables --
9.6.1 Audit Trails --
10 Scrubbing Data with Non-1NF Tables --
10.1 Repeated Groups --
10.1.1 Sorting within a Repeated Group --
10.2 Designing Scrubbing Tables --
10.3 Scrubbing Constraints --
10.4 Calendar Scrubs --
10.4.1 Special Dates --
10.5 String Scrubbing --
10.6 Sharing SQL Data --
10.6.1 A Look at Data Evolution --
10.6.2 Databases --
10.7 Extract, Transform, and Load Products --
10.7.1 Loading Data Warehouses --
10.7.2 Doing It All in SQL --
10.7.3 Extract, Load, and then Transform --
11 Thinking in SQL --
11.1 Warm-up Exercises --
11.1.1 The Whole and Not the Parts --
11.1.2 Characteristic Functions --
11.1.3 Locking into a Solution Early --
11.2 Heuristics --
11.2.1 Put the Specification into a Clear Statement --
11.2.2 Add the Words Set of All in Front of the Nouns --
11.2.3 Remove Active Verbs from the Problem Statement --
11.2.4 You Can Still Use Stubs --
11.2.5 Do Not Worry about Displaying the Data --
11.2.6 Your First Attempts Need Special Handling --
11.2.7 Do Not Be Afraid to Throw Away Your First Attempts at DDL --
11.2.8 Save Your First Attempts at DML --
11.2.9 Do Not Think with Boxes and Arrows --
11.2.10 Draw Circles and Set Diagrams --
11.2.11 Learn Your Dialect --
11.2.12 Imagine that Your WHERE Clause Is Super Amoeba --
11.2.13 Use the Newsgroups, Blogs, and Internet --
11.3 Do Not Use BIT or BOOLEAN Flags in SQL --
11.3.1 Flags Are at the Wrong Level --
11.3.2 Flags Confuse Proper Attributes --
12 Group Characteristics --
12.1 Grouping Is Not Equality --
12.2 Using Groups without Looking Inside --
12.2.1 Semiset-Oriented Approach --
12.2.2 Grouped Solutions --
12.2.3 Aggregated Solutions --
12.3 Grouping over Time --
12.3.1 Piece-by-Piece Solution --
12.3.2 Data as a Whole Solution --
12.4 Other Tricks with HAVING Clauses --
12.5 Groupings, Rollups, and Cubes --
12.5.1 GROUPING SET Clause --
12.5.2 The ROLLUP Clause --
12.5.3 The CUBE Clause --
12.5.4 A Footnote about Super Grouping --
12.6 The WINDOW Clause --
12.6.1 The PARTITION BY Clause --
12.6.2 The ORDER BY Clause --
12.6.3 The RANGE Clause --
12.6.4 Programming Tricks --
13 Turning Specifications into Code --
13.1 Signs of Bad SQL --
13.1.1 Is the Code Formatted Like Another Language? --
13.1.2 Assuming Sequential Access --
13.1.3 Cursors --
13.1.4 Poor Cohesion --
13.1.5 Table-Valued Functions --
13.1.6 Multiple Names for the Same Data Element --
13.1.7 Formatting in the Database --
13.1.8 Keeping Dates in Strings --
13.1.9 BIT Flags, BOOLEAN, and Other Computed Columns --
13.1.10 Attribute Splitting Across Columns --
13.1.11 Attribute Splitting Across Rows --
13.1.12 Attribute Splitting Across Tables --
13.2 Methods of Attack --
13.2.1 Cursor-Based Solution --
13.2.2 Semiset-Oriented Approach --
13.2.3 Pure Set-Oriented Approach --
13.2.4 Advantages of Set-Oriented Code --
13.3 Translating Vague Specifications --
13.3.1 Go Back to the DDL --
13.3.2 Changing Specifications --
14 Using Procedure and Function Calls --
14.1 Clearing out Spaces in a String --
14.1.1 Procedural Solution #1 --
14.1.2 Functional Solution #1 --
14.1.3 Functional Solution #2 --
14.2 The PRD() Aggregate Function --
14.3 Long Parameter Lists in Procedures and Functions --
14.3.1 The IN() Predicate Parameter Lists --
15 Numbering Rows --
15.1 Procedural Solutions --
15.1.1 Reordering on a Numbering Column --
15.2 OLAP Functions --
15.2.1 Simple Row Numbering --
15.2.2 RANK() and DENSE_RANK() --
15.3 Sections --
--
16 Keeping Computed Data --
16.1 Procedural Solution --
16.2 Relational Solution --
16.3 Other Kinds of Computed Data --
17 Triggers for Constraints --
17.1 Triggers for Computations --
17.2 Complex Constraints via CHECK() and CASE Constraints --
17.3 Complex Constraints via VIEWs --
17.3.1 Set-Oriented Solutions --
17.4 Operations on VIEWs as Constraints --
17.4.1 The Basic Three Operations --
17.4.2 WITH CHECK OPTION Clause --
17.4.3 WITH CHECK OPTION as CHECK() clause --
17.4.4 How VIEWs Behave --
17.4.5 UNIONed VIEWs --
17.4.6 Simple INSTEAD OF Triggers --
17.4.7 Warnings about INSTEAD OF Triggers --
18 Procedural and Data Driven Solutions --
18.1 Removing Letters in a String --
18.1.1 The Procedural Solution --
18.1.2 Pure SQL Solution --
18.1.3 Impure SQL Solution --
18.2 Two Approaches to Sudoku --
18.2.1 Procedural Approach --
18.2.2 Data-Driven Approach --
18.2.3 Handling the Given Digits --
18.3 Data Constraint Approach --
18.4 Bin Packing Problems --
18.4.1 The Procedural Approach --
18.4.2 The SQL Approach --
18.5 Inventory Costs over Time --
18.5.1 Inventory UPDATE Statements --
18.5.2 Bin Packing Returns --
Index. SQL is declarative, not procedural --
Auxiliary tables --
Temporal tables --
Virtual tables --
Auxiliary tables --
Views --
Virtual tables --
Complicated functions via tables --
Temporal tables --
Scrubbing data with non-1nf tables --
Thinking in SQL --
Group characteristics --
Turning specifications into code --
Using procedure and function calls --
Numbering rows --
Keeping computed data --
Triggers for constraints --
Procedural and data-driven solutions.
Series Title: Morgan Kaufmann series in data management systems.
Other Titles: Thinking in sets
Thinking in sets
Responsibility: Joe Celko.

Abstract:

Perfectly intelligent programmers often struggle when forced to work with SQL. Why? This title intends to change the way you think about the problems you solve with SQL programs. Focusing on three  Read more...

Reviews

User-contributed reviews
Retrieving GoodReads reviews...
Retrieving DOGObooks reviews...

Tags

Be the first.
Confirm this request

You may have already requested this item. Please select Ok if you would like to proceed with this request anyway.

Linked Data


Primary Entity

<http://www.worldcat.org/oclc/228148104> # Joe Celko's thinking in sets : auxiliary, temporal, and virtual tables in SQL
    a schema:Book, schema:CreativeWork, schema:MediaObject ;
   library:oclcnum "228148104" ;
   library:placeOfPublication <http://experiment.worldcat.org/entity/work/data/115338411#Place/amsterdam> ; # Amsterdam
   library:placeOfPublication <http://dbpedia.org/resource/Boston> ; # Boston
   library:placeOfPublication <http://id.loc.gov/vocabulary/countries/ne> ;
   schema:about <http://experiment.worldcat.org/entity/work/data/115338411#Topic/computers_programming_languages_general> ; # COMPUTERS--Programming Languages--General
   schema:about <http://dewey.info/class/005.13/e22/> ;
   schema:about <http://id.worldcat.org/fast/1102869> ; # SQL (Computer program language)
   schema:about <http://id.worldcat.org/fast/889108> ; # Declarative programming
   schema:alternateName "Thinking in sets" ;
   schema:bookFormat schema:EBook ;
   schema:copyrightYear "2008" ;
   schema:creator <http://viaf.org/viaf/50934021> ; # Joe Celko
   schema:datePublished "2008" ;
   schema:description "Table of Contents -- Preface xvii -- 1 SQL Is Declarative, Not Procedural -- 1.1 Different Programming Models -- 1.2 Different Data Models -- 1.2.1 Columns Are Not Fields -- 1.2.2 Rows Are Not Records -- 1.2.3 Tables Are Not Files -- 1.2.4 Relational Keys Are Not Record Locators -- 1.2.5 Kinds of Keys -- 1.2.6 Desirable Properties of Relational Keys -- 1.2.7 Unique But Not Invariant -- 1.3 Tables as Entities -- 1.4 Tables as Relationships -- 1.5 Statements Are Not Procedures -- 1.6 Molecular, Atomic, and Subatomic Data Elements -- 1.6.1 Table Splitting -- 1.6.2 Column Splitting -- 1.6.3 Temporal Splitting -- 1.6.4 Faking Non-1NF Data -- 1.6.5 Molecular Data Elements -- 1.6.6 Isomer Data Elements -- 1.6.7 Validating a Molecule -- 2 Hardware, Data Volume, and Maintaining Databases -- 2.1 Parallelism -- 2.2 Cheap Main Storage -- 2.3 Solid-State Disk -- 2.4 Cheaper Secondary and Tertiary Storage -- 2.5 The Data Changed -- 2.6 The Mindset Has Not Changed -- 3 Data Access and Records -- 3.1 Sequential Access -- 3.1.1 Tape-Searching Algorithms -- 3.2 Indexes -- 3.2.1 Single-Table Indexes -- 3.2.2 Multiple-Table Indexes -- 3.2.3 Type of Indexes -- 3.3 Hashing -- 3.3.1 Digit Selection -- 3.3.2 Division Hashing -- 3.3.3 Multiplication Hashing -- 3.3.4 Folding -- 3.3.5 Table Lookups -- 3.3.6 Collisions -- 3.4 Bit Vector Indexes -- 3.5 Parallel Access -- 3.6 Row and Column Storage -- 3.6.1 Row-Based Storage -- 3.6.2 Column-Based Storage -- 3.7 JOIN Algorithms -- 3.7.1 Nested-Loop Join Algorithm -- 3.7.2 Sort-Merge Join Method -- 3.7.3 Hash Join Method -- 3.7.4 Shins Algorithm -- 4 Lookup Tables -- 4.1 Data Element Names -- 4.2 Multiparameter Lookup Tables -- 4.3 Constants Table -- 4.4 OTLT or MUCK Table Problems -- 4.5 Defi nition of a Proper Table -- -- 5 Auxiliary Tables -- 5.1 Sequence Table -- 5.1.1 Creating a Sequence Table -- 5.1.2 Sequence Constructor -- 5.1.3 Replacing an Iterative Loop -- 5.2 Permutations -- 5.2.1 Permutations via Recursion -- 5.2.2 Permutations via CROSS JOIN -- 5.3 Functions -- 5.3.1 Functions without a Simple Formula -- 5.4 Encryption via Tables -- 5.5 Random Numbers -- 5.6 Interpolation -- 6 Views -- 6.1 Mullins VIEW Usage Rules -- 6.1.1 Effi cient Access and Computations -- 6.1.2 Column Renaming -- 6.1.3 Proliferation Avoidance -- 6.1.4 The VIEW Synchronization Rule -- 6.2 Updatable and Read-Only VIEWs -- 6.3 Types of VIEWs -- 6.3.1 Single-Table Projection and Restriction -- 6.3.2 Calculated Columns -- 6.3.3 Translated Columns -- 6.3.4 Grouped VIEWs -- 6.3.5 UNIONed VIEWs -- 6.3.6 JOINs in VIEWs -- 6.3.7 Nested VIEWs -- 6.4 Modeling Classes with Tables -- 6.4.1 Class Hierarchies in SQL -- 6.4.2 Subclasses via ASSERTIONs and TRIGGERs -- 6.5 How VIEWs Are Handled in the Database System -- 6.5.1 VIEW Column List -- 6.5.2 VIEW Materialization -- 6.6 In-Line Text Expansion -- 6.7 WITH CHECK OPTION Clause -- 6.7.1 WITH CHECK OPTION as CHECK() Clause -- 6.8 Dropping VIEWs -- 6.9 Outdated Uses for VIEWs -- 6.9.1 Domain Support -- 6.9.2 Table Expression VIEWs -- 6.9.3 VIEWs for Table Level CHECK() Constraints -- 6.9.4 One VIEW per Base Table -- 7 Virtual Tables -- 7.1 Derived Tables -- 7.1.1 Column Naming Rules -- 7.1.2 Scoping Rules -- 7.1.3 Exposed Table Names -- 7.1.4 LATERAL() Clause -- 7.2 Common Table Expressions -- 7.2.1 Nonrecursive CTEs -- 7.2.2 Recursive CTEs -- 7.3 Temporary Tables -- 7.3.1 ANSI/ISO Standards -- 7.3.2 Vendors Models -- 7.4 The Information Schema -- 7.4.1 The INFORMATION_SCHEMA Declarations -- 7.4.2 A Quick List of VIEWS and Their Purposes -- 7.4.3 DOMAIN Declarations -- 7.4.4 Defi nition Schema -- 7.4.5 INFORMATION_SCHEMA Assertions -- 8 Complicated Functions via Tables -- 8.1 Functions without a Simple Formula -- 8.1.1 Encryption via Tables -- 8.2 Check Digits via Tables -- 8.2.1 Check Digits Defi ned -- 8.2.2 Error Detection versus Error Correction -- 8.3 Classes of Algorithms -- 8.3.1 Weighted-Sum Algorithms -- 8.3.2 Power-Sum Check Digits -- 8.3.3 Luhn Algorithm -- 8.3.4 Dihedral Five Check Digit -- 8.4 Declarations, Not Functions, Not Procedures -- 8.5 Data Mining for Auxiliary Tables -- 9 Temporal Tables -- 9.1 The Nature of Time -- 9.1.1 Durations, Not Chronons -- 9.1.2 Granularity -- 9.2 The ISO Half-Open Interval Model -- 9.2.1 Use of NULL for Eternity -- 9.2.2 Single Timestamp Tables -- 9.2.3 Overlapping Intervals -- 9.3 State Transition Tables -- 9.4 Consolidating Intervals -- 9.4.1 Cursors and Triggers -- 9.4.2 OLAP Function Solution -- 9.4.3 CTE Solution -- 9.5 Calendar Tables -- 9.5.1 Day of Week via Tables -- 9.5.2 Holiday Lists -- 9.5.3 Report Periods -- 9.5.4 Self-Updating Views -- 9.6 History Tables -- 9.6.1 Audit Trails -- 10 Scrubbing Data with Non-1NF Tables -- 10.1 Repeated Groups -- 10.1.1 Sorting within a Repeated Group -- 10.2 Designing Scrubbing Tables -- 10.3 Scrubbing Constraints -- 10.4 Calendar Scrubs -- 10.4.1 Special Dates -- 10.5 String Scrubbing -- 10.6 Sharing SQL Data -- 10.6.1 A Look at Data Evolution -- 10.6.2 Databases -- 10.7 Extract, Transform, and Load Products -- 10.7.1 Loading Data Warehouses -- 10.7.2 Doing It All in SQL -- 10.7.3 Extract, Load, and then Transform -- 11 Thinking in SQL -- 11.1 Warm-up Exercises -- 11.1.1 The Whole and Not the Parts -- 11.1.2 Characteristic Functions -- 11.1.3 Locking into a Solution Early -- 11.2 Heuristics -- 11.2.1 Put the Specification into a Clear Statement -- 11.2.2 Add the Words Set of All in Front of the Nouns -- 11.2.3 Remove Active Verbs from the Problem Statement -- 11.2.4 You Can Still Use Stubs -- 11.2.5 Do Not Worry about Displaying the Data -- 11.2.6 Your First Attempts Need Special Handling -- 11.2.7 Do Not Be Afraid to Throw Away Your First Attempts at DDL -- 11.2.8 Save Your First Attempts at DML -- 11.2.9 Do Not Think with Boxes and Arrows -- 11.2.10 Draw Circles and Set Diagrams -- 11.2.11 Learn Your Dialect -- 11.2.12 Imagine that Your WHERE Clause Is Super Amoeba -- 11.2.13 Use the Newsgroups, Blogs, and Internet -- 11.3 Do Not Use BIT or BOOLEAN Flags in SQL -- 11.3.1 Flags Are at the Wrong Level -- 11.3.2 Flags Confuse Proper Attributes -- 12 Group Characteristics -- 12.1 Grouping Is Not Equality -- 12.2 Using Groups without Looking Inside -- 12.2.1 Semiset-Oriented Approach -- 12.2.2 Grouped Solutions -- 12.2.3 Aggregated Solutions -- 12.3 Grouping over Time -- 12.3.1 Piece-by-Piece Solution -- 12.3.2 Data as a Whole Solution -- 12.4 Other Tricks with HAVING Clauses -- 12.5 Groupings, Rollups, and Cubes -- 12.5.1 GROUPING SET Clause -- 12.5.2 The ROLLUP Clause -- 12.5.3 The CUBE Clause -- 12.5.4 A Footnote about Super Grouping -- 12.6 The WINDOW Clause -- 12.6.1 The PARTITION BY Clause -- 12.6.2 The ORDER BY Clause -- 12.6.3 The RANGE Clause -- 12.6.4 Programming Tricks -- 13 Turning Specifications into Code -- 13.1 Signs of Bad SQL -- 13.1.1 Is the Code Formatted Like Another Language? -- 13.1.2 Assuming Sequential Access -- 13.1.3 Cursors -- 13.1.4 Poor Cohesion -- 13.1.5 Table-Valued Functions -- 13.1.6 Multiple Names for the Same Data Element -- 13.1.7 Formatting in the Database -- 13.1.8 Keeping Dates in Strings -- 13.1.9 BIT Flags, BOOLEAN, and Other Computed Columns -- 13.1.10 Attribute Splitting Across Columns -- 13.1.11 Attribute Splitting Across Rows -- 13.1.12 Attribute Splitting Across Tables -- 13.2 Methods of Attack -- 13.2.1 Cursor-Based Solution -- 13.2.2 Semiset-Oriented Approach -- 13.2.3 Pure Set-Oriented Approach -- 13.2.4 Advantages of Set-Oriented Code -- 13.3 Translating Vague Specifications -- 13.3.1 Go Back to the DDL -- 13.3.2 Changing Specifications -- 14 Using Procedure and Function Calls -- 14.1 Clearing out Spaces in a String -- 14.1.1 Procedural Solution #1 -- 14.1.2 Functional Solution #1 -- 14.1.3 Functional Solution #2 -- 14.2 The PRD() Aggregate Function -- 14.3 Long Parameter Lists in Procedures and Functions -- 14.3.1 The IN() Predicate Parameter Lists -- 15 Numbering Rows -- 15.1 Procedural Solutions -- 15.1.1 Reordering on a Numbering Column -- 15.2 OLAP Functions -- 15.2.1 Simple Row Numbering -- 15.2.2 RANK() and DENSE_RANK() -- 15.3 Sections -- -- 16 Keeping Computed Data -- 16.1 Procedural Solution -- 16.2 Relational Solution -- 16.3 Other Kinds of Computed Data -- 17 Triggers for Constraints -- 17.1 Triggers for Computations -- 17.2 Complex Constraints via CHECK() and CASE Constraints -- 17.3 Complex Constraints via VIEWs -- 17.3.1 Set-Oriented Solutions -- 17.4 Operations on VIEWs as Constraints -- 17.4.1 The Basic Three Operations -- 17.4.2 WITH CHECK OPTION Clause -- 17.4.3 WITH CHECK OPTION as CHECK() clause -- 17.4.4 How VIEWs Behave -- 17.4.5 UNIONed VIEWs -- 17.4.6 Simple INSTEAD OF Triggers -- 17.4.7 Warnings about INSTEAD OF Triggers -- 18 Procedural and Data Driven Solutions -- 18.1 Removing Letters in a String -- 18.1.1 The Procedural Solution -- 18.1.2 Pure SQL Solution -- 18.1.3 Impure SQL Solution -- 18.2 Two Approaches to Sudoku -- 18.2.1 Procedural Approach -- 18.2.2 Data-Driven Approach -- 18.2.3 Handling the Given Digits -- 18.3 Data Constraint Approach -- 18.4 Bin Packing Problems -- 18.4.1 The Procedural Approach -- 18.4.2 The SQL Approach -- 18.5 Inventory Costs over Time -- 18.5.1 Inventory UPDATE Statements -- 18.5.2 Bin Packing Returns -- Index."@en ;
   schema:description "Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity. This book will change the way you think about the problems you solve with SQL programs. Focusing on three key table-based techniques, Celko reveals their power through detailed examples and clear explanations. As you master these techniques, youll find you are able to conceptualize problems as rooted in sets and solvable through declarative programming. Before long, youll be coding more quickly, writing more efficient code, and applying the full power of SQL Filled with the insights of one of the worlds leading SQL authorities - noted for his knowledge and his ability to teach what he knows. Focuses on auxiliary tables (for computing functions and other values by joins), temporal tables (for temporal queries, historical data, and audit information), and virtual tables (for improved performance). Presents clear guidance for selecting and correctly applying the right table technique."@en ;
   schema:description "SQL is declarative, not procedural -- Auxiliary tables -- Temporal tables -- Virtual tables -- Auxiliary tables -- Views -- Virtual tables -- Complicated functions via tables -- Temporal tables -- Scrubbing data with non-1nf tables -- Thinking in SQL -- Group characteristics -- Turning specifications into code -- Using procedure and function calls -- Numbering rows -- Keeping computed data -- Triggers for constraints -- Procedural and data-driven solutions."@en ;
   schema:exampleOfWork <http://worldcat.org/entity/work/id/115338411> ; # Thinking in sets
   schema:genre "Electronic books"@en ;
   schema:inLanguage "en" ;
   schema:isPartOf <http://experiment.worldcat.org/entity/work/data/115338411#Series/morgan_kaufmann_series_in_data_management_systems> ; # Morgan Kaufmann series in data management systems.
   schema:isPartOf <http://experiment.worldcat.org/entity/work/data/115338411#Series/the_morgan_kaufmann_series_in_data_management_systems> ; # The Morgan Kaufmann series in data management systems
   schema:isSimilarTo <http://www.worldcat.org/oclc/176861723> ;
   schema:name "Joe Celko's thinking in sets : auxiliary, temporal, and virtual tables in SQL"@en ;
   schema:productID "228148104" ;
   schema:publication <http://www.worldcat.org/title/-/oclc/228148104#PublicationEvent/amsterdam_boston_elsevier_morgan_kaufmann_2008> ;
   schema:publisher <http://experiment.worldcat.org/entity/work/data/115338411#Agent/elsevier_morgan_kaufmann> ; # Elsevier / Morgan Kaufmann
   schema:url <http://www.engineeringvillage.com/controller/servlet/OpenURL?genre=book&isbn=9780123741370> ;
   schema:url <http://www.myilibrary.com?id=117212> ;
   schema:url <http://proquest.tech.safaribooksonline.de/9780080557526> ;
   schema:url <http://www.123library.org/book_details/?id=36985> ;
   schema:url <http://www.sciencedirect.com/science/book/9780123741370> ;
   schema:url <http://public.eblib.com/choice/publicfullrecord.aspx?p=330237> ;
   schema:url <http://proquest.safaribooksonline.com/9780080557526> ;
   schema:url <http://public.ebookcentral.proquest.com/choice/publicfullrecord.aspx?p=330237> ;
   schema:url <http://www.books24x7.com/marc.asp?bookid=37210> ;
   schema:url <http://search.ebscohost.com/login.aspx?direct=true&scope=site&db=nlebk&db=nlabk&AN=218771> ;
   schema:url <http://site.ebrary.com/id/10209701> ;
   schema:url <http://www.myilibrary.com?id=117212&ref=toc> ;
   schema:workExample <http://worldcat.org/isbn/9781281172129> ;
   schema:workExample <http://worldcat.org/isbn/9780123741370> ;
   schema:workExample <http://worldcat.org/isbn/9780080557526> ;
   wdrs:describedby <http://www.worldcat.org/title/-/oclc/228148104> ;
    .


Related Entities

<http://dbpedia.org/resource/Boston> # Boston
    a schema:Place ;
   schema:name "Boston" ;
    .

<http://experiment.worldcat.org/entity/work/data/115338411#Agent/elsevier_morgan_kaufmann> # Elsevier / Morgan Kaufmann
    a bgn:Agent ;
   schema:name "Elsevier / Morgan Kaufmann" ;
    .

<http://experiment.worldcat.org/entity/work/data/115338411#Series/morgan_kaufmann_series_in_data_management_systems> # Morgan Kaufmann series in data management systems.
    a bgn:PublicationSeries ;
   schema:hasPart <http://www.worldcat.org/oclc/228148104> ; # Joe Celko's thinking in sets : auxiliary, temporal, and virtual tables in SQL
   schema:name "Morgan Kaufmann series in data management systems." ;
    .

<http://experiment.worldcat.org/entity/work/data/115338411#Series/the_morgan_kaufmann_series_in_data_management_systems> # The Morgan Kaufmann series in data management systems
    a bgn:PublicationSeries ;
   schema:hasPart <http://www.worldcat.org/oclc/228148104> ; # Joe Celko's thinking in sets : auxiliary, temporal, and virtual tables in SQL
   schema:name "The Morgan Kaufmann series in data management systems" ;
    .

<http://experiment.worldcat.org/entity/work/data/115338411#Topic/computers_programming_languages_general> # COMPUTERS--Programming Languages--General
    a schema:Intangible ;
   schema:name "COMPUTERS--Programming Languages--General"@en ;
    .

<http://id.worldcat.org/fast/1102869> # SQL (Computer program language)
    a schema:Intangible ;
   schema:name "SQL (Computer program language)"@en ;
    .

<http://id.worldcat.org/fast/889108> # Declarative programming
    a schema:Intangible ;
   schema:name "Declarative programming"@en ;
    .

<http://viaf.org/viaf/50934021> # Joe Celko
    a schema:Person ;
   schema:familyName "Celko" ;
   schema:givenName "Joe" ;
   schema:name "Joe Celko" ;
    .

<http://worldcat.org/entity/work/id/115338411> # Thinking in sets
   schema:name "Thinking in sets" ;
    .

<http://worldcat.org/isbn/9780080557526>
    a schema:ProductModel ;
   schema:isbn "008055752X" ;
   schema:isbn "9780080557526" ;
    .

<http://worldcat.org/isbn/9780123741370>
    a schema:ProductModel ;
   schema:isbn "0123741378" ;
   schema:isbn "9780123741370" ;
    .

<http://worldcat.org/isbn/9781281172129>
    a schema:ProductModel ;
   schema:isbn "128117212X" ;
   schema:isbn "9781281172129" ;
    .

<http://www.engineeringvillage.com/controller/servlet/OpenURL?genre=book&isbn=9780123741370>
   rdfs:comment "An electronic book accessible through the World Wide Web; click for information" ;
    .

<http://www.worldcat.org/oclc/176861723>
    a schema:CreativeWork ;
   rdfs:label "Joe Celko's thinking in sets." ;
   schema:description "Print version:" ;
   schema:isSimilarTo <http://www.worldcat.org/oclc/228148104> ; # Joe Celko's thinking in sets : auxiliary, temporal, and virtual tables in SQL
    .


Content-negotiable representations

Close Window

Please sign in to WorldCat 

Don't have an account? You can easily create a free account.