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

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Getting Ready
	About the Sample Database
	Setting Up
		Database Management Software
		Database Client
		The Sample Database
	What You Probably Know Already
		Some Philosophical Concepts
			Data vs. Information vs. Values
			Database Tables
		Writing SQL
		Basic SQL
		Data Types
		SQL Clauses
			Filtering Data with the WHERE Clause
				Multiple Assertions
				Wildcard Matches
			Sorting with the ORDER BY Clause
				Limiting Results
				Sorting Strings
		Calculating Columns
			Calculating with NULLs
				Aliases
				Subqueries
			The CASE Expression
			Casting a Value
			Views
		Joins
			The ON Clause
			Join Types
		Aggregates
		Working with Tables
			Data Types
			Constraints
			Foreign Keys
			Indexes
		Manipulating Data
		Set Operations
	Coming Up
Chapter 2: Working with Table Design
	Understanding Normalized Tables
	Columns Should Be Independent
		Adding the Towns Table
		Adding a Foreign Key to the Town
		Update the Customers Table
		Remove the Old Address Columns
			Create a customerdetails View
			Drop the Address Columns
		Changing the Town
		Adding the Country
		Additional Comments
	Improving Database Integrity
		Fixing Issues with a Nullable Column
			Replacing NULL Quantities
			Setting the NOT NULL Constraint for Quantity
			Setting a DEFAULT for Quantity
			Adding a Positive CHECK Constraint for Quantity
			Combining the Changes
			Making the Changes in SQLite
		Other Adjustments
			Ensuring the Prices Are Not Negative
			Ensuring That an Author Is Born Before Dying
	Adding Indexes
		Adding an Index to the Books and Authors Tables
		Creating a Unique Index
	Review
		Normal Form
		Multiple Values
		Altering Tables
		Views
		Indexes
		The Final Product
	Summary
	Coming Up
Chapter 3: Table Relationships and Joins
	An Overview of Relationships
	One-to-Many Relationship
		Counting One-to-Many Joins
		The NOT IN Quirk
		Creating a Books and Authors View
	One-to-One Relationships
		One-to-Maybe Relationships
	Multiple Values
		Many-to-Many Relationships
		Joining Many-to-Many Tables
		Summarizing Multiple Values
		Combining the Joins
		Many-to-Many Relationships Happen All the Time
	Another Many-to-Many Example
	Inserting into Related Tables
		Adding a Book and an Author
			Adding an Author
			Adding a Book
		Adding a New Sale
			Adding a New Sale in the Sales Table
			Adding the Sale Items and Getting the Prices
			Completing the Sale
	Review
		Types of Relationships
		Joining Tables
		Views
		Inserting into Related Tables
	Summary
	Coming Up
Chapter 4: Working with Calculated Data
	Calculation Basics
		Using Aliases
			Alias Names
			AS Is Optional
			Aliases Are Not Available in the Rest of the Query
		Dealing with NULLs
			Coalesce
			Fixing the Author Names
		Using Calculations in Other Clauses
	More Details on Calculations
		Casting
		Numeric Calculations
			Basic Arithmetic
			Mathematical Functions
			Approximation Functions
			Formatting Numbers
		String Calculations
			Case Sensitivity
			ASCII and Unicode
			Concatenation
			String Functions
		Date Operations
			Entering and Storing a Date/Time
			Getting the Current Date/Time
			Grouping and Sorting by Date/Time
			Extracting Parts of a Date/Time
				Date Extracting in PostgreSQL, MariaDB/MySQL, and Oracle
				Date Extracting in Microsoft SQL
			Formatting a Date
			Date Arithmetic
	The CASE Expression
		Various Uses of CASE
		Coalesce Is like a Special Case of CASE
		Nested CASE Expression
	Summary
		Aliases
		NULLs
		Casting Types
		Calculating with Numbers
		Calculating with Strings
		Calculating with Dates
		The CASE Expression
	Coming Up
Chapter 5: Aggregating Data
	The Basic Aggregate Functions
		NULL
	Understanding Aggregates
	Aggregating Some of the Values
		Distinct Values
		Aggregate Filter
	Grouping by Calculated Values
		Grouping with CASE Statements
		Revisiting the Delivery Status
		Ordering by Arbitrary Strings
	Group Concatenation
	Summarizing the Summary with Grouping Sets
		Preparing Data for Summarizing
		Combining Summaries with the UNION Clause
		Using GROUPING SETS, CUBE, and ROLLUP
			GROUPING SETS and CUBE (PostgreSQL, MSSQL, and Oracle)
			USING ROLLUP (PostgreSQL, MSSQL, Oracle, and MariaDB/MySQL)
			Sorting the Results
			Renaming Values in Oracle
	Histograms, Mean, Mode, and Median
		Calculating the Mean
		Generating a Frequency Table
		Calculating the Mode
		Calculating the Median
		The Standard Deviation
	Summary
		Basic Aggregate Functions
		NULLs
		The Aggregating Process
		Aggregate Filters
		GROUP BY
		Mixing Subtotals
		Statistics
	Coming Up
Chapter 6: Using Views and Friends
	Working with Views
		Creating a View
		Using ORDER BY in MSSQL
		Tips for Working with View
			Don’t Cascade Views Too Much
			Don’t Use SELECT *
			Avoid Using ORDER BY
		Table-Valued Functions
			TVFs in PostgreSQL
			TVFs in Microsoft SQL
		What Can You Do with a View?
			Convenience
			As an Interface
			Working with External Applications
	Caching Data and Temporary Tables
	Computed Columns
	Summary
		Views
		Table Valued Functions
		Temporary Tables
			Computed Columns
	Coming Up
