Querying MySQL: Make your MySQL database analytics accessible with SQL operations, data extraction, and custom queries
- Length: 672 pages
- Edition: 1
- Language: English
- Publisher: BPB Publications
- Publication Date: 2022-06-28
- ISBN-10: 9355512678
- ISBN-13: 9789355512673
- Sales Rank: #0 (See Top 100 Books)
Carry out real-world analytics to advance your career
Key Features
- Learning the principles of data analysis with MySQL datasets in a practical manner.
- Extensive examples guide the reader through all stages of SQL proficiency, from complete newbie to master.
- Includes methods and methodologies for doing sophisticated MySQL database queries.
Description
This book will take you on an interactive journey to understand SQL querying from the most basic to the most expert level utilising a variety of SQL techniques to extract relevant insights from your MySQL databases.
The first section of this book serves as an introduction to the fundamental ideas of both SQL and MySQL. It then walks readers through constructing queries using simple and then more sophisticated table joins and gradually more advanced data filtering to produce advanced queries based on a range of SQL techniques. The reader will better understand MySQL database analysis by mastering the fundamentals of SQL computation, relational logic, and data grouping and aggregation. SQL fundamentals such as query formatting, constructing subqueries, generating derived tables, joining and filtering tables, etc., are also covered. In addition, the book explains how to do rolling analysis, study time-series data, and manage huge and complex MySQL datasets.
After completing the book, readers can create their methods for analysing relational MySQL datasets and gaining the needed insights.
What you will learn
- Review SQL concepts like table join, data filtering, query structuring, and table derivation.
- Make it easier to work with data that is spread out over several tables with similar or identical structures.
- Use simple math and logic, multiple filters, and data aggregation to look at the data.
- Learn how to use table expressions, subqueries, and complex calculations well.
- Use advanced methods to do rolling analysis and time series analysis.
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 Acknowledgements Errata Table of Contents Introduction Why Learn SQL? Why MySQL? What Is SQL? Who This Book Is For What This Book Will Bring You How to Read This Book SQL Novices Refreshing Your Knowledge Coming from Another SQL Database In-Depth Querying The Structure of This Book The Sample Data and Sample Queries MySQL Versions Time to Get Started Querying MySQL 1. Writing Basic SQL Queries Prerequisites 1. Relational Databases 2. Running MySQL Workbench 3. Connecting to a Database 4. Displaying the Tables in a Database 5. Finding All the Views in a Database 6. Using the Command Line Client Tricks and Traps 7. Displaying the Data in a Table How It Works Tricks and Traps 8. Displaying Data from a Specific Field How it Works Tricks and Traps 9. Finding the Columns in a Table 10. Displaying Data from a Specific Set of Fields How it Works Tricks and Traps 11. Using the Command Line to Show the Structure of a Table How it Works Tricks and Traps 12. Modifying the Field Name in the Output How It Works Tricks and Traps 13. Sorting Data How it Works Tricks and Traps 14. Sorting Data in Reverse Alphabetical Order How it Works 15. Applying Multiple Sort Criteria How it Works Tricks and Traps 16. Limiting the Number of Records Displayed How it Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 2. Using Multiple Tables When Querying Data Storing Data in Multiple Tables 1. Joining Tables How it Works Tricks and Traps 2. Joining Tables With Different Link Field Names Tricks and Traps 3. Removing Duplicates from Query Output How it Works Tricks and Traps 4. Joining Multiple Tables How it Works Tricks and Traps 5. Using Table Aliases How it Works Tricks and Traps 6. Joining Many Tables How it Works Tricks and Traps 7. Visualizing Databases 8. Using Views to Memorize Complex Table Joins Conclusion Core Knowledge Learned in This Chapter 3. Using Advanced Table Joins The Many Types of Table Joins 1. Using Left Joins to Return All the Data in One Table but Not from the Other Table How it Works Tricks and Traps 2. Right Joins to Return All the Data in One Table but Not from the Other How it Works Tricks and Traps 3. Intermediate Table Joins How it Works 4. Using Multiple Fields in Joins How it Works Tricks and Traps 5. Joining a Table to Itself How it Works Tricks and Traps 6. Joining Tables on Ranges of Values How it Works Tricks and Traps 7. Cross Joins How it Works Tricks and Traps 8. Join Concepts Conclusion Core Knowledge Learned in This Chapter 4. Filtering Data Using SQL to Filter Data 1. Filtering Data Using Joins 2. Filtering Data Using Multiple Table Joins How it Works 3. Filtering Data Output Using Intermediate Tables How it Works 4. Filtering Text How it Works Tricks and Traps 5. Applying Multiple Text Filters How it Works Tricks and Traps 6. Excluding an Element How it Works Tricks and Traps 7. Using Multiple Exclusion Filters How it Works Tricks and Traps 8. Filtering Numbers Over a Defined Threshold How it Works Tricks and Traps 9. Filtering Numbers Under a Defined Threshold How it Works Tricks and Traps 10. Filtering on Values Up to and Including a Specific Number How it Works Tricks and Traps 11. Filtering on a Range of Values How it Works Tricks and Traps 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 Complex Filtering Techniques 1. Using Either/Or Filters How it Works Tricks and Traps 2. Using Multiple Separate Criteria Concurrently How it Works Tricks and Traps 3. Using Multiple Filters and an Exclusion How it Works Tricks and Traps 4. Filtering on Both Text and Numbers Simultaneously How it Works Tricks and Traps 5. Applying Complex Alternative Filters at the Same Time How it Works Tricks and Traps 6. Case-Sensitive Searches How it Works Tricks and Traps 7. Removing Case-Sensitivity in Filters How it Works Tricks and Traps 8. Using Wildcard Searches How it Works Tricks and Traps 9. Using Wildcards to Exclude Data How it Works 10. Forcing Case-Sensitivity in Wildcard Filters How it Works Tricks and Traps 11. Using a Specific Part of Text to Filter Data How it Works Tricks and Traps 12. Using NULLs, or Nonexistent Data How it Works Tricks and Traps 13. Searching Using Regular Expressions How it Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 6. Making Simple Calculations Performing Calculations in SQL 1. Doing Simple Math How it Works Tricks and Traps 2. Examining Data Types in MySQL Views Tricks and Traps 3. Isolating Sections of Formulas When Applying Math How it Works Tricks and Traps 4. Calculating Ratios How it Works Tricks and Traps 5. Avoiding Divide by Zero Errors How it Works Tricks and Traps 6. Increasing Values by a Defined Percentage How it Works 7. Sorting Output by the Result of a Calculation How it Works Tricks and Traps 8. Handling Missing Data How it Works Tricks and Traps 9. Filtering on a Calculation How it Works Tricks and Traps 10. Using Complex Calculated Filters How it Works Tricks and Traps 11. Writing Accurate SQL Code Faster Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 7. Aggregating Output Aggregating Data in SQL 1. Calculating Table Totals How it Works Tricks and Traps 2. Using Calculated Aggregations How it Works Tricks and Traps 3. Using Grouped Aggregations How it Works Tricks and Traps 4. Using Multiple Levels of Grouping How it Works Tricks and Traps 5. Calculating Averages How it Works Tricks and Traps 6. Counting Grouped Elements How it Works Tricks and Traps 7. Counting Unique Elements How it Works 8. Displaying Upper and Lower Numeric Thresholds How it Works Tricks and Traps 9. Filtering Groups How it Works Tricks and Traps 10. Filtering on Aggregated Results How it Works 11. Selecting Data Based on Aggregated Results as Well as Specific Filter Criteria How it Works Tricks and Traps 12. Sorting by Aggregated Results How it Works Conclusion Core Knowledge Learned in This Chapter 8. Working with Dates in MySQL Analyzing Data over Time 1. Filtering Records by Date How it Works Tricks and Traps 2. Using a Range of Dates to Filter Data How it Works Tricks and Traps 3. How to Specify Dates in MySQL Queries Tricks and Traps 4. Finding the Number of Days Between Two Dates How it Works Tricks and Traps 5. Aggregating Data over a Date Range How it Works Tricks and Traps 6. Eliminating the Time Element in a Date Filter How it Works Tricks and Traps 7. Filtering by Year How it Works Tricks and Traps 8. Filtering Records over a Series of Years How it Works Tricks and Traps 9. Isolating Data for a Specific Year and Month How it Works Tricks and Traps 10. Finding Data for a Given Quarter How it Works Tricks and Traps 11. Filtering Data by Weekday How it Works 12. Finding Records for a Specific Week of the Year How it Works Tricks and Traps 13. Aggregating Data by the Day of Week in a Given Year How it Works Tricks and Traps 14. Grouping Data by the Full Weekday Name How it Works Tricks and Traps 15. Aggregated Totals and Averages by Day of Year How it Works 16. Aggregated Totals and Averages by Day of the Month How it Works Tricks and Traps 17. Displaying Aggregated Values per Month How it Works Tricks and Traps 18. Displaying Cumulative Data over 75 Days Up to a Specific Date How it Works Tricks and Traps 19. Displaying the Data for the Previous Three Months How it Works Tricks and Traps 20. 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 1. Adding Text to the Output How it Works Tricks and Traps 2. Adding Multiple Pieces of Text to Numbers How it Works Tricks and Traps 3. Amalgamating Columns Using Different Separators How it Works Tricks and Traps 4. Avoiding NULLs in Concatenated Output How it Works 5. Concatenating and Grouping How it Works Tricks and Traps 6. Amalgamating Columns How it Works Tricks and Traps 7. Converting Text to Uppercase How it Works Tricks and Traps 8. Converting Text to Lowercase How it Works Tricks and Traps 9. Extracting the First Few Characters from a Field How it Works Tricks and Traps 10. Displaying the Three Characters at the Right of Text How it Works Tricks and Traps 11. Displaying a Given Number of Characters at a Specific Place in Text How it Works Tricks and Traps 12. Filtering Records Based on Part of a Field How it Works Tricks and Traps 13. Filtering Data Using Specific Characters at a Given Position Inside a Field How it Works Tricks and Traps 14. Joining on Part of a Field How it Works Conclusion Core Knowledge Learned in This Chapter 10. Formatting Numbers and Dates Presenting Numbers and Dates 1. Removing the Decimals from the Output How it Works Tricks and Traps 2. Rounding a Field Up to the Nearest Whole Number How it Works Tricks and Traps 3. Rounding a Value to the Nearest Whole Number How it Works Tricks and Traps 4. Rounding a Value Up or Down to the Nearest Thousand How it Works Tricks and Traps 5. Displaying a Value in a Specific Numeric Format How it Works Tricks and Traps 6. Displaying a Value in a Specific Currency How it Works Tricks and Traps 7. Specifying the National Number Format to Use How it Works Tricks and Traps 8. Outputting a Date in a Specific Date Format How it Works Tricks and Traps 9. Outputting a Date in the ISO Date Format How it Works Tricks and Traps 10. Presenting the Time in a Specific Format Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 11. Using Basic Logic to Enhance Analysis Applying SQL Logic 1. Generating an Alert When a Value Is Too High How it Works Tricks and Traps 2. Shortening Text and Adding Ellipses to Indicate Truncation How it Works 3. Designing Complex Calculated Alerts How it Works Tricks and Traps 4. Creating Key Performance Indicators How it Works Tricks and Traps 5. Classifying a Series of Elements Without the Necessary Categories Present in Your Data How it Works Tricks and Traps 6. Creating Ad Hoc Category Groupings How it Works Tricks and Traps 7. Applying Multiple Ad Hoc Categories How it Works Tricks and Traps 8. Categorizing Data Using Multiple Nested Classifications How it Works Tricks and Traps 9. Aggregating Ad-Hoc Categories How it Works Tricks and Traps 10. Placing NULLs at the Start or End of a List How it Works Tricks and Traps 11. Classifying Data by Impromptu Categories How it Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 12. Subqueries What Are Subqueries? 1. Adding Aggregated Fields to Detailed Data Sets How it Works Tricks and Traps 2. Displaying a Value as the Percentage of a Total How it Works Tricks and Traps 3. Using a Subquery to Filter Data How it Works Tricks and Traps 4. Using a Subquery as Part of a Calculation to Filter Data How it Works Tricks and Traps 5. Filtering on an Aggregated Range of Data Using Multiple Subqueries How it Works Tricks and Traps 6. Filtering on Aggregated Output Using a Second Aggregation How it Works Tricks and Traps 7. Nested Subqueries How it Works Tricks and Traps 8. Using Subqueries to Exclude Data How it Works Tricks and Traps 9. Multiple Nested Subqueries How it Works Tricks and Traps 10. Filtering across Queries and Subqueries How it Works Tricks and Traps 11. 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? 1. Using a Derived Table to Create Intermediate Calculations How it Works Tricks and Traps 2. Grouping and Ordering Data Using a Custom Classification How it Works Tricks and Traps 3. Joining Derived Tables to Other Tables How it Works Tricks and Traps 4. Using Multiple Results from a Derived Table to Filter Data How it Works Tricks and Traps 5. Complex Aggregated Derived Tables How it Works Tricks and Traps 6. Joining Multiple Derived Tables How it Works Tricks and Traps 7. Using Multiple Derived Tables for Complex Aggregations How it Works Tricks and Traps 8. Using Derived Tables to Join Unconnected Tables How it Works Tricks and Traps 9. Compare Year-on-Year Data Using a Derived Table How it Works Tricks and Traps 10. 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 1. A Basic Common Table Expression How it Works Tricks and Traps 2. Calculating Averages across Multiple Values Using a CTE How it Works 3. Reusing CTEs in a Query How it Works Tricks and Traps 4. Using a CTE in a Derived Table to Deliver Two Different Levels of Aggregation How it Works Tricks and Traps 5. Using a CTE to Isolate Data from a Separate Dataset at a Different Level of Detail How it Works Tricks and Traps 6. Multiple Common Table Expressions How it Works Tricks and Traps 7. Nested Common Table Expressions How it Works Tricks and Traps 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? 1. Simple Correlated Subqueries How it Works Tricks and Traps 2. Correlated Subqueries to Display Percentages of a Specific Total How it Works Tricks and Traps 3. Comparing Datasets Using a Correlated Subquery How it Works Tricks and Traps 4. Duplicating the Output of a Correlated Subquery in the Query Results How it Works 5. Aggregated Correlated Subqueries How it Works Tricks and Traps 6. Using Correlated Subqueries to Filter Data on an Aggregate Value How it Works Tricks and Traps 7. Using Correlated Subqueries to Detect If Records Exist How it Works Tricks and Traps 8. Using a Correlated Subquery to Exclude Data How it Works Tricks and Traps 9. Complex Joins in Correlated Subqueries How it Works Tricks and Traps 10. Using a Correlated Subquery to Verify Values across Tables How it Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 16. Dataset Manipulation Using Datasets to Mix and Match Data 1. Read Data from Multiple Identical Tables Using the UNION Operator How it Works Tricks and Traps 2. Isolate Identical Data in Multiple Tables How it Works Tricks and Traps 3. Isolate Common Elements in Multiple Subsets of Data How it Works Tricks and Traps 4. Joining Multiple Identical Tables in a Subquery How it Works Tricks and Traps 5. Isolating Nonidentical Records from Two Datasets How it Works Tricks and Traps 6. Displaying the Complete Records for Nonidentical data How it Works Tricks and Traps 7. Displaying the Complete Records for Identical data How it Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter 17. Using SQL for More Advanced Calculations Additional Calculation Techniques 1. Calculating the Percentage Represented by Each Record in a Dataset How it Works Tricks and Traps 2. Replacing Multiple Subqueries How it Works Tricks and Traps 3. Remove Decimals in Calculations How it Works 4. Numeric Data Types Tricks and Traps 5. Converting Formatted Source Data into Usable Numbers How it Works Tricks and Traps 6. Testing for Failures When Removing Formatting Characters How it Works 7. Testing for Non-Numeric Values How it Works Tricks and Traps 8. Finding the Remainder in a Division Using the Modulo Function How it Works Tricks and Traps 9. Creating Financial Calculations How it Works Tricks and Traps 10. Using a Tally Table to Produce a Sequential List of Numbers How it Works Tricks and Traps 11. Generating Completely Random Sample Output from a Dataset How It Works Conclusion Core Knowledge Learned in This Chapter 18. Segmenting and Classifying Data Ranking and Segmenting Data 1. Organizing Data by Rank How it Works Tricks and Traps 2. Creating Multiple Groups of Rankings How it Works Tricks and Traps 3. Creating Multiple Ranked Groups and Subgroups How it Works Tricks and Traps 4. Filtering Data by Ranked Items How it Works Tricks and Traps 5. Classifying Data by Strict Order of Rank How it Works Tricks and Traps 6. Segment Data into Deciles How it Works Tricks and Traps 7. Plot Values for a Percentile How it Works Tricks and Traps 8. Extract Data from a Specific Quintile How it Works Tricks and Traps 9. Returning the Top āNā Percent of a Dataset How it Works Tricks and Traps 10. Calculating Cumulative Distribution How it Works Tricks and Traps 11. Classifying Data Using the PERCENT_RANK() Function 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 1. Adding a Running Total How it Works Tricks and Traps 2. Using Window Functions in an Aggregated Query How it Works Tricks and Traps 3. Restarting Running Totals How it Works Tricks and Traps 4. Applying Window Functions to a Subquery How it Works 5. Adding Unique IDs on the Fly Using ROW_NUMBER() How it Works Tricks and Traps 6. Displaying Records for Missing Data How it Works 7. Displaying a Complete Range of Dates and Relevant Data How it Works Tricks and Traps 8. Comparing Data with the Data from a Previous Record How it Works Tricks and Traps 9. Comparing Data over Time Using the FIRST_VALUE() and LAST_VALUE() Functions How it Works 10. Displaying Rolling Averages over a Specified Number of Records How it Works Tricks and Traps 11. Show the First Sale and Last Four Sales per Client How it Works Conclusion Core Knowledge Learned in This Chapter 20. Analyzing Data over Time Time Analysis 1. Aggregating Values for the Year to Date How it Works Tricks and Traps 2. Aggregating Values for the Month to Date How it Works Tricks and Traps 3. Returning Aggregate Values for the Quarter to Date How it Works Tricks and Traps 4. Isolating Data for the Previous Month How it Works Tricks and Traps 5. Using a Derived Table to Compare Data with Values from a Previous Year How it Works Tricks and Traps 6. Finding the Total Amount for Each Weekday over a Year How it Works Tricks and Traps 7. Count the Number of Weekend Days between Two Dates How it Works Tricks and Traps 8. Aggregate Data for the Last Day of the Month How it Works Tricks and Traps 9. Aggregate Data for the Last Friday of the Month How it Works 10. Analyzing Timespans as Years, Months, and Days How it Works Tricks and Traps 11. Isolate Time Periods from Date and Time Data How it Works 12. Listing Data by Time of Day How it Works Tricks and Traps 13. Aggregating Data by Hourly Bandings How it Works Tricks and Traps 14. 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 1. Creating a Pivot Table How it Works Tricks and Traps 2. Creating a Pivot Table Displaying Multiple Row Groupings How it Works 3. Unpivoting Data How it Works Tricks and Traps 4. Adding Totals to Aggregate Queries How it Works Tricks and Traps 5. Creating Clear Tables That Include Totals and Subtotals How it Works 6. Handling Hierarchical Data How it Works Tricks and Traps 7. Producing Indented Hierarchies How it Works 8. Replace Acronyms with Full Text in the Final Output How it Works Tricks and Traps 9. Replacing a Specified Number of Characters with Other Text How it Works Tricks and Traps 10. Creating a Comma-Separated List from Multiple Records How it Works Tricks and Traps 11. Exporting Comma-Separated Lists How it Works Tricks and Traps 12. Exporting Lists with Headers How it Works Tricks and Traps 13. Exporting Fixed-Width Lists How it Works Tricks and Traps 14. Removing Extra Spaces from Output How it Works Tricks and Traps Conclusion Core Knowledge Learned in This Chapter Appendix A: Installing MySQL Installing MySQL 1. Installing MySQL on Windows 2. Installing MySQL on a Macintosh 3. Installing MySQL on Linux Appendix B: Installing MySQL Workbench 1. Installing MySQL Workbench on Windows 2. Installing MySQL Workbench on a Macintosh 3. Running MySQL Workbench 4. Creating a Connection to MySQL Appendix C: Setting Up the Sample Database 1. Downloading the Sample Data on Windows 2. Loading the Sample Data into the PrestigeCars Database 3. 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.