Job Ready SQL

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