SQL Server 2012, Part 7 of 9: Introduction to T-SQL

Course description

This course will start with a quick overview of the history of SQL, then dive right into using T-SQL. You’ll learn about batches and scripts, how to declare and use variables—including the inevitable data type conversions—as well as the rich supply of operators available. Next you’ll learn about some of the many built-in T-SQL functions, for doing things like working with numbers, string manipulation, as well as global functions for getting state information. You’ll also learn about how to rank results using special functions and the OVER clause.

Each LearnNowOnline training course is made up of Modules (typically an hour in length). Within each module there are Topics (typically 15-30 minutes each) and Subtopics (typically 2-5 minutes each). There is a Post Exam for each Module that must be passed with a score of 70% or higher to successfully and fully complete the course.

Prerequisites

This course assumes that you have at least a basic familiarity with the concept of relational databases and a basic understanding of what SQL Server is and the high-level tools in it, as well as how to create and manage objects using Management Studio. You should also have a basic understanding of how SQL Server implements security, including its authentication and authorization schemes, and how to assign permissions on securable objects to principals.

Meet the expert

Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

Video Runtime

126 Minutes

Time to complete

481 Minutes

Course Outline

Introduction to T-SQL

Introduction to Transact-SQL (13:28)

• Introduction (00:56)
• Overview of Transact-SQL (03:49)
• Batches and Scripts (00:49)
• Demo: DDL Statements (03:45)
• Demo: Variables (03:40)
• Summary (00:28)

Operators and Converting (20:21)

• Introduction (00:38)
• Operators Overview (03:04)
• Demo: Compound Operators (01:49)
• Demo: Data Conversions (04:58)
• Type Coercion and Precedence (01:09)
• Demo: Coercion and Precedence (02:09)
• Demo: Convert with Style (02:24)
• Demo: Enhanced Convert Options (01:03)
• Implicit Conversions (01:26)
• Demo: Convert and Format (01:11)
• Summary (00:24)

Null and Number Functions (25:14)

• Introduction (00:43)
• Using Built-In Functions (01:59)
• Working With Nulls (01:23)
• Demo: Working With Nulls (02:26)
• Demo: IsNull Function (02:40)
• Demo: Null If function (02:31)
• Demo: Coalesce Function (03:09)
• Demo: IsNumeric Function (03:06)
• Demo: Rand Function (02:26)
• Demo: Round Function (02:11)
• Demo: Rand and Round (02:12)
• Summary (00:22)

Functions, Flow, Results

String Functions (21:57)

• Introduction (00:38)
• Demo: REPLACE (02:01)
• Demo: STUFF (01:17)
• Demo: LEN, LEFT and RIGHT (01:42)
• Demo: SUBSTRING (01:08)
• Demo: CHARINDEX (02:57)
• Demo: PATINDEX (04:55)
• Demo: SPACE (01:16)
• Demo: CHAR and ASCII (01:01)
• Demo: LOWER and UPPER (03:46)
• Demo: LTRIM and RTRIM (00:48)
• Summary (00:23)

Date and Global Functions (14:59)

• Introduction (00:32)
• Demo: GETDATE (00:46)
• Demo: DATEPART (01:13)
• Demo: DATENAME (00:42)
• Demo: DATEADD (00:50)
• Demo: DATEDIFF (03:19)
• Demo: Global Variables (00:36)
• Demo: ROWCOUNT (01:01)
• Demo: IDENTITY and TRANCOUNT (03:45)
• Demo: ERROR (01:50)
• Summary (00:22)

Controlling Program Flow (20:31)

• Introduction (00:46)
• Demo: IF Statement (01:29)
• Demo: BEGIN and END Block (02:48)
• Demo: GOTO (01:59)
• Demo: RETURN (01:36)
• Demo: CASE (03:26)
• Demo: CASE Second Syntax (04:43)
• Demo: WHILE (01:51)
• Demo: WAITFOR (01:27)
• Summary (00:22)

Ranking Results (10:22)

• Introduction (00:37)
• Ranking Results (01:10)
• Demo: Ranking Results (04:28)
• Demo: Ranking Examples (03:47)
• Summary (00:18)