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