Search icon
Subscription
0
Cart icon
Close icon
You have no products in your basket yet
Save more on your purchases!
Savings automatically calculated. No voucher code required
Arrow left icon
All Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletters
Free Learning
Arrow right icon
SQL Query Design Patterns and Best Practices
SQL Query Design Patterns and Best Practices

SQL Query Design Patterns and Best Practices: A practical guide to writing readable and maintainable SQL queries using its design patterns

By Steve Hughes , Dennis Neer , Dr. Ram Babu Singh , Shabbir H. Mala , Leslie Andrews , Chi Zhang
€20.99 €8.99
Full star icon Full star icon Full star icon Full star icon Full star icon 5 (1 Ratings)
Book Mar 2023 270 pages 1st Edition
eBook
€20.99 €8.99
Print
€26.99
Subscription
€14.99 Monthly
eBook
€20.99 €8.99
Print
€26.99
Subscription
€14.99 Monthly

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon AI Assistant (beta) to help accelerate your learning
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Buy Now

Product Details


Publication date : Mar 31, 2023
Length 270 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781837633289
Category :
Table of content icon View table of contents Preview book icon Preview Book

SQL Query Design Patterns and Best Practices

Reducing Rows and Columns in Your Result Sets

Today the sources of data that a data analyst has access to have grown to the point that the amount of data that is available to you is unlimited. The challenge that a data analyst faces today is to determine how to generate a result set that is manageable and has the information that ensures that it will meet the needs of the analyst for their reports and analysis. If there is too much data, the result set will become unmanageable and unusable due to information overload; too little data and the data will have gaps, and the end user will lose trust in the data.

In this chapter, we will review how you determine how much data and what data you should keep in your result set and how to filter that data efficiently. We will also review how to determine which columns you should keep and how you can efficiently select the correct columns. The chapter will then wrap up with a short discussion on how these activities will impact future data aggregations.

By the end of this chapter, you will understand how to identify the data and columns that you need and the most efficient method for getting the data into a usable result set that can easily be recreated.

In this chapter, we will cover the following main topics:

  • Identifying data to be removed from the dataset
  • Understanding the value of creating views versus removing data
  • Exploring the impact of row and column reductions on aggregations

Technical requirements

To work with the examples and illustrations in this chapter, you will need to have SQL Server Management Studio installed. We will be working with the WideWorldImportersDW database on SQL Server. Please refer to the Appendix for tool installation and database restoration guidance.

You will find the code from this chapter on GitHub: https://github.com/PacktPublishing/SQL-Query-Design-Best-Practices/tree/main/Chapter01

Left arrow icon Right arrow icon
Download code icon Download Code

Key benefits

  • Examine query design and performance using query plans and indexes
  • Solve business problems using advanced techniques such as common table expressions and window functions
  • Use SQL in modern data platform solutions with JSON and Jupyter notebooks

Description

SQL has been the de facto standard when interacting with databases for decades and shows no signs of going away. Through the years, report developers or data wranglers have had to learn SQL on the fly to meet the business needs, so if you are someone who needs to write queries, SQL Query Design and Pattern Best Practices is for you. This book will guide you through making efficient SQL queries by reducing set sizes for effective results. You’ll learn how to format your results to make them easier to consume at their destination. From there, the book will take you through solving complex business problems using more advanced techniques, such as common table expressions and window functions, and advance to uncovering issues resulting from security in the underlying dataset. Armed with this knowledge, you’ll have a foundation for building queries and be ready to shift focus to using tools, such as query plans and indexes, to optimize those queries. The book will go over the modern data estate, which includes data lakes and JSON data, and wrap up with a brief on how to use Jupyter notebooks in your SQL journey. By the end of this SQL book, you’ll be able to make efficient SQL queries that will improve your report writing and the overall SQL experience.

What you will learn

Build efficient queries by reducing the data being returned Manipulate your data and format it for easier consumption Form common table expressions and window functions to solve complex business issues Understand the impact of SQL security on your results Understand and use query plans to optimize your queries Understand the impact of indexes on your query performance and design Work with data lake data and JSON in SQL queries Organize your queries using Jupyter notebooks

What do you get with eBook?

Product feature icon Instant access to your Digital eBook purchase
Product feature icon Download this book in EPUB and PDF formats
Product feature icon AI Assistant (beta) to help accelerate your learning
Product feature icon Access this title in our online reader with advanced features
Product feature icon DRM FREE - Read whenever, wherever and however you want
Buy Now

Product Details


Publication date : Mar 31, 2023
Length 270 pages
Edition : 1st Edition
Language : English
ISBN-13 : 9781837633289
Category :

Table of Contents

