Home Page Icon
Home Page
Table of Contents for
Part 5: Typical Queries
Close
Part 5: Typical Queries
by Joe Celko
Joe Celko's SQL for Smarties, 5th Edition
Cover image
Title page
Table of Contents
Copyright
Dedication
Introduction to the Fifth Edition
What is New in this Edition
Corrections and Additions
Part 1: Data Declaration Features
Chapter 1: Databases Versus File Systems
Abstract
1.1 The Schema Statement
1.2 Tables as Entities
1.3 Tables as Relationships
1.4 Rows Versus Records
1.5 Columns Versus Fields
Chapter 2: Transactions and Concurrency Control
Abstract
2.1 Sessions
2.2 Transactions and ACID
2.3 Concurrency Control
2.4 The Isolation Levels
2.5 Pessimistic Concurrency Control
2.6 SNAPSHOT Isolation Optimistic Concurrency
2.7 Logical Concurrency Control
2.8 CAP Theorem
2.9 BASE
2.10 Server-Side Consistency
2.11 Error Handling
2.12 Deadlock and Livelocks
Chapter 3: Tables
Abstract
3.1 CREATE TABLE Statements
3.2 Column Definitions
3.3 Computed Columns
3.4 [NOT] DEFERRABLE Constraints
3.5 CREATE DOMAIN and CREATE SEQUENCE
3.6 Character Set Related Constructs
Chapter 4: Keys, Locators, and Generated Values
Abstract
4.1 Key Types
4.2 Practical Hints for Denormalization
Chapter 5: Normalization
Abstract
5.1 Functional and Multivalued Dependencies
5.2 First Normal Form (1NF)
5.3 Second Normal Form (2NF)
5.4 Third Normal Form (3NF)
5.5 Elementary Key Normal Form (EKNF)
5.6 Boyce-Codd Normal Form (BCNF)
5.7 Fourth Normal Form (4NF)
5.8 Fifth Normal Form (5NF)
5.9 Domain-Key Normal Form (DKNF)
5.10 Practical Hints for Normalization
5.11 Non-Normal Form Redundancy
Chapter 6: VIEWs, Derived, and Other Virtual Tables
Abstract
6.1 VIEWs in Queries
6.2 Updatable and Read-Only VIEWs
6.3 Types of VIEWs
6.4 How VIEWs are Handled in the Database Engine
6.5 WITH CHECK OPTION Clause
6.6 Dropping VIEWs
6.7 Materialized Query Tables
Chapter 7: Auxiliary Tables
Abstract
7.1 Series Table
7.2 Look-up Auxiliary Tables
7.3 Advance Auxiliary Function Tables
7.4 Global Constants Tables
7.5 A Note on Converting Procedural Code to Tables
Chapter 8: Other Schema Objects
Abstract
8.1 CREATE SCHEMA Statement
8.2 Schema Tables
8.3 Temporary Tables
8.4 CREATE ASSERTION Statement
8.5 CREATE DOMAIN Statement
8.6 CREATE COLLATION Statement
8.7 CREATE TRANSLATION Statement
8.8 CREATE PROCEDURE Statement
8.9 TRIGGERs
8.10 The TRIGGER Model
Chapter 9: DDL Flaws to Avoid
Abstract
9.1 Tibbling and Related Errors
9.2 Attribute Splitting
9.3 Overloading Design Flaws
9.4 Non-Normal Form Redundancy
Part 2: Data Types
Chapter 10: Numeric Data in SQL
Abstract
10.1 Exact Numeric Data Types
10.2 Approximate Numeric Data Types
10.3 Numeric Type Conversions
10.4 Four Function Arithmetic
10.5 Converting Values to and from NULL
10.6 Mathematical Functions
10.7 IP Addresses
Chapter 11: Character Data Types in SQL
Abstract
11.1 Problems with SQL Strings
11.2 Standard String Functions
11.3 Common Vendor Extensions
11.4 Cutter Tables
Chapter 12: Temporal Data Types in SQL
Abstract
12.1 Notes on Calendar Standards
12.2 The Nature of Temporal Data Models
12.3 SQL Temporal Data Types
12.4 INTERVAL Data Types
12.5 Queries with Date Arithmetic
12.6 Use of NULL for “Eternity”
12.7 The OVERLAPS() Predicate
12.8 State-Transition Constraints
12.9 Calendar Tables
Chapter 13: Multiple Column Data Elements
Abstract
13.1 Vector and Coordinate Data Elements
13.2 Hierarchical Data Elements
Chapter 14: NULLs—Missing Data in SQL
Abstract
14.1 Empty and Missing Tables
14.2 Missing Values in Columns
14.3 Context and Missing Values
14.4 Comparing NULLs
14.5 NULLs and Logic
14.6 Math and NULLs
14.7 Functions for NULLs
14.8 NULLs and Host Languages
14.9 Design Advice for NULLs
14.10 A Note on Multiple NULL Values
Chapter 15: Table Operations
Abstract
15.1 DELETE FROM Statement
15.2 INSERT INTO Statement
15.3 The UPDATE Statement
15.4 A Note on Flaws in a Common Vendor Extension
15.5 MERGE Statement
Chapter 16: Set Operations
Abstract
16.1 UNION and UNION ALL
16.2 INTERSECT and EXCEPT
16.3 A Note on ALL and SELECT DISTINCT
16.4 Equality and Proper Subsets
Part 3: Row and Column Level Features
Chapter 17: Comparison or Theta Operators
Abstract
17.1 Converting Data Types
17.2 Row Comparisons in SQL
17.3 IS [NOT] DISTINCT FROM Operator
17.4 Monadic Operators
Chapter 18: Subquery Predicates
Abstract
18.1 The UNIQUE Predicate
18.2 The [NOT] IN() Predicate
18.3 [NOT] EXISTS() Predicate
18.4 < theta > [SOME | ANY] < subquery >
18.5 < theta > ALL < subquery >
Chapter 19: BETWEEN and OVERLAPS Predicates
Abstract
19.1 The BETWEEN Predicate
19.2 OVERLAPS Predicate
Chapter 20: CASE Expression Family
Abstract
20.1 CASE Expression
20.2 Subquery Expressions and Constants
Chapter 21: LIKE and SIMILAR TO Predicates
Abstract
21.1 Tricks with Patterns
21.2 Results with NULL Values and Empty Strings
21.3 LIKE is Not Equality
21.4 Extending the LIKE Predicate with a Join
21.5 CASE Expressions and LIKE Predicates
21.6 SIMILAR TO Predicates
21.7 Tricks with Strings
Chapter 22: Basic SELECT Statement
Abstract
22.1 CTEs
22.2 FROM Clause
22.3 WHERE Clause
22.4 GROUP BY Clause
22.5 HAVING Clause
22.6 SELECT Clause
22.7 ORDER BY Clause
22.8 Nested Query Expressions and Orthogonality
Chapter 23: Basic Aggregate Functions
Abstract
23.1 COUNT() Functions
23.2 SUM() Function
23.3 AVG() Function
23.4 Extrema Functions
23.5 The LIST() Aggregate Function
23.6 The Mode Aggregate Function
23.7 The Median Aggregate Function
23.8 The PRD() Aggregate Function
Chapter 24: Advance Descriptive Statistics
Abstract
24.1 Binary Table Functions
24.2 Correlation
Chapter 25: OLAP Aggregation in SQL
Abstract
25.1 Querying Versus Reporting
25.2 GROUPING Operators
25.3 The Window Clause
25.4 Windowed Aggregate Functions
25.5 Ordinal Functions
25.6 Vendor Extensions
25.7 A Bit of History
Chapter 26: Advanced SELECT Statements
Abstract
26.1 Correlated Subqueries
26.2 Infixed INNER JOINs
26.3 OUTER JOINs
26.4 UNION JOIN Operators
26.5 Scalar SELECT Expressions
26.6 Old Versus New JOIN Syntax
26.7 Constrained Joins
26.8 Dr. Codd's T-Join
26.9 Missing Values in Data
26.10 Missing and Mixed Data in Rows
Part 4: Data Structures in SQL
Chapter 27: Graphs in SQL
Abstract
27.1 Basic Graph Characteristics
27.2 Paths in a Graph
27.3 Acyclic Graphs as Nested Sets
27.4 Adjacency Matrix Model
27.5 Points Inside Polygons
27.6 Taxicab Geometry
27.7 Equivalence Classes and Cliques
27.8 Conclusion
Chapter 28: Trees and Hierarchies in SQL
Abstract
28.1 Adjacency List Model
28.2 Finding the Root Node
28.3 Finding Leaf Nodes
28.4 Finding Levels in a Tree
28.5 Tree Operations
28.6 Nested Sets Model
28.7 Finding Root and Leaf Nodes
28.8 Finding Subtrees
28.9 Finding Levels and Paths in a Tree
28.10 Functions in the Nested Sets Model
28.11 Deleting Nodes and Subtrees
28.12 Summary Functions on Trees
28.13 Inserting and Updating Trees
28.14 Converting Adjacency List to Nested Sets Model
28.15 Converting Nested Sets to Adjacency List Model
28.16 Comparing Nodes and Structure
Chapter 29: Queues
Abstract
29.1 Basic DDL
29.2 Enqueue, Dequeue, and Empty Procedures
29.3 Rearrangement
29.4 Queues and Math
29.5 Priority Queues
29.6 FIFO and LIFO Queues
Chapter 30: Matrices in SQL
Abstract
30.1 Arrays via Named Columns
30.2 Arrays via Subscript Columns
30.3 Matrix Operations in SQL
30.4 Flattening a Table into an Array
30.5 Comparing Arrays in Table Format
30.6 Other Matrix Operations
Part 5: Typical Queries
Chapter 31: Partitioning and Aggregating Data in Queries
Abstract
31.1 Coverings and Partitions
31.2 Advanced Grouping, Windowed Aggregation, and OLAP in SQL
Chapter 32: Sub-sequences, Regions, Runs, Gaps, and Islands
Abstract
32.1 Finding Subregions of Size (n)
32.2 Numbering Regions
32.3 Finding Regions of Maximum Size
32.4 Bound Queries
32.5 Run and Sequence Queries
32.6 Summation of a Handmade Series
32.7 Swapping and Sliding Values in a List
32.8 Condensing a List of Numbers
32.9 Folding a List of Numbers
32.10 Coverings
32.11 Equivalence Classes and Cliques
Chapter 33: Auctions
Abstract
33.1 General Types of Bidding
33.2 Types of Auctions
33.3 LIFO and FIFO Inventory
33.4 Bin Packing
Chapter 34: Relational Division
Abstract
34.1 Division with a Remainder
34.2 Exact Division
34.3 Note on Performance
34.4 Todd’s Division
34.5 Division with JOINs
34.6 Division with Set Operators
34.7 Romley’s Division
34.8 Boolean Expressions in Relational Division
Chapter 35: Temporal Queries
Abstract
35.1 Temporal Math
35.2 Calendars
35.3 Time Series
35.4 Julian Dates
35.5 Other Temporal Functions
35.6 Multi-day Periods
35.7 Modeling Time in Tables
35.8 LEAD() and LAG() Functions
35.9 Problems with the Year 2000: A Historical Overview
Part 6: Implementation and Coding Issues
Chapter 36: Procedural Semi-Procedural and Declarative Programming in SQL
Abstract
36.1 Words Matter
36.2 Cleaning Code
Chapter 37: Nesting Levels in SQL
Abstract
37.1 Derived Tables
37.2 Column Naming Rules
37.3 Scoping Rules
37.4 Exposed Table Names
37.5 Common Table Expressions (CTEs)
37.6 LATERAL Tables
37.7 Programming Tips
Chapter 38: Embedded SQL, CLI Dynamic SQL, and SQL/PSM
Abstract
38.1 Embedded SQL
38.2 SQL/CLI
38.3 Dynamic SQL
38.4 SQL/PSM History
38.5 CSV Parameters
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Chapter 30: Matrices in SQL
Next
Next Chapter
Chapter 31: Partitioning and Aggregating Data in Queries
Part 5
Typical Queries
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset