Job Ready SQL

Learn the most important SQL skills and apply them in your job—quickly and efficiently!

SQL (Structured Query Language) is the modern language that almost every relational database system supports for adding data, retrieving data, and modifying data in a database. Although basic visual tools are available to help end-users input common commands, data scientists, business intelligence analysts, Cloud engineers, Machine Learning programmers, and other professionals routinely need to query a database using SQL.

Job Ready SQL provides you with the foundational skills necessary to work with data of any kind. Offering a straightforward ‘learn-by-doing’ approach, this concise and highly practical guide teaches you all the basics of SQL so you can apply your knowledge in real-world environments immediately. Throughout the book, each lesson includes clear explanations of key concepts and hands-on exercises that mirror real-world SQL tasks.

• Teaches the basics of SQL database creation and management using easy-to-understand language
• Helps readers develop an understanding of fundamental concepts and more advanced applications such as data engineering and data science
• Discusses the key types of SQL commands, including Data Definition Language (DDL) commands and Data Manipulation Language (DML) commands
• Includes useful reference information on querying SQL-based databases

Job Ready SQL is a must-have resource for students and working professionals looking to quickly get up to speed with SQL and take their relational database skills to the next level.

KIMBERLY A. WEISS is Senior Manager of Curriculum Operations for Wiley Edge. She has extensive experience developing interactive instructional content for a wide variety of learners.

HAYTHEM BALTI, PhD, is Associate Dean at Wiley Edge (formerly mthree), a software development and data science education platform.

Acknowledgments v

About the Authors vi

About the Technical Writer vii

About the Technical Editor viii

Introduction xix

Part I: Introduction to Database Concepts 1

Lesson 1: Exploring Relational Databases and SQL 3

Saving Data 4

What Is a Database? 5

Database Uses 5

Data vs. Information 6

Structured vs. Unstructured 6

Database vs. DBMS 7

Relational Database Concepts 7

ACID Compliance 9

ACID Properties 10

Atomicity 10

Consistency 10

Isolation 12

Durability 12

Databases and Log Files 12

Entity Integrity 13

Ensuring Uniqueness 13

Finding Records 14

Backup Strategies 15

Summary 16

Exercises 17

Exercise 1.1: Customers and Orders 17

Exercise 1.2: Libraries and the Books Within 17

Exercise 1.3: Your Scenario 18

Lesson 2: Applying Normalization 19

What Is Normalization? 19

Data Redundancy Is a Problem 20

Storage Reduction 21

Functional Dependencies 22

Normalizing Data 22

First Normal Form 23

Top- to- Bottom or Left- to- Right Ordering 23

Every Row Can Be Uniquely Identified 24

Every Field Contains Only One Value 24

Summary of First Normal Form 25

Second Normal Form 26

Normalize to 1NF 27

Composite Keys 28

Summary of Second Normal Form 31

Third Normal Form 33

Denormalization 35

Summary 37

Exercises 37

Exercise 2.1: Employees 38

Exercise 2.2: Libraries and the Books Within 38

Exercise 2.3: Hotels 39

Exercise 2.4: Students and Courses 39

Exercise 2.5: On the Menu 40

Lesson 3: Creating Entity- Relationship Diagrams 41

Using ERDs 42

Available Tools 43

ERD Components 45

Creating Tables 45

Adding Fields 46

Identifying Keys 47

Including Additional Tables 47

Showing Relationships 48

ERD of Database 50

What About Many- to- Many Relationships? 51

Summary 52

Exercises 53

Exercise 3.1: Customers and Orders 53

Exercise 3.2: The Relationship Between Libraries and Books 53

Exercise 3.3: Many to Many No More 53

Exercise 3.4: Diagramming the Menu 54

Exercise 3.5: Database Design Assessment 54

Lesson 4: Pulling It All Together: Normalizing a Vinyl Record

Shop Database 57

The Vinyl Record Shop Data Overview 58

Step 1: Identify the Entities and Attributes 59

Step 1 Results 60

Step 2: First Normal Form 61

Determining Primary Keys 62

Resolving Multivalued Fields 63

Normalizing the Song Entity 65

Step 2 Results 67

Step 3: Second Normal Form 69

Step 3 Results 69

Step 4: Third Normal Form 69

Step 4 Results 70

ERD in 3NF 71

Step 5: Finalize the Structure 73

Final Steps 73

Summary 75

Part II: Applying SQL 77

Lesson 5: Working with MySQL Server 79

MySQL Installation 80

Step 1: Get the Download 80

Step 2: Skipping the Login 80

Step 3: Starting the Install 81

Step 4: Tool Selection 82

Step 5: Product Configuration 83

Step 6: MySQL Router Configuration 87

MySQL Notifier 90

Command- Line Interface 91

