Ebook Joe Celko s SQL for smarties advanced SQL programming 3rd Edition by Joe Celko ISBN 0080460046 9780080460048

Ebook Joe Celko s SQL for smarties advanced SQL programming 3rd Edition by Joe Celko ISBN 0080460046 9780080460048

Ebook Joe Celko s SQL for smarties advanced SQL programming 3rd Edition by Joe Celko - Ebook PDF Instant Download/Delivery: 0080460046, 9780080460048 
Full download Ebook Joe Celko s SQL for smarties advanced SQL programming 3rd Edition after payment

Product details:

ISBN 10: 0080460046
ISBN 13: 9780080460048
Author: Joe Celko

SQL for Smarties was hailed as the first book devoted explicitly to the advanced techniques needed to transform an experienced SQL programmer into an expert. Now, 10 years later and in the third edition, this classic still reigns supreme as the book written by an SQL master that teaches future SQL masters. These are not just tips and techniques; Joe also offers the best solutions to old and new challenges and conveys the way you need to think in order to get the most out of SQL programming efforts for both correctness and performance.

In the third edition, Joe features new examples and updates to SQL-99, expanded sections of Query techniques, and a new section on schema design, with the same war-story teaching style that made the first and second editions of this book classics.

* Expert advice from a noted SQL authority and award-winning columnist, who has given ten years of service to the ANSI SQL standards committee and many more years of dependable help to readers of online forums.
* Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is an SQL-92 or SQL-99 environment.
* Offers tips for working around system deficiencies.
* Continues to use war stories--updated!--that give insights into real-world SQL programming challenges.

Ebook Joe Celko s SQL for smarties advanced SQL programming 3rd Edition Table of contents:

1 Database Design

1.1 Schema and Table Creation

1.1.1 CREATE SCHEMA Statement

1.1.2 Manipulating Tables

1.1.3 Column Constraints

1.1.4 UNIQUE Constraints versus UNIQUE Indexes

1.1.5 Nested UNIQUE Constraints

1.1.6 Overlapping Keys

1.1.7 CREATE ASSERTION Constraints

1.1.8 Using VIEWs for Schema Level Constraints

1.1.9 Using PRIMARY KEYs and ASSERTIONs for Constraints

1.1.10 Avoiding Attribute Splitting

1.1.11 Modeling Class Hierarchies in DDL

1.2 Generating Unique Sequential Numbers for Keys

1.2.1 IDENTITY Columns

1.2.2 ROWID and Physical Disk Addresses

1.2.3 Sequential Numbering in Pure SQL

1.2.4 GUIDs

1.2.5 Sequence Generator Functions

1.2.6 Unique Value Generators

1.2.7 Preallocated Values

1.2.8 Random Order Values

1.3 A Remark on Duplicate Rows

1.4 Other Schema Objects

1.4.1 Schema Tables

1.4.2 Temporary Tables

1.4.3 CREATE DOMAIN Statement

1.4.4 CREATE TRIGGER Statement

1.4.5 CREATE PROCEDURE Statement

1.4.6 DECLARE CURSOR Statement

2 Normalization

2.1 Functional and Multivalued Dependencies

2.2 First Normal Form (1NF)

2.2.1 Note on Repeated Groups

2.3 Second Normal Form (2NF)

2.4 Third Normal Form (3NF)

2.5 Elementary Key Normal Form (EKNF)

2.6 Boyce-Codd Normal Form (BCNF)

2.7 Fourth Normal Form (4NF)

2.8 Fifth Normal Form (5NF)

2.9 Domain-Key Normal Form (DKNF)

2.10 Practical Hints for Normalization

2.11 Key Types

2.11.1 Natural Keys

2.11.2 Artificial Keys

2.11.3 Exposed Physical Locators

2.11.4 Practical Hints for Denormalization

2.11.5 Row Sorting

3 Numeric Data in SQL

3.1 Numeric Types

3.1.1 BIT, BYTE, and BOOLEAN Data Types

3.2 Numeric Type Conversion

3.2.1 Rounding and Truncating

3.2.2 CAST() Function

3.3 Four-Function Arithmetic

3.4 Arithmetic and NULLs

3.5 Converting Values to and from NULL

3.5.1 NULLIF() Function

3.5.2 COALESCE() Function

3.6 Vendor Math Functions

3.6.1 Number Theory Operators

3.6.2 Exponential Functions

3.6.3 Scaling Functions

3.6.4 Converting Numbers to Words

4 Temporal Data Types in SQL

