SQL Pocket Guide, 4th Edition

Table of contents

1. Preface
   1. Why SQL?
   2. Goals of This Book
   3. Updates to the Fourth Edition
   4. Navigating This Book
   5. Conventions Used in This Book
   6. Using Code Examples
   7. O’Reilly Online Learning
   8. How to Contact Us
   9. Acknowledgments
2. 1. SQL Crash Course
   1. What Is a Database?
      1. SQL
      2. NoSQL
      3. Database Management Systems (DBMS)
   2. A SQL Query
      1. The SELECT Statement
      2. Order of Execution
   3. A Data Model
3. 2. Where Can I Write SQL Code?
   1. RDBMS Software
      1. SQLite
      2. MySQL
      3. Oracle
      4. PostgreSQL
      5. SQL Server
   2. Database Tools
      1. Connect a Database Tool to a Database
   3. Other Programming Languages
      1. Connect Python to a Database
      2. Connect R to a Database
4. 3. The SQL Language
   1. Comparison to Other Languages
   2. ANSI Standards
   3. SQL Terms
      1. Keywords and Functions
      2. Identifiers and Aliases
      3. Statements and Clauses
      4. Expressions and Predicates
      5. Comments, Quotes, and Whitespace
   4. Sublanguages
5. 4. Querying Basics
   1. The SELECT Clause
      1. Aliasing Columns
      2. Qualifying Columns
      3. Selecting Subqueries
      4. DISTINCT
   2. The FROM Clause
      1. From Multiple Tables
      2. From Subqueries
   3. The WHERE Clause
      1. Filtering on Subqueries
   4. The GROUP BY Clause
   5. The HAVING Clause
   6. The ORDER BY Clause
   7. The LIMIT Clause
6. 5. Creating, Updating, and Deleting
   1. Databases
      1. Display Names of Existing Databases
      2. Display Name of Current Database
      3. Switch to Another Database
      4. Create a Database
      5. Delete a Database
   2. Creating Tables
      1. Create a Simple Table
      2. Display Names of Existing Tables
      3. Create a Table That Does Not Already Exist
      4. Create a Table with Constraints
      5. Create a Table with Primary and Foreign Keys
      6. Create a Table with an Automatically Generated Field
      7. Insert the Results of a Query into a Table
   3. Modifying Tables
      1. Rename a Table or Column
      2. Display, Add, and Delete Columns
      3. Display, Add, and Delete Rows
      4. Display, Add, Modify, and Delete Constraints
      5. Update a Column of Data
      6. Update Rows of Data
      7. Update Rows of Data with the Results of a Query
      8. Delete a Table
   4. Indexes
      1. Create an Index to Speed Up Queries
   5. Views
      1. Create a View to Save the Results of a Query
   6. Transaction Management
      1. Double-Check Changes Before a COMMIT
      2. Undo Changes with a ROLLBACK
7. 6. Data Types
   1. How to Choose a Data Type
   2. Numeric Data
      1. Numeric Values
      2. Integer Data Types
      3. Decimal Data Types
   3. String Data
      1. String Values
      2. Character Data Types
      3. Unicode Data Types
   4. Datetime Data
      1. Datetime Values
      2. Datetime Data Types
   5. Other Data
      1. Boolean Data
      2. External Files (Images, Documents, etc.)
8. 7. Operators and Functions
   1. Operators
      1. Logical Operators
      2. Comparison Operators
      3. Math Operators
   2. Aggregate Functions
   3. Numeric Functions
      1. Apply Math Functions
      2. Generate Random Numbers
      3. Round and Truncate Numbers
      4. Convert Data to a Numeric Data Type
   4. String Functions
      1. Find the Length of a String
      2. Change the Case of a String
      3. Trim Unwanted Characters Around a String
      4. Concatenate Strings
      5. Search for Text in a String
      6. Extract a Portion of a String
      7. Replace Text in a String
      8. Delete Text from a String
      9. Use Regular Expressions
      10. Convert Data to a String Data Type
   5. Datetime Functions
      1. Return the Current Date or Time
      2. Add or Subtract a Date or Time Interval
      3. Extract a Part of a Date or Time
      4. Determine the Day of the Week of a Date
      5. Round a Date to the Nearest Time Unit
      6. Convert a String to a Datetime Data Type
   6. Null Functions
      1. Return an Alternative Value if There Is a Null Value
9. 8. Advanced Querying Concepts
   1. Case Statements
      1. Display Values Based on If-Then Logic for a Single Column
      2. Display Values Based on If-Then Logic for Multiple Columns
   2. Grouping and Summarizing
      1. GROUP BY Basics
      2. Aggregate Rows into a Single Value or List
      3. ROLLUP, CUBE, and GROUPING SETS
   3. Window Functions
      1. Rank the Rows in a Table
      2. Return the First Value in Each Group
      3. Return the Second Value in Each Group
      4. Return the First Two Values in Each Group
      5. Return the Prior Row Value
      6. Calculate the Moving Average
      7. Calculate the Running Total
   4. Pivoting and Unpivoting
      1. Break Up the Values of a Column into Multiple Columns
      2. List the Values of Multiple Columns in a Single Column
10. 9. Working with Multiple Tables and Queries
    1. Joining Tables
       1. Join Basics and INNER JOIN
       2. LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN
       3. USING and NATURAL JOIN
       4. CROSS JOIN and Self Join
    2. Union Operators
       1. UNION
       2. EXCEPT and INTERSECT
    3. Common Table Expressions
       1. CTEs Versus Subqueries
       2. Recursive CTEs
11. 10. How Do I…?
    1. Find the Rows Containing Duplicate Values
    2. Select Rows with the Max Value for Another Column
    3. Concatenate Text from Multiple Fields into a Single Field
    4. Find All Tables Containing a Specific Column Name
    5. Update a Table Where the ID Matches Another Table
12. Index
13. About the Author