Mein Konto
Login Anmeldung
Alles
Warenkorb
Neuesten Trends
Belletristik
Sachbücher
Kinderbücher
Seltene Bücher
Musik
DVD & Blu-Ray
Kategorie
Wunschlisten
Warenkorb
Alles
Kostenloser Versand
Trustpilot
Unsere Operationen sind klimaneutral

Effective SQL John Viescas

Effective SQL von John Viescas

Effective SQL John Viescas

€33,99
Zustand - Sehr Gut
Nur noch 1
Sehr Gut

Effective SQL Zusammenfassung

Effective SQL: 61 Specific Ways to Write Better SQL John Viescas

Given the authors' reputations, I expected to be impressed. I was blown away! . . . Most SQL books sit on my shelf. This one will live on my desk.
-Roger Carlson, Microsoft Access MVP (2006-2015)
Rather than stumble around reinventing wheels or catching glimpses of the proper approaches, do yourself a favor: Buy this book.
-Dave Stokes, MySQL Community Manager, Oracle Corporation
Effective SQL brings together practical solutions and insights so you can solve complex problems with SQL and design databases that simplify data management in the future. It's the only modern book that brings together advanced best practices and realistic example code for all of these versions of SQL: IBM DB2, Microsoft Access, Microsoft SQL Server, MySQL, Oracle Database, and PostgreSQL.
Drawing on their immense experience as world-class database consultants and instructors, the authors identify 61 proven approaches to writing better SQL. Wherever SQL versions vary, the authors illuminate the key nuances, so you can get the most out of whatever version you prefer. This full-color guide provides clear, practical explanations; expert tips; and plenty of usable code. Going far beyond mere syntax, it addresses issues ranging from optimizing database designs to managing hierarchies and metadata. If you already know SQL's basics, this guide will help you become a world-class SQL problem-solver.
  • Craft better logical data models, and fix flawed models
  • Implement indexes that improve query performance
  • Handle external data from sources you don't control
  • Extract and aggregate the information you need, as efficiently as possible
  • Write more flexible subqueries
  • Analyze and retrieve metadata using your database platform of choice
  • Use Cartesian Products and Tally Tables to solve problems you can't address with conventional JOINs
  • Model hierarchical data: managing SQL's tradeoffs and shortcomings

Effective SQL Bewertungen

