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

Table of Contents....5

About the Author....14

About the Technical Reviewer....15

Acknowledgments....16

Introduction....17

Chapter 1: Starting with SQL....20

Basic SELECT Statement....21

Case Sensitivity....22

Spacing....22

Clause Ordering....23

The Semicolon (;)....23

Selecting Specific Columns....24

Column Order....25

Layout....25

Using SELECT ....26

Calculated Columns....26

Aliases....27

Comments....28

Block Comments....29

Uses of Comments....29

Filtering Rows....30

Clause Ordering....31

Placing the Semicolon....32

Ordering the Results....32

Clause Order....33

Distinct Rows....34

Summary....37

Writing SQL....38

Columns....39

Comments....39

Filtering Data....39

Row Order....40

Clause Order....40

Coming Up....40

Chapter 2: Database....41

About the Sample Database....41

Database....42

Database Terminology....43

Data vs. Value....43

Tables....44

Table Terminology....44

Normalized Tables....45

Data Is Atomic....45

Columns Are Independent....46

Columns Are of a Single Type....47

Rows Are Unordered....49

Rows Are Unique....49

Rows Are Independent....50

Column Names Are Unique....50

Columns Are Unordered....51

Multiple Values....51

Using Related Tables....52

Example: Paintings and Artists....52

Examples of Alternative Terminology....53

A More Complex Relationship....54

Summary....55

Terms....55

Normalized Tables....56

Multiple Values....56

Coming Up....57

Chapter 3: Filtering Data....58

The WHERE Clause....58

Unrelated Assertions....61

All and Nothing....61

Dealing with NULL....62

Deliberately Ignoring NULLs....63

Finding NULLs....64

Numbers....66

Discrete vs. Continuous Values....66

Strings....69

Quotes....70

More on MySQLMariaDB Modes....71

More on Double and Single Quotes....72

Case Sensitivity....74

Trailing Spaces....75

Filtering with String Functions....76

Handling Quotes and Apostrophes....78

Before and After Strings....79

Dates....80

Dates Are Not Strings....81

Alternative Date Formats....82

Date Comparisons....83

Filtering with a Date Calculation....86

Multiple Assertions....87

AND and OR....87

The IN Operator....93

Derived Lists....95

Wildcard Matches....98

Case Sensitivity and Patterns....100

Pattern Characters....101

Wildcards with Non-strings....104

Extensions to Wildcards....106

Regular Expressions (PostgreSQL, MySQLMariaDB, Oracle)....106

Simpler Extensions (PostgreSQL, MSSQL)....107

A Simple Pattern Match Example....108

Summary....109

NULL....110

Numbers....110

Strings....110

Dates....111

Multiple Assertions....111

The IN Operator....111

Wildcard Matches....111

Coming Up....112

Chapter 4: Ordering Results....113

Using the ORDER BY Clause....113

Sort Direction....115

Missing Data (NULL)....116

Data Types....118

Case Sensitivity and Collation....122

Multiple Columns....123

Interdependence of Columns....125

Sort Direction on Multiple Columns....126

Sorting by Calculated Columns....128

Limiting the Number of Results....131

Paging....132

Using LIMIT OFFSET (MySQLMariaDB, SQLite, and PostgreSQL)....133

Using TOP (MSSQL)....133

Fetching a Random Row....134

Nonalphabetical String Order....135

Special Strings....136

Summary....137

Sorting with ORDER BY....137

Limiting Results....138

Sorting Strings....138

Coming Up....138

Chapter 5: Calculating Column Values....139

Testing Calculations....141

Emulating Variables....142

Some Basic Calculations....143

Basic Number Calculations....143

Basic String Calculations....144

Basic Date Calculations....145

Working with NULL....145

Using Aliases....151

Aliases Without AS....152

Awkward Aliases....154

Calculating with Numbers....156

Arithmetic Operators....156

Integers....157

Remainder....159

Extra Decimals....160

Mathematical Functions....161

Approximation Functions....162

Formatting Functions....163

Calculating with Dates....165

Simple Calculations....165

Age Calculations....167

Extracting Parts of a Date....169

Date Extraction in PostgreSQL, MariaDBMySQL, and Oracle....169

Date Extraction in Microsoft SQL....170

Extracting a Date from a Datetime....171

Formatting a Date....171

Date Formatting in PostgreSQL and Oracle....172

Date Formatting in MariaDBMySQL....173

Date Formatting in Microsoft SQL Server....173

Using a Formatted Date for Grouping by Month....174

Using a Formatted Date for Grouping by Weekday....175

Strings....176

Character Functions....177

String Length....178

Searching for a Substring....178

Replace....178

Change Case....179

Trim Spaces....179

Substrings....179

Subqueries....181

The CASE Expression....185

Casting to Different Data Types....189

The cast() Function....190

Casting to a String....191

Casting Date Literals....193

Creating a View....193

Using Views in Microsoft SQL....196

Summary....197

Data Types....197

