Job Ready SQL

ISBN-13: 9781394181032 / Angielski / Miękka / 2023 / 500 str.

Kimberly A. Weiss; Haythem Balti

Spis treści

Acknowledgments vAbout the Authors viAbout the Technical Writer viiAbout the Technical Editor viiiIntroduction xixPart I: Introduction to Database Concepts 1Lesson 1: Exploring Relational Databases and SQL 3Saving Data 4What Is a Database? 5Database Uses 5Data vs. Information 6Structured vs. Unstructured 6Database vs. DBMS 7Relational Database Concepts 7ACID Compliance 9ACID Properties 10Atomicity 10Consistency 10Isolation 12Durability 12Databases and Log Files 12Entity Integrity 13Ensuring Uniqueness 13Finding Records 14Backup Strategies 15Summary 16Exercises 17Exercise 1.1: Customers and Orders 17Exercise 1.2: Libraries and the Books Within 17Exercise 1.3: Your Scenario 18Lesson 2: Applying Normalization 19What Is Normalization? 19Data Redundancy Is a Problem 20Storage Reduction 21Functional Dependencies 22Normalizing Data 22First Normal Form 23Top- to- Bottom or Left- to- Right Ordering 23Every Row Can Be Uniquely Identified 24Every Field Contains Only One Value 24Summary of First Normal Form 25Second Normal Form 26Normalize to 1NF 27Composite Keys 28Summary of Second Normal Form 31Third Normal Form 33Denormalization 35Summary 37Exercises 37Exercise 2.1: Employees 38Exercise 2.2: Libraries and the Books Within 38Exercise 2.3: Hotels 39Exercise 2.4: Students and Courses 39Exercise 2.5: On the Menu 40Lesson 3: Creating Entity- Relationship Diagrams 41Using ERDs 42Available Tools 43ERD Components 45Creating Tables 45Adding Fields 46Identifying Keys 47Including Additional Tables 47Showing Relationships 48ERD of Database 50What About Many- to- Many Relationships? 51Summary 52Exercises 53Exercise 3.1: Customers and Orders 53Exercise 3.2: The Relationship Between Libraries and Books 53Exercise 3.3: Many to Many No More 53Exercise 3.4: Diagramming the Menu 54Exercise 3.5: Database Design Assessment 54Lesson 4: Pulling It All Together: Normalizing a Vinyl RecordShop Database 57The Vinyl Record Shop Data Overview 58Step 1: Identify the Entities and Attributes 59Step 1 Results 60Step 2: First Normal Form 61Determining Primary Keys 62Resolving Multivalued Fields 63Normalizing the Song Entity 65Step 2 Results 67Step 3: Second Normal Form 69Step 3 Results 69Step 4: Third Normal Form 69Step 4 Results 70ERD in 3NF 71Step 5: Finalize the Structure 73Final Steps 73Summary 75Part II: Applying SQL 77Lesson 5: Working with MySQL Server 79MySQL Installation 80Step 1: Get the Download 80Step 2: Skipping the Login 80Step 3: Starting the Install 81Step 4: Tool Selection 82Step 5: Product Configuration 83Step 6: MySQL Router Configuration 87MySQL Notifier 90Command- Line Interface 91Getting Started with MySQL Workbench 93Use MySQL Workbench 96Run a Test Command 101Summary 102Exercises 103Exercise 5.1: Running the Tools 104Exercise 5.2: Listing the Cities 104Exercise 5.3: Small Cities 104Lesson 6: Diving into SQL 105Introduction to SQL 106SQL Syntax 106Semicolon 107Line Breaks and Indents 107Letter Case 108Commas 109Spaces 110Quotation Marks 110Spelling 111Working with Null Values 111Null vs. Zero 111Nullable Fields 112Consequences of Null Values 113Working with Indexes 116Primary vs. Secondary Storage 117Indexing Fields 117Default Indexes 118Unique and Nonunique Indexes 119Summary 119Exercises 120Exercise 6.1: Remember Your Lines 120Exercise 6.2: Contact Questions 120Exercise 6.3: Missing Contact 121Lesson 7: Database Management Using DDL 123Database Management 124Create a New Database 124List Existing Databases 125Use a Database 126Delete an Existing Database 127MySQL Data Types 127Data Types 128Numeric Data Types 128Integer Types 128Decimal Types 129String Types 130Date/Time 130Managing Tables in MySQL 131Create a Table 131List Tables 133View a Table 134Change a Table 135Dropping a Field 135Setting a Key Value 135Modifying a Field 136Adding a Field 137Altering Tables with Existing Data 137Delete a Table 137Summarizing the book Table Changes 138Managing Relationships in MySQL 139Define a Foreign Key 139Entity Integrity 141Referential Integrity 141Adding Data to a Foreign Key Field 141Updating Data in a Primary Record 142Deleting Data from a Primary Record 142Work- Arounds for Referential Integrity 142Remove the Foreign Key Constraints 142Using ON UPDATE 142Using ON DELETE 143Summary 143Exercises 144Exercise 7.1: Books Database 144Part 1: Define the Tables 146Part 2: Books Database SQL Scripts 146Part 3: Test the Script 147Exercise 7.2: DDL Activity: Movies Database 147Part 1: Define the Tables 148Part 2: Create the Script 149Part 3: Test the Script 149Lesson 8: Pulling It All Together: Building the Vinyl RecordShop Database 151Step 1: Examine the Structure 152Organize the Tables 154Create the Script File 155Step 2: Create the Database 155Step 3: Create the Primary Tables 157Column Order 158On Your Own 159Step 4: Create the Related Tables 160Create the song Table 160Create the songAlbum Table 162Create the bandArtist Table on Your Own 164Step 5: Finalize the Script 164Summary 167Part III: Data Management and Manipulation 169Lesson 9: Applying CRUD: Basic Data Management and Manipulation 171Data Manipulation Language 172Create a Database 172Create the Database 175Check That the Database Exists 176Insert Data 176Adding Without Columns Identified 177Adding Columns with Column Names 177The Better Option 178Inserting Multiple Rows 179Incrementing Auto- Increment Out of Order 180Inserting a Foreign Key 181Update Data 182Updating One Row 183Preview Before You Update 184Updating Multiple Rows 184Disabling SQL_SAFE_UPDATES 185Delete Data 187Summary 191Exercises 191Exercise 9.1: Setting Up a Book List 192Exercise 9.2: Updating Books 193Exercise 9.3: Removing a Book 193Lesson 10: Working with SELECT Queries 195Setting Up a Database 196Using the SELECT Keyword 199Using Single- Table SELECT 199Using SELECT * 201Using the WHERE Clause 202Filtering Numbers 205Filtering Dates 207Pattern Matching Text 207NULL: The "Billion- Dollar Mistake" 209Performing Calculations 211Summary 213Exercises 214Exercise 10.1: Complaints 214Exercise 10.2: Personal Trainer 215Instructions 216Activity 1 216Activity 2 216Activity 3 217Activity 4 217Activity 5 217Activity 6 217Activity 7 218Activity 8 218Activity 9 218Activity 10 218Activity 11 219Activity 12 219Activity 13 220Activity 14 220Activity 15 220Activity 16 220Activity 17 221Activity 18 221Activity 19 221Lesson 11: Adding JOIN Queries 223Starting with a Schema 224Get Data from Multiple Tables 226Use the JOIN Clause 228Inner Join 228Optional Syntax Elements 230Omitting Table Names 230Omitting the INNER Keyword 232Multiple JOINs 232INNER JOIN Limitations 235OUTER JOIN: LEFT, RIGHT, and FULL 236Replacing a NULL Value with Ifnull() 238Projects Without Workers 239Workers Without a Project 241Self- JOIN and Aliases 243Cross Join 246Summary 247Exercises 247Exercise 11.1: User Stories 248Exercise 11.2: Personal Trainer Activities 248Activity 1 (64 Rows) 248Activity 2 (9 Rows) 248Activity 3 (9 Rows) 250Activity 4 (35 Rows) 250Activity 5 (25 Rows) 250Activity 6 (78 Rows) 250Activity 7 (200 Rows) 250Activity 8 (0 or 1 Row) 250Activity 9 (12 Rows) 250Activity 10 (16 Rows) 251Activity 11 (50 Rows) 251Activity 12 (6 Rows, 4 Unique Rows) 251Activity 13 (26 Workouts, 3 Goals) 251Activity 14 (744 Rows) 251Lesson 12: Sorting and Limiting Query Results 253Using ORDER BY 254Sort by a Single Column 254Sort by Multiple Columns 256Changing the Order of the Columns 258Handling NULL 260Using LIMIT 261Using DISTINCT 263Summary 264Exercises 265Getting Started: World Database 265Generating an ERD for World 266Guidelines 267Exercise 12.1: What's in the World Database? 267Exercise 12.2: Small Cities (42 rows) 267Exercise 12.3: Cities by Region (4,079 rows) 267Exercise 12.4: Speaking French (22 rows) 267Exercise 12.5: No Independence (47 rows) 268Exercise 12.6: Country Languages (990 rows) 268Exercise 12.7: No Language (6 rows) 268Exercise 12.8: City Population (232 rows) 268Exercise 12.9: Average City Population (7 rows) 268Exercise 12.10: GNP 269Exercise 12.11: Capital Cities (4,079 rows) 269Exercise 12.12: Country Capital Cities (239 rows) 269Lesson 13: Grouping and Aggregates 271Aggregate Functions 272Using GROUP BY 273Grouping and Multiple Columns 275Adding DISTINCT 277Using HAVING 279SELECT Evaluation Order 281Other Examples 281Summary 283Exercises 284The Personal Trainer Database 284Exercise 13.1: Number of Clients (1 row) 286Exercise 13.2: Counting Client Birth Dates (1 row) 286Exercise 13.3: Clients by City (20 rows) 286Exercise 13.4: Invoice Totals (1,000 rows) 286Exercise 13.5: Invoices More Than $500 (234 rows) 287Exercise 13.6: Average Line Item Totals (3 rows) 287Exercise 13.7: More Than $1, 000Paid (146 rows) 287Exercise 13.8: Counts by Category (13 rows) 288Exercise 13.9: Exercises (64 rows) 288Exercise 13.10: Client Birth Dates (26 rows) 288Exercise 13.11: Client Goal Count (500 rows, 50 rows with no goal) 289Exercise 13.12: Exercise Unit Value (82 rows) 289Exercise 13.13: Categorized Exercise Unit Value (82 rows) 289Exercise 13.14: Level Ages (4 rows) 290Lesson 14: Pulling It All Together: Adding Data to the Vinyl Record Shop Database 291Organize the Tables 292Create a Script File 293Inserting Data 294What Is a Flat File? 294Sql Insert 295Inserting by Table Order 296Adding by Field Name 297On Your Own 298Update Records 299Import CSV Data 300Set Up MySQL 301Prepare the CSV File 303Import the File 308Command- Line Import 308MySQL Workbench 310Add Data to the Script 317Test the Script 319Wrap Up the Vinyl Music Shop Script 319Summary 319Lesson 15: Diving into Advanced SQL Topics 321Adding Subqueries 322Subqueries in the IN Operator 322Subqueries for Tables 323Subqueries for Values 325Working with Views 326Understanding Transactions 327Transaction Example 328Acid 329Schema Optimization 331Choosing Optimal Data Types 331Indexing 333B- Tree Indexes 334Hash Indexes 335Summary 336Exercises 337Exercise 15.1: Recent Tasks 337Exercise 15.2: Before Grumps 338Exercise 15.3: Project Due Dates 338Exercise 15.4: The Work of Ealasaid Blinco 338Exercise 15.5: Other Databases 339Appendix A: Bonus Lesson on Applying SQL with Python 341Appendix B: SQL Quick Reference 367Index 375

O autorze

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.