cookies-icon

Our website uses cookies. By using this website, you agree to their use in accordance with the browser settings. You can modify your browser settings on your own. For more information see our Privacy Policy.

Course

Basic

Common Functions in MS SQL Server

Learn how to use common T-SQL functions to process various types of data with ease. Soon, you’ll be able to modify textual data, perform calculations, round numbers up or down, change date and time formats, and even adjust time zone info!

Table of contents

14 free interactive coding challenges

Lifetime access

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

limited to this course only

$39

Buy now

Want a better deal?

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

Get unlimited lifetime access to all 65 present and future courses

Save $450

Unlimited lifetime access

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

to all 65 present and future courses

$149

$599

75% OFF

Buy now
Details
149

Interactive exercises

10 h

Estimated time

2183

Users enrolled

☆☆☆☆☆
★★★★★

433 ratings

Description Table of contents Reviews Start learning

T-SQL, which is used in Microsoft SQL Server, has many built-in functions. Understanding T-SQL functions is essential to anyone who wants to work with SQL Server. Data analysts use these functions to prepare accurate reports. SQL developers and software developers use them to write concise and readable queries. In both cases, T-SQL functions make it easier to get the right results!

The course offers 150 interactive exercises. You’ll read clear explanations on what each SQL Server function does, and then you’ll write your own T-SQL queries to solve realistic problems. The practice exercises are fun and engaging, and they’ll give you confidence in using T-SQL.

Cover image for the course 'Common Functions in MS SQL Server'

Try our T-SQL course and become proficient in SQL Server functions!

What Do You Need to Take This Course?

  • A web browser and an Internet connection
  • Some understanding of T-SQL, such as the concepts presented in our T-SQL Basics for SQL Server course*

This Course Will Teach You How To:

  • Recognize and work with text, numeric, and date and time data
  • Use SQL Server text functions to search, split, combine, and change strings
  • Perform complicated calculations with SQL Server numeric functions
  • Select the right T-SQL function to get and change date and time data
  • Handle NULLs safely and effectively
  • Change data from one data type to another in T-SQL

Plus, you'll get to practice your skills on 149 interactive exercises.

Who Should Take This Course?

  • Anyone who wants to learn about common SQL Server functions
  • Data analysts who need to create complex reports
  • SQL Server developers who want to become more proficient in SQL Server functions
  • Students taking classes in relational databases using SQL Server
  • People interviewing for jobs where they will work with SQL Server
  • Business SQL Server users who want to improve their query-writing skills

What's in it for me?

  • 149 interactive exercises. Learn at your own pace, from anywhere and anytime. Interact with hands-on exercises for improved retention.
  • Lifetime access to the course. When you purchase the course, you’ll get instant personal access to all of its content.
  • Certificate of completion. After you successfully finish all of the exercises, you’ll get a downloadable PDF certificate to showcase your accomplishment.
  • 30-day money back guarantee. If you’re not satisfied with the quality of the course, you can get a refund within 30 days of your purchase.
  • Hints for the exercises. You can ask questions and share insights with other members of our community through the Disqus tab. You can also drop us a line directly, and we’ll be more than happy to answer! 😉

*The material in Common Functions in MS SQL Server is intended for intermediate users. To complete this course, you’ll need to know:

  • How to select from a single table, including writing complex WHERE conditions
  • How to JOIN tables
  • How GROUP BY and HAVING work

Table of contents

Free

Paid content

0%

Course progress

0/149

Exercises completed

1.

Intro Quiz

In this course, we assume you know the basics of T-SQL: selecting information from a single table, joining queries, and grouping results. Take the test to see if you know SQL Server well enough to continue with the course.

0/7
Start now

Introductory quiz

Introduction
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Congratulations

2.

String functions

Learn the basic SQL Server text functions to transform text values and make your reports look pretty.

0/30
Start now

Introduction

Introduction
The Copywriter table
The Slogan table

Concatenation

The concatenation (+) operator
The CONCAT() function
The CONCAT() function vs. the + operator
The CONCAT_WS() functions

Simple text functions

