Part 1: Why Should Testers Know SQL?

---

Many software applications are architected into three logical layers: the User Interface (UI), the Business Logic, and the Data Layer.

A very popular implementation of this architecture is called the Model-View-Controller pattern. A tester should be able to work with tools and concepts that are specific to each layer. When a tester can help pinpoint which layer an error is occurring in, its beneficial to the whole development team. This blog will be focusing on the data layer and the importance of SQL skills for testers and will walk through some common scenarios for testers and work through a solution.

Let’s get started with the first scenario:

Tester Level: Junior

Scenario:

Users of a website can either be a super user or regular user. The UI for super users has a lot more functionality represented with more visible menus and menu items. Tester Terry needs to be able to switch her User account back and forth from regular user to super user in order to work through different test cases in the test plan.

Architect Andrew explains to Tester Terry how to change a user’s rights in the database:

“Just update the integer value in column Level in table User_Rights for the row where the value in column Rights equals Administration. The integer should be set to 1 for regular user or 2 for super user. And by the way, there is foreign key in this table from the Users table so make sure you only change the admin rights for yourself!”

Terry responds, “No problem dude” and goes back to her desk – with 100% confidence that she can accomplish the task because she had just finished a basic SQL tutorial.

Resolution:

Terry needs to write and execute a set of SQL statements to make this happen. She will probably end up running this SQL many times during the project. What tools and SQL concepts will she use?

The tool needed to write and execute SQL statements against a SQL database is called a SQL administration client. There are many varieties of SQL clients to choose from but typically they will use the same tool the database developers use. I typically use Microsoft’s SQL Management Studio since many of our applications use Microsoft SQL Server as the database. Just google “SQL administration clients” to see a ton of other tools available.

Tester Terry will need some additional info from the database team to be able run SQL against the database such as the server name or IP address, the database name, a database username, and its password.

From parsing what Architect Andrew said, Tester Terry knows the following SQL concepts will be involved in a solution: tables, column, integer, primary and foreign keys, selects, updates, where clauses, and joins. (BTW, there are many online tutorials to learn and practice these concepts.)

It’s always wise to do some discovery work by examining the column names and rows in the tables you will be working with. Execute a few select statements with top which will limit the number rows displayed if there are lots of rows in the table. Use an * to see all columns.

Select top 30 * from Users
Select top 10 * from User_Rights

Before writing your SQL, I like to advise people to think about the logical steps needed to accomplish the task and write a quick plan in pseudocode (English fragments). It then becomes easy to convert it to SQL commands.

1. Find the row that represents you in the User table and get its primary key.
2. Use that primary key in step 1 to find the correct row in the User_Rights table. Don’t forget to make sure it’s also the “administration” rights row.
3. For that row, change the value in the “Level” column to 1 or 2.

Always write update and delete SQL as just select statements first to make sure you are grabbing the correct row to process.

Let’s start converting the plan to SQL. Column names are in [].

1. Select [primaryKey] from Users where [lastName]=’Hakimian’ and [firstName]=’Daniel’
2. Select [level] from User_Rights where [rights]=’Administration’ and [fkUser]=Users.primaryKey
3. Update User_Rights set [level]= 2

If a where clause has a comparison of column values from two different table, it’s an indicator that you can combine your select as one SQL statement using a join.

select

[level]

from

User_Rights left join Users on User_Rights.fkUser = User.primaryKey

where

User_Rights.[rights]=’Administration’ and User.[lastName]=’Hakimian’ and User.[firstName]=’Daniel’

Execute the select SQL a few times and when you are confident that it works correctly, convert it to SQL update statement.

update

User_Rights

set

[level] = 2

from

User_Rights left join Users on User_Rights.[fkUser] = User.[primaryKey]

where

User_Rights.[rights]=’Administration’ and User.[lastName]=’Hakimian’ and User.[firstName]=’Daniel’

This is one of many possible solutions to write the update in SQL. Whichever option you choose, I recommend you create and stick to a solid process that you can use to write simple to more complex SQL.

1. Create a quick logical plan
2. Write and test your SQL snippets as select statements prior to using them in updates and deletes
3. Optimize your SQL statements
4. Save your final working SQL in a file so you can reuse it throughout the project.

Now Tester Terry will be able to quickly switch her own user account back and forth from regular user to super user as needed during the rest of the project!

Part 2 in this series will hone in on a scenario a Senior Tester might see during a project.

Part 3 covers a scenario for a Senior Development Engineer in Test.

Original article posted here.