Job Ready SQL

Cover
Title Page
Copyright Page
Acknowledgments
About the Authors
About the Technical Writer
About the Technical Editor
Contents at a Glance
Contents
Introduction
What Does This Book Cover?
Reader Support for This Book
Companion Download Files
How to Contact the Publisher
Part 1 Introduction to Database Concepts
Lesson 1 Exploring Relational Databases and SQL
Saving Data
What Is a Database?
Database Uses
Data vs. Information
Structured vs. Unstructured
Database vs. DBMS
Relational Database Concepts
ACID Compliance
ACID Properties
Atomicity
Consistency
Isolation
Durability
Databases and Log Files
Entity Integrity
Ensuring Uniqueness
Finding Records
Backup Strategies
Summary
Exercises
Exercise 1.1: Customers and Orders
Exercise 1.2: Libraries and the Books Within
Exercise 1.3: Your Scenario
Lesson 2 Applying Normalization
What Is Normalization?
Data Redundancy Is a Problem
Storage Reduction
Functional Dependencies
Normalizing Data
First Normal Form
Top-to-Bottom or Left-to-Right Ordering
Every Row Can Be Uniquely Identified
Every Field Contains Only One Value
Summary of First Normal Form
Second Normal Form
Normalize to 1NF
Composite Keys
Summary of Second Normal Form
Third Normal Form
Denormalization
Summary
Exercises
Exercise 2.1: Employees
Exercise 2.2: Libraries and the Books Within
Exercise 2.3: Hotels
Exercise 2.4: Students and Courses
Exercise 2.5: On the Menu
Lesson 3 Creating Entity-Relationship Diagrams
Using ERDs
Available Tools
ERD Components
Creating Tables
Adding Fields
Identifying Keys
Including Additional Tables
Showing Relationships
ERD of Database
What About Many-to-Many Relationships?
Summary
Exercises
Exercise 3.1: Customers and Orders
Exercise 3.2: The Relationship Between Libraries and Books
Exercise 3.3: Many to Many No More
Exercise 3.4: Diagramming the Menu
Exercise 3.5: Database Design Assessment
Lesson 4 Pulling It All Together: Normalizing a Vinyl Record Shop Database
The Vinyl Record Shop Data Overview
Step 1: Identify the Entities and Attributes
Step 1 Results
Step 2: First Normal Form
Determining Primary Keys
Resolving Multivalued Fields
Normalizing the Song Entity
Step 2 Results
Step 3: Second Normal Form
Step 3 Results
Step 4: Third Normal Form
Step 4 Results
ERD in 3NF
Step 5: Finalize the Structure
Final Steps
Summary
Part 2 Applying SQL
Lesson 5 Working with MySQL Server
MySQL Installation
Step 1: Get the Download
Step 2: Skipping the Login
Step 3: Starting the Install
Step 4: Tool Selection
Step 5: Product Configuration
Step 6: MySQL Router Configuration
MySQL Notifier
Command-Line Interface
Getting Started with MySQL Workbench
Use MySQL Workbench
Run a Test Command
Summary
Exercises
Exercise 5.1: Running the Tools
Exercise 5.2: Listing the Cities
Exercise 5.3: Small Cities
Lesson 6 Diving into SQL
Introduction to SQL
SQL Syntax
Semicolon
Line Breaks and Indents
Letter Case
Commas
Spaces
Quotation Marks
Spelling
Working with Null Values
Null vs. Zero
Nullable Fields
Consequences of Null Values
Working with Indexes
Primary vs. Secondary Storage
Indexing Fields
Default Indexes
Unique and Nonunique Indexes
Summary
Exercises
Exercise 6.1: Remember Your Lines
Exercise 6.2: Contact Questions
Exercise 6.3: Missing Contact
Lesson 7 Database Management Using DDL
Database Management
Create a New Database
List Existing Databases
Use a Database
Delete an Existing Database
MySQL Data Types
Data Types
Numeric Data Types
Integer Types
Decimal Types
String Types
Date/Time
Managing Tables in MySQL
Create a Table
List Tables
View a Table
Change a Table
Dropping a Field
Setting a Key Value
Modifying a Field
Adding a Field
Altering Tables with Existing Data
Delete a Table
Summarizing the book Table Changes
Managing Relationships in MySQL
Define a Foreign Key
Entity Integrity
Referential Integrity
Adding Data to a Foreign Key Field
Updating Data in a Primary Record
Deleting Data from a Primary Record
Work-Arounds for Referential Integrity
Remove the Foreign Key Constraints
Using ON UPDATE
Using ON DELETE
Summary
Exercises
Exercise 7.1: Books Database
Part 1: Define the Tables
Part 2: Books Database SQL Scripts
Part 3: Test the Script
Exercise 7.2: DDL Activity: Movies Database
Part 1: Define the Tables
Part 2: Create the Script
Part 3: Test the Script
Lesson 8 Pulling It All Together: Building the Vinyl Record Shop Database
Step 1: Examine the Structure
Organize the Tables
Create the Script File
Step 2: Create the Database
Step 3: Create the Primary Tables
Column Order
On Your Own
Step 4: Create the Related Tables
Create the song Table
Create the songAlbum Table
Create the bandArtist Table on Your Own
Step 5: Finalize the Script
Summary
Part 3 Data Management and Manipulation
Lesson 9 Applying CRUD: Basic Data Management and Manipulation
Data Manipulation Language
Create a Database
Create the Database
Check That the Database Exists
Insert Data
Adding Without Columns Identified
Adding Columns with Column Names
The Better Option
Inserting Multiple Rows
Incrementing Auto-Increment Out of Order
Inserting a Foreign Key
Update Data
Updating One Row
Preview Before You Update
Updating Multiple Rows
Disabling SQL_SAFE_UPDATES
Delete Data
Summary
Exercises
Exercise 9.1: Setting Up a Book List
Exercise 9.2: Updating Books
Exercise 9.3: Removing a Book
Lesson 10 Working with SELECT Queries
Setting Up a Database
Using the SELECT Keyword
Using Single-Table SELECT
Using SELECT *
Using the WHERE Clause
Filtering Numbers
Filtering Dates
Pattern Matching Text
NULL: The “Billion-Dollar Mistake”
Performing Calculations
Summary
Exercises
Exercise 10.1: Complaints
Exercise 10.2: Personal Trainer
Instructions
Activity 1
Activity 2
Activity 3
Activity 4
Activity 5
Activity 6
Activity 7
Activity 8
Activity 9
Activity 10
Activity 11
Activity 12
Activity 13
Activity 14
Activity 15
Activity 16
Activity 17
Activity 18
Activity 19
Lesson 11 Adding JOIN Queries
Starting with a Schema
Get Data from Multiple Tables
Use the JOIN Clause
INNER JOIN
Optional Syntax Elements
Omitting Table Names
Omitting the INNER Keyword
Multiple JOINs
INNER JOIN Limitations
OUTER JOIN: LEFT, RIGHT, and FULL
Replacing a NULL Value with IFNULL()
Projects Without Workers
Workers Without a Project
Self-JOIN and Aliases
CROSS JOIN
Summary
Exercises
Exercise 11.1: User Stories
Exercise 11.2: Personal Trainer Activities
Activity 1 (64 Rows)
Activity 2 (9 Rows)
Activity 3 (9 Rows)
Activity 4 (35 Rows)
Activity 5 (25 Rows)
Activity 6 (78 Rows)
Activity 7 (200 Rows)
Activity 8 (0 or 1 Row)
Activity 9 (12 Rows)
Activity 10 (16 Rows)
Activity 11 (50 Rows)
Activity 12 (6 Rows, 4 Unique Rows)
Activity 13 (26 Workouts, 3 Goals)
Activity 14 (744 Rows)
Lesson 12 Sorting and Limiting Query Results
Using ORDER BY
Sort by a Single Column
Sort by Multiple Columns
Changing the Order of the Columns
Handling NULL
Using LIMIT
Using DISTINCT
Summary
Exercises
Getting Started: World Database
Generating an ERD for World
Guidelines
Exercise 12.1: What’s in the World Database?
Exercise 12.2: Small Cities (42 rows)
Exercise 12.3: Cities by Region (4,079 rows)
Exercise 12.4: Speaking French (22 rows)
Exercise 12.5: No Independence (47 rows)
Exercise 12.6: Country Languages (990 rows)
Exercise 12.7: No Language (6 rows)
Exercise 12.8: City Population (232 rows)
Exercise 12.9: Average City Population (7 rows)
Exercise 12.10: GNP
Exercise 12.11: Capital Cities (4,079 rows)
Exercise 12.12: Country Capital Cities (239 rows)
Lesson 13 Grouping and Aggregates
Aggregate Functions
Using GROUP BY
Grouping and Multiple Columns
Adding DISTINCT
Using HAVING
SELECT Evaluation Order
Other Examples
Summary
Exercises
The Personal Trainer Database
Exercise 13.1: Number of Clients (1 row)
Exercise 13.2: Counting Client Birth Dates (1 row)
Exercise 13.3: Clients by City (20 rows)
Exercise 13.4: Invoice Totals (1,000 rows)
Exercise 13.5: Invoices More Than $500 (234 rows)
Exercise 13.6: Average Line Item Totals (3 rows)
Exercise 13.7: More Than $1,000 Paid (146 rows)
Exercise 13.8: Counts by Category (13 rows)
Exercise 13.9: Exercises (64 rows)
Exercise 13.10: Client Birth Dates (26 rows)
Exercise 13.11: Client Goal Count (500 rows, 50 rows with no goal)
Exercise 13.12: Exercise Unit Value (82 rows)
Exercise 13.13: Categorized Exercise Unit Value (82 rows)
Exercise 13.14: Level Ages (4 rows)
Lesson 14 Pulling It All Together: Adding Data to the Vinyl Record Shop Database
Organize the Tables
Create a Script File
Inserting Data
What Is a Flat File?
SQL INSERT
Inserting by Table Order
Adding by Field Name
On Your Own
Update Records
Import CSV Data
Set Up MySQL
Prepare the CSV File
Import the File
Command-Line Import
MySQL Workbench
Add Data to the Script
Test the Script
Wrap Up the Vinyl Music Shop Script
Summary
Lesson 15 Diving into Advanced SQL Topics
Adding Subqueries
Subqueries in the IN Operator
Subqueries for Tables
Subqueries for Values
Working with Views
Understanding Transactions
Transaction Example
ACID
Schema Optimization
Choosing Optimal Data Types
Indexing
B-TreeIndexes
Hash Indexes
Summary
Exercises
Exercise 15.1: Recent Tasks
Exercise 15.2: Before Grumps
Exercise 15.3: Project Due Dates
Exercise 15.4: The Work of Ealasaid Blinco
Exercise 15.5: Other Databases
Appendix A Bonus Lesson on Applying SQL with Python
Database Operations
Using PyMySQL
Getting Your Version of MySQL
Create a Database
Drop a Database
Connect to a Database
Display All Databases
Table Operations
Create a Table
Alter a Table
Remove a Table
Rebuild the Table
Data Operations: CRUD
Create Data
Retrieve Data
Update Data
Delete Data
Summary
Exercises
Exercise A.1: Creating an Employee Database
Exercise A.2: Removing a Database
Exercise A.3: Creating a Table of Employees
Exercise A.4: Adding Employees
Exercise A.5: Retrieving Employees
Exercise A.6: Updating Employees
Exercise A.7: Deleting Employees
Exercise A.8: Nobel Laureates
Exercise A.9: Restaurants
Appendix B SQL Quick Reference
Working with Databases
Defining Tables, Columns, and Rows
Performing Table Queries
Filtering and Grouping Selection Results
Performing Queries Using Multiple Tables
Basic SQL Data Types
Index
EULA