4.1 Notes on Calendar Standards

4.2 SQL Temporal Data Types

4.2.1 Tips for Handling Dates, Timestamps, and Times

4.2.2 Date Format Standards

4.2.3 Handling Timestamps

4.2.4 Handling Times

4.3 Queries with Date Arithmetic

4.4 The Nature of Temporal Data Models

4.4.1 Temporal Duplicates

4.4.2 Temporal Databases

4.4.3 Temporal Projection and Selection

4.4.4 Temporal Joins

4.4.5 Modifying Valid-Time State Tables

4.4.6 Current Modifications

4.4.7 Sequenced Modifications

4.4.8 Nonsequenced Modifications

4.4.9 Transaction-Time State Tables

4.4.10 Maintaining the Audit Log

4.4.11 Querying the Audit Log

4.4.12 Modifying the Audit Log

4.4.13 Bitemporal Tables

4.4.14 Temporal Support in Standard SQL

5 Character Data Types in SQL

5.1 Problems with SQL Strings

5.1.1 Problems of String Equality

5.1.2 Problems of String Ordering

5.1.3 Problems of String Grouping

5.2 Standard String Functions

5.3 Common Vendor Extensions

5.3.1 Phonetic Matching

5.4 Cutter Tables

6 NULLs: Missing Data in SQL

6.1 Empty and Missing Tables

6.2 Missing Values in Columns

6.3 Context and Missing Values

6.4 Comparing NULLs

6.5 NULLs and Logic

6.5.1 NULLS in Subquery Predicates

6.5.2 Standard SQL Solutions

6.6 Math and NULLs

6.7 Functions and NULLs

6.8 NULLs and Host Languages

6.9 Design Advice for NULLs

6.9.1 Avoiding NULLs from the Host Programs

6.10 A Note on Multiple NULL Values

7 Multiple Column Data Elements

7.1 Distance Functions

7.2 Storing an IP Address in SQL

7.2.1 A Single VARCHAR(15) Column

7.2.2 One INTEGER Column

7.2.3 Four SMALLINT Columns

7.3 Currency and Other Unit Conversions

7.4 Social Security Numbers

7.5 Rational Numbers

8 Table Operations

8.1 DELETE FROM Statement

8.1.1 The DELETE FROM Clause

8.1.2 The WHERE Clause

8.1.3 Deleting Based on Data in a Second Table

8.1.4 Deleting within the Same Table

8.1.5 Deleting in Multiple Tables without Referential Integrity

8.2 INSERT INTO Statement

8.2.1 INSERT INTO Clause

8.2.2 The Nature of Inserts

8.2.3 Bulk Load and Unload Utilities

8.3 The UPDATE Statement

8.3.1 The UPDATE Clause

8.3.2 The WHERE Clause

8.3.3 The SET Clause

8.3.4 Updating with a Second Table

8.3.5 Using the CASE Expression in UPDATEs

8.4 A Note on Flaws in a Common Vendor Extension

8.5 MERGE Statement

9 Comparison or Theta Operators

9.1 Converting Data Types

9.2 Row Comparisons in SQL

10 Valued Predicates

10.1 IS NULL Predicate

10.1.1 Sources of NULLs

10.2 IS [NOT]{TRUE | FALSE | UNKNOWN} Predicate

10.3 IS [NOT] NORMALIZED Predicate

11 CASE Expressions

11.1 The CASE Expression

11.1.1 The COALESCE() and NULLIF() Functions

11.1.2 CASE Expressions with GROUP BY

11.1.3 CASE, CHECK() Clauses and Logical Implication

11.1.4 Subquery Expressions and Constants

11.2 Rozenshtein Characteristic Functions

12 LIKE Predicate

12.1 Tricks with Patterns

12.2 Results with NULL Values and Empty Strings

12.3 LIKE Is Not Equality

12.4 Avoiding the LIKE Predicate with a Join

12.5 CASE Expressions and LIKE Predicates

12.6 SIMILAR TO Predicates

12.7 Tricks with Strings

12.7.1 String Character Content

12.7.2 Searching versus Declaring a String

12.7.3 Creating an Index on a String

13 BETWEEN and OVERLAPS Predicates

13.1 The BETWEEN Predicate

13.1.1 Results with NULL Values

13.1.2 Results with Empty Sets

13.1.3 Programming Tips

13.2 OVERLAPS Predicate

13.2.1 Time Periods and OVERLAPS Predicate

14 The [NOT] IN() Predicate

