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