T-SQL in One Hour a Day, Sams Teach Yourself

Alison Balter is the president of InfoTech Services Group, Inc., a computer consulting firm based in Venice Beach, California. Alison is a highly experienced independent trainer and consultant specializing in Windows applications training and development. During her 30 years in the computer industry, she has trained and consulted with many corporations and government agencies. Since Alison founded InfoTech Services Group, Inc. (formerly InfoTechnology Partners) in 1990, its client base has expanded to include major corporations and government agencies such as Cisco, Shell Oil, Accenture, AIG Insurance, Northrop, the Drug Enforcement Administration, Prudential Insurance, Transamerica Insurance, Fox Broadcasting, the United States Navy, the United States Marines, the University of Southern California (USC), Massachusetts Institute of Technology (MIT), and others. Alison is the author of more than 300 internationally marketed computer training videos, including 18 Access 2000 videos, 35 Access 2002 videos, 15 Access 2003 videos, a complete series of both user and developer videos on Access 2007, and Access 2010 and Access 2013 user videos. Alison travels throughout North America giving training seminars in Microsoft Access and Microsoft SQL Server. Alison is also author of 14 books published by Sams Publishing including: Alison Balter's Mastering Access 95 Development, Alison Balter's Mastering Access 97 Development, Alison Balter's Mastering Access 2000 Development, Alison Balter's Mastering Access 2002 Desktop Development, Alison Balter's Mastering Access 2002 Enterprise Development, Alison Balter's Mastering Microsoft Access Office 2003, Teach Yourself Microsoft Office Access 2003 in 24 Hours, Access Office 2003 in a Snap, Alison Balter's Mastering Access 2007 Development, a power user book on Microsoft Access 2007, Using Access 2010, Access 2013 Absolute Beginner's Guide, and Teach Yourself SQL Express 2005 in 24 Hours. An active participant in many user groups and other organizations, Alison is a past president of the Independent Computer Consultants Association of Los Angeles and of the Los Angeles Clipper Users' Group. She is also past president of the Ventura County Professional Women's Network. Alison is a Microsoft Access MVP and was selected as Ventura County Woman Business Owner of the Year for 2012/2013. On a personal note, Alison keeps herself busy skiing, taking yoga classes, running, walking, lifting weights, hiking, and traveling. She most enjoys spending time with her husband, Dan, their daughter Alexis, and their son Brendan. Contact Alison via Alison@techismything.com or visit InfoTech Services Group's website at www.TechIsMyThing.com.

Introduction 1

1: Database Basics 5

What Is a Database? 5

What Is a Table? 5

What Is a Database Diagram? 6

What Is a View? 7

What Is a Stored Procedure? 8

What Is a User-Defined Function? 9

What Is a Trigger? 10

2: SQL Server Basics 13

Versions of SQL Server 2014 Available 13

SQL Server Components 16

Introduction to Microsoft SQL Server Management Studio 19

Connecting to a Database Server 25

Installing the Sample Files 27

3: Creating a SQL Server Database 33

Creating the Database 33

Defining Database Options 36

The Transaction Log 39

Attaching to an Existing Database 40

4: Working with SQL Server Tables 45

Creating SQL Server Tables 45

Adding Fields to the Tables You Create 46

Working with Constraints 50

Creating an Identity Specification 56

Adding Computed Columns 57

Working with User-Defined Data Types 58

Adding and Modifying Indexes 60

Saving Your Table 64

5: Working with Table Relationships 67

An Introduction to Relationships 67

Creating and Working with Database Diagrams 70

Working with Table Relationships 77

Designating Table and Column Specifications 79

Adding a Relationship Name and Description 81

Determining When Foreign Key Relationships Constrain the

Data Entered in a Column 81

Designating Insert and Update Specifications 84

6: Getting to Know the SELECT Statement 89

Introducing T-SQL 89

Working with the SELECT Statement 90

Adding on the FROM Clause 92

Including the WHERE Clause 93

Using the ORDER BY Clause 101

7: Taking the SELECT Statement to the Next Level 105

Adding the DISTINCT Keyword 105

Working with the FOR XML Clause 107

Working with the GROUP BY Clause 109

Including Aggregate Functions in Your SQL Statements 110

Taking Advantage of the HAVING Clause 117

Creating Top Values Queries 118

8: Building SQL Statements Based on Multiple Tables 121

Working with Join Types 121

9: Powerful Join Techniques 129

Utilizing Full Joins 129

Taking Advantage of Self-Joins 130

Exploring the Power of Union Queries 133

Working with Subqueries 136

Using the INTERSECT Operator 137

Working with the EXCEPT Operator 138

10: Modifying Data with Action Queries 141

The UPDATE Statement 141

The INSERT Statement    143

The SELECT INTO Statement 145

The DELETE Statement 146

The TRUNCATE Statement 147

11: Getting to Know the T-SQL Functions 149

Working with Numeric Functions 149

Taking Advantage of String Functions 151

Exploring the Date/Time Functions 163

Working with Nulls 170...