14.1 Optimizing the IN() Predicate

14.2 Replacing ORs with the IN() Predicate

14.3 NULLs and the IN() Predicate

14.4 IN() Predicate and Referential Constraints

14.5 IN() Predicate and Scalar Queries

15 EXISTS() Predicate

15.1 EXISTS and NULLs

15.2 EXISTS and INNER JOINs

15.3 NOT EXISTS and OUTER JOINs

15.4 EXISTS() and Quantifiers

15.5 EXISTS() and Referential Constraints

15.6 EXISTS and Three-Valued Logic

16 Quantified Subquery Predicates

16.1 Scalar Subquery Comparisons

16.2 Quantifiers and Missing Data

16.3 The ALL Predicate and Extrema Functions

16.4 The UNIQUE Predicate

17 The SELECT Statement

17.1 SELECT and JOINs

17.1.1 One-Level SELECT Statement

17.1.2 Correlated Subqueries in a SELECT Statement

17.1.3 SELECT Statement Syntax

17.1.4 The ORDER BY Clause

17.2 OUTER JOINs

17.2.1 Syntax for OUTER JOINs

17.2.2 NULLs and OUTER JOINs

17.2.3 NATURAL versus Searched OUTER JOINs

17.2.4 Self OUTER JOINs

17.2.5 Two or More OUTER JOINs

17.2.6 OUTER JOINs and Aggregate Functions

17.2.7 FULL OUTER JOIN

17.2.8 WHERE Clause OUTER JOIN Operators

17.3 Old versus New JOIN Syntax

17.4 Scope of Derived Table Names

17.5 JOINs by Function Calls

17.6 The UNION JOIN

17.7 Packing Joins

17.8 Dr. Codd's T-Join

17.8.1 The Croatian Solution

17.8.2 The Swedish Solution

17.8.3 The Colombian Solution

18 VIEWs, Derived Tables, Materialized Tables, and Temporary Tables

18.1 VIEWs in Queries

18.2 Updatable and Read-Only VIEWs

18.3 Types of VIEWs

18.3.1 Single-Table Projection and Restriction

18.3.2 Calculated Columns

18.3.3 Translated Columns

18.3.4 Grouped VIEWs

18.3.5 UNIONed VIEWs

18.3.6 JOINs in VIEWs

18.3.7 Nested VIEWs

18.4 How VIEWs Are Handled in the Database System

18.4.1 View Column List

18.4.2 VIEW Materialization

18.4.3 In-Line Text Expansion

18.4.4 Pointer Structures

18.4.5 Indexing and Views

18.5 WITH CHECK OPTION Clause

18.5.1 WITH CHECK OPTION as CHECK() Clause

18.6 Dropping VIEWs

18.7 TEMPORARY TABLE Declarations

18.8 Hints on Using VIEWs and TEMPORARY TABLEs

18.8.1 Using VIEWs

18.8.2 Using TEMPORARY TABLEs

18.8.3 Flattening a Table with a VIEW

18.9 Using Derived Tables

18.9.1 Derived Tables in the FROM clause

18.9.2 Derived Tables with a VALUES Constructor

18.10 Derived Tables in the WITH Clause

19 Partitioning Data in Queries

19.1 Coverings and Partitions

19.1.1 Partitioning by Ranges

19.1.2 Partition by Functions

19.1.3 Partition by Sequences

19.2 Relational Division

19.2.1 Division with a Remainder

19.2.2 Exact Division

19.2.3 Note on Performance

19.2.4 Todd's Division

19.2.5 Division with JOINs

19.2.6 Division with Set Operators

19.3 Romley's Division

19.4 Boolean Expressions in an RDBMS

19.5 FIFO and LIFO Subsets

20 Grouping Operations

20.1 GROUP BY Clause

20.1.1 NULLs and Groups

20.2 GROUP BY and HAVING

20.2.1 Group Characteristics and the HAVING Clause

20.3 Multiple Aggregation Levels

20.3.1 Grouped VIEWs for Multiple Aggregation Levels

20.3.2 Subquery Expressions for Multiple Aggregation Levels

20.3.3 CASE Expressions for Multiple Aggregation Levels

20.4 Grouping on Computed Columns

20.5 Grouping into Pairs

20.6 Sorting and GROUP BY

21 Aggregate Functions

21.1 COUNT() Functions

21.2 SUM() Functions

21.3 AVG() Functions

21.3.1 Averages with Empty Groups

21.3.2 Averages across Columns

21.4 Extrema Functions