Given the reputation of the authors, I expected to be impressed. Impressed doesn't cover it, though. I was blown away! Most SQL books tell you 'how.' This one tells you 'why.' Most SQL books separate database design from implementation. This one integrates design considerations into every facet of SQL use. Most SQL books sit on my shelf. This one will live on my desk. -Roger Carlson, Microsoft Access MVP (2006-2015) It can be easy to learn the basics of SQL, but it is very difficult to build accurate and efficient SQL, especially for critical systems with complex requirements. But now, with this great new book, you can get up to speed and write effective SQL much more quickly, no matter which DBMS you use. - Craig S. Mullins, Mullins Consulting, Inc., DB2 Gold Consultant and IBM Champion for Analytics This is a great book. It is written in language that can be understood by a relative beginner and yet contains tips and tricks that will benefit the most hardened workhorse. It will therefore appeal to readers across the whole range of expertise and should be in the library of anybody who is seriously concerned with designing, managing, or programming databases. -Graham Mandeno, database consultant and Microsoft MVP (1996-2015) This book is an excellent resource for database designers and developers working with relational and SQL-based databases-it's an easy read with great examples that combine theory with practical examples seamlessly. Examples for top relational databases Oracle, DB2, SQL Server, MySQL, and PostgreSQL are included throughout. The book walks the reader through sophisticated techniques to deal with things such as hierarchical data and tally tables, along with explanations of the inner workings and performance implications of SQL using GROUP BY, EXISTS, IN, correlated and non-correlated subqueries, window functions, and joins. The tips you won't find anywhere else, and the fun examples help to make this book stand out from the crowd. -Tim Quinlan, database architect and Oracle Certified DBA This book is good for those who need to support multiple dialects of SQL. It's divided up into stand-alone items that you just grab and go. I have been doing SQL in various flavors since 1992 and even I picked up a few things. -Tom Moreau, Ph.D., SQL Server MVP (2001-2012) This book is a powerful, compact, and easily understandable presentation of how to use SQL-it shows the application of SQL to real-world questions in order to teach the construction of queries, and it explains the relationship of 'how data is stored' to 'how data is queried' so that you obtain results successfully and effectively. -Kenneth D. Snell, Ph.D., database consultant and former Microsoft Access MVP It has been problematic for many that there is no book on going from a novice database administrator to a much more advanced status until now. Effective SQL is a road map, a guide, a Rosetta Stone, and a coach on moving from basic Structured Query Language (SQL) to much more advanced uses to solve real-world problems. Rather than stumble around reinventing the wheel or catching glimpses of the proper ways to use a database, do yourself a favor and buy a copy of this book. Not only will you see many different approaches it would take years to see as a database consultant, but you will get a detailed understanding of why the databases of many vendors do what they do. Save time, effort, and wear and tear on your walls from banging your head against them and get this book. -Dave Stokes, MySQL Community Manager, Oracle Corporation Effective SQL is a 'must have' for any serious database developer. It shows how powerful SQL can be in solving real-world problems in a step-by-step manner. The authors use easy-to-understand language in pointing out every advantage and disadvantage of each solution presented in the book. As we all know, there are multiple ways of accomplishing the same thing in SQL, but the authors explain why a particular query is more efficient than others. The part I liked best about the book is the summary at the end of each section, which reemphasizes the take-away points and reminds the reader which pitfalls to avoid. I highly recommend this book to all my fellow database developers. -Leo (theDBguy(TM)), UtterAccess Moderator and Microsoft Access MVP I think this is the book that is relevant not only for developers, but also for DBAs, as it talks about writing efficient SQL and various ways of achieving a desired result. In my opinion, this is a must-have book. Another reason to have this book is that it covers most of the commonly used RDBMSs, and so if someone is looking to transition from one RDBMS to another, this is the book to pick up. The authors have done a fantastic job. My heartiest congratulations to them. -Vivek Sharma, technologist, Hybrid Cloud Solutions, Core Technology and Cloud, Oracle Asia Pacific

Über John Viescas

John L. Viescas is an independent database consultant with more than 45 years of experience. He began his career as a systems analyst, designing large database applications for IBM mainframe systems. He spent six years at Applied Data Research in Dallas, Texas, where he directed a staff of more than 30 people and was responsible for research, product development, and customer support of database products for IBM mainframe computers. While working at Applied Data Research, John completed a degree in business finance at the University of Texas at Dallas, graduating cum laude.

John joined Tandem Computers, Inc., in 1988, where he was responsible for the development and implementation of database marketing programs in Tandem's U.S. Western Sales region. He developed and delivered technical seminars on Tandem's relational database management system, NonStop SQL. John wrote his first book, A Quick Reference Guide to SQL (Microsoft Press, 1989), as a research project to document the similarities in the syntax among the ANSI-86 SQL standard, IBM's DB2, Microsoft's SQL Server, Oracle Corporation's Oracle, and Tandem's NonStop SQL. He wrote the first edition of Running Microsoft (R) Access (Microsoft Press, 1992) while on sabbatical from Tandem. He has since written four editions of Running, three editions of Microsoft (R) Office Access Inside Out (Microsoft Press, 2003, 2007 and 2010-the successor to the Running series), and Building Microsoft (R) Access Applications (Microsoft Press, 2005). He is also the best-selling author of SQL Queries for Mere Mortals (R), Third Edition (Addison-Wesley, 2014). John currently holds the record for the most consecutive years being awarded MVP from Microsoft, having received the award from 1993-2015. John makes his home with his wife of more than 30 years in Paris, France.

Douglas J. Steele has been working with computers, both mainframe and PC, for almost 45 years. (Yes, he did use punch cards in the beginning!) He worked for a large international oil company for more than 31 years before retiring in 2012. Databases and data modeling were a focus for most of that time, although he finished his career by developing the SCCM task sequence to roll Windows 7 out to over 100,000 computers worldwide.