NULLs....197

Aliases....198

Subqueries....198

The CASE Expression....198

Casting a Value....198

Views....199

Coming Up....199

Chapter 6: Joining Tables....200

How a Join Works....201

Joining the Tables....204

Alternative Syntax....204

Selecting the Results....206

Table Aliases....208

Developing a Price List....209

Join Types....210

The INNER JOIN....212

The LEFT OUTER JOIN and RIGHT OUTER JOIN....212

The Preferred Outer Join....214

Some Recommendations on JOINS....215

(Almost) Always Alias Your Tables....216

Which Table Comes First?....216

Decide Whether You Use INNER and OUTER....216

Finishing the Price List....216

Joining Many Tables....217

Building a Larger JOIN....219

Simplifying the Result....221

Revisiting Some Subqueries....224

A More Complex Join....226

Using a Self-Join....231

Summary....235

Syntax....235

Table Aliases....235

The ON Clause....235

Join Types....236

Coming Up....236

Chapter 7: Aggregating Data....237

Counting Data....238

Counting Values....238

How Aggregates Work....239

Counting Selectively....240

Distinct Values....246

Summarizing Numbers....247

Bad Examples....249

Scales of Measurement....250

Aggregating Calculated Data....251

Other Aggregate Functions....253

Using Aggregates As Filters....254

Grouping....257

Using the GROUP BY Clause....259

GROUP BY vs. DISTINCT....264

Grouping with Multiple Tables....265

Redundant Groups....268

Preparing Data for Aggregating....270

Using CASE in a CTE....273

Using a Join in the CTE....274

Summarizing Strings....276

Filtering Grouped Results with HAVING....278

Using Results in a CTE....282

Finding Duplicates....283

Using Aggregates on Aggregates....286

Summary....289

Coming Up....290

Chapter 8: Working with Tables....291

How Tables Are Created....292

Creating a Table....293

Column Names....294

Data Type....295

Primary Keys....296

Constraints....298

NOT NULL....299

UNIQUE....300

DEFAULT....300

CHECK....301

Foreign Keys....301

Indexes....303

Adding Rows to a Table....304

Deleting Rows from a Table....306

Adding More Rows....308

Updating Rows....309

Altering the Table....311

DML in Real Life....313

Security....313

Front-End Software....314

Summary....315

Data Types....315

Constraints....315

Foreign Keys....316

Indexes....316

Manipulating Data....316

Chapter 9: Set Operations....317

Unions....317

Selective Unions....321

SELECT Clauses Must Be Compatible....323

Only Column Names from the First SELECT Statement Are Used....325

Sorting Results....326

Intersections....328

Differences....331

Some Tricks with Set Operations....335

Comparing Results....335

Virtual Tables....337

Mixing Aggregates....340

Summary....344

Appendix 1: Differences Between SQL Dialects....346

Writing SQL....346

Semicolons....347

Data Types....347

Dates....347

Case Sensitivity....348

Quote Marks....348

Sorting (ORDER BY)....349

Limiting Results....349

Filtering (WHERE)....349

Case Sensitivity....349

String Comparisons....349

Dates....350

Wildcard Matching....350

Calculations....350

SELECT Without FROM....350

Arithmetic....351

Formatting Functions....351

Date Functions....352

Concatenation....352

String Functions....352

Joining Tables....353

Aggregate Functions....353

Manipulating Data....353

Manipulating Tables....353

Appendix 2: A Crash Course in PDO....354

PDO Objects....354

The PDO Object....355

The PDOStatement Object....355

Working with PDO....356

Establishing a Connection....356

Other DBMSs....357

Prepared Statements and SQL Injection....357

Prepared Statements....358

Repeated Execution....359

Unprepared (Direct) SQL Statements....360

SELECT Statements....360

INSERT, UPDATE, and DELETE Statements....360

Selecting Data....361

Fetching Data....362

The Result Set....362

Fetching a Single Column....364

A Simple Login Script....364

Getting the Last Auto-Incremented Key....365

Error Reporting....366

Summary of PDO....367

Connection....367

Executing Simple Statements....367

INSERT, UPDATE, and DELETE....367

SELECT Statements....368

Executing Prepared Statements....368

Reading Data....368

Reading a Single Row....368

Reading Multiple Rows....369

Reading a Single Column....369

Appendix 3: Additional Notes....370

Cultural Notes....370

Addresses and Phone Numbers....370

Towns....370

States....371

Postcodes....371

Phone Numbers....371

Email Addresses....372

Measurements, Prices, and Currency....372

Dates....372

SQL Data Values....372

Stored Values....373

Variables....374

Literals....375

Calculated Values....375

Some Notes on Dates (and Times)....376

Oracle Date Format....377

Microsoft Age Function....377

Working with SQLite Dates....378

ISO 8601 Dates and Times....378

Date Functions....379

Manipulating Dates and Times....380

Formatting Dates and Times....380

Index....382

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, andcalculate 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