Getting Started with SQL and Databases: Managing and Manipulating Data with SQL

✍ Scribed by Mark Simon

• Publisher: Apress
• Tongue: English
• Leaves: 390
• Category: Library

✦ Synopsis

Learn the basics of writing SQL scripts. Using Standard SQL as the starting point, this book teaches writing SQL in various popular dialects, including PostgreSQL, MySQL/MariaDB, Microsoft SQL Server, Oracle, and SQLite.

The book starts with a general introduction to writing SQL and covers the basic concepts. Author Mark Simon then covers database principles, and how database tables are designed. He teaches you how to filter data using the WHERE clause, and you will work with NULL, numbers, dates, and strings. You will also understand sorting results using the ORDER BY clause, sorting by calculated columns, and limiting the number of results. By the end of the book, you will know how to insert and update data, and summarize data with aggregate functions and groups. Three appendices cover differences between SQL dialects, working with tables, and a crash course in PDO.

What You Will Learn

• Filter, sort, and calculate data
• Summarize data with aggregate functions
• Modify data with insert, update, and delete statements
• Study design principles in developing a database

Who This Book Is For

Developers and analysts working with SQL, as well as web developers who want a stronger understanding of working with databases

✦ Table of Contents

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: Starting with SQL
Basic SELECT Statement
Case Sensitivity
Spacing
Clause Ordering
The Semicolon (;)
Selecting Specific Columns
Column Order
Layout
Using SELECT *
Calculated Columns
Aliases
Comments
Block Comments
Uses of Comments
Filtering Rows
Clause Ordering
Placing the Semicolon
Ordering the Results
Clause Order
Distinct Rows
Summary
Writing SQL
Columns
Comments
Filtering Data
Row Order
Clause Order
Coming Up
Chapter 2: Database
About the Sample Database
Database
Database Terminology
Data vs. Value
Tables
Table Terminology
Normalized Tables
Data Is Atomic
Columns Are Independent
Columns Are of a Single Type
Rows Are Unordered
Rows Are Unique
Rows Are Independent
Column Names Are Unique
Columns Are Unordered
Multiple Values
Using Related Tables
Example: Paintings and Artists
Examples of Alternative Terminology
A More Complex Relationship
Summary
Terms
Normalized Tables
Multiple Values
Coming Up
Chapter 3: Filtering Data
The WHERE Clause
Unrelated Assertions
All and Nothing
Dealing with NULL
Deliberately Ignoring NULLs
Finding NULLs
Numbers
Discrete vs. Continuous Values
Strings
Quotes
More on MySQL/MariaDB Modes
More on Double and Single Quotes
Case Sensitivity
Trailing Spaces
Filtering with String Functions
Handling Quotes and Apostrophes
Before and After Strings
Dates
Dates Are Not Strings
Alternative Date Formats
Date Comparisons
Filtering with a Date Calculation
Multiple Assertions
AND and OR
The IN Operator
Derived Lists
Wildcard Matches
Case Sensitivity and Patterns
Pattern Characters
Wildcards with Non-strings
Extensions to Wildcards
Regular Expressions (PostgreSQL, MySQL/MariaDB, Oracle)
Simpler Extensions (PostgreSQL, MSSQL)
A Simple Pattern Match Example
Summary
NULL
Numbers
Strings
Dates
Multiple Assertions
The IN Operator
Wildcard Matches
Coming Up
Chapter 4: Ordering Results
Using the ORDER BY Clause
Sort Direction
Missing Data (NULL)
Data Types
Case Sensitivity and Collation
Multiple Columns
Interdependence of Columns
Sort Direction on Multiple Columns
Sorting by Calculated Columns
Limiting the Number of Results
Paging
Using LIMIT … OFFSET … (MySQL/MariaDB, SQLite, and PostgreSQL)
Using TOP (MSSQL)
Fetching a Random Row
Nonalphabetical String Order
Special Strings
Summary
Sorting with ORDER BY
Limiting Results
Sorting Strings
Coming Up
Chapter 5: Calculating Column Values
Testing Calculations
Emulating Variables
Some Basic Calculations
Basic Number Calculations
Basic String Calculations
Basic Date Calculations
Working with NULL
Using Aliases
Aliases Without AS
Awkward Aliases
Calculating with Numbers
Arithmetic Operators
Integers
Remainder
Extra Decimals
Mathematical Functions
Approximation Functions
Formatting Functions
Calculating with Dates
Simple Calculations
Age Calculations
Extracting Parts of a Date
Date Extraction in PostgreSQL, MariaDB/MySQL, and Oracle
Date Extraction in Microsoft SQL
Extracting a Date from a Datetime
Formatting a Date
Date Formatting in PostgreSQL and Oracle
Date Formatting in MariaDB/MySQL
Date Formatting in Microsoft SQL Server
Using a Formatted Date for Grouping by Month
Using a Formatted Date for Grouping by Weekday
Strings
Character Functions
String Length
Searching for a Substring
Replace
Change Case
Trim Spaces
Substrings
Subqueries
The CASE Expression
Casting to Different Data Types
The cast() Function
Casting to a String
Casting Date Literals
Creating a View
Using Views in Microsoft SQL
Summary
Data Types
NULLs
Aliases
Subqueries
The CASE Expression
Casting a Value
Views
Coming Up
Chapter 6: Joining Tables
How a Join Works
Joining the Tables
Alternative Syntax
Selecting the Results
Table Aliases
Developing a Price List
Join Types
The INNER JOIN
The LEFT OUTER JOIN and RIGHT OUTER JOIN
The “Preferred” Outer Join
Some Recommendations on JOINS
(Almost) Always Alias Your Tables
Which Table Comes First?
Decide Whether You Use INNER and OUTER
Finishing the Price List
Joining Many Tables
Building a Larger JOIN
Simplifying the Result
Revisiting Some Subqueries
A More Complex Join
Using a Self-Join
Summary
Syntax
Table Aliases
The ON Clause
Join Types
Coming Up
Chapter 7: Aggregating Data
Counting Data
Counting Values
How Aggregates Work
Counting Selectively
Distinct Values
Summarizing Numbers
Bad Examples
Scales of Measurement
Aggregating Calculated Data
Other Aggregate Functions
Using Aggregates As Filters
Grouping
Using the GROUP BY Clause
GROUP BY vs. DISTINCT
Grouping with Multiple Tables
Redundant Groups
Preparing Data for Aggregating
Using CASE in a CTE
Using a Join in the CTE
Summarizing Strings
Filtering Grouped Results with HAVING
Using Results in a CTE
Finding Duplicates
Using Aggregates on Aggregates
Summary
Coming Up
Chapter 8: Working with Tables
How Tables Are Created
Creating a Table
Column Names
Data Type
Primary Keys
Constraints
NOT NULL
UNIQUE
DEFAULT
CHECK
Foreign Keys
Indexes
Adding Rows to a Table
Deleting Rows from a Table
Adding More Rows
Updating Rows
Altering the Table
DML in Real Life
Security
Front-End Software
Summary
Data Types
Constraints
Foreign Keys
Indexes
Manipulating Data
Chapter 9: Set Operations
Unions
Selective Unions
SELECT Clauses Must Be Compatible
Only Column Names from the First SELECT Statement Are Used
Sorting Results
Intersections
Differences
Some Tricks with Set Operations
Comparing Results
Virtual Tables
Mixing Aggregates
Summary
Appendix 1: Differences Between SQL Dialects
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
Appendix 2: A Crash Course in PDO
PDO Objects
The PDO Object
The PDOStatement Object
Working with PDO
Establishing a Connection
Other DBMSs
Prepared Statements and SQL Injection
Prepared Statements
Repeated Execution
Unprepared (Direct) SQL Statements
SELECT Statements
INSERT, UPDATE, and DELETE Statements
Selecting Data
Fetching Data
The Result Set
Fetching a Single Column
A Simple Login Script
Getting the Last Auto-Incremented Key
Error Reporting
Summary of PDO
Connection
Executing Simple Statements
INSERT, UPDATE, and DELETE
SELECT Statements
Executing Prepared Statements
Reading Data
Reading a Single Row
Reading Multiple Rows
Reading a Single Column
Appendix 3: Additional Notes
Cultural Notes
Addresses and Phone Numbers
Towns
States
Postcodes
Phone Numbers
Email Addresses
Measurements, Prices, and Currency
Dates
SQL Data Values
Stored Values
Variables
Literals
Calculated Values
Some Notes on Dates (and Times)
Oracle Date Format
Microsoft Age Function
Working with SQLite Dates
ISO 8601 Dates and Times
Date Functions
Manipulating Dates and Times
Formatting Dates and Times
Index