دانلود کتاب Learning SQL: Master SQL Fundamentals

فهرست مطالب :

Cover
Copyright
Table of Contents
Preface
	Why Learn SQL?
	Why Use This Book to Do It?
	Structure of This Book
	Conventions Used in This Book
	Using the Examples in This Book
	O’Reilly Online Learning
	How to Contact Us
	Acknowledgments
Chapter 1. A Little Background
	Introduction to Databases
		Nonrelational Database Systems
		The Relational Model
		Some Terminology
	What Is SQL?
		SQL Statement Classes
		SQL: A Nonprocedural Language
		SQL Examples
	What Is MySQL?
	SQL Unplugged
	What’s in Store
Chapter 2. Creating and Populating a Database
	Creating a MySQL Database
	Using the mysql Command-Line Tool
	MySQL Data Types
		Character Data
		Numeric Data
		Temporal Data
	Table Creation
		Step 1: Design
		Step 2: Refinement
		Step 3: Building SQL Schema Statements
	Populating and Modifying Tables
		Inserting Data
		Updating Data
		Deleting Data
	When Good Statements Go Bad
		Nonunique Primary Key
		Nonexistent Foreign Key
		Column Value Violations
		Invalid Date Conversions
	The Sakila Database
Chapter 3. Query Primer
	Query Mechanics
	Query Clauses
	The select Clause
		Column Aliases
		Removing Duplicates
	The from Clause
		Tables
		Table Links
		Defining Table Aliases
	The where Clause
	The group by and having Clauses
	The order by Clause
		Ascending Versus Descending Sort Order
		Sorting via Numeric Placeholders
	Test Your Knowledge
		Exercise 3-1
		Exercise 3-2
		Exercise 3-3
		Exercise 3-4
Chapter 4. Filtering
	Condition Evaluation
		Using Parentheses
		Using the not Operator
	Building a Condition
	Condition Types
		Equality Conditions
		Range Conditions
		Membership Conditions
		Matching Conditions
	Null: That Four-Letter Word
	Test Your Knowledge
		Exercise 4-1
		Exercise 4-2
		Exercise 4-3
		Exercise 4-4
Chapter 5. Querying Multiple Tables
	What Is a Join?
		Cartesian Product
		Inner Joins
		The ANSI Join Syntax
	Joining Three or More Tables
		Using Subqueries as Tables
		Using the Same Table Twice
	Self-Joins
	Test Your Knowledge
		Exercise 5-1
		Exercise 5-2
		Exercise 5-3
Chapter 6. Working with Sets
	Set Theory Primer
	Set Theory in Practice
	Set Operators
		The union Operator
		The intersect Operator
		The except Operator
	Set Operation Rules
		Sorting Compound Query Results
		Set Operation Precedence
	Test Your Knowledge
		Exercise 6-1
		Exercise 6-2
		Exercise 6-3
Chapter 7. Data Generation, Manipulation, and Conversion
	Working with String Data
		String Generation
		String Manipulation
	Working with Numeric Data
		Performing Arithmetic Functions
		Controlling Number Precision
		Handling Signed Data
	Working with Temporal Data
		Dealing with Time Zones
		Generating Temporal Data
		Manipulating Temporal Data
	Conversion Functions
	Test Your Knowledge
		Exercise 7-1
		Exercise 7-2
		Exercise 7-3
Chapter 8. Grouping and Aggregates
	Grouping Concepts
	Aggregate Functions
		Implicit Versus Explicit Groups
		Counting Distinct Values
		Using Expressions
		How Nulls Are Handled
	Generating Groups
		Single-Column Grouping
		Multicolumn Grouping
		Grouping via Expressions
		Generating Rollups
	Group Filter Conditions
	Test Your Knowledge
		Exercise 8-1
		Exercise 8-2
		Exercise 8-3
Chapter 9. Subqueries
	What Is a Subquery?
	Subquery Types
	Noncorrelated Subqueries
		Multiple-Row, Single-Column Subqueries
		Multicolumn Subqueries
	Correlated Subqueries
		The exists Operator
		Data Manipulation Using Correlated Subqueries
	When to Use Subqueries
		Subqueries as Data Sources
		Subqueries as Expression Generators
	Subquery Wrap-Up
	Test Your Knowledge
		Exercise 9-1
		Exercise 9-2
		Exercise 9-3
