Querying SQL Server: Run T-SQL operations, data extraction, data manipulation, and custom queries to deliver simplified analytics
- Length: 620 pages
- Edition: 1
- Language: English
- Publisher: BPB Publications
- Publication Date: 2022-06-21
- ISBN-10: 9355512376
- ISBN-13: 9789355512376
- Sales Rank: #2389876 (See Top 100 Books)
Learning real-world analytics using SQL
Key Features
- Hands-on approach to learning the fundamentals of data analysis
- Covers all levels of SQL expertise from novice to master with examples
- Includes techniques and approaches to query complex databases more effectively
Description
This book will take you on an interactive journey to master SQL querying from the most fundamental to the most advanced level using various T-SQL techniques to provide meaningful insights from your SQL Server data.
The book begins with an introduction to the fundamental concepts of both SQL and SQL Server. It then guides readers through the process of building queries, using simple and then more complex table joins and progressively more advanced data filtering, to deliver advanced queries based on a variety of SQL techniques. The reader will understand how to deliver relevant and trustworthy analysis of the data through learning how to apply fundamental computations in T-SQL, relational logic, and how to group and aggregate data. There is also coverage of other SQL concepts, such as formatting queries, writing subqueries, creating derived tables, joining and filtering tables, etc. In addition to that, the book discusses how to conduct rolling analyses, examine time-series data, and manage large and complex datasets.
After finishing the book, readers won’t merely be able to write complex queries; rather, they will be able to construct their methodology for analysing relational datasets and arriving at the desired insights.
What you will learn
- Analyze data using simple math and logic, multiple filters, and data aggregation.
- Become proficient with table expressions, subqueries, and writing complex computations.
- Perform rolling analysis and time series analysis using advanced methods.
- Review SQL concepts such as table joins, data filtering, query structuring, and table derivation.
Who this book is for
This book is for data professionals, such as database developers, SQL developers, data analysts, and BI experts, who wish to construct complex queries and extract the desired analytics from their datasets. The book also encourages smart new learners to learn SQL and use it right away in their first jobs.
Cover Page Title Page Copyright Page About the Author About the Reviewer Acknowledgement Preface Errata Table of Contents 1. Writing Basic SQL Queries Prerequisites 1.1 Relational Databases 1.2 Running SQL Server Management Studio 1.3 Displaying the Tables in a Database 1.4 Finding All the Views in a Database 1.5 Schemas 1.6 Displaying the Data in a Table How It Works Tricks and Traps 1.7 Limiting the Number of Records Displayed How It Works Tricks and Traps 1.8 Displaying Data from a Specific Field How It Works Tricks and Traps 1.9 Finding the Columns in a Table 1.10 Displaying Data from a Specific Set of Fields How It Works Tricks and Traps 1.11 Modifying the Field Name How It Works Tricks and Traps 1.12 Sorting Data How It Works Tricks and Traps 1.13 Sorting Data in Reverse Alphabetical Order How It Works 1.14 Applying Multiple Sort Criteria How It Works Tricks and Traps 1.15 Choosing the Database to Query Conclusion Core Knowledge Learned in This Chapter 2. Using Multiple Tables When Querying Data Storing Data in Multiple Tables 2.1 Joining Tables How It Works Tricks and Traps 2.2 Removing Duplicates from Query Output How It Works Tricks and Traps 2.3 Joining Multiple Tables How It Works Tricks and Traps 2.4 Using Table Aliases How It Works Tricks and Traps 2.5 Joining Many Tables How It Works Tricks and Traps 2.6 Visualizing Databases 2.7 Using Views to Memorize Complex Table Joins Conclusion Core Knowledge Learned in This Chapter 3. Using Advanced Table Joins 3.1 Using Left Joins to Return All the Data in One Table but Not from the Other Table How It Works Tricks and Traps 3.2 Right Joins to Return All the Data in One Table but Not from the Other How It Works Tricks and Traps 3.3 Full Joins to Return All the Data from Both Tables in a Join How It Works Tricks and Traps 3.4 Intermediate Table Joins How It Works 3.5 Using Multiple Fields in Joins How It Works Tricks and Traps 3.6 Joining a Table to Itself How It Works Tricks and Traps 3.7 Joining Tables on Ranges of Values How It Works Tricks and Traps 3.8 Cross Joins How It Works Tricks and Traps 3.9 Join Concepts Conclusion Core Knowledge Learned in This Chapter 4. Filtering Data Using SQL to Filter Data 4.1 Filtering Data Using Inner Joins 4.2 Filtering Data Using Multiple Table Joins 4.3 Filtering Data Output Using Intermediate Tables How It Works 4.4 Filtering Text How It Works Tricks and Traps 4.5 Applying Multiple Text Filters How It Works Tricks and Traps 4.6 Excluding an Element How It Works Tricks and Traps 4.7 Using Multiple Exclusion Filters How It Works Tricks and Traps 4.8 Filtering Numbers Over a Defined Threshold How It Works Tricks and Traps 4.9 Filtering Numbers Under a Defined Threshold How It Works Tricks and Traps 4.10 Filtering on Values Up to and Including a Specific Number How It Works Tricks and Traps 4.11 Filtering on a Range of Values How It Works Tricks and Traps 4.12 Using Boolean Filters (True or False) How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 5. Applying Complex Filters to Queries Advanced Filters 5.1 Using Either/Or Filters How It Works Tricks and Traps 5.2 Using Multiple Separate Criteria Concurrently How It Works Tricks and Traps 5.3 Using Multiple Filters and an Exclusion How It Works Tricks and Traps 5.4 Filtering on Both Text and Numbers Simultaneously How It Works Tricks and Traps 5.5 Applying Complex Alternative Filters at the Same Time How It Works Tricks and Traps 5.6 Using Wildcard Searches How It Works Tricks and Traps 5.7 Using Wildcards to Exclude Data How It Works 5.8 Using a Specific Part of Text to Filter Data How It Works Tricks and Traps 5.9 Using NULLs, or Nonexistent Data How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 6. Making Simple Calculations Performing Calculations in SQL 6.1 Doing Simple Math How It Works Tricks and Traps 6.2 Examining Data Types in SQL Tables and Views 6.3 Isolating Sections of Formulas When Applying Math How It Works Tricks and Traps 6.4 Calculating Ratios How It Works 6.5 Increasing Values by a Defined Percentage How It Works 6.6 Ordering the Output of Calculations How It Works Tricks and Traps 6.7 Handling Missing Data How It Works Tricks and Traps 6.8 Filtering on a Calculation How It Works Tricks and Traps 6.9 Using Complex Calculated Filters How It Works Tricks and Traps 6.10 Writing Accurate SQL Code Faster Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 7. Aggregating Output Aggregating Data in SQL 7.1 Calculating Table Totals How It Works Tricks and Traps 7.2 Using Calculated Aggregations How It Works Tricks and Traps 7.3 Using Grouped Aggregations How It Works Tricks and Traps 7.4 Using Multiple Levels of Grouping How It Works Tricks and Traps 7.5 Calculating Averages How It Works Tricks and Traps 7.6 Counting Grouped Elements How It Works Tricks and Traps 7.7 Counting Unique Elements How It Works 7.8 Displaying Upper and Lower Numeric Thresholds How It Works Tricks and Traps 7.9 Filtering Groups How It Works Tricks and Traps 7.10 Filtering on Aggregated Results How It Works 7.11 Selecting Data Based on Aggregated Results as Well as Specific Filter Criteria How It Works Tricks and Traps 7.12 Sorting by Aggregated Results How It Works Conclusion Core Knowledge Learned in This Chapter 8. Working with Dates in SQL Server Analyzing Data over Time 8.1 Filtering Records by Date How It Works Tricks and Traps 8.2 Using a Range of Dates to Filter Data How It Works Tricks and Traps 8.3 Finding the Number of Days Between Two Dates How It Works Tricks and Traps 8.4 Aggregating Data over a Date Range How It Works Tricks and Traps 8.5 Eliminating the Time Element in a Date Filter How It Works Tricks and Traps 8.6 Filtering by Year How It Works Tricks and Traps 8.7 Filtering Records over a Series of Years How It Works Tricks and Traps 8.8 Isolating Data for a Specific Year and Month How It Works 8.9 Finding Data for a Given Quarter How It Works Tricks and Traps 8.10 Filtering Data by Weekday How It Works Tricks and Traps 8.11 Finding Records for a Specific Week of the Year How It Works Tricks and Traps 8.12 Aggregating Data by the Day of Week in a Given Year How It Works Tricks and Traps 8.13 Grouping Data by the Full Weekday How It Works Tricks and Traps 8.14 Displaying Cumulative Data over 90 Days Up to a Specific Date How It Works Tricks and Traps 8.15 Displaying the Data for the Previous Three Months How It Works 8.16 Finding the Current System Date How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 9. Formatting Text in Query Output Enhancing the Output from SQL Queries 9.1 Adding Text to the Output How It Works Tricks and Traps 9.2 Adding Text to Numbers How It Works Tricks and Traps 9.3 Amalgamating Columns How It Works Tricks and Traps 9.4 Concatenating and Grouping How It Works Tricks and Traps 9.5 Avoiding NULLs in Text-Based Data How It Works Tricks and Traps 9.6 Adding Multiple Pieces of Text to Numbers How It Works Tricks and Traps 9.7 Converting Text to Uppercase How It Works Tricks and Traps 9.8 Converting Text to Lowercase How It Works 9.9 Extracting the First Few Characters from a Field How It Works Tricks and Traps 9.10 Displaying the Three Characters at the Right of Text How It Works Tricks and Traps 9.11 Displaying a Given Number of Characters at a Specific Place in Text How It Works Tricks and Traps 9.12 Filtering Records Based on Part of a Field How It Works Tricks and Traps 9.13 Filtering Data Using Specific Characters at a Given Position Inside a Field How It Works Conclusion Core Knowledge Learned in This Chapter 10. Formatting Numbers and Dates Presenting Numbers and Dates 10.1 Removing the Decimals from the Output How It Works Tricks and Traps 10.2 Rounding a Field Up to the Nearest Whole Number How It Works Tricks and Traps 10.3 Rounding a Value to the Nearest Whole Number How It Works Tricks and Traps 10.4 Rounding a Value Up or Down to the Nearest Thousand How It Works 10.5 Displaying a Value in a Specific Currency How It Works Tricks and Traps 10.6 Defining Your Own Number Formats How It Works Tricks and Traps 10.7 Outputting a Date in the ISO Date Format How It Works Tricks and Traps 10.8 Applying a Format to a Date How It Works Tricks and Traps 10.9 Defining Your Own Date Format How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 11. Using Basic Logic to Enhance Analysis Applying SQL Logic 11.1 Generating an Alert When a Value Is Too High How It Works Tricks and Traps 11.2 Shortening Text and Adding Ellipses to Indicate Truncation How It Works 11.3 Designing Complex Calculated Alerts How It Works Tricks and Traps 11.4 Creating Key Performance Indicators How It Works Tricks and Traps 11.5 Classifying a Series of Elements Without the Necessary Categories Present in Your Data How It Works Tricks and Traps 11.6 Creating Ad Hoc Category Groupings How It Works Tricks and Traps 11.7 Applying Multiple Ad Hoc Categories How It Works Tricks and Traps 11.8 Categorizing Data Using Nested Classifications How It Works Tricks and Traps 11.9 Placing NULLs at the Start or End of a List How It Works Tricks and Traps 11.10 Classifying Data by Impromptu Categories How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 12. Subqueries What Are Subqueries? 12.1 Adding Aggregated Fields to Detailed Data Sets How It Works Tricks and Traps 12.2 Displaying a Value as the Percentage of a Total How It Works Tricks and Traps 12.3 Using a Subquery to Filter Data How It Works Tricks and Traps 12.4 Using a Subquery as Part of a Calculation to Filter Data How It Works Tricks and Traps 12.5 Filtering on an Aggregated Range of Data Using Multiple Subqueries How It Works Tricks and Traps 12.6 Filtering on Aggregated Output Using a Second Aggregation How It Works Tricks and Traps 12.7 Using Multiple Results from a Subquery to Filter Data How It Works Tricks and Traps 12.8 Complex Aggregated Subqueries How It Works Tricks and Traps 12.9 Using a Subquery to Filter a Percentage of a Dataset How It Works Tricks and Traps 12.10 Nested Subqueries How It Works Tricks and Traps 12.11 Using Subqueries to Exclude Data How It Works Tricks and Traps 12.12 Filtering across Queries and Subqueries How It Works Tricks and Traps 12.13 Applying Separate Filters to the Subquery and the Main Query How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 13. Derived Tables What Is a Derived Table? 13.1 Using a Derived Table to Create Intermediate Calculations How It Works Tricks and Traps 13.2 Grouping and Ordering Data Using a Custom Classification How It Works 13.3 Joining Derived Tables to Other Tables How It Works Tricks and Traps 13.4 Joining Multiple Derived Tables How It Works Tricks and Traps 13.5 Using Multiple Derived Tables for Complex Aggregations How It Works Tricks and Traps 13.6 Using Derived Tables to Join Unconnected Tables How It Works Tricks and Traps 13.7 Compare Year-on-Year Data Using a Derived Table How It Works Tricks and Traps 13.8 Synchronizing Filters between a Derived Table and the Main Query How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 14. Common Table Expressions Simplifying Complex Queries with Common Table Expressions 14.1 A Basic Common Table Expression How It Works Tricks and Traps 14.2 Calculating Averages across Multiple Values Using a CTE How It Works 14.3 Reusing CTEs in a Query How It Works Tricks and Traps 14.4 Using a CTE in a Derived Table to Deliver Two Different Levels of Aggregation How It Works Tricks and Traps 14.5 Using a CTE to Isolate Data from a Separate Dataset at a Different Level of Detail How It Works Tricks and Traps 14.6 Multiple Common Table Expressions How It Works Tricks and Traps 14.7 Nested Common Table Expressions How It Works Tricks and Traps 14.8 Using Multiple Common Table Expressions to Compare Disparate Datasets How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 15. Correlated Subqueries Why Use Correlated Subqueries? 15.1 Simple Correlated Subqueries How It Works Tricks and Traps 15.2 Correlated Subqueries to Display Percentages of a Specific Total How It Works Tricks and Traps 15.3 Comparing Datasets Using a Correlated Subquery How It Works Tricks and Traps 15.4 Duplicating the Output of a Correlated Subquery in the Query Results How It Works 15.5 Aggregated Correlated Subqueries How It Works Tricks and Traps 15.6 Using Correlated Subqueries to Filter Data on an Aggregate Value How It Works Tricks and Traps 15.7 Using Correlated Subqueries to Detect If Records Exist How It Works Tricks and Traps 15.8 Using a Correlated Subquery to Exclude Data How It Works Tricks and Traps 15.9 Complex Joins in Correlated Subqueries How It Works Tricks and Traps 15.10 Using a Correlated Subquery to Verify Values across Tables How It Works Conclusion Core Knowledge Learned in This Chapter 16. Dataset Manipulation Using Datasets to Mix and Match Data 16.1 Read Data from Multiple Identical Tables Using the UNION Operator How It Works Tricks and Traps 16.2 Isolate Identical Data in Multiple Tables Using the INTERSECT Operator How It Works Tricks and Traps 16.3 Isolating Nonidentical Records Using the EXCEPT Operator How It Works Tricks and Traps 16.4 Joining Multiple Identical Tables in a Subquery How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 17. Using SQL for More Advanced Calculations Additional Calculation Techniques 17.1 Calculating the Percentage Represented by Each Record in a Dataset How It Works Tricks and Traps 17.2 Replacing Multiple Subqueries How It Works Tricks and Traps 17.3 Reusing Calculated Values in Queries How It Works Tricks and Traps 17.4 Remove Decimals in Calculations How It Works 17.5 Numeric Data Types Tricks and Traps 17.6 Handling Source Data Where Figures Are Stored as Text How It Works Tricks and Traps 17.7 Converting Formatted Source Data into Useable Numbers How It Works Tricks and Traps 17.8 Avoiding Divide-by-Zero Errors How It Works Tricks and Traps 17.9 Finding the Remainder in a Division Using the Modulo Function How It Works Tricks and Traps 17.10 Creating Financial Calculations How It Works Tricks and Traps 17.11 Using a Tally Table to Produce a Sequential List of Numbers How It Works Tricks and Traps 17.12 Generating Completely Random Sample Output from a Dataset How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 18. Segmenting and Classifying Data Ranking and Segmenting Data 18.1 Organizing Data by Rank How It Works Tricks and Traps 18.2 Creating Multiple Groups of Rankings How It Works Tricks and Traps 18.3 Creating Multiple Ranked Groups and Subgroups How It Works Tricks and Traps 18.4 Filtering Data by Ranked Items How It Works Tricks and Traps 18.5 Classifying Data by Strict Order of Rank How It Works Tricks and Traps 18.6 Segment Data into Deciles How It Works Tricks and Traps 18.7 Plot Values for a Percentile How It Works Tricks and Traps 18.8 Extract Data from a Specific Quintile How It Works Tricks and Traps 18.9 Display Median Values How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 19. Rolling Analysis Running Totals, Running Averages, Running Counts, and Comparative Values 19.1 Adding a Running Total How It Works Tricks and Traps 19.2 Using Windowing Functions in an Aggregated Query How It Works Tricks and Traps 19.3 Grouping Running Totals Tricks and Traps 19.4 Applying Windowing Functions to a Subquery How It Works 19.5 Adding Unique IDs on the Fly Using ROW_NUMBER() How It Works Tricks and Traps 19.6 Displaying Records for Missing Data How It Works 19.7 Displaying a Complete Range of Dates and Relevant Data How It Works Tricks and Traps 19.8 Comparing Data with the Data from a Previous Record How It Works Tricks and Traps 19.9 Comparing Data over Time Using the FIRST_VALUE() and LAST_VALUE() Functions How It Works 19.10 Displaying Rolling Averages over a Specified Number of Records How It Works Tricks and Traps 19.11 Show the First Sale and Last Four Sales per Client How It Works 19.12 Calculating Cumulative Distribution How It Works Tricks and Traps 19.13 Classifying Data Using the PERCENT_RANK() Function How It Works Tricks and Traps 19.14 Using the LAG() Function with Alphabetical Data How It Works Conclusion Core Knowledge Learned in This Chapter 20. Analyzing Data Over Time Time Analysis 20.1 Aggregating Values for the Year to Date How It Works Tricks and Traps 20.2 Aggregating Values for the Month to Date How It Works Tricks and Traps 20.3 Returning Aggregate Values for the Quarter to Date How It Works Tricks and Traps 20.4 Isolating Data for the Previous Month How It Works Tricks and Traps 20.5 Using a Derived Table to Compare Data with Values from a Previous Year How It Works Tricks and Traps 20.6 Finding the Total Amount for Each Weekday How It Works Tricks and Traps 20.7 Find the Weekend Dates Inside a Date Range How It Works Tricks and Traps 20.8 Count the Number of Weekend Days between Two Dates How It Works Tricks and Traps 20.9 Aggregate Data for the Last Day of the Month How It Works 20.10 Aggregate Data for the Last Friday of the Month How It Works 20.11 Analyzing Timespans as Years, Months, and Days How It Works 20.12 Isolate Time Periods from Date and Time Data How It Works Tricks and Traps 20.13 Listing Data by Time of Day How It Works Tricks and Traps 20.14 Aggregating Data by Hourly Bandings How It Works Tricks and Traps 20.15 Aggregate Data by Quarter of Hour How It Works Conclusion Core Knowledge Learned in This Chapter 21. Complex Data Output Presenting Complex Output with SQL 21.1 Creating a Pivot Table How It Works Tricks and Traps 21.2 Creating a Pivot Table Displaying Multiple Row Groupings How It Works Tricks and Traps 21.3 Unpivoting Data How It Works Tricks and Traps 21.4 Adding Totals to Aggregate Queries How It Works Tricks and Traps 21.5 Creating Subtotals and Totals in Aggregated Queries How It Works Tricks and Traps 21.6 Creating Clear Tables That Include Totals and Subtotals How It Works 21.7 Handling Hierarchical Data How It Works Tricks and Traps 21.8 Producing Indented Hierarchies How It Works Tricks and Traps 21.9 Ordering a Hierarchy Using HierearchID How It Works Tricks and Traps 21.10 Replace Acronyms with Full Text in the Final Output How It Works Tricks and Traps 21.11 Replacing a Specified Number of Characters with Other Text How It Works Tricks and Traps 21.12 Creating a CommA.Separated List from Multiple Records How It Works Tricks and Traps 21.13 Creating Multiple Records from a List How It Works Tricks and Traps 21.14 Outputting Simple XML How It Works Tricks and Traps 21.15 Structuring XML Output How It Works Tricks and Traps 21.16 Outputting JSON How It Works Tricks and Traps 21.17 Defining JSON Output How It Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter Appendix A: Installing SQL Server Installing SQL Server Tricks and Traps Appendix B: Installing SQL Server Management Studio Installing SQL Server Management Studio Tricks and Traps Appendix C: Setting Up the Sample Database Downloading the Sample Data Creating the PrestigeCars Database Loading the Sample Data into the PrestigeCars Database Opening the Sample Queries Index
Donate to keep this site alive
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.