Learn to write SQL queries to select and analyze data, and improve your ability to manipulate data. This book will help you take your existing skills to the next level.
Author Mark Simon kicks things off with a quick review of basic SQL knowledge, followed by a demonstration of how efficient SQL databases are designed and how to extract just the right data from them. You’ll then learn about each individual table’s structure and how to work with the relationships between tables. As you progress through the book, you will learn more sophisticated techniques such as using common table expressions and subqueries, analyzing your data using aggregate and windowing functions, and how to save queries in the form of views and other methods. This book employs an accessible approach to work through a realistic sample, enabling you to learn concepts as they arise to improve parts of the database or to work with the data itself.
After completing this book, you will have a more thorough understanding of database structure and how to use advanced techniques to extract, manage, and analyze data.
What Will You Learn
- Gain a stronger understanding of database design principles, especially individual tables
- Understand the relationships between tables
- Utilize techniques such as views, subqueries, common table expressions, and windowing functions
Who Is This Book For:
SQL Databases users who want to improve their knowledge and techniques.
Author(s): Mark Simon
Edition: 1
Publisher: Apress
Year: 2023
Language: English
Commentary: Publisher PDF
Pages: 474
City: Berkeley, CA
Tags: Database; PostgreSQL; Oracle; MySQL; MariaDB; SQLite; SQL; Data Analysis; Anomaly Detection; SQL; Relational Databases; Statistics; Queries; Window Functions
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
