Learning SQL on SQL Server 2005

Spis treści ebooka

• Learning SQL on SQL Server 2005
• Dedication
• SPECIAL OFFER: Upgrade this ebook with OReilly
• A Note Regarding Supplemental Files
• Preface
  • Why This Book?
  • SQL and SQL Server
  • Audience and Coverage
  • A Few Notes About SQL Server 2005 Installation
  • Conventions Used in This Book
  • Using Code Examples
  • How to Contact Us
  • Acknowledgments
• 1. Starting Microsoft SQL Server 2005
  • Starting Microsoft SQL Server 2005 and SQL Server 2005s Management Studio
  • Creating a Database in Microsoft SQL Server 2005
    • Objects in the Student_course Database
    • Default Tables in the Student_course Database
    • Default System Databases
  • The Query Editor
    • Opening the Query Editor by Right-Clicking
    • Opening the Query Editor Using the New Query Button
    • Opening or Activating the Database Using USE
  • Creating Tables Using the Load Script
  • Viewing Table Definitions
  • Modifying Table Definitions
    • Modifying Column Definitions
    • Modifying the Table Definition Directly
  • Viewing Table Data
  • Deleting a Table
  • Deleting a Database
  • Entering a SQL Query or Statement
  • Parsing a Query
  • Executing a Query
    • Color Coding
  • Saving a Query
  • Displaying the Results
    • Viewing Results in Grid Form
    • Viewing Results in Text Form
    • Saving Results to File
  • Stopping Execution of a Long Query
    • Viewing Error Messages
  • Printing the Query and Results
  • Customizing SQL Server 2005
    • The Environment tab
    • The Source Control Tab
    • The Text Editor Tab
    • The Query Execution Tab
    • The Query Results Tab
    • The Designer Tab
  • Summary
  • Review Questions
  • Exercises
• 2. Beginning SQL Commands in SQL Server
  • Displaying Data with the SELECT Statement
    • SELECT without the FROM
    • Displaying or Selecting Columns from a Table
    • Displaying or SELECTing One Column from a Table
    • Displaying or SELECTing More than One Column from a Table
    • Displaying or SELECTing All Columns of a Table
    • ORDER BY
      • ORDER BY and NULLs
    • Ascending and Descending Order
    • Ordering Within an Order
  • Displaying or SELECTing Rows or Tuples from a Table
    • Filtering with WHERE
    • The AND Operator
    • The OR Operator
    • The BETWEEN Operator
    • Negating the BETWEEN Operator
  • The COUNT Function
    • IS NULL
    • IS NOT NULL
  • The ROWCOUNT Function
  • Using Aliases
    • Column Aliases
    • Table Aliases
    • Table Aliases Used as Table Qualifiers
  • Synonyms
  • Adding Comments to SQL Statements
  • Some Conventions for Writing SQL Statements
  • A Few Notes About SQL Server 2005 Syntax
  • Summary
  • Review Questions
  • Exercises
• 3. Creating, Populating, Altering, and Deleting Tables
  • Data Types in SQL Server 2005
    • Numeric Data Types
      • Integer data types
      • Decimal data types
    • Character Data Types
      • The CHAR data type
      • The VARCHAR data type
      • The TEXT data type
      • The NCHAR data type
      • The NVARCHAR data type
      • Unicode character strings
      • Selecting the character data types
    • Date and Time Data Types
    • Miscellaneous Data Types
      • The BINARY data type
      • The IMAGE data type
      • The BIT data type
      • The monetary data types
      • The TABLE data type
      • The SQL_VARIANT data type
      • The UNIQUEIDENTIFIER data type
      • The XML data type
    • Selecting Data Types
  • Creating a Table
  • Inserting Values into a Table
    • Using INSERT INTO .. VALUES
    • Using INSERT INTO .. SELECT
  • The UPDATE Command
  • The ALTER TABLE Command
    • Adding a Column to a Table
    • Changing a Columns Data Type in a Table
      • Changing a columns length in a table
    • Deleting a Column from a Table
  • The DELETE Command
  • Deleting a Table
  • Summary
  • Review Questions
  • Exercises
  • References
• 4. Joins
  • The JOIN
    • Example 1
    • Example 2
    • The INNER JOIN
    • Using a WHERE Clause Instead of a JOIN
    • Associative Property of the JOIN
    • Column Types in Joins
    • Performance Hint for Efficient Joins
  • The Cartesian Product
    • Uses of the Cartesian Product
    • CROSS JOIN Used to Generate a Cartesian Product
  • Equi-Joins and Non-Equi-Joins
    • Equi-Joins
    • Non-Equi-Joins
  • Self Joins
  • Using ORDER BY with a Join
  • Joining More Than Two Tables
    • Joining Multiple Tables Using a Nested JOIN
  • The OUTER JOIN
    • The LEFT OUTER JOIN
    • The RIGHT OUTER JOIN
    • The FULL OUTER JOIN
  • Summary
  • Review Questions
  • Exercises
