Intro

T-SQL on Azure — It' a kind of magic in Data Engineering world.

I love Queen, but could you use T-SQL as Data Engineer? I think, I can help you.

You can use loops and conditional statements in T-SQL. Only ?

The answer is NO!

But what else ?

So, we can draw maps, prepare and query XML files, create reports and much more.

Start

If you want to follow me please read Microsoft Article about how to design a relational database in a single database within Azure SQL Database https://docs.microsoft.com/bs-latn-ba/azure/sql-database/sql-database-design-first-database. In this tutorial, you learn how to use the Azure portal and SQL Server Management Studio (SSMS) to:

• Create a single database using the Azure portal
• Set up a server-level IP firewall rule using the Azure portal
• Connect to the database with SSMS
• Create tables with SSMS
• Bulk load data with BCP
• Query data with SSMS

Here I am using the same database. So, create database or only read the article and let's go!

Declare a variable

Let me show you how to declare something simple. I would like to declare a variable.

/****** Declare variable varchar(255)  ******/
declare @theyoungest varchar(255);
/****** Gets the youngest person's full name  ******/
SELECT top(1)
  @theyoungest = CONCAT_WS('-',LastName, FirstName)
  FROM [dbo].[Person]
  order by DateOfBirth desc;
  print @theyoungest;

Output: Beach-Cayden

First I declared variable @theyoungest, then I wrote simple SQL query which took LastName and FirstName.

You can also see that I implemented function CONCAT_WS. Thanks that I connected two text field together. I printed variable in the end.

Conditional statements

Let me show you how to create IF statement

/****** Declare variable DATE  ******/
declare @theyoungestDate DATE;
/****** Statement assigns Date Of Birth to @theyoungestDate ******/
SELECT top (1)
  @theyoungestDate = DateOfBirth
  FROM [dbo].[Person]	
  order by DateOfBirth desc;
print @theyoungestDate

I created variable @theyoungestDate type DATE, then I assigned DateOfBirth to variable.

Now, I want to show you the essence of IF statement in T-SQL

/****  Declare variable INT and set numer of date value  ****/
declare @numerOfDay INT = DATEPART(weekday, @theyoungestDate);
/***** Conditional statement IF which checks if the youngest person was born on sunday  *****/
IF @numerOfDay = 1 
	BEGIN
		PRINT 'THIS PERSON WAS BORN ON SUNDAY ;)'
	END
ELSE
	BEGIN
		PRINT 'THIS PERSON WAS NOT BORN ON SUNDAY ;)'
	END

Output: 'THIS PERSON WAS NOT BORN ON SUNDAY ;)'

I think that, you can easily understand IF from comments in T-SQL code, but the most important thing is BEGIN, END. Body of IF and ELSE always begins from BEGIN. You have to add END in the end of IF or END.

/****** Declare variable varchar(255)  ******/
declare @dayName varchar(255)
/********  Conditional statement case   *********/
SELECT @dayName=
  CASE   
                WHEN @numerOfDay = 1 THEN 'SUNDAY'   
	  WHEN @numerOfDay = 2 THEN 'MONDAY'
	  WHEN @numerOfDay = 3 THEN 'TUESDAY'
	  WHEN @numerOfDay = 4 THEN 'WEDNESDAY'
	  WHEN @numerOfDay = 5 THEN 'THURSDAY'
	  WHEN @numerOfDay = 6 THEN 'FRIDAY'
	  WHEN @numerOfDay = 7 THEN 'SATURDAY'
   END 
   print 'THIS PERSON WAS BORN ON ' + @dayName

Output: 'THIS PERSON WAS BORN ON TUESDAY ;)'

Above you can see how I created CASE statement which assigned name of day to variable @dayName.

You need to notice that instead of BEGIN I implemented CASE in my T-SQL code.

While loop

My next program iterates and put value into table, just look:

/*** Program iterates and put value into table TestWhile ***/
create table TestWhile (id INT)
DECLARE @i int=1
    while @i <1000
    begin
        insert TestWhile values (@i)
        set @i+=1
    end

To use while loop, you need to declare auxiliary variable @i which we can use as an index of loop.

The most important thing is: set @i+=1, this increments index.

Conclusion

You can see now, how T-SQL might be helpfully with database(Data Engineer). Have you more experience with T-SQL, just leave comment.

So, every time when you need to write complicated query in SQL

Just think about T-SQL and use as Data Engineer

Thank for your attention! Please put more smile in digital world ;)