You are previewing Sams Teach Yourself T-SQL in One Hour a Day.
O'Reilly logo
Sams Teach Yourself T-SQL in One Hour a Day

Book Description

Master T-SQL database design, development, and administration the easy way–hands-on!

In just one hour a day, you’ll build all the skills you need to create effective database applications with T-SQL and SQL Server. With this complete tutorial, you’ll quickly master the basics and then move on to more advanced features and concepts:

  • Learn the fundamentals of T-SQL from the ground up, one step at a time

  • Succeed with the newest versions of T-SQL, SQL Server, and SQL Server Management Studio

  • Use T-SQL effectively as both an application developer and DBA

  • Master powerful stored procedures, triggers, transactions, and user-defined functions (UDFs)

  • Systematically optimize and secure your SQL Server databases

  • Learn on your own time, at your own pace

  • No previous T-SQL or database programming experience required

  • Learn how to design efficient, reliable SQL Server databases

  • Define efficient tables, table relationships, fields, and constraints

  • Make the most of T-SQL’s SELECT and UPDATE statements

  • Work effectively with simple and complex views and joins

  • Master stored procedure techniques every developer should know

  • Build and use powerful User-Defined Functions (UDFs)

  • Secure databases with authentication, roles, permissions, and principals

  • Configure, maintain, and tune SQL Server for maximum reliability, performance, and value

  • Back up, restore, and audit databases

  • Optimize databases with the SQL Server Profiler, System Monitor, and Index Tuning Wizard

  • Leverage valuable insight and time saving techniques from a world renowned database expert

  • Register your book at informit.com/register for access to source code, example files, updates, and corrections as they become available.

    Table of Contents

    1. About This eBook
    2. Title Page
    3. Copyright Page
    4. Table of Contents
    5. About the Author
    6. Dedication
    7. Acknowledgments
    8. We Want to Hear from You!
    9. Reader Services
    10. Introduction
    11. Lesson 1. Database Basics
      1. What Is a Database?
      2. What Is a Table?
      3. What Is a Database Diagram?
      4. What Is a View?
      5. What Is a Stored Procedure?
      6. What Is a User-Defined Function?
      7. What Is a Trigger?
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Quiz Answers
      11. Activities
    12. Lesson 2. SQL Server Basics
      1. Versions of SQL Server 2014 Available
        1. SQL Server 2014 Express Edition
        2. SQL Server 2014 Web Edition
        3. SQL Server 2014 Business Intelligence Edition
        4. SQL Server 2014 Standard Edition
        5. SQL Server 2014 Enterprise Edition
      2. SQL Server Components
        1. SQL Profiler
        2. SQL Server Agent
        3. Database Engine Tuning Advisor
      3. Introduction to Microsoft SQL Server Management Studio
        1. The Databases Node
        2. The Security Node
        3. Server Objects Node
        4. The Replication Node
        5. Management Node
      4. Connecting to a Database Server
      5. Installing the Sample Files
      6. Q&A
      7. Workshop
        1. Quiz
        2. Quiz Answers
      8. Activities
    13. Lesson 3. Creating a SQL Server Database
      1. Creating the Database
      2. Defining Database Options
        1. File Groups
      3. The Transaction Log
      4. Attaching to an Existing Database
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Quiz Answers
      8. Activities
    14. Lesson 4. Working with SQL Server Tables
      1. Creating SQL Server Tables
      2. Adding Fields to the Tables You Create
      3. Working with Constraints
        1. Primary Key Constraints
        2. Foreign Key Constraints
        3. Default Constraints
        4. Not Null Constraints
        5. Check Constraints
        6. Rules
        7. Unique Constraints
      4. Creating an Identity Specification
      5. Adding Computed Columns
      6. Working with User-Defined Data Types
      7. Adding and Modifying Indexes
      8. Saving Your Table
      9. Summary
      10. Q&A
      11. Workshop
        1. Quiz
        2. Quiz Answers
        3. Activities
    15. Lesson 5. Working with Table Relationships
      1. An Introduction to Relationships
        1. One-to-Many
        2. One-to-One
        3. Many-to-Many
      2. Creating and Working with Database Diagrams
        1. Creating a Database Diagram
        2. Adding Tables to a Database Diagram
        3. Removing Tables from a Database Diagram
      3. Working with Table Relationships
        1. Adding a Foreign Key Relationship
        2. Deleting a Foreign Key Relationship
      4. Designating Table and Column Specifications
      5. Adding a Relationship Name and Description
      6. Determining When Foreign Key Relationships Constrain the Data Entered in a Column
      7. Designating Insert and Update Specifications
        1. The Delete Rule
        2. The Update Rule
      8. Summary
      9. Q&A
      10. Workshop
        1. Quiz
        2. Quiz Answers
      11. Activities
    16. Lesson 6. Getting to Know the SELECT Statement
      1. Introducing T-SQL
      2. Working with the SELECT Statement
        1. Selecting All Fields
        2. Selecting Specific Fields
        3. Adding an Expression
      3. Adding on the FROM Clause
        1. Using Table Aliases
      4. Including the WHERE Clause
        1. Rules When Filtering Data
        2. Working with Dates and Times
        3. Using IN and NOT
      5. Using the ORDER BY Clause
        1. Changing the Sort Direction
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Quiz Answers
      9. Activities
    17. Lesson 7. Taking the SELECT Statement to the Next Level
      1. Adding the DISTINCT Keyword
      2. Working with the FOR XML Clause
      3. Working with the GROUP BY Clause
      4. Including Aggregate Functions in Your SQL Statements
        1. Using the COUNT Function
        2. Working with the COUNT_BIG Function
        3. Exploring the SUM Function
        4. Working with the AVG Function
        5. Using the MIN Function
        6. Using the MAX Function
      5. Taking Advantage of the HAVING Clause
      6. Creating Top Values Queries
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Quiz Answers
      10. Activities
    18. Lesson 8. Building SQL Statements Based on Multiple Tables
      1. Working with Join Types
        1. Using Inner Joins
        2. Creating Outer Joins
      2. Summary
      3. Q&A
      4. Workshop
        1. Quiz
        2. Quiz Answers
      5. Activities
    19. Lesson 9. Powerful Join Techniques
      1. Utilizing Full Joins
      2. Taking Advantage of Self-Joins
      3. Exploring the Power of Union Queries
      4. Working with Subqueries
      5. Using the INTERSECT Operator
      6. Working with the EXCEPT Operator
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Quiz Answers
      10. Activities
    20. Lesson 10. Modifying Data with Action Queries
      1. The UPDATE Statement
      2. The INSERT Statement
      3. The SELECT INTO Statement
      4. The DELETE Statement
      5. The TRUNCATE Statement
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Quiz Answers
        3. Activities
    21. Lesson 11. Getting to Know the T-SQL Functions
      1. Working with Numeric Functions
        1. Using the IsNumeric Function
        2. Exploring the ROUND Function
      2. Taking Advantage of String Functions
        1. Using the LEFT Function
        2. Working with the RIGHT Function
        3. Exploring the LEN Function
        4. Using the REPLACE Function
        5. Using the REVERSE Function
        6. Using the REPLICATE Function
        7. Taking Advantage of the STUFF Function
        8. Using the SPACE Function
        9. Using the SUBSTRING Function
        10. Using the LOWER Function
        11. Using the UPPER Function
        12. Working with the LTRIM Function
        13. Working with the RTRIM Function
      3. Exploring the Date/Time Functions
        1. Using the GETDATE Function
        2. Learning About the MONTH Function
        3. Exploring the DAY Function
        4. Working with the YEAR Function
        5. Exploring the Powerful DATEPART Function
        6. Using the DATENAME Function
        7. Working with the DATEADD Function
        8. Using the DATEDIFF Function
      4. Working with Nulls
        1. Exploring the ISNULL Function
        2. Taking Advantage of the NULLIF Function
        3. Working with the COALESCE Function
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Quiz Answers
      8. Activities
    22. Lesson 12. Working with SQL Server Views
      1. An Introduction to Views
        1. Creating a Simple View
        2. Using the Microsoft SQL Server Management Studio Query Builder to Create a View
      2. Using T-SQL to Create or Modify a View
      3. Summary
      4. Q&A
      5. Workshop
        1. Quiz
        2. Quiz Answers
        3. Activities
    23. Lesson 13. Using T-SQL to Design SQL Server Stored Procedures
      1. The Basics of Working with Stored Procedures
        1. Designing a Stored Procedure in the Query Editor
        2. Using T-SQL to Create a Stored Procedure
        3. Executing the Stored Procedures You Build
      2. Declaring and Working with Variables
      3. Controlling the Flow
        1. Using IF...ELSE
        2. Working with BEGIN...END
        3. Exploring GOTO, RETURN, and Labels
        4. Working with the CASE Statement
        5. Exploring the WHILE Statement
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Quiz Answers
      7. Activities
    24. Lesson 14. Stored Procedure Techniques Every Developer Should Know
      1. The SET NOCOUNT Statement
      2. Using the @@ Functions
        1. Using the @@RowCount System Variable
        2. Using the @@TranCount System Variable
        3. Using the @@Identity System Variable
        4. Using the @@Error System Variable
      3. Working with Parameters
        1. Input Parameters
        2. Output Parameters
      4. Errors and Error Handling
        1. Handling Runtime Errors
        2. Returning Success and Failure Information from a Stored Procedure
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Quiz Answers
      8. Activities
    25. Lesson 15. Power Stored Procedure Techniques
      1. Modifying Data with Stored Procedures
        1. Inserting Data
        2. Updating Data
        3. Deleting Data
      2. Stored Procedures and Transactions
        1. Types of Transactions
        2. Implementing Transactions
      3. Summary
      4. Q&A
      5. Workshop
        1. Quiz
        2. Quiz Answers
      6. Activities
    26. Lesson 16. Stored Procedure Special Topics
      1. Stored Procedures and Temporary Tables
      2. Stored Procedures and Cursors
      3. Stored Procedures and Security
      4. Summary
      5. Q&A
      6. Workshop
        1. Quiz
        2. Quiz Answers
      7. Activities
    27. Lesson 17. Building and Working with User-Defined Functions
      1. Scalar Functions
        1. Inline Table-Valued Functions
        2. Multi-Statement Table-Valued Functions
      2. Summary
      3. Q&A
      4. Workshop
        1. Quiz
        2. Quiz Answers
      5. Activities
    28. Lesson 18. Creating and Working with Triggers
      1. Creating Triggers
      2. Creating an Insert Trigger
      3. Creating an Update Trigger
      4. Creating a Delete Trigger
      5. Downsides of Triggers
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Quiz Answers
      9. Activities
    29. Lesson 19. Authentication
      1. The Basics of Security
      2. Types of Authentication
      3. Creating Logins
        1. Adding a Windows Login
        2. Adding a SQL Server Login
        3. Granting Database Access to Logins
        4. Understanding the SA Login
      4. Creating Roles
        1. Types of Roles
        2. Ownership
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Quiz Answers
      8. Activities
    30. Lesson 20. SQL Server Permissions Validation
      1. Types of Permissions
        1. Adding Database Users
        2. Working with Permission Statements
        3. Administering Object Permissions
      2. Getting to Know Table Permissions
      3. Getting to Know View Permissions
      4. Getting to Know Stored Procedure Permissions
      5. Getting to Know Function Permissions
      6. Implementing Column-Level Security
      7. Summary
      8. Q&A
      9. Workshop
        1. Quiz
        2. Quiz Answers
      10. Activities
    31. Lesson 21. Configuring, Maintaining, and Tuning SQL Server
      1. Selecting and Tuning Hardware
        1. Memory—The More RAM, the Better!
        2. Processor
        3. Storage
        4. Network
      2. Configuring and Tuning SQL Server
        1. Memory Options
        2. Processor Options
        3. Security Options
        4. Connections Options
        5. Database Settings Options
        6. Advanced Options
        7. Permissions Options
      3. Summary
      4. Q&A
      5. Workshop
        1. Quiz
        2. Quiz Answers
      6. Activities
    32. Lesson 22. Maintaining the Databases You Build
      1. Backing Up Your Databases
      2. Restoring a Database
      3. The Database Engine Tuning Advisor
        1. Creating a Workload
      4. Creating and Working with Database Maintenance Plans
      5. Summary
      6. Q&A
      7. Workshop
        1. Quiz
        2. Quiz Answers
      8. Activities
    33. Lesson 23. Performance Monitoring
      1. Executing Queries in SQL Server Management Studio
      2. Displaying and Analyzing the Estimated Execution Plan
      3. Adding Indexes to Allow Queries to Execute More Efficiently
      4. Setting Query Options
      5. SQL Server Profiler
      6. Summary
      7. Q&A
      8. Workshop
        1. Quiz
        2. Quiz Answers
      9. Activities
    34. Lesson 24. Installing and Upgrading SQL Server
      1. Installing SQL Server 2014 Enterprise Edition
      2. Installing SQL Server Management Studio
      3. Summary
      4. Q&A
      5. Workshop
        1. Quiz
        2. Quiz Answers
      6. Activities
    35. Index
    36. Code Snippets