SQL Pocket Guide, 4th Edition

5. Creating, Updating, and Deleting
Databases
Display Names of Existing Databases
Display Name of Current Database
Switch to Another Database
Create a Database
Delete a Database
Creating Tables
Create a Simple Table
Display Names of Existing Tables
Create a Table That Does Not Already Exist
Create a Table with Constraints
Create a Table with Primary and Foreign Keys
Create a Table with an Automatically Generated Field
Insert the Results of a Query into a Table
Modifying Tables
Rename a Table or Column
Display, Add, and Delete Columns
Display, Add, and Delete Rows
Display, Add, Modify, and Delete Constraints
Update a Column of Data
Update Rows of Data
Update Rows of Data with the Results of a Query
Delete a Table
Indexes
Create an Index to Speed Up Queries
Views
Create a View to Save the Results of a Query
Transaction Management
Double-Check Changes Before a COMMIT
Undo Changes with a ROLLBACK

6. Data Types
How to Choose a Data Type
Numeric Data
Numeric Values
Integer Data Types
Decimal Data Types
String Data
String Values
Character Data Types
Unicode Data Types
Datetime Data
Datetime Values
Datetime Data Types
Other Data
Boolean Data
External Files (Images, Documents, etc.)

7. Operators and Functions
Operators
Logical Operators
Comparison Operators
Math Operators
Aggregate Functions
Numeric Functions
Apply Math Functions
Generate Random Numbers
Round and Truncate Numbers
Convert Data to a Numeric Data Type
String Functions
Find the Length of a String
Change the Case of a String
Trim Unwanted Characters Around a String
Concatenate Strings
Search for Text in a String
Extract a Portion of a String
Replace Text in a String
Delete Text from a String
Use Regular Expressions
Convert Data to a String Data Type
Datetime Functions
Return the Current Date or Time
Add or Subtract a Date or Time Interval
Extract a Part of a Date or Time
Determine the Day of the Week of a Date
Round a Date to the Nearest Time Unit
Convert a String to a Datetime Data Type
Null Functions
Return an Alternative Value if There Is a Null Value

8. Advanced Querying Concepts
Case Statements
Display Values Based on If-Then Logic for a Single Column
Display Values Based on If-Then Logic for Multiple Columns
Grouping and Summarizing
GROUP BY Basics
Aggregate Rows into a Single Value or List
ROLLUP, CUBE, and GROUPING SETS
Window Functions
Rank the Rows in a Table
Return the First Value in Each Group
Return the Second Value in Each Group
Return the First Two Values in Each Group
Return the Prior Row Value
Calculate the Moving Average
Calculate the Running Total
Pivoting and Unpivoting
Break Up the Values of a Column into Multiple Columns
List the Values of Multiple Columns in a Single Column

9. Working with Multiple Tables and Queries
Joining Tables
Join Basics and INNER JOIN
LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN
USING and NATURAL JOIN
CROSS JOIN and Self Join
Union Operators
UNION
EXCEPT and INTERSECT
Common Table Expressions
CTEs Versus Subqueries
Recursive CTEs

10. How Do I…?
Find the Rows Containing Duplicate Values
Select Rows with the Max Value for Another Column
Concatenate Text from Multiple Fields into a Single Field
Find All Tables Containing a Specific Column Name
Update a Table Where the ID Matches Another Table

Index