Recognized by Microsoft as an MVP (Most Valuable Professional) for more than 16 years, Doug has authored numerous articles on Access, was co-author of Access Solutions: Tips, Tricks, and Secrets from Microsoft Access MVPs (Wiley Publishing, 2010), and has been technical editor for a number of books.

Doug holds a master's degree in systems design engineering from the University of Waterloo (Ontario, Canada), where his research centered on designing user interfaces for non-traditional computer users. (Of course, this was in the late '70s, so few people were traditional computer users at the time!) This research stemmed from his background in music (he holds an associateship in piano performance from the Royal Conservatory of Music, Toronto). He is also obsessed with beer, and is a graduate of the brewmaster and brewery operations management program at Niagara College (Niagara-on-the-Lake, Ontario).

Doug lives with his lovely wife of more than 34 years in St. Catharines, Ontario. Doug can be reached at [email protected].

Ben G. Clothier is a Solution Architect with IT Impact, Inc., a premier Access and SQL Server development shop based in Chicago, Illinois. He has worked as a freelance consultant with notable shops including J Street Technology and Advisicon, and has worked on Access projects from small, one-man solutions to company-wide line of business applications. Notable projects include job tracking and inventory for a cement company, a Medicare insurance plan generator for an insurance provider, and order management for an international shipping company. Ben is an administrator at UtterAccess and was a coauthor, with Teresa Hennig, George Hepworth and Doug Yudovich of Microsoft (R) Access 2013 Programming (Wiley 2013), and with Tim Runcie and George Hepworth, of Microsoft (R) Access in a SharePoint World (Advisicon, 2011), and a contributing author for Access 2010 Programmer's Reference (Wiley, 2010). He holds certifications for Microsoft SQL Server 2012 Solution Associate and MySQL 5.0 Certified Developer, among others. He has been a Microsoft MVP since 2009.

Ben lives in San Antonio, Texas, with his wife, Suzanne, and his son, Harry.

Inhaltsverzeichnis

Foreword xiii

Acknowledgments xv

About the Authors xvii

About the Technical Editors xix

Introduction 1

A Brief History of SQL 1

Database Systems We Considered 5

Sample Databases 6

Where to Find the Samples on GitHub 7

Summary of the Chapters 8

Chapter 1: Data Model Design 11

Item 1: Verify That All Tables Have a Primary Key 11

Item 2: Eliminate Redundant Storage of Data Items 15

Item 3: Get Rid of Repeating Groups 19

Item 4: Store Only One Property per Column 21

Item 5: Understand Why Storing Calculated Data Is Usually a Bad Idea 25

Item 6: Define Foreign Keys to Protect Referential Integrity 30

Item 7: Be Sure Your Table Relationships Make Sense 33

Item 8: When 3NF Is Not Enough, Normalize More 37

Item 9: Use Denormalization for Information Warehouses 43

Chapter 2: Programmability and Index Design 47

Item 10: Factor in Nulls When Creating Indexes 47

Item 11: Carefully Consider Creation of Indexes to Minimize Index and Data Scanning 52

Item 12: Use Indexes for More than Just Filtering 56

Item 13: Don't Go Overboard with Triggers 61

Item 14: Consider Using a Filtered Index to Include or Exclude a Subset of Data 65

Item 15: Use Declarative Constraints Instead of Programming Checks 68

Item 16: Know Which SQL Dialect Your Product Uses and Write Accordingly 70

Item 17: Know When to Use Calculated Results in Indexes 74

Chapter 3: When You Can't Change the Design 79

Item 18: Use Views to Simplify What Cannot Be Changed 79

Item 19: Use ETL to Turn Nonrelational Data into Information 85

Item 20: Create Summary Tables and Maintain Them 90

Item 21: Use UNION Statements to Unpivot Non-normalized Data 94

Chapter 4: Filtering and Finding Data 101

Item 22: Understand Relational Algebra and How It Is Implemented in SQL 101

Item 23: Find Non-matches or Missing Records 108

Item 24: Know When to Use CASE to Solve a Problem 110