The LEN() function
Practicing LEN()
The DATALENGTH() function
The LOWER() and UPPER() functions
The LTRIM() and RTRIM() functions
The TRIM() function
The TRIM() function with characters
Text patterns: % and _
Text patterns: [ ]
Text functions - practice time!

How to modify Strings

The SUBSTRING() function
The LEFT() and RIGHT() functions
The CHARINDEX() function
The REVERSE() function
The REPLACE() function
The TRANSLATE() function
The STUFF() function
The REPLICATE() function

Summary

Summary
Exercise 1
Exercise 2
Exercise 3
Congratulations

3.

Numeric functions

Master numeric functions in SQL Server. Learn about the caveats of mathematical operators in T-SQL, various rounding functions, and computing proportions.

0/27

Review

Introduction
Get to know the Character table
First, a quick review
Numeric functions: concatenation
Numeric functions: STR()

So you think you can count?

Problems with subtraction
Binary arithmetic is not exact
Problems with division
Integer division
New keyword: CAST()
The CONVERT() function
Division by zero, part one
Division by zero, part two
Getting rid of division by zero
The modulo operator (%)

Rounding functions and more

Numeric functions: ROUND()
Precision in ROUND()
Other rounding functions
Numeric functions: FLOOR()
Numeric functions: ABS()
Numeric functions: SQRT()

Review and practice

Summary
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Congratulations

4.

Date and time

Study date and time functions and learn how to select events from a period of time.

0/54

Introduction

Introduction
The Route table
The Aircraft table
The Flight table

Dealing with dates

Dates in T-SQL
DATE: The T-SQL default format
Using logical operators with dates
Using BETWEEN with dates
Sorting dates with ORDER BY
Practice time!

Working with time data

Time in T-SQL
Using logical operators with time
Using BETWEEN with time
Sorting by TIME columns
Practice time!

Date and time date

Date and time
Date and time data types
Using date and time with constant values
Using BETWEEN with date and time data
Sorting date and time data with ORDER BY
Practice time!

Extracting dates and times

The YEAR(), MONTH(), and DAY() functions
Using YEAR(), MONTH(), and DAY() with aggregate functions
Using the DATEPART() function
Using the DATEPART() function in grouping
The DATENAME() function
Practice time!

Doing arithmetic with dates

The DATEADD() function
The DATEADD() to subtract dates
The DATEDIFF() function
The EOMONTH() function
Practice time!

Converting date and time data

The CAST() function
The CONVERT() function
The AT TIME ZONE function
The FORMAT() function
Practice time!

Current date and time data

Getting the current date and time in T-SQL
The CURRENT_TIMESTAMP and GETDATE() functions
The GETUTCDATE() function
High-precision date and time functions
Current date and time
Current date and time in queries
Current date and time in queries—additional practice
What we've learned so far

Building date and time data from parts

The DATEFROMPARTS() function
Other functions

Summary and review

Summary
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Congratulations

5.

Other T-SQL Functions

Learn about SQL Server functions working with NULLs and other useful functions.

0/19

Introduction

Introduction
The Product table
A quick review of NULLs

The ISNULL() function

The ISNULL() function
Using ISNULL() with aggregate functions
Using ISNULL() in the WHERE clause

The COALESCE() function

New function: COALESCE()
Data types in COALESCE ()
Using conversion with COALESCE ()
Using COALESCE() with other functions
Using COALESCE() with calculations
Using COALESCE() with multiple parameters

The NULLIF() function

The NULLIF() function
NULLIF() – practice 1
NULLIF() – practice 2

Summary

Summary
Exercise 1
Exercise 2
Congratulations

6.

Final quiz

Check your knowledge of SQL Server functions in this quiz.

0/12

Quiz

Introduction
The Person table
Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5
Exercise 6
Exercise 7
Exercise 8
Exercise 9
Congratulations

Reviews (16)

Average rating

4.81/5

☆☆☆☆☆
★★★★★

433 ratings

Details

5 stars

86%

4 stars

13%

3 stars

0%

2 stars

0%

1 stars

1%