21.4.1 Simple Extrema Functions

21.4.2 Generalized Extrema Functions

21.4.3 Multiple Criteria Extrema Functions

21.4.4 GREATEST() and LEAST() Functions

21.5 The LIST() Aggregate Function

21.5.1 The LIST() Function with a Procedure

21.5.2 The LIST() Function by Crosstabs

21.6 The PRD() Aggregate Function

21.6.1 PRD() Function by Expressions

21.6.2 The PRD() Aggregate Function by Logarithms

21.7 Bitwise Aggregate Functions

21.7.1 Bitwise OR Aggregate Function

21.7.2 Bitwise AND Aggregate Function

22 Auxiliary Tables

22.1 The Sequence Table

22.1.1 Enumerating a List

22.1.2 Mapping a Sequence into a Cycle

22.1.3 Replacing an Iterative Loop

22.2 Lookup Auxiliary Tables

22.2.1 Simple Translation Auxiliary Tables

22.2.2 Multiple Translation Auxiliary Tables

22.2.3 Multiple Parameter Auxiliary Tables

22.2.4 Range Auxiliary Tables

22.2.5 Hierarchical Auxiliary Tables

22.2.6 One True Lookup Table

22.3 Auxiliary Function Tables

22.3.1 Inverse Functions with Auxiliary Tables

22.3.2 Interpolation with Auxiliary Function Tables

22.4 Global Constants Tables

23 Statistics in SQL

23.1 The Mode

23.2 The AVG() Function

23.3 The Median

23.3.1 Date's First Median

23.3.2 Celko's First Median

23.3.3 Date's Second Median

23.3.4 Murchison's Median

23.3.5 Celko's Second Median

23.3.6 Vaughan's Median with VIEWs

23.3.7 Median with Characteristic Function

23.3.8 Celko's Third Median

23.3.9 Ken Henderson's Median

23.4 Variance and Standard Deviation

23.5 Average Deviation

23.6 Cumulative Statistics

23.6.1 Running Totals

23.6.2 Running Differences

23.6.3 Cumulative Percentages

23.6.4 Rankings and Related Statistics

23.6.5 Quintiles and Related Statistics

23.7 Cross Tabulations

23.7.1 Crosstabs by Cross Join

23.7.2 Crosstabs by Outer Joins

23.7.3 Crosstabs by Subquery

23.7.4 Crosstabs by CASE Expression

23.8 Harmonic Mean and Geometric Mean

23.9 Multivariable Descriptive Statistics in SQL

23.9.1 Covariance

23.9.2 Pearson's r

23.9.3 NULLs in Multivariable Descriptive Statistics

24 Regions, Runs, Gaps, Sequences, and Series

24.1 Finding Subregions of Size (n)

24.2 Numbering Regions

24.3 Finding Regions of Maximum Size

24.4 Bound Queries

24.5 Run and Sequence Queries

24.5.1 Filling in Sequence Numbers

24.6 Summation of a Series

24.7 Swapping and Sliding Values in a List

24.8 Condensing a List of Numbers

24.9 Folding a List of Numbers

24.10 Coverings

25 Arrays in SQL

25.1 Arrays via Named Columns

25.2 Arrays via Subscript Columns

25.3 Matrix Operations in SQL

25.3.1 Matrix Equality

25.3.2 Matrix Addition

25.3.3 Matrix Multiplication

25.3.4 Other Matrix Operations

25.4 Flattening a Table into an Array

25.5 Comparing Arrays in Table Format

26 Set Operations

26.1 UNION and UNION ALL

26.1.1 Order of Execution

26.1.2 Mixed UNION and UNION ALL Operators

26.1.3 UNION of Columns from the Same Table

26.2 INTERSECT and EXCEPT

26.2.1 INTERSECT and EXCEPT without NULLs and Duplicates

26.2.2 INTERSECT and EXCEPT with NULLs and Duplicates

26.3 A Note on ALL and SELECT DISTINCT

26.4 Equality and Proper Subsets

27 Subsets

27.1 Every nth Item in a Table

27.2 Picking Random Rows from a Table

27.3 The CONTAINS Operators

27.3.1 Proper Subset Operators

27.3.2 Table Equality

27.4 Picking a Representative Subset

28 Trees and Hierarchies in SQL

28.1 Adjacency List Model

28.1.1 Complex Constraints

28.1.2 Procedural Traversal for Queries

28.1.3 Altering the Table

28.2 The Path Enumeration Model

28.2.1 Finding Subtrees and Nodes