21 Chapters
Preface Chevron down icon Chevron up icon
1. Part 1: Refining Your Queries to Get the Results You Need Chevron down icon Chevron up icon
2. Chapter 1: Reducing Rows and Columns in Your Result Sets Chevron down icon Chevron up icon
3. Chapter 2: Efficiently Aggregating Data Chevron down icon Chevron up icon
4. Chapter 3: Formatting Your Results for Easier Consumption Chevron down icon Chevron up icon
5. Chapter 4: Manipulating Data Results Using Conditional SQL Chevron down icon Chevron up icon
6. Part 2: Solving Complex Business and Data Problems in Your Queries Chevron down icon Chevron up icon
7. Chapter 5: Using Common Table Expressions Chevron down icon Chevron up icon
8. Chapter 6: Analyze Your Data Using Window Functions Chevron down icon Chevron up icon
9. Chapter 7: Reshaping Data with Advanced Techniques Chevron down icon Chevron up icon
10. Chapter 8: Impact of SQL Server Security on Query Results Chevron down icon Chevron up icon
11. Part 3: Optimizing Your Queries to Improve Performance Chevron down icon Chevron up icon
12. Chapter 9: Understanding Query Plans Chevron down icon Chevron up icon
13. Chapter 10: Understanding the Impact of Indexes on Query Design Chevron down icon Chevron up icon
14. Part 4: Working with Your Data on the Modern Data Platform Chevron down icon Chevron up icon
15. Chapter 11: Handling JSON Data in SQL Server Chevron down icon Chevron up icon
16. Chapter 12: Integrating File Data and Data Lake Content with SQL Chevron down icon Chevron up icon
17. Chapter 13: Organizing and Sharing Your Queries with Jupyter Notebooks Chevron down icon Chevron up icon
18. Index Chevron down icon Chevron up icon
19. Other Books You May Enjoy Chevron down icon Chevron up icon
Appendix: Preparing Your Environment Chevron down icon Chevron up icon

Customer reviews

Filter icon Filter
Top Reviews
Rating distribution
Full star icon Full star icon Full star icon Full star icon Full star icon 5
(1 Ratings)
5 star 100%
4 star 0%
3 star 0%
2 star 0%
1 star 0%

Filter reviews by


Enrico Barillari Nov 7, 2023
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Un libro interessante ed utile, lo consiglio anche a chi ha esperienza, per un ripasso ed un'integrazione
Feefo Verified review Feefo image
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

How do I buy and download an eBook? Chevron down icon Chevron up icon

Where there is an eBook version of a title available, you can buy it from the book details for that title. Add either the standalone eBook or the eBook and print book bundle to your shopping cart. Your eBook will show in your cart as a product on its own. After completing checkout and payment in the normal way, you will receive your receipt on the screen containing a link to a personalised PDF download file. This link will remain active for 30 days. You can download backup copies of the file by logging in to your account at any time.

If you already have Adobe reader installed, then clicking on the link will download and open the PDF file directly. If you don't, then save the PDF file on your machine and download the Reader to view it.

Please Note: Packt eBooks are non-returnable and non-refundable.

Packt eBook and Licensing When you buy an eBook from Packt Publishing, completing your purchase means you accept the terms of our licence agreement. Please read the full text of the agreement. In it we have tried to balance the need for the ebook to be usable for you the reader with our needs to protect the rights of us as Publishers and of our authors. In summary, the agreement says:

  • You may make copies of your eBook for your own use onto any machine
  • You may not pass copies of the eBook on to anyone else
How can I make a purchase on your website? Chevron down icon Chevron up icon

If you want to purchase a video course, eBook or Bundle (Print+eBook) please follow below steps:

  1. Register on our website using your email address and the password.
  2. Search for the title by name or ISBN using the search option.
  3. Select the title you want to purchase.
  4. Choose the format you wish to purchase the title in; if you order the Print Book, you get a free eBook copy of the same title. 
  5. Proceed with the checkout process (payment to be made using Credit Card, Debit Cart, or PayPal)
Where can I access support around an eBook? Chevron down icon Chevron up icon
  • If you experience a problem with using or installing Adobe Reader, the contact Adobe directly.
  • To view the errata for the book, see www.packtpub.com/support and view the pages for the title you have.
  • To view your account details or to download a new copy of the book go to www.packtpub.com/account
  • To contact us directly if a problem is not resolved, use www.packtpub.com/contact-us
What eBook formats do Packt support? Chevron down icon Chevron up icon

Our eBooks are currently available in a variety of formats such as PDF and ePubs. In the future, this may well change with trends and development in technology, but please note that our PDFs are not Adobe eBook Reader format, which has greater restrictions on security.

You will need to use Adobe Reader v9 or later in order to read Packt's PDF eBooks.

What are the benefits of eBooks? Chevron down icon Chevron up icon
  • You can get the information you need immediately
  • You can easily take them with you on a laptop
  • You can download them an unlimited number of times
  • You can print them out
  • They are copy-paste enabled
  • They are searchable
  • There is no password protection
  • They are lower price than print
  • They save resources and space
What is an eBook? Chevron down icon Chevron up icon

Packt eBooks are a complete electronic version of the print edition, available in PDF and ePub formats. Every piece of content down to the page numbering is the same. Because we save the costs of printing and shipping the book to you, we are able to offer eBooks at a lower cost than print editions.

When you have purchased an eBook, simply login to your account and click on the link in Your Download Area. We recommend you saving the file to your hard drive before opening it.

For optimal viewing of our eBooks, we recommend you download and install the free Adobe Reader version 9.