SQL Pocket Guide. 4th Edition

Spis treści książki

• Preface
  • Why SQL?
  • Goals of This Book
  • Updates to the Fourth Edition
  • Navigating This Book
  • Conventions Used in This Book
  • Using Code Examples
  • OReilly Online Learning
  • How to Contact Us
  • Acknowledgments
• 1. SQL Crash Course
  • What Is a Database?
    • SQL
    • NoSQL
    • Database Management Systems (DBMS)
  • A SQL Query
    • The SELECT Statement
    • Order of Execution
  • A Data Model
• 2. Where Can I Write SQL Code?
  • RDBMS Software
    • SQLite
    • MySQL
    • Oracle
    • PostgreSQL
    • SQL Server
  • Database Tools
    • Connect a Database Tool to a Database
  • Other Programming Languages
    • Connect Python to a Database
      • Step 1: Install a database driver for Python
      • Step 2: Set up a database connection in Python
      • Step 3: Write SQL code in Python
    • Connect R to a Database
      • Step 1: Install a database driver for R
      • Step 2: Set up a database connection in R
      • Step 3: Write SQL code in R
• 3. The SQL Language
  • Comparison to Other Languages
  • ANSI Standards
  • SQL Terms
    • Keywords and Functions
      • Keywords
      • Functions
    • Identifiers and Aliases
      • Identifiers
      • Aliases
    • Statements and Clauses
      • Statements
      • Clauses
    • Expressions and Predicates
      • Expressions
      • Predicates
    • Comments, Quotes, and Whitespace
      • Comments
      • Quotes
      • Whitespace
  • Sublanguages
• 4. Querying Basics
  • The SELECT Clause
    • Aliasing Columns
      • Aliases with case sensitivity and punctuation
    • Qualifying Columns
      • Qualifying tables
    • Selecting Subqueries
      • Performance issues with correlated subqueries
    • DISTINCT
      • COUNT and DISTINCT
  • The FROM Clause
    • From Multiple Tables
      • Table aliases
      • JOIN ON
      • Results table
      • JOIN variations
    • From Subqueries
      • Step 1: Execute the subquery
      • Step 2: Execute the entire query
  • The WHERE Clause
    • Filtering on Subqueries
      • Why use a subquery in the WHERE clause?
      • Other Ways to Filter Data
  • The GROUP BY Clause
  • The HAVING Clause
  • The ORDER BY Clause
  • The LIMIT Clause
• 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
      • Constraint: Not allowing NULL values in a column with NOT NULL
      • Constraint: Setting default values in a column with DEFAULT
      • Constraint: Restricting values in a column with CHECK
      • Constraint: Requiring unique values in a column with UNIQUE
    • Create a Table with Primary and Foreign Keys
      • Specify a primary key
      • Specify a foreign key
    • Create a Table with an Automatically Generated Field
    • Insert the Results of a Query into a Table
    • Insert Data from a Text File into a Table
  • Modifying Tables
    • Rename a Table or Column
      • Rename a table
      • Rename a column
    • Display, Add, and Delete Columns
      • Display the columns of a table
      • Add a column to a table
      • Delete a column from a table
    • Display, Add, and Delete Rows
      • Display rows of a table
      • Add rows to a table
      • Delete rows from a table
    • Display, Add, Modify, and Delete Constraints
      • Display the constraints of a table
      • Add a constraint
      • Modify a constraint
      • Delete a constraint
    • Update a Column of Data
    • Update Rows of Data
    • Update Rows of Data with the Results of a Query
    • Delete a Table
      • Delete a table with foreign key references
  • Indexes
    • Create an Index to Speed Up Queries
      • Delete an index
  • Views
    • Create a View to Save the Results of a Query
      • Display existing views
      • Update a view
      • Delete a view
  • 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 values
      • Decimal values
      • Floating point values
    • Integer Data Types
    • Decimal Data Types
    • Floating Point Data Types
  • String Data
    • String Values
      • String basics
      • Alternatives to single quotes
      • Escape sequences
    • Character Data Types
    • Unicode Data Types
  • Datetime Data
    • Datetime Values
      • Date values
      • Time values
      • Date and time values
    • Datetime Data Types
      • MySQL datetime data types
      • Oracle datetime data types
      • Check the datetime formats in Oracle
      • PostgreSQL datetime data types
      • SQL Server datetime data types
      • SQLite datetime data types
  • Other Data
    • Boolean Data
      • Boolean data types
    • External Files (Images, Documents, etc.)
      • Binary and hexadecimal values
      • Binary data types
• 7. Operators and Functions
  • Operators
    • Logical Operators
    • Comparison Operators
      • BETWEEN
      • EXISTS
      • IN
      • IS NULL
      • LIKE
    • 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
      • Remove spaces around a string
      • Remove other characters around a string
      • Remove characters from the left or right side of 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
      • Regular Expressions in MySQL
      • Regular expressions in Oracle
      • Regular expressions in PostgreSQL
      • Regular expressions in SQL Server
    • Convert Data to a String Data Type
  • Datetime Functions
    • Return the Current Date or Time
    • Add or Subtract a Date or Time Interval
    • Find the Difference Between Two Dates or Times
    • 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
      • Rounding in Oracle
      • Rounding in PostgreSQL
    • Convert a String to a Datetime Data Type
      • The CAST function
      • The STR_TO_DATE, TO_DATE, and CONVERT functions
      • Apply a date function to a string column
  • 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
      • Grouping by multiple columns
    • Aggregate Rows into a Single Value or List
    • ROLLUP, CUBE, and GROUPING SETS
      • ROLLUP
      • CUBE
      • 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
      • Join basics
      • INNER JOIN
    • LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN
      • LEFT JOIN
      • RIGHT JOIN
      • FULL OUTER JOIN
    • USING and NATURAL JOIN
      • USING
      • NATURAL JOIN
    • CROSS JOIN and Self Join
      • CROSS JOIN
      • Self join
  • Union Operators
    • UNION
      • UNION ALL
      • UNION with other clauses
      • UNION with more than two tables
    • EXCEPT and INTERSECT
      • EXCEPT
      • INTERSECT
  • Common Table Expressions
    • CTEs Versus Subqueries
    • Recursive CTEs
      • Fill in missing rows in a sequence of data
      • Return all the parents of a child row
• 10. How Do I?
  • Find the Rows Containing Duplicate Values
  • Select Rows with the Max Value for Another Column
    • Solution
    • Explanation
    • Keywords in the query
    • Potential extensions
  • Concatenate Text from Multiple Fields into a Single Field
  • Find All Tables Containing a Specific Column Name
    • Solution
    • Potential extensions
  • Update a Table Where the ID Matches Another Table
    • Solution
    • Potential extensions
• Index

pokaż cały spis treści

ukryj recenzje