Learn SQL in a Month of Lunches

Learn SQL in a Month of Lunches
brief contents
contents
preface
acknowledgments
about this book
	Who should read this book
	How this book is organized: A road map
	About the code
	liveBook discussion forum
about the author
1 Before you begin
	1.1	Why SQL matters
	1.2	Is this book for you?
		1.2.1	The many uses for SQL
		1.2.2	The many flavors of SQL
		1.2.3	A word about AI and SQL
	1.3	How to use this book
		1.3.1	The main chapters
		1.3.2	Hands-on labs
		1.3.3	Further exploration
	1.4	Setting up your lab environment
		1.4.1	Installing MySQL and MySQL Workbench
		1.4.2	Executing the lab scripts
	1.5	Online resources
	1.6	Being immediately effective with SQL
2 Your first SQL query
	2.1	You know tables if you already know spreadsheets
	2.2	Learning SQL is like taking an English class
	2.3	Writing your first SQL query
	2.4	Key terms and keywords
	2.5	Lab
	2.6	Lab answers
3 Querying data
	3.1	Rules for the SELECT statement
		3.1.1	SELECT requirements
		3.1.2	Keywords and reserved words
		3.1.3	Case insensitivity
		3.1.4	Formatting and whitespace
	3.2	Retrieving data from a table
		3.2.1	Retrieving an individual column
		3.2.2	Retrieving multiple columns
		3.2.3	Renaming output columns using aliases
		3.2.4	Retrieving all columns
	3.3	Lab
	3.4	Lab answers
4 Sorting, skipping, and commenting data
	4.1	Sorting data
		4.1.1	Sorting by one column
		4.1.2	Sorting by multiple columns
		4.1.3	Specifying sort direction
		4.1.4	Sorting by hidden columns
		4.1.5	Sorting by position
	4.2	Skipping data
		4.2.1	Using LIMIT to reduce results
		4.2.2	Using OFFSET to select a different limited set
		4.2.3	Limiting data in another RDBMS
	4.3	Commenting data
	4.4	Lab
	4.5	Lab answers
5 Filtering on specific values
	5.1	Filtering on a single condition
		5.1.1	Filtering on numeric values
		5.1.2	Filtering on string values
		5.1.3	Filtering on date values
	5.2	Filtering on multiple conditions
		5.2.1	Filtering that requires all conditions
		5.2.2	Filtering that requires any one of many conditions
		5.2.3	Controlling the order of multiple filters
		5.2.4	Filtering and using ORDER BY
	5.3	Lab
	5.4	Lab answers
6 Filtering with multiple values, ranges, and exclusions
	6.1	Filtering on specific values
	6.2	Filtering on a range of values
		6.2.1	Filtering on an open-ended range
		6.2.2	Filtering a defined range
	6.3	Negating filter conditions
		6.3.1	Negating a specific value
		6.3.2	Negating any filter condition
	6.4	Combining types of filter conditions
	6.5	Reviewing comparison operators
	6.6	Lab
	6.7	Lab answers
7 Filtering with wildcards and null values
	7.1	Filtering with wildcards
		7.1.1	Filtering with the percent sign
		7.1.2	Filtering with an underscore
	7.2	Filtering with null values
		7.2.1	How not to search for null values
		7.2.2	How to search for null values correctly
		7.2.3	How to search for values that are not null
	7.3	Lab
	7.4	Lab answers
8 Querying multiple tables
	8.1	The rules of data relationships
		8.1.1	Data without relationships
		8.1.2	Data with relationships
	8.2	The way to join data
		8.2.1	Joining two tables
		8.2.2	Joining more tables
	8.3	Table aliases
	8.4	The other way to join data
	8.5	Lab
	8.6	Lab answers
9 Using different kinds of joins
	9.1	Inner joins
	9.2	Outer joins
		9.2.1	Left outer joins
		9.2.2	Right outer joins
		9.2.3	Using outer joins to find rows without matching values
		9.2.4	Interchanging left and right joins
		9.2.5	The USING keyword
		9.2.6	Natural joins
	9.3	Cross joins
	9.4	Lab
	9.5	Lab answers
10 Combining queries with set operators
	10.1	Using set operators
	10.2	UNION
	10.3	UNION ALL
	10.4	Emulating FULL OUTER JOIN in MySQL
	10.5	INTERSECT
	10.6	EXCEPT
	10.7	Lab
	10.8	Lab answers
11 Using subqueries and logical operators
	11.1	A simple subquery
	11.2	Logical operators and subqueries
		11.2.1	The ANY and IN operators
		11.2.2	The ALL and NOT IN operators
		11.2.3	The EXISTS and NOT EXISTS operators
	11.3	Subqueries in other parts of a query
		11.3.1	Subqueries in the FROM clause
		11.3.2	Subqueries in the SELECT clause
	11.4	Lab
	11.5	Lab answers
12 Grouping data
	12.1	Aggregate functions
		12.1.1	The SUM function
		12.1.2	The COUNT function
		12.1.3	The MIN function
		12.1.4	The MAX function
		12.1.5	The AVG function
		12.1.6	Filtering and aggregating combined values
	12.2	Aggregating data with GROUP BY
		12.2.1	GROUP BY requirements
		12.2.2	GROUP BY and null values
	12.3	Filtering with HAVING
	12.4	Logical query processing
	12.5	The DISTINCT keyword
	12.6	Lab
	12.7	Lab answers