Chapter 7: Working with Subqueries and Common Table Expressions
	Correlated and Non-correlated Subqueries
	Subqueries in the SELECT Clause
	Subqueries in the WHERE Clause
		Subqueries with Simple Aggregates
		Big Spenders
		Last Orders, Please
		Duplicated Customers
	Subqueries in the FROM Clause
		Nested Subqueries
	Using WHERE EXISTS (Subquery)
		WHERE EXISTS with Non-correlated Subqueries
		WHERE EXISTS with Correlated Subqueries
		WHERE EXISTS vs. the IN() Expression
	LATERAL JOINS (a.k.a. CROSS APPLY) and Friends
		Adding Columns
		Multiple Columns
	Working with Common Table Expressions
		Syntax
		Using a CTE to Prepare Calculations
	Summary
		Correlated and Non-correlated Subqueries
		The WHERE EXISTS Expression
		LATERAL JOINS (a.k.a. CROSS APPLY)
		Common Table Expressions
	Coming Up
Chapter 8: Window Functions
	Writing Window Functions
		Simple Aggregate Windows
	Aggregate Functions
	Aggregate Window Functions and ORDER BY
		The Framing Clause
		Creating a Daily Sales View
		A Sliding Window
	Window Function Subtotals
		PARTITION BY Multiple Columns
	Ranking Functions
		Basic Ranking Functions
		Ranking with PARTITION BY
		Paging Results
	Working with ntile
		A Workaround for ntile
	Working with Previous and Next Rows
	Summary
		Window Clauses
	Coming Up
Chapter 9: More on Common Table Expressions
	CTEs As Variables
		Setting Hard-Coded Constants
		Deriving Constants
	Using Aggregates in the CTE
		Finding the Most Recent Sales per Customer
		Finding Customers with Duplicate Names
	CTE Parameter Names
	Using Multiple Common Table Expressions
		Summarizing Duplicate Names with Multiple CTEs
	Recursive CTEs
		Generating a Sequence
		Joining a Sequence CTE to Get Missing Values
		Daily Comparison Including Missing Days
		Traversing a Hierarchy
			Getting a Single-Level Hierarchy
			Multilevel Hierarchy Using Recursive CTE
			Cleaning the Tail End of the List
	Working with Table Literals
		Using a Table Literal for Testing
		Using a Table Literal for Sorting
		Using a Table Literal As a Lookup
		Splitting a String
			Splitting More Complex Data
	Summary
		Simple CTEs
		Parameter Names
		Multiple CTEs
		Recursive CTEs
	Coming Up
Chapter 10: More Techniques: Triggers, Pivot Tables, and Variables
	Understanding Triggers
		Some Trigger Basics
		Preparing the Data to Be Archived
		Creating the Trigger
			PostgreSQL Triggers
			MySQL/MariaDB Triggers
			MSSQL Triggers
			SQLite Triggers
			Oracle Triggers
		Pros and Cons of Triggers
	Pivoting Data
		Pivoting the Data
		Manually Pivoting Data
		Using the Pivot Feature (MSSQL, Oracle)
			Using the Unpivot Feature
	Working with SQL Variables
		Code Blocks
		Updated Code to Add a Sale
			Using Variables in PostgreSQL
			Using Variables in MariaDB/MySQL
			Using Variables in MSSQL
			Using Variables in Oracle
	Review
		Triggers
		Pivot Tables
		SQL Variables
	Summary
Appendix A: Cultural Notes
	Addresses and Phone Numbers
		Towns
		States
		Postcodes
		Phone Numbers
	Email Addresses
	Measurements and Prices and Currency
	Dates
Appendix B: DBMS Differences
	Writing SQL
		Semicolons
		Data Types
		Dates
		Case Sensitivity
		Quote Marks
	Sorting (ORDER BY)
		Limiting Results
	Filtering (WHERE)
		Case Sensitivity
		String Comparisons
		Dates
		Wildcard Matching
	Calculations
		SELECT Without FROM
		Arithmetic
		Formatting Functions
		Date Functions
		Concatenation
		String Functions
	Joining Tables
	Aggregate Functions
	Manipulating Data
	Manipulating Tables
	Autoincremented Primary Keys
	Other Quirks and Variations
		PostgreSQL Quirks and Variations
		Microsoft Quirks and Variations
		Oracle Quirks and Variations
		MariaDB/MySQL Quirks and Variations
Appendix C: Using SQL with Python
	Installing the Database Connector Module
		Installing the MSSQL Module on Windows
		Installing the MSSQL Module on Macintosh or Linux
	Creating a Connection
		Connecting to SQLite
		Connecting to MSSQL
		Connecting to MariaDB/MySQL
		Connecting to PostgreSQL
		Connecting to Oracle
	Fetching from the Database
	Using Parameters in the Query
	Adding a New Sale
		The SQL Strings
			SQL Strings for PostgreSQL
			SQL Strings for SQLite
			SQL Strings for MSSQL
			SQL Strings for MariaDB/MySQL
			SQL Strings for Oracle
		Adding the Sale
		Adding the Sale Items
		Completing the Sale
Index