Getting Started with MySQL Workbench 93

Use MySQL Workbench 96

Run a Test Command 101

Summary 102

Exercises 103

Exercise 5.1: Running the Tools 104

Exercise 5.2: Listing the Cities 104

Exercise 5.3: Small Cities 104

Lesson 6: Diving into SQL 105

Introduction to SQL 106

SQL Syntax 106

Semicolon 107

Line Breaks and Indents 107

Letter Case 108

Commas 109

Spaces 110

Quotation Marks 110

Spelling 111

Working with Null Values 111

Null vs. Zero 111

Nullable Fields 112

Consequences of Null Values 113

Working with Indexes 116

Primary vs. Secondary Storage 117

Indexing Fields 117

Default Indexes 118

Unique and Nonunique Indexes 119

Summary 119

Exercises 120

Exercise 6.1: Remember Your Lines 120

Exercise 6.2: Contact Questions 120

Exercise 6.3: Missing Contact 121

Lesson 7: Database Management Using DDL 123

Database Management 124

Create a New Database 124

List Existing Databases 125

Use a Database 126

Delete an Existing Database 127

MySQL Data Types 127

Data Types 128

Numeric Data Types 128

Integer Types 128

Decimal Types 129

String Types 130

Date/Time 130

Managing Tables in MySQL 131

Create a Table 131

List Tables 133

View a Table 134

Change a Table 135

Dropping a Field 135

Setting a Key Value 135

Modifying a Field 136

Adding a Field 137

Altering Tables with Existing Data 137

Delete a Table 137

Summarizing the book Table Changes 138

Managing Relationships in MySQL 139

Define a Foreign Key 139

Entity Integrity 141

Referential Integrity 141

Adding Data to a Foreign Key Field 141

Updating Data in a Primary Record 142

Deleting Data from a Primary Record 142

Work- Arounds for Referential Integrity 142

Remove the Foreign Key Constraints 142

Using ON UPDATE 142

Using ON DELETE 143

Summary 143

Exercises 144

Exercise 7.1: Books Database 144

Part 1: Define the Tables 146

Part 2: Books Database SQL Scripts 146

Part 3: Test the Script 147

Exercise 7.2: DDL Activity: Movies Database 147

Part 1: Define the Tables 148

Part 2: Create the Script 149

Part 3: Test the Script 149

Lesson 8: Pulling It All Together: Building the Vinyl Record

Shop Database 151

Step 1: Examine the Structure 152

Organize the Tables 154

Create the Script File 155

Step 2: Create the Database 155

Step 3: Create the Primary Tables 157

Column Order 158

On Your Own 159

Step 4: Create the Related Tables 160

Create the song Table 160

Create the songAlbum Table 162

Create the bandArtist Table on Your Own 164

Step 5: Finalize the Script 164

Summary 167

Part III: Data Management and Manipulation 169

Lesson 9: Applying CRUD: Basic Data Management and Manipulation 171

Data Manipulation Language 172

Create a Database 172

Create the Database 175

Check That the Database Exists 176

Insert Data 176

Adding Without Columns Identified 177

Adding Columns with Column Names 177

The Better Option 178

Inserting Multiple Rows 179

Incrementing Auto- Increment Out of Order 180

Inserting a Foreign Key 181

Update Data 182

Updating One Row 183

Preview Before You Update 184

Updating Multiple Rows 184

Disabling SQL_SAFE_UPDATES 185

Delete Data 187

Summary 191

Exercises 191

Exercise 9.1: Setting Up a Book List 192

Exercise 9.2: Updating Books 193

Exercise 9.3: Removing a Book 193

Lesson 10: Working with SELECT Queries 195

Setting Up a Database 196

Using the SELECT Keyword 199

Using Single- Table SELECT 199

Using SELECT * 201

Using the WHERE Clause 202

Filtering Numbers 205

Filtering Dates 207

Pattern Matching Text 207

NULL: The “Billion- Dollar Mistake” 209

Performing Calculations 211

Summary 213

Exercises 214

Exercise 10.1: Complaints 214

Exercise 10.2: Personal Trainer 215

Instructions 216

Activity 1 216

Activity 2 216

Activity 3 217

Activity 4 217

Activity 5 217

Activity 6 217

Activity 7 218

Activity 8 218

Activity 9 218

Activity 10 218

Activity 11 219

Activity 12 219

Activity 13 220

Activity 14 220

Activity 15 220

Activity 16 220

Activity 17 221

Activity 18 221

Activity 19 221

Lesson 11: Adding JOIN Queries 223

Starting with a Schema 224

Get Data from Multiple Tables 226

Use the JOIN Clause 228

Inner Join 228

Optional Syntax Elements 230

Omitting Table Names 230

Omitting the INNER Keyword 232