13 Using variables
	13.1	User-defined variables
		13.1.1	Declaring your first user-defined variable
		13.1.2	Understanding rules for user-defined variables
		13.1.3	Using your first user-defined variable
	13.2	Filtering with variables in FROM and HAVING clauses
	13.3	Assigning an unknown value to a variable
		13.3.1	Reviewing how a query works
		13.3.2	Assigning an unknown variable with SELECT
		13.3.3	Considering performance with variables
		13.3.4	Troubleshooting considerations with variables
	13.4	Other notes about variables
		13.4.1	Assigning a literal value using SELECT
		13.4.2	Assigning a value of NULL to a variable
		13.4.3	Changing the type of data used by a variable
	13.5	Lab
	13.6	Lab answers
14 Querying with functions
	14.1	The problems with functions
		14.1.1	Function commands vary for each RDBMS
		14.1.2	Function commands can be inefficient
	14.2	String functions
		14.2.1	Case functions
		14.2.2	Trim functions
		14.2.3	Other string functions
	14.3	Date and time functions
		14.3.1	Date functions that return numeric values
		14.3.2	Date functions that return string values
		14.3.3	Other date and time functions
	14.4	Informational functions
		14.4.1	Date and time information
		14.4.2	Connection information
	14.5	Lab
	14.6	Lab answers
15 Combining or calculating values with functions
	15.1	Combining string values
		15.1.1	CONCAT
		15.1.2	CONCAT_WS
		15.1.3	COALESCE
	15.2	Converting values
		15.2.1	REPLACE
		15.2.2	CONVERT and CAST
	15.3	Numeric calculations with functions
	15.4	Lab
	15.5	Lab answers
16 Inserting data
	16.1	Inserting specific values
		16.1.1	Inserting a new row
		16.1.2	Inserting multiple new rows
		16.1.3	Inserting a partial row
		16.1.4	A word of caution about omitting columns
	16.2	Inserting a row with a query
	16.3	Inserting a row with variables
	16.4	Lab
	16.5	Lab answers
17 Updating and deleting data
	17.1	Updating values
		17.1.1	Working with data manipulation in real time
		17.1.2	Requirements for updates
		17.1.3	Updating values in one or more columns
		17.1.4	Updating values with a multitable query
	17.2	Deleting rows
		17.2.1	Deleting one or more rows
		17.2.2	Deleting a row with a multitable query
		17.2.3	Deleting all rows in a table
	17.3	One big tip for data manipulation
	17.4	Lab
	17.5	Lab answers
18 Storing data in tables
	18.1	Creating a table
		18.1.1	Considerations before creating a table
		18.1.2	Creating a table
		18.1.3	Adding values to an empty table
	18.2	Altering a table
		18.2.1	Adding a column to a table
		18.2.2	Considerations before adding a column
	18.3	Primary keys
		18.3.1	Considerations for primary keys
		18.3.2	Adding a primary key
	18.4	Foreign keys and constraints
		18.4.1	Data diagrams
		18.4.2	Adding a foreign-key constraint
	18.5	Deleting a table, column, or constraint
		18.5.1	Deleting a constraint
		18.5.2	Deleting a column
		18.5.3	Deleting a table
	18.6	Lab
	18.7	Lab answers
19 Creating constraints and indexes
	19.1	Constraints
		19.1.1	NOT NULL constraints
		19.1.2	DEFAULT constraints
		19.1.3	UNIQUE constraints
		19.1.4	CHECK constraints
	19.2	Automatically incrementing values for a column
	19.3	Indexes
		19.3.1	Clustered indexes
		19.3.2	Nonclustered indexes
	19.4	Lab
	19.5	Lab answers
20 Reusing queries with views and stored procedures
	20.1	Views
		20.1.1	Creating views
		20.1.2	Filtering with views
		20.1.3	Joining views
		20.1.4	Considerations for views
	20.2	Stored procedures
		20.2.1	Creating stored procedures
		20.2.2	Using variables with stored procedures
		20.2.3	Considerations for stored procedures
	20.3	Differences between views and stored procedures
	20.4	Lab
	20.5	Lab answers
21 Making decisions in queries
	21.1	Conditional functions and expressions
		21.1.1	COALESCE function
		21.1.2	IFNULL function
		21.1.3	CASE expression
	21.2	Decision structures
		21.2.1	IF and THEN
		21.2.2	ELSE
		21.2.3	Multiple conditions
	21.3	Lab
	21.4	Lab answers
22 Using cursors
	22.1	Reviewing variables and parameters
		22.1.1	Variables inside stored procedures
		22.1.2	Output parameters
	22.2	Cursors
		22.2.1	Anatomy of a cursor
		22.2.2	Creating a cursor
	22.3	Alternatives to cursors
		22.3.1	Using WHILE
		22.3.2	Temporary tables
	22.4	Considerations for using cursors
		22.4.1	Thinking in sets
		22.4.2	Thinking about cursor use
	22.5	Lab
	22.6	Lab answers
23 Using someone else’s script
	23.1	Someone else’s script: Creating a table
		23.1.1	The CREATE TABLE script
		23.1.2	Reviewing the CREATE TABLE script
		23.1.3	Improving the CREATE TABLE script
	23.2	Someone else’s script: Inserting data
		23.2.1	The INSERT stored procedure
		23.2.2	Reviewing the INSERT stored procedure
		23.2.3	Improving the INSERT stored procedure
		23.2.4	Improving the INSERT stored procedure even more
24 Never the end
	24.1	More SQL
	24.2	Other SQL resources
	24.3	Farewell
index