Item 25: Know Techniques to Solve Multiple-Criteria Problems 115

Item 26: Divide Your Data If You Need a Perfect Match 120

Item 27: Know How to Correctly Filter a Range of Dates on a Column Containing Both Date and Time 124

Item 28: Write Sargable Queries to Ensure That the Engine Will Use Indexes 127

Item 29: Correctly Filter the Right Side of a Left Join 132

Chapter 5: Aggregation 135

Item 30: Understand How GROUP BY Works 135

Item 31: Keep the GROUP BY Clause Small 142

Item 32: Leverage GROUP BY/HAVING to Solve Complex Problems 145

Item 33: Find Maximum or Minimum Values Without Using GROUP BY 150

Item 34: Avoid Getting an Erroneous COUNT() When Using OUTER JOIN 156

Item 35: Include Zero-Value Rows When Testing for HAVING COUNT(x) < Some Number 159

Item 36: Use DISTINCT to Get Distinct Counts 163

Item 37: Know How to Use Window Functions 166

Item 38: Create Row Numbers and Rank a Row over Other Rows 169

Item 39: Create a Moving Aggregate 172

Chapter 6: Subqueries 179

Item 40: Know Where You Can Use Subqueries 179

Item 41: Know the Difference between Correlated and Non-correlated Subqueries 184

Item 42: If Possible, Use Common Table Expressions Instead of Subqueries 190

Item 43: Create More Efficient Queries Using Joins Rather than Subqueries 197

Chapter 7: Getting and Analyzing Metadata 201

Item 44: Learn to Use Your System's Query Analyzer 201

Item 45: Learn to Get Metadata about Your Database 212

Item 46: Understand How the Execution Plan Works 217

Chapter 8: Cartesian Products 227

Item 47: Produce Combinations of Rows between Two Tables and Flag Rows in the Second That Indirectly Relate to the First 227

Item 48: Understand How to Rank Rows by Equal Quantiles 231

Item 49: Know How to Pair Rows in a Table with All Other Rows 235

Item 50: Understand How to List Categories and the Count of First, Second, or Third Preferences 240

Chapter 9: Tally Tables 247

Item 51: Use a Tally Table to Generate Null Rows Based on a Parameter 247

Item 52: Use a Tally Table and Window Functions for Sequencing 252

Item 53: Generate Multiple Rows Based on Range Values in a Tally Table 257

Item 54: Convert a Value in One Table Based on a Range of Values in a Tally Table 261

Item 55: Use a Date Table to Simplify Date Calculation 268

Item 56: Create an Appointment Calendar Table with All Dates Enumerated in a Range 275

Item 57: Pivot Data Using a Tally Table 278

Chapter 10: Modeling Hierarchical Data 285

Item 58: Use an Adjacency List Model as the Starting Point 286

Item 59: Use Nested Sets for Fast Querying Performance with Infrequent Updates 288

Item 60: Use a Materialized Path for Simple Setup and Limited Searching 291

Item 61: Use Ancestry Traversal Closure for Complex Searching 294

Appendix: Date and Time Types, Operations, and Functions 299

IBM DB2 299

Microsoft Access 303

Microsoft SQL Server 305

MySQL 308

Oracle 313

PostgreSQL 315

Index 317

Zusätzliche Informationen

GOR013472019
9780134578897
0134578899
Effective SQL: 61 Specific Ways to Write Better SQL John Viescas
John Viescas
Effective Software Development Series
Gebraucht - Sehr Gut
Broschiert
Pearson Education (US)
20170206
352
N/A
Die Abbildung des Buches dient nur Illustrationszwecken, die tatsächliche Bindung, das Cover und die Auflage können sich davon unterscheiden.
Dies ist ein gebrauchtes Buch. Es wurde schon einmal gelesen und weist von der früheren Nutzung Gebrauchsspuren auf. Wir gehen davon aus, dass es im Großen und Ganzen in einem sehr guten Zustand ist. Sollten Sie jedoch nicht vollständig zufrieden sein, setzen Sie sich bitte mit uns in Verbindung.