Common Functions in MS SQL Server

Table of contents

1. 1. Intro Quiz

   0/7

   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.

   List of exercises Start now
   1. Introductory quiz
      1. Introduction
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Exercise 4
      6. Exercise 5
      7. Congratulations

2. 2. String functions

   0/30

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

   List of exercises Start now
   1. Introduction
      1. Introduction
      2. The Copywriter table
      3. The Slogan table
   2. Concatenation
      1. The concatenation (+) operator
      2. The CONCAT() function
      3. The CONCAT() function vs. the + operator
      4. The CONCAT_WS() functions
   3. Simple text functions
      1. The LEN() function
      2. Practicing LEN()
      3. The DATALENGTH() function
      4. The LOWER() and UPPER() functions
      5. The LTRIM() and RTRIM() functions
      6. The TRIM() function
      7. The TRIM() function with characters
      8. Text patterns: % and _
      9. Text patterns: [ ]
      10. Text functions - practice time!
   4. How to modify Strings
      1. The SUBSTRING() function
      2. The LEFT() and RIGHT() functions
      3. The CHARINDEX() function
      4. The REVERSE() function
      5. The REPLACE() function
      6. The TRANSLATE() function
      7. The STUFF() function
      8. The REPLICATE() function
   5. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Congratulations

3. 3. Numeric functions

   0/27

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

   List of exercises Buy course
   1. Review
      1. Introduction
      2. Get to know the Character table
      3. First, a quick review
      4. Numeric functions: concatenation
      5. Numeric functions: STR()
   2. So you think you can count?
      1. Problems with subtraction
      2. Binary arithmetic is not exact
      3. Problems with division
      4. Integer division
      5. New keyword: CAST()
      6. The CONVERT() function
      7. Division by zero, part one
      8. Division by zero, part two
      9. Getting rid of division by zero
      10. The modulo operator (%)
   3. Rounding functions and more
      1. Numeric functions: ROUND()
      2. Precision in ROUND()
      3. Other rounding functions
      4. Numeric functions: FLOOR()
      5. Numeric functions: ABS()
      6. Numeric functions: SQRT()
   4. Review and practice
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Exercise 4
      6. Congratulations

4. 4. Date and time

   0/54

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

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. The Route table
      3. The Aircraft table
      4. The Flight table
   2. Dealing with dates
      1. Dates in T-SQL
      2. DATE: The T-SQL default format
      3. Using logical operators with dates
      4. Using BETWEEN with dates
      5. Sorting dates with ORDER BY
      6. Practice time!
   3. Working with time data
      1. Time in T-SQL
      2. Using logical operators with time
      3. Using BETWEEN with time
      4. Sorting by TIME columns
      5. Practice time!
   4. Date and time date
      1. Date and time
      2. Date and time data types
      3. Using date and time with constant values
      4. Using BETWEEN with date and time data
      5. Sorting date and time data with ORDER BY
      6. Practice time!
   5. Extracting dates and times
      1. The YEAR(), MONTH(), and DAY() functions
      2. Using YEAR(), MONTH(), and DAY() with aggregate functions
      3. Using the DATEPART() function
      4. Using the DATEPART() function in grouping
      5. The DATENAME() function
      6. Practice time!
   6. Doing arithmetic with dates
      1. The DATEADD() function
      2. The DATEADD() to subtract dates
      3. The DATEDIFF() function
      4. The EOMONTH() function
      5. Practice time!
   7. Converting date and time data
      1. The CAST() function
      2. The CONVERT() function
      3. The AT TIME ZONE function
      4. The FORMAT() function
      5. Practice time!
   8. Current date and time data
      1. Getting the current date and time in T-SQL
      2. The CURRENT_TIMESTAMP and GETDATE() functions
      3. The GETUTCDATE() function
      4. High-precision date and time functions
      5. Current date and time
      6. Current date and time in queries
      7. Current date and time in queries—additional practice
      8. What we've learned so far
   9. Building date and time data from parts
      1. The DATEFROMPARTS() function
      2. Other functions
   10. Summary and review
       1. Summary
       2. Exercise 1
       3. Exercise 2
       4. Exercise 3
       5. Exercise 4
       6. Exercise 5
       7. Congratulations

5. 5. Other T-SQL Functions

   0/19

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

   List of exercises Buy course
   1. Introduction
      1. Introduction
      2. The Product table
      3. A quick review of NULLs
   2. The ISNULL() function
      1. The ISNULL() function
      2. Using ISNULL() with aggregate functions
      3. Using ISNULL() in the WHERE clause
   3. The COALESCE() function
      1. New function: COALESCE()
      2. Data types in COALESCE ()
      3. Using conversion with COALESCE ()
      4. Using COALESCE() with other functions
      5. Using COALESCE() with calculations
      6. Using COALESCE() with multiple parameters
   4. The NULLIF() function
      1. The NULLIF() function
      2. NULLIF() – practice 1
      3. NULLIF() – practice 2
   5. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Congratulations

6. 6. Final quiz

   0/12

   Check your knowledge of SQL Server functions in this quiz.

   List of exercises Buy course
   1. Quiz
      1. Introduction
      2. The Person table
      3. Exercise 1
      4. Exercise 2
      5. Exercise 3
      6. Exercise 4
      7. Exercise 5
      8. Exercise 6
      9. Exercise 7
      10. Exercise 8
      11. Exercise 9
      12. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more