• 5. Functions
  • Aggregate Functions
    • The COUNT Function
    • The SUM Function
    • The AVG Function
    • The MIN and MAX Functions
  • Row-Level Functions
    • Arithmetic Operations on a Column
    • The ROUND Function
    • Other Common Numeric Functions
    • The ISNULL Function
      • Example 1
      • Example 2
    • The NULLIF Function
    • Other Row-Level Functions
  • Other Functions
    • The TOP Function
      • Handling the BOTTOM
      • Handling a tie
    • The TOP Function with PERCENT
    • The DISTINCT Function
      • Using DISTINCT with other aggregate functions
  • String Functions
    • String Concatenation
    • String Extractors
      • The SUBSTRING function
      • The LEFT and RIGHT functions
      • The LTRIM and RTRIM functions
      • The CHARINDEX function
    • The UPPER and LOWER Functions
    • The LEN Function
    • Matching Substrings Using LIKE
      • Using the wildcard character with LIKE
      • Finding a range of characters
      • Finding a particular character
      • Finding a single character or single digitthe underscore wildcard character
      • Using NOT LIKE
  • CONVERSION Functions
    • The CAST Function
    • The STR Function
    • The CONVERT Function
  • DATE Functions
    • Creating a Table with the DATETIME Data Type
    • Default Date Formats and Changing Date Formats
    • Date Functions
      • The DATEADD function
      • The DATEDIFF function
      • The DATEPART function
      • The YEAR function
      • The MONTH function
      • The DAY function
      • The GETDATE function
      • Inserting the current date and time
  • Summary
    • Table of Functions
  • Review Questions
  • Exercises
• 6. Query Development and Derived Structures
  • Query Development
  • Parentheses in SQL Expressions
    • Operator Precedence
    • Data Type Precedence
  • Derived Structures
    • Views
      • Creating views
      • Using views
      • ORDER BY in views
      • SELECT INTO in views
      • Column aliases in views
      • Data in views
        • Changing data in views
        • Changing data in tables
      • Deleting views
    • Temporary Tables
      • Creating temporary tables
        • Creating local temporary tables
        • Creating global temporary tables
        • Deleting temporary tables
  • Query Development with Derived Structures
    • Step 1: Develop a Query Step by Step
    • Step 2: Using a Derived Structure
      • Option 1: Turning your query into a view
      • Option 2: Using an inline view
      • Option 3: Using a global temporary table
  • Summary
  • Review Questions
  • Exercises
• 7. Set Operations
  • Introducing Set Operations
    • Union Compatibility
  • The UNION Operation
    • Similar Columns in Unions
    • Unioning Constants or Variables
  • The UNION ALL Operation
  • Handling UNION and UNION ALL Situations with an Unequal Number of Columns
  • The IN and NOT..IN Predicates
    • Using IN
      • Using IN as a subquery
    • The INTERSECT Operator
    • Using NOT..IN
      • Using NOT..IN in a subquery
  • The Difference Operation
    • Example 1
    • Example 2
  • The Union and the Join
    • When a JOIN May Be Used Versus When a UNION May Be Used
      • Example 1: A straightforward join operation
      • Example 2: A not-so-straightforward query
    • A Summary of the Other Differences Between the UNION and the JOIN
  • A UNION Used to Implement a Full Outer Join
  • Summary
  • Review Questions
  • Exercises
  • Optional Exercise
• 8. Joins Versus Subqueries
  • Subquery with an IN Predicate
  • The Subquery as a Join
  • When the Join Cannot Be Turned into a Subquery
  • More Examples Involving Joins and IN
    • Example 1
    • Example 2
    • Example 3
  • Using Subqueries with Operators
  • Summary
  • Review Questions
  • Exercises
• 9. Aggregation and GROUP BY
  • A SELECT in Modified BNF
  • The GROUP BY Clause
    • GROUP BY and ORDER BY
    • GROUP BY and DISTINCT
  • The HAVING Clause
    • HAVING and WHERE
  • GROUP BY and HAVING: Aggregates of Aggregates
    • Aggregation and Grouping in SQL Server 2005
      • Aggregation and grouping handled with a global temporary table
      • Aggregation and grouping handled with an inline view
  • Auditing in Subqueries
  • Nulls Revisited
  • Summary
  • Review Questions
  • Exercises
• 10. Correlated Subqueries
  • Noncorrelated Subqueries
  • Correlated Subqueries
  • Existence Queries and Correlation
    • Using EXISTS
    • From IN to EXISTS
    • NOT EXISTS
  • SQL Universal and Existential Qualifiers
    • Example 1
      • The way the query works
    • Example 2
      • The way this query works
    • Example 3
  • Summary
  • Review Questions
  • Exercises
• 11. Indexes and Constraints on Tables
  • The Simple CREATE TABLE
  • Indexes
    • The Simple CREATE INDEX
    • Deleting Indexes Using SQL
  • Constraints
    • The NOT NULL Constraint
    • The PRIMARY KEY Constraint
      • Option 1
      • Option 2
      • Option 3
      • Concatenated primary keys
    • The UNIQUE Constraint
    • The CHECK Constraint
    • Deleting a Constraint
    • Referential Integrity Constraints
      • Defining the referential integrity constraint
      • Adding the foreign key after tables are created
      • DELETE and the referential CONSTRAINT
      • UPDATE and the referential CONSTRAINT
      • Using the ON DELETE and ON UPDATE together
  • Summary
  • Review Questions
  • Exercises
• A. The Student Database and Other Tables Used in This Book
  • • Brief English Description of the ER Diagram
• B. Script Used to Create the Student_course Database
• Glossary of Terms
• Important Commands and Functions
• Index
• About the Authors
• Colophon
• SPECIAL OFFER: Upgrade this ebook with OReilly
• Copyright

pokaż cały spis treści

ukryj recenzje