Multiple JOINs 232

INNER JOIN Limitations 235

OUTER JOIN: LEFT, RIGHT, and FULL 236

Replacing a NULL Value with Ifnull() 238

Projects Without Workers 239

Workers Without a Project 241

Self- JOIN and Aliases 243

Cross Join 246

Summary 247

Exercises 247

Exercise 11.1: User Stories 248

Exercise 11.2: Personal Trainer Activities 248

Activity 1 (64 Rows) 248

Activity 2 (9 Rows) 248

Activity 3 (9 Rows) 250

Activity 4 (35 Rows) 250

Activity 5 (25 Rows) 250

Activity 6 (78 Rows) 250

Activity 7 (200 Rows) 250

Activity 8 (0 or 1 Row) 250

Activity 9 (12 Rows) 250

Activity 10 (16 Rows) 251

Activity 11 (50 Rows) 251

Activity 12 (6 Rows, 4 Unique Rows) 251

Activity 13 (26 Workouts, 3 Goals) 251

Activity 14 (744 Rows) 251

Lesson 12: Sorting and Limiting Query Results 253

Using ORDER BY 254

Sort by a Single Column 254

Sort by Multiple Columns 256

Changing the Order of the Columns 258

Handling NULL 260

Using LIMIT 261

Using DISTINCT 263

Summary 264

Exercises 265

Getting Started: World Database 265

Generating an ERD for World 266

Guidelines 267

Exercise 12.1: What’s in the World Database? 267

Exercise 12.2: Small Cities (42 rows) 267

Exercise 12.3: Cities by Region (4,079 rows) 267

Exercise 12.4: Speaking French (22 rows) 267

Exercise 12.5: No Independence (47 rows) 268

Exercise 12.6: Country Languages (990 rows) 268

Exercise 12.7: No Language (6 rows) 268

Exercise 12.8: City Population (232 rows) 268

Exercise 12.9: Average City Population (7 rows) 268

Exercise 12.10: GNP 269

Exercise 12.11: Capital Cities (4,079 rows) 269

Exercise 12.12: Country Capital Cities (239 rows) 269

Lesson 13: Grouping and Aggregates 271

Aggregate Functions 272

Using GROUP BY 273

Grouping and Multiple Columns 275

Adding DISTINCT 277

Using HAVING 279

SELECT Evaluation Order 281

Other Examples 281

Summary 283

Exercises 284

The Personal Trainer Database 284

Exercise 13.1: Number of Clients (1 row) 286

Exercise 13.2: Counting Client Birth Dates (1 row) 286

Exercise 13.3: Clients by City (20 rows) 286

Exercise 13.4: Invoice Totals (1,000 rows) 286

Exercise 13.5: Invoices More Than $500 (234 rows) 287

Exercise 13.6: Average Line Item Totals (3 rows) 287

Exercise 13.7: More Than $1, 000

Paid (146 rows) 287

Exercise 13.8: Counts by Category (13 rows) 288

Exercise 13.9: Exercises (64 rows) 288

Exercise 13.10: Client Birth Dates (26 rows) 288

Exercise 13.11: Client Goal Count (500 rows, 50 rows with no goal) 289

Exercise 13.12: Exercise Unit Value (82 rows) 289

Exercise 13.13: Categorized Exercise Unit Value (82 rows) 289

Exercise 13.14: Level Ages (4 rows) 290

Lesson 14: Pulling It All Together: Adding Data to the Vinyl Record Shop Database 291

Organize the Tables 292

Create a Script File 293

Inserting Data 294

What Is a Flat File? 294

Sql Insert 295

Inserting by Table Order 296

Adding by Field Name 297

On Your Own 298

Update Records 299

Import CSV Data 300

Set Up MySQL 301

Prepare the CSV File 303

Import the File 308

Command- Line Import 308

MySQL Workbench 310

Add Data to the Script 317

Test the Script 319

Wrap Up the Vinyl Music Shop Script 319

Summary 319

Lesson 15: Diving into Advanced SQL Topics 321

Adding Subqueries 322

Subqueries in the IN Operator 322

Subqueries for Tables 323

Subqueries for Values 325

Working with Views 326

Understanding Transactions 327

Transaction Example 328

Acid 329

Schema Optimization 331

Choosing Optimal Data Types 331

Indexing 333

B- Tree Indexes 334

Hash Indexes 335

Summary 336

Exercises 337

Exercise 15.1: Recent Tasks 337

Exercise 15.2: Before Grumps 338

Exercise 15.3: Project Due Dates 338

Exercise 15.4: The Work of Ealasaid Blinco 338

Exercise 15.5: Other Databases 339

Appendix A: Bonus Lesson on Applying SQL with Python 341

Appendix B: SQL Quick Reference 367

Index 375

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.