Book description
Joe Celkos SQL for Smarties: Advanced SQL Programming offers tips and techniques in advanced programming. This book is the fourth edition and it consists of 39 chapters, starting with a comparison between databases and file systems. It covers transactions and currency control, schema level objects, locating data and schema numbers, base tables, and auxiliary tables. Furthermore, procedural, semi-procedural, and declarative programming are explored in this book.
The book also presents the different normal forms in database normalization, including the first, second, third, fourth, fifth, elementary key, domain-key, and Boyce-Codd normal forms. It also offers practical hints for normalization and denormalization. The book discusses different data types, such as the numeric, temporal and character data types; the different predicates; and the simple and advanced SELECT statements. In addition, the book presents virtual tables, and it discusses data partitions in queries; grouping operations; simple aggregate functions; and descriptive statistics, matrices and graphs in SQL. The book concludes with a discussion about optimizing SQL. It will be of great value to SQL programmers.
- Expert advice from a noted SQL authority and award-winning columnist who has given ten years service to the ANSI SQL standards committee
- Teaches scores of advanced techniques that can be used with any product, in any SQL environment, whether it is an SQL 92 or SQL 2008 environment
- Offers tips for working around deficiencies and gives insight into real-world challenges
Table of contents
- Cover image
- Title page
- Table of Contents
- Copyright
- Dedication
- About the Author
- Introduction to the Fourth Edition
- 1. Databases versus File Systems
- 2. Transactions and Concurrency Control
- 3. Schema Level Objects
- 4. Locating Data and Special Numbers
-
5. Base Tables and Related Elements
- 5.1 CREATE TABLE Statement
- 5.2 Nested UNIQUE Constraints
- 5.3 CREATE ASSERTION Constraints
- 5.4 TEMPORARY Tables
- 5.5 Manipulating Tables
- 5.6 Avoiding Attribute Splitting
- 5.7 Modeling Class Hierarchies in DDL
- 5.8 Exposed Physical Locators
- 5.9 Auto-Incrementing Columns
- 5.10 Generated Identifiers
- 5.11 A Remark on Duplicate Rows
- 5.12 Other Schema Objects
- 5.13 Temporary Tables
- 5.14 CREATE DOMAIN Statement
- 5.15 CREATE TRIGGER Statement
- 5.16 CREATE PROCEDURE Statement
- 5.17 DECLARE CURSOR Statement
- 6. Procedural, Semiprocedural, and Declarative Programming
- 7. Procedural Constructs
- 8. Auxiliary Tables
-
9. Normalization
- 9.1 Functional and Multivalued Dependencies
- 9.2 First Normal Form (1NF)
- 9.3 Second Normal Form (2NF)
- 9.4 Third Normal Form (3NF)
- 9.5 Elementary Key Normal Form (EKNF)
- 9.6 Boyce-Codd Normal Form (BCNF)
- 9.7 Fourth Normal Form (4NF)
- 9.8 Fifth Normal Form (5NF)
- 9.9 Domain-Key Normal Form (DKNF)
- 9.10 Practical Hints for Normalization
- 9.11 Key Types
- 9.12 Practical Hints for Denormalization
- 10. Numeric Data Types
- 11. Temporal Data Types
- 12. Character Data Types
- 13. NULLs: Missing Data in SQL
- 14. Multiple Column Data Elements
- 15. Table Operations
- 16. Comparison or Theta Operators
- 17. Valued Predicates
- 18. CASE Expressions
- 19. LIKE and SIMILAR TO Predicates
- 20. BETWEEN and OVERLAPS Predicates
- 21. The [NOT] IN() Predicate
- 22. EXISTS() Predicate
- 23. Quantified Subquery Predicates
- 24. The Simple SELECT Statement
- 25. Advanced SELECT Statements
-
26. Virtual Tables: VIEWs, Derived Tables, CTEs, and MQTs
- 26.1 VIEWs in Queries
- 26.2 Updatable and Read-Only VIEWs
- 26.3 Types of VIEWs
- 26.4 How VIEWs Are Handled in the Database Engine
- 26.5 WITH CHECK OPTION Clause
- 26.6 Dropping VIEWs
- 26.7 Hints on Using VIEWs versus TEMPORARY TABLEs
- 26.8 Using Derived Tables
- 26.9 Common Table Expressions
- 26.10 Recursive Common Table Expressions
- 26.11 Materialized Query Tables
- 27. Partitioning Data in Queries
- 28. Grouping Operations
- 29. Simple Aggregate Functions
- 30. Advanced Grouping, Windowed Aggregation, and OLAP in SQL
- 31. Descriptive Statistics in SQL
- 32. Subsequences, Regions, Runs, Gaps, and Islands
- 33. Matrices in SQL
- 34. Set Operations
- 35. Subsets
- 36. Trees and Hierarchies in SQL
- 37. Graphs in SQL
- 38. Temporal Queries
-
39. Optimizing SQL
- 39.1 Access Methods
- 39.2 How to Index
- 39.3 Give Extra Information
- 39.4 Index Multiple Columns Carefully
- 39.5 Watch the IN Predicate
- 39.6 Avoid UNIONs
- 39.7 Prefer Joins over Nested Queries
- 39.8 Use Fewer Statements
- 39.9 Avoid Sorting
- 39.10 Avoid CROSS JOINs
- 39.11 Know Your Optimizer
- 39.12 Recompile Static SQL after Schema Changes
- 39.13 Temporary Tables Are Sometimes Handy
- 39.14 Update Statistics
- 39.15 Do Not Trust Newer Features
- Index
Product information
- Title: Joe Celko's SQL for Smarties, 4th Edition
- Author(s):
- Release date: November 2010
- Publisher(s): Morgan Kaufmann
- ISBN: 9780123820235
You might also like
book
Joe Celko's SQL for Smarties, 3rd Edition
SQL for Smarties was hailed as the first book devoted explicitly to the advanced techniques needed …
book
Joe Celko's SQL for Smarties, 5th Edition
SQL for Smarties was hailed as the first book devoted explicitly to the advanced techniques needed …
book
Joe Celko's SQL Puzzles and Answers, 2nd Edition
Joe Celko's SQL Puzzles and Answers, Second Edition, challenges you with his trickiest puzzles and then …
book
Joe Celko's Trees and Hierarchies in SQL for Smarties, 2nd Edition
The demand for SQL information and training continues to grow with the need for a database …