Chapter 10. Joins Revisited
	Outer Joins
		Left Versus Right Outer Joins
		Three-Way Outer Joins
	Cross Joins
	Natural Joins
	Test Your Knowledge
		Exercise 10-1
		Exercise 10-2
		Exercise 10-3 (Extra Credit)
Chapter 11. Conditional Logic
	What Is Conditional Logic?
	The case Expression
		Searched case Expressions
		Simple case Expressions
	Examples of case Expressions
		Result Set Transformations
		Checking for Existence
		Division-by-Zero Errors
		Conditional Updates
		Handling Null Values
	Test Your Knowledge
		Exercise 11-1
		Exercise 11-2
Chapter 12. Transactions
	Multiuser Databases
		Locking
		Lock Granularities
	What Is a Transaction?
		Starting a Transaction
		Ending a Transaction
		Transaction Savepoints
	Test Your Knowledge
		Exercise 12-1
Chapter 13. Indexes and Constraints
	Indexes
		Index Creation
		Types of Indexes
		How Indexes Are Used
		The Downside of Indexes
	Constraints
		Constraint Creation
	Test Your Knowledge
		Exercise 13-1
		Exercise 13-2
Chapter 14. Views
	What Are Views?
	Why Use Views?
		Data Security
		Data Aggregation
		Hiding Complexity
		Joining Partitioned Data
	Updatable Views
		Updating Simple Views
		Updating Complex Views
	Test Your Knowledge
		Exercise 14-1
		Exercise 14-2
Chapter 15. Metadata
	Data About Data
	information_schema
	Working with Metadata
		Schema Generation Scripts
		Deployment Verification
		Dynamic SQL Generation
	Test Your Knowledge
		Exercise 15-1
		Exercise 15-2
Chapter 16. Analytic Functions
	Analytic Function Concepts
		Data Windows
		Localized Sorting
	Ranking
		Ranking Functions
		Generating Multiple Rankings
	Reporting Functions
		Window Frames
		Lag and Lead
		Column Value Concatenation
	Test Your Knowledge
		Exercise 16-1
		Exercise 16-2
		Exercise 16-3
Chapter 17. Working with Large Databases
	Partitioning
		Partitioning Concepts
		Table Partitioning
		Index Partitioning
		Partitioning Methods
		Partitioning Benefits
	Clustering
	Sharding
	Big Data
		Hadoop
		NoSQL and Document Databases
		Cloud Computing
Chapter 18. SQL and Big Data
	Introduction to Apache Drill
	Querying Files Using Drill
	Querying MySQL Using Drill
	Querying MongoDB Using Drill
	Drill with Multiple Data Sources
	Future of SQL
Appendix A. ER Diagram for Example Database
Appendix B. Solutions to Exercises
	Chapter 3
		Exercise 3-1
		Exercise 3-2
		Exercise 3-3
		Exercise 3-4
	Chapter 4
		Exercise 4-1
		Exercise 4-2
		Exercise 4-3
		Exercise 4-4
	Chapter 5
		Exercise 5-1
		Exercise 5-2
		Exercise 5-3
	Chapter 6
		Exercise 6-1
		Exercise 6-2
		Exercise 6-3
	Chapter 7
		Exercise 7-1
		Exercise 7-2
		Exercise 7-3
	Chapter 8
		Exercise 8-1
		Exercise 8-2
		Exercise 8-3
	Chapter 9
		Exercise 9-1
		Exercise 9-2
		Exercise 9-3
	Chapter 10
		Exercise 10-1
		Exercise 10-2
		Exercise 10-3 (Extra Credit)
	Chapter 11
		Exercise 11-1
		Exercise 11-2
	Chapter 12
		Exercise 12-1
	Chapter 13
		Exercise 13-1
		Exercise 13-2
	Chapter 14
		Exercise 14-1
		Exercise 14-2
	Chapter 15
		Exercise 15-1
		Exercise 15-2
	Chapter 16
		Exercise 16-1
		Exercise 16-2
		Exercise 16-3
Index
About the Author
Colophon