28.2.2 Finding Levels and Subordinates

28.2.3 Deleting Nodes and Subtrees

28.2.4 Integrity Constraints

28.3 Nested Set Model of Hierarchies

28.3.1 The Counting Property

28.3.2 The Containment Property

28.3.3 Subordinates

28.3.4 Hierarchical Aggregations

28.3.5 Deleting Nodes and Subtrees

28.3.6 Converting Adjacency List to Nested Set Model

28.4 Other Models for Trees and Hierarchies

29 Temporal Queries

29.1 Temporal Math

29.2 Personal Calendars

29.3 Time Series

29.3.1 Gaps in a Time Series

29.3.2 Continuous Time Periods

29.3.3 Missing Times in Contiguous Events

29.3.4 Locating Dates

29.3.5 Temporal Starting and Ending Points

29.3.6 Average Wait Times

29.4 Julian Dates

29.5 Date and Time Extraction Functions

29.6 Other Temporal Functions

29.7 Weeks

29.7.1 Sorting by Weekday Names

29.8 Modeling Time in Tables

29.8.1 Using Duration Pairs

29.9 Calendar Auxiliary Table

29.10 Problems with the Year 2000

29.10.1 The Zeros

29.10.2 Leap Year

29.10.3 The Millennium

29.10.4 Weird Dates in Legacy Data

29.10.5 The Aftermath

30 Graphs in SQL

30.1 Basic Graph Characteristics

30.1.1 All Nodes in the Graph

30.1.2 Path Endpoints

30.1.3 Reachable Nodes

30.1.4 Edges

30.1.5 Indegree and Outdegree

30.1.6 Source, Sink, Isolated, and Internal Nodes

30.2 Paths in a Graph

30.2.1 Length of Paths

30.2.2 Shortest Path

30.2.3 Paths by Iteration

30.2.4 Listing the Paths

30.3 Acyclic Graphs as Nested Sets

30.4 Paths with CTE

30.4.1 Nonacyclic Graphs

30.5 Adjacency Matrix Model

30.6 Points inside Polygons

31 OLAP in SQL

31.1 Star Schema

31.2 OLAP Functionality

31.2.1 RANK and DENSE_RANK

31.2.2 Row Numbering

31.2.3 GROUPING Operators

31.2.4 The Window Clause

31.2.5 OLAP Examples of SQL

31.2.6 Enterprise-Wide Dimensional Layer

31.3 A Bit of History

32 Transactions and Concurrency Control

32.1 Sessions

32.2 Transactions and ACID

32.2.1 Atomicity

32.2.2 Consistency

32.2.3 Isolation

32.2.4 Durability

32.3 Concurrency Control

32.3.1 The Five Phenomena

32.3.2 The Isolation Levels

32.3.3 CURSOR STABILITY Isolation Level

32.4 Pessimistic Concurrency Control

32.5 SNAPSHOT Isolation: Optimistic Concurrency

32.6 Logical Concurrency Control

32.7 Deadlock and Livelocks

33 Optimizing SQL

33.1 Access Methods

33.1.1 Sequential Access

33.1.2 Indexed Access

33.1.3 Hashed Indexes

33.1.4 Bit Vector Indexes

33.2 Expressions and Unnested Queries

33.2.1 Use Simple Expressions

33.2.2 String Expressions

33.3 Give Extra Join Information in Queries

33.4 Index Tables Carefully

33.5 Watch the IN Predicate

33.6 Avoid UNIONs

33.7 Prefer Joins over Nested Queries

33.8 Avoid Expressions on Indexed Columns

33.9 Avoid Sorting

33.10 Avoid CROSS JOINs

33.11 Learn to Use Indexes Carefully

33.12 Order Indexes Carefully

33.13 Know Your Optimizer

33.14 Recompile Static SQL after Schema Changes

33.15 Temporary Tables Are Sometimes Handy

33.16 Update Statistics

back matter

Appendix: References

General References

Logic

Mathematical Techniques

Random Numbers

Scales and Measurements

Missing Values

Regular 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 Groups

Web Sites Related to SQL

Statistics

Temporal Databases

New Citations

index

People also search for Ebook Joe Celko s SQL for smarties advanced SQL programming 3rd Edition:

how to learn sql from scratch
    
joe celko's sql for smarties
    
joe celko sql
    
joe celko's sql for smarties advanced sql programming
    
sql for smarties

Tags: Joe Celko, Joe Celko s SQL, smarties, advanced SQL programming