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.

Auteur

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.

Contenu

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 D…