1 Introduction

15

1.1 Relational Databases

17

1.2 What Is SQL?

20

1.3 Target Audience

23

1.4 What You’ll Learn from This Book

24

1.5 Tips for Reading This Book

26

2 Basic Elements of Relational Databases

29

2.1 Fundamentals

29

2.1.1 Databases

30

2.1.2 Database Products

30

2.1.3 Schemas

34

2.2 Structures

38

2.2.1 Tables

38

2.2.2 Data Types

43

2.2.3 Views

46

2.3 Keys and Indexing

49

2.3.1 Primary Keys

49

2.3.2 Foreign Keys

52

2.3.3 Indexes

55

2.4 Normalization

59

2.4.1 First Normal Form

59

2.4.2 Second Normal Form

60

2.4.3 Third Normal Form

61

2.4.4 Results

61

2.5 Operations

62

2.5.1 Stored Procedures

63

2.5.2 Triggers

65

2.6 Authorization

68

2.7 Summary

70

3 Setting Up the Environment

71

3.1 Our Database of Choice

73

3.2 Local Database Setup

75

3.2.1 Windows Installation

75

3.2.2 macOS Installation

81

3.3 SQL IDE Setup

83

3.3.1 pgAdmin

84

3.3.2 Visual Studio Code

92

3.4 Summary

97

4 Data Definition Language

99

4.1 Database Manipulation

100

4.1.1 Database Creation

100

4.1.2 Database Modification

101

4.1.3 Database Deletion

106

4.2 Schema Manipulation

107

4.2.1 Schema Creation

107

4.2.2 Schema Modification

108

4.2.3 Schema Deletion

112

4.3 Table Manipulation

115

4.3.1 Table Creation

115

4.3.2 Table Modification

134

4.3.3 Table Deletion

145

4.4 View Manipulation

147

4.4.1 View Creation

149

4.4.2 View Modification

151

4.4.3 View Deletion

152

4.5 Common Pitfalls of Data Definition Language

153

4.5.1 Renaming Objects

153

4.5.2 Deleting Objects

153

4.6 Summary

154

5 Data Manipulation Language

155

5.1 Building a Data Manipulation Language Playground

156

5.2 Data Manipulation Language Operations

162

5.2.1 Insert

162

5.2.2 Update

174

5.2.3 Delete

185

5.2.4 Common Pitfalls of Data Manipulation Language Operations

190

5.3 Data Manipulation Language Automation

191

5.3.1 Stored Procedures

192

5.3.2 Triggers

199

5.4 Summary

220

6 Transaction Control Language

221

6.1 Building a Transaction Control Language Playground

222

6.2 Why Is Transaction Control Language Necessary?

228

6.3 COMMIT and ROLLBACK

230

6.4 Save Points

235

6.5 Locks and Concurrency

241

6.5.1 Locks

242

6.5.2 Transaction Isolation

248

6.6 Common Pitfalls of Transaction Control Language

251

6.7 Summary

251

7 Data Query Language

253

7.1 Building a Data Query Language Playground

254

7.1.1 Master Data Tables

255

7.1.2 Order Tables

258

7.1.3 Delivery Tables

260

7.1.4 Invoice Tables

264

7.1.5 Complaint Tables

269

7.1.6 Mock Data

270

7.2 Single Table Queries

273

7.2.1 Selecting All Columns

274

7.2.2 Selecting Some Columns

276

7.2.3 Filtering Rows with WHERE Conditions

278

7.2.4 Ordering Results

293

7.2.5 Partial Selection

297

7.2.6 Unique Selection

299

7.2.7 Null Values

301

7.2.8 Aggregate Functions

303

7.3 Multitable Queries

312

7.3.1 Refresher on Relationships between Tables

312

7.3.2 INNER JOIN

315

7.3.3 Using Aliases

322

7.3.4 OUTER JOIN

325

7.3.5 Self-Join

334

7.3.6 Subqueries

337

7.3.7 Set Operations

349

7.4 String Functions

358

7.4.1 Concatenation

358

7.4.2 LEFT and RIGHT

361

7.4.3 Length

363

7.4.4 LOWER and UPPER

365

7.4.5 LTRIM, RTRIM, and TRIM

366

7.4.6 REVERSE

368

7.4.7 SUBSTRING

368

7.4.8 Regular Expressions

369

7.5 Math and Numeric Functions

373

7.5.1 Math Operators

373

7.5.2 ABS

376

7.5.3 RANDOM

379

7.5.4 ROUND, FLOOR, and CEILING

380

7.5.5 SIGN

383

7.6 Temporal Functions

383

7.6.1 Interval Calculations

384

7.6.2 Current Date and Time

385

7.6.3 Date Differences

385

7.6.4 Extracting Date Parts

387

7.6.5 Time Zones

388

7.7 Window Functions

389

7.7.1 RANK

390

7.7.2 DENSE_RANK

393

7.7.3 ROW NUMBER

395

7.7.4 LEAD

401

7.7.5 LAG

404

7.7.6 NTILE

405

7.7.7 FIRST_VALUE

407

7.8 Miscellaneous Functions

410

7.8.1 CAST

410

7.8.2 CASE

411

7.8.3 COALESCE

414

7.8.4 Common Table Expressions

415

7.9 Summary

418

8 Data Control Language

421

8.1 Building a Data Control Language Playground

422

8.2 User and Role Manipulation

424

8.2.1 Users

424

8.2.2 Roles

426

8.2.3 User Manipulation

429

8.2.4 Role Manipulation

430

8.3 Granting Access

431

8.3.1 Granting Access Directly

432

8.3.2 Granting Access Through Roles

436

8.3.3 Granting Access through Role Hierarchies

439

8.4 Revoking Access

440

8.5 Reporting Privileges

441

8.6 Summary

446

1 Entity-Relationship Diagrams

451

A.1 Entity-Relationship Diagrams and Their Significance

452

A.2 Entities

453

A.3 Relationships Among Entities

457

A.3.1 Zero to One

457

A.3.2 One and Only One

459

A.3.3 Zero to Many

460

A.3.4 One to Many

463

A.4 Entity-Relationship Diagrams Exercises

465

A.4.1 Retail Store

465

A.4.2 Social Media Platform

466

A.4.3 Hospital

468

A.5 Summary

470

2 Tips and Tricks

471

B.1 SQL Injection

471

B.2 Application-Level Authorization

473

B.3 Sensitive Data

476

B.3.1 Encryption

476

B.3.2 Anonymization

477

B.3.3 No Storage

477

B.3.4 Database Features

477

B.4 Logical Deletion

477

B.5 Indexes versus Summary Tables

478

B.6 Legal Concerns

479

B.7 Primary Key Determination

480

B.7.1 External Values

480

B.7.2 Manual Values

480

B.7.3 Autoincrementing Values

481

B.7.4 Globally Unique Identifiers

481

B.8 Logging

482

B.9 Backup and Recovery

482