Microsoft SQL Server T-SQL in 10 Minutes, Sams Teach Yourself by Forta, Ben -- Read -- Imperial Library of Trantor

Index

About This E-Book Title Page Copyright Page Accessing the Free Web Edition Contents About the Author We Want to Hear from You! Introduction

Who Is This Book For? Companion Website Conventions Used in This Book

Lesson 1. Understanding SQL

Database Basics

What Is a Database? Tables Columns and Datatypes Rows Primary Keys

What Is SQL? Try It Yourself Summary

Lesson 2. Introducing SQL Server

What Is SQL Server?

Client Server Software SQL Server Versions SQL Server Tools

Getting Started with SQL Server and T-SQL

Getting Access To a Server Obtaining the Software Installing the Software

Preparing for Your Lessons Summary

Lesson 3. Working with SQL Server

Making the Connection Navigating SQL Server Management Studio Creating the Example Tables Selecting a Database Learning About Databases and Tables Summary

Lesson 4. Retrieving Data

The SELECT Statement Retrieving Individual Columns Retrieving Multiple Columns Retrieving All Columns Retrieving Distinct Rows Limiting Results Using Fully Qualified Table Names Summary

Lesson 5. Sorting Retrieved Data

Sorting Data Sorting by Multiple Columns Specifying Sort Direction Summary

Lesson 6. Filtering Data

Using the WHERE Clause The WHERE Clause Operators

Checking Against a Single Value Checking for Nonmatches Checking for a Range of Values Checking for No Value

Summary

Lesson 7. Advanced Data Filtering

Combining WHERE Clauses

Using the AND Operator Using the OR Operator Understanding Order of Evaluation

Using the IN Operator Using the NOT Operator Summary

Lesson 8. Using Wildcard Filtering

Using the LIKE Operator

The Percent Sign (%) Wildcard The Underscore (_) Wildcard The Brackets ([]) Wildcard

Tips for Using Wildcards Summary

Lesson 9. Creating Calculated Fields

Understanding Calculated Fields Concatenating Fields

Using Aliases

Performing Mathematical Calculations Summary

Lesson 10. Using Data Manipulation Functions

Understanding Functions Using Functions

Text Manipulation Functions Date and Time Manipulation Functions Numeric Manipulation Functions

Summary

Lesson 11. Summarizing Data

Using Aggregate Functions

The Avg() Function The Count() Function The Max() Function The Min() Function The Sum() Function

Aggregates on Distinct Values Combining Aggregate Functions Summary

Lesson 12. Grouping Data

Understanding Data Grouping Creating Groups Filtering Groups Grouping and Sorting SELECT Clause Ordering Summary

Lesson 13. Working with Subqueries

Understanding Subqueries Filtering by Subquery Using Subqueries as Calculated Fields Checking for Existence with Subqueries Summary

Lesson 14. Joining Tables

Understanding Joins

Understanding Relational Tables Why Use Joins?

Creating a Join

The Importance of the WHERE Clause Inner Joins Joining Multiple Tables

Summary

Lesson 15. Creating Advanced Joins

Using Table Aliases Using Different Join Types

Self Joins Natural Joins Outer Joins

Using Joins with Aggregate Functions Using Joins and Join Conditions Summary

Lesson 16. Combining Queries

Understanding Combined Queries Creating Combined Queries

Using UNION UNION Rules Including or Eliminating Duplicate Rows Sorting Combined Query Results

Summary

Lesson 17. Full-Text Searching

Understanding Full-Text Searching Setting Up Full-Text Searching

Enabling Full-Text Searching Support Creating a Full-Text Catalog Creating a Full-Text Index Managing Catalogs and Indexes

Performing Full-Text Searches

Searching Using FREETEXT Searching Using CONTAINS Ranking Search Results

Summary

Lesson 18. Inserting Data

Understanding Data Insertion Inserting Complete Rows Inserting Multiple Rows Inserting Retrieved Data Reporting On Inserted Data Summary

Lesson 19. Updating and Deleting Data

Updating Data Deleting Data Reporting On Updated And Deleted Data Guidelines for Updating and Deleting Data Summary

Lesson 20. Creating and Manipulating Tables

Creating Tables

Basic Table Creation Working with NULL Values Primary Keys Revisited Using IDENTITY Specifying Default Values

Updating Tables Deleting Tables Renaming Tables Summary

Lesson 21. Using Views

Understanding Views

Why Use Views View Rules and Restrictions

Using Views

Using Views to Simplify Complex Joins Using Views to Reformat Retrieved Data Using Views to Filter Unwanted Data Using Views with Calculated Fields Updating Views

Summary

Lesson 22. Programming with T-SQL

Understanding T-SQL Programming Using Variables

Declaring Variables Assigning Values to Variables Viewing Variable Contents Using Variables in T-SQL Statements

Using Conditional Processing Grouping Statements Using Looping Summary

Lesson 23. Working with Stored Procedures

Understanding Stored Procedures Why Use Stored Procedures Using Stored Procedures

Executing Stored Procedures Creating Stored Procedures Dropping Stored Procedures Working with Parameters Building Intelligent Stored Procedures

Summary

Lesson 24. Using Cursors

Understanding Cursors Working with Cursors

Creating and Removing Cursors Opening and Closing Cursors Using Cursor Data

Summary

Lesson 25. Using Triggers

Understanding Triggers

Creating Triggers Dropping Triggers Enabling and Disabling Triggers Determining Trigger Assignments

Using Triggers

INSERT Triggers DELETE Triggers UPDATE Triggers More on Triggers

Summary

Lesson 26. Managing Transaction Processing

Understanding Transaction Processing Controlling Transactions

Using ROLLBACK Using COMMIT Using Savepoints Changing Autocommit Behavior

Summary

Lesson 27. Working with XML and JSON

Using SQL Server XML Support

Retrieving Data As XML Storing XML Data Searching for XML Data

Using SQL Server JSON Support Retrieving Data as JSON The JSON Functions Summary

Lesson 28. Globalization and Localization

Understanding Character Sets and Collation Sequences Working with Collation Sequences Managing Case Sensitivity Working with Unicode Summary

Lesson 29. Managing Security

Understanding Access Control Managing Users

Creating User Accounts Deleting User Accounts Enabling and Disabling Accounts Renaming Logins Changing Passwords

Managing Access Rights

Setting Access Rights Removing Access Rights

Summary

Lesson 30. Improving Performance

Improving Performance Summary

Appendix A. The Example Tables

Understanding the Example Tables

Table Descriptions

Appendix B. T-SQL Statement Syntax

BEGIN TRANSACTION ALTER TABLE COMMIT TRANSACTION CREATE INDEX CREATE LOGIN CREATE PROCEDURE CREATE TABLE CREATE VIEW DELETE DROP INSERT INSERT SELECT ROLLBACK TRANSACTION SAVE TRANSACTION SELECT UPDATE

Appendix C. T-SQL Datatypes

String Datatypes Numeric Datatypes Date and Time Datatypes Binary Datatypes Other Datatypes

Appendix D. T-SQL Reserved Words Index

← Prev
Back
Next →

← Prev
Back
Next →