Leveling Up with SQL: Advanced Techniques for Transforming Data into Insights

Table of Contents....5 About the Author....13 About the Technical Reviewer....14 Acknowledgments....15 Introduction....16 Chapter 1: Getting Ready....18 About the Sample Database....18 Setting Up....20 Database Management Software....20 Database Client....21 The Sample Database....21 What You Probably Know Already....22 Some Philosophical Concepts....23 Data vs. Information vs. Values....23 Database Tables....25 Writing SQL....27 Basic SQL....27 Data Types....28 SQL Clauses....29 Filtering Data with the WHERE Clause....30 Multiple Assertions....30 Wildcard Matches....30 Sorting with the ORDER BY Clause....31 Limiting Results....31 Sorting Strings....32 Calculating Columns....32 Calculating with NULLs....33 Aliases....33 Subqueries....33 The CASE Expression....33 Casting a Value....34 Views....34 Joins....34 The ON Clause....35 Join Types....35 Aggregates....35 Working with Tables....37 Data Types....37 Constraints....37 Foreign Keys....38 Indexes....38 Manipulating Data....38 Set Operations....38 Coming Up....39 Chapter 2: Working with Table Design....41 Understanding Normalized Tables....41 Columns Should Be Independent....43 Adding the Towns Table....44 Adding a Foreign Key to the Town....45 Update the Customers Table....46 Remove the Old Address Columns....48 Create a customerdetails View....48 Drop the Address Columns....49 Changing the Town....51 Adding the Country....52 Additional Comments....54 Improving Database Integrity....54 Fixing Issues with a Nullable Column....56 Replacing NULL Quantities....57 Setting the NOT NULL Constraint for Quantity....58 Setting a DEFAULT for Quantity....58 Adding a Positive CHECK Constraint for Quantity....59 Combining the Changes....60 Making the Changes in SQLite....60 Other Adjustments....61 Ensuring the Prices Are Not Negative....63 Ensuring That an Author Is Born Before Dying....64 Adding Indexes....65 Adding an Index to the Books and Authors Tables....66 Creating a Unique Index....68 Review....70 Normal Form....70 Multiple Values....71 Altering Tables....71 Views....71 Indexes....72 The Final Product....72 Summary....73 Coming Up....74 Chapter 3: Table Relationships and Joins....75 An Overview of Relationships....76 One-to-Many Relationship....77 Counting One-to-Many Joins....80 The NOT IN Quirk....85 Creating a Books and Authors View....86 One-to-One Relationships....88 One-to-Maybe Relationships....88 Multiple Values....92 Many-to-Many Relationships....93 Joining Many-to-Many Tables....98 Summarizing Multiple Values....100 Combining the Joins....102 Many-to-Many Relationships Happen All the Time....106 Another Many-to-Many Example....106 Inserting into Related Tables....109 Adding a Book and an Author....110 Adding an Author....111 Adding a Book....113 Adding a New Sale....114 Adding a New Sale in the Sales Table....115 Adding the Sale Items and Getting the Prices....116 Completing the Sale....117 Review....118 Types of Relationships....119 Joining Tables....119 Views....120 Inserting into Related Tables....120 Summary....120 Coming Up....120 Chapter 4: Working with Calculated Data....121 Calculation Basics....122 Using Aliases....124 Alias Names....125 AS Is Optional....126 Aliases Are Not Available in the Rest of the Query....128 Dealing with NULLs....129 Coalesce....131 Fixing the Author Names....133 Using Calculations in Other Clauses....134 More Details on Calculations....138 Casting....138 Numeric Calculations....143 Basic Arithmetic....143 Mathematical Functions....144 Approximation Functions....145 Formatting Numbers....146 String Calculations....148 Case Sensitivity....149 ASCII and Unicode....150 Concatenation....151 String Functions....153 Date Operations....155 Entering and Storing a Date/Time....156 Getting the Current Date/Time....158 Grouping and Sorting by Date/Time....159 Extracting Parts of a Date/Time....160 Date Extracting in PostgreSQL, MariaDB/MySQL, and Oracle....160 Date Extracting in Microsoft SQL....161 Formatting a Date....162 Date Arithmetic....164 The CASE Expression....167 Various Uses of CASE....168 Coalesce Is like a Special Case of CASE....170 Nested CASE Expression....171 Summary....174 Aliases....175 NULLs....175 Casting Types....175 Calculating with Numbers....175 Calculating with Strings....176 Calculating with Dates....176 The CASE Expression....176 Coming Up....177 Chapter 5: Aggregating Data....178 The Basic Aggregate Functions....178 NULL....181 Understanding Aggregates....181 Aggregating Some of the Values....185 Distinct Values....185 Aggregate Filter....186 Grouping by Calculated Values....188 Grouping with CASE Statements....192 Revisiting the Delivery Status....194 Ordering by Arbitrary Strings....196 Group Concatenation....198 Summarizing the Summary with Grouping Sets....200 Preparing Data for Summarizing....201 Combining Summaries with the UNION Clause....204 Using GROUPING SETS, CUBE, and ROLLUP....210 GROUPING SETS and CUBE (PostgreSQL, MSSQL, and Oracle)....210 USING ROLLUP (PostgreSQL, MSSQL, Oracle, and MariaDB/MySQL)....211 Sorting the Results....212 Renaming Values in Oracle....214 Histograms, Mean, Mode, and Median....216 Calculating the Mean....218 Generating a Frequency Table....218 Calculating the Mode....220 Calculating the Median....222 The Standard Deviation....223 Summary....224 Basic Aggregate Functions....224 NULLs....225 The Aggregating Process....225 Aggregate Filters....225 GROUP BY....226 Mixing Subtotals....226 Statistics....227 Coming Up....227 Chapter 6: Using Views and Friends....228 Working with Views....229 Creating a View....231 Using ORDER BY in MSSQL....234 Tips for Working with View....234 Don’t Cascade Views Too Much....235 Don’t Use SELECT *....235 Avoid Using ORDER BY....235 Table-Valued Functions....236 TVFs in PostgreSQL....237 TVFs in Microsoft SQL....239 What Can You Do with a View?....240 Convenience....240 As an Interface....240 Working with External Applications....242 Caching Data and Temporary Tables....242 Computed Columns....246 Summary....248 Views....248 Table Valued Functions....248 Temporary Tables....249 Computed Columns....249 Coming Up....249 Chapter 7: Working with Subqueries and Common Table Expressions....250 Correlated and Non-correlated Subqueries....254 Subqueries in the SELECT Clause....258 Subqueries in the WHERE Clause....261 Subqueries with Simple Aggregates....261 Big Spenders....261 Last Orders, Please....264 Duplicated Customers....266 Subqueries in the FROM Clause....267 Nested Subqueries....270 Using WHERE EXISTS (Subquery)....273 WHERE EXISTS with Non-correlated Subqueries....274 WHERE EXISTS with Correlated Subqueries....274 WHERE EXISTS vs. the IN() Expression....275 LATERAL JOINS (a.k.a. CROSS APPLY) and Friends....276 Adding Columns....278 Multiple Columns....280 Working with Common Table Expressions....282 Syntax....283 Using a CTE to Prepare Calculations....284 Summary....287 Correlated and Non-correlated Subqueries....287 The WHERE EXISTS Expression....288 LATERAL JOINS (a.k.a. CROSS APPLY)....288 Common Table Expressions....288 Coming Up....288 Chapter 8: Window Functions....290 Writing Window Functions....291 Simple Aggregate Windows....292 Aggregate Functions....294 Aggregate Window Functions and ORDER BY....299 The Framing Clause....300 Creating a Daily Sales View....302 A Sliding Window....303 Window Function Subtotals....305 PARTITION BY Multiple Columns....309 Ranking Functions....311 Basic Ranking Functions....312 Ranking with PARTITION BY....315 Paging Results....317 Working with ntile....320 A Workaround for ntile....322 Working with Previous and Next Rows....324 Summary....326 Window Clauses....326 Coming Up....327 Chapter 9: More on Common Table Expressions....328 CTEs As Variables....328 Setting Hard-Coded Constants....329 Deriving Constants....331 Using Aggregates in the CTE....332 Finding the Most Recent Sales per Customer....332 Finding Customers with Duplicate Names....334 CTE Parameter Names....335 Using Multiple Common Table Expressions....336 Summarizing Duplicate Names with Multiple CTEs....337 Recursive CTEs....340 Generating a Sequence....343 Joining a Sequence CTE to Get Missing Values....346 Daily Comparison Including Missing Days....348 Traversing a Hierarchy....351 Getting a Single-Level Hierarchy....352 Multilevel Hierarchy Using Recursive CTE....353 Cleaning the Tail End of the List....356 Working with Table Literals....357 Using a Table Literal for Testing....359 Using a Table Literal for Sorting....363 Using a Table Literal As a Lookup....366 Splitting a String....368 Splitting More Complex Data....373 Summary....379 Simple CTEs....379 Parameter Names....380 Multiple CTEs....380 Recursive CTEs....380 Coming Up....380 Chapter 10: More Techniques: Triggers, Pivot Tables, and Variables....381 Understanding Triggers....382 Some Trigger Basics....383 Preparing the Data to Be Archived....384 Creating the Trigger....386 PostgreSQL Triggers....387 MySQL/MariaDB Triggers....388 MSSQL Triggers....390 SQLite Triggers....392 Oracle Triggers....392 Pros and Cons of Triggers....394 Pivoting Data....395 Pivoting the Data....396 Manually Pivoting Data....398 Using the Pivot Feature (MSSQL, Oracle)....403 Using the Unpivot Feature....405 Working with SQL Variables....408 Code Blocks....409 Updated Code to Add a Sale....410 Using Variables in PostgreSQL....411 Using Variables in MariaDB/MySQL....412 Using Variables in MSSQL....414 Using Variables in Oracle....416 Review....418 Triggers....418 Pivot Tables....419 SQL Variables....419 Summary....420 Appendix A: Cultural Notes....421 Addresses and Phone Numbers....421 Towns....421 States....421 Postcodes....422 Phone Numbers....422 Email Addresses....423 Measurements and Prices and Currency....423 Dates....423 Appendix B: DBMS Differences....424 Writing SQL....424 Semicolons....425 Data Types....425 Dates....425 Case Sensitivity....425 Quote Marks....426 Sorting (ORDER BY)....426 Limiting Results....427 Filtering (WHERE)....427 Case Sensitivity....427 String Comparisons....427 Dates....427 Wildcard Matching....428 Calculations....428 SELECT Without FROM....428 Arithmetic....429 Formatting Functions....429 Date Functions....429 Concatenation....429 String Functions....430 Joining Tables....430 Aggregate Functions....430 Manipulating Data....431 Manipulating Tables....431 Autoincremented Primary Keys....431 Other Quirks and Variations....432 PostgreSQL Quirks and Variations....432 Microsoft Quirks and Variations....432 Oracle Quirks and Variations....433 MariaDB/MySQL Quirks and Variations....433 Appendix C: Using SQL with Python....434 Installing the Database Connector Module....435 Installing the MSSQL Module on Windows....435 Installing the MSSQL Module on Macintosh or Linux....436 Creating a Connection....437 Connecting to SQLite....437 Connecting to MSSQL....438 Connecting to MariaDB/MySQL....439 Connecting to PostgreSQL....439 Connecting to Oracle....440 Fetching from the Database....440 Using Parameters in the Query....442 Adding a New Sale....444 The SQL Strings....445 SQL Strings for PostgreSQL....446 SQL Strings for SQLite....447 SQL Strings for MSSQL....447 SQL Strings for MariaDB/MySQL....448 SQL Strings for Oracle....449 Adding the Sale....450 Adding the Sale Items....451 Completing the Sale....453 Index....455