Part 3: Why Should Testers Know SQL?
In part 2 of this blog series, we covered a SQL scenario a Senior Tester may encounter during a project.
Here, we will look at a scenario for a SDET (Software Development Engineer in Test).
SQL Covered: Variables, Math Functions, Temp Tables, While loops
Tester Level: SDET (Software Development Engineer in Test)
A new weather application is being designed, which uses hourly wind speeds and directions for prediction purposes. The company making the application wants to run functional tests and load test the application. They need to populate some of the database tables with 2 years of hourly data and ask SDET Sally to do it.
Architect Andrew asks SDET Sally to build an easy and repeatable process that can generate additional records with random values from a predefined set of values whenever needed.
SDET Sally assures the team she can create the test data.
SDET Sally could generate an Excel data file and import it into a SQL table, but she decides it would be easier and more reusable just to create the random data on the fly using a SQL script. She needs to be able to create random decimals, integers and even words in SQL when needed in her SQL scripts.
Let’s listen in on SDET Sally’s thought process as she codes the SQL:
“I need to make sure I remember how the rand() math function works. Let me Google it.”
rand(seed) – takes an optional seed value and returns a float type number from 0 (inclusive) to 1 (exclusive).
“I’ll run this code a few times to see some results:”
“Ok, it generates random decimals with 15 significant values. I usually don’t need that many significant digits so I should round these decimals to the significant digits I want. Let’s try putting this in the SQL round function:”
|Select round( rand() ,4)
Select round( rand() ,5)
Select round( rand() ,1)
Sally can generate random decimals with the correct amount of significant digits, but know she often needs to generate random integers from a subset of valid integers.
“I am going to have transform these random decimals into random integers. Let’s write the code to get random integers between 30 and 75 (inclusive) and then I can create a function out of it once it is tested. I know that multiplying by 10 always shifts the decimal one place to the right, so let’s multiply the random decimal by 30:”
“Ok. I get random decimals back between 0 (inclusive) and 30 (exclusive). Let’s chop off the decimal portion to make them integers:”
|Select floor( rand()*30 )||28
“Perfect, but I need numbers between 30 and 75, lets shift this function up 30 and to the right 36 to generate the correct range of integers:”
|Select floor( rand()*(75-30+1 ) )+ 30||30
“I’ll replace the hardcoded numbers with variables to make it a generic algorithm:”
Declare @lowerBound int, @upperBound int, @randomInteger int
Set @lowerBound=30; set @upperBound=75
Set @randomInteger =floor( rand()*(@upperBound +-@lowerBound + 1 ) ) + @lowerBound
“OK, I can generate random decimals and integers – now I need random words from a list of words. First, I’ll create a temp table with a set of valid words:”
declare @WIND_DIRECTION_TABLE table( Direction varchar(50))
insert into @WIND_DIRECTION_TABLE values(‘North’),(‘South’),(‘East’),(‘West’)
“I just need to select a random row from the table. Like with most things in coding, there are different ways to do the same thing. Some techniques are more efficient than others depending on the size of the word set. I’ll start with an easy one and optimize it later if I have to:”
Declare @randomWord nvarchar(50)
Select Top 1 @randomWord = [Direction]
Order By NEWID()
Here is a homework assignment for the reader: research the NEWID() function and figure out why this works!
“Now that I have my random generator code, I can use the code wherever I need it to create records with random values in certain columns. I can put it in a loop and create as many records as we need for our tests. For now, I’ll make a table variable to test my random generators and loop logic and see how fast it works:”
SDET Sally is satisfied with her code and decides it is a good time to break for dinner.
Can you predict how long it will take to generate these 300,000 records? Make your prediction and then open a SQL client and go find out!