Some Useful SQL Queries for Software Testers

Syed Aslam Basha here. I am a tester on the Information Security Tools team.

As a tester, apart from UI testing I test DB for integrity. Our boss is encouraging us to share tips that save us time on the team so here are a few of mine. In this blog post I want to highlight some SQL queries I use all the time.

• Query to select usernames where the username is duplicate

  •     1: Select distinct t1.username from tooluser t1 where (Select Count(t2.username) from tooluser t2 Where t1.username= t2.username ) > 1 
    

• Query to delete a single row from a table which has duplicate data

  •     1: delete top 1 from tooluser where id=1 
    

• Query to check Number of Partitions in the given fact table

  •     1: select * from sys.partitions where object_id=(select object_id from sys.tables where name='FactFWProxy') 
        2: select * from sys.partition_range_values; 
    

• Find out common records in tables A and B which are in different databases

      1: declare @A table(username varchar(50))
      2: declare @B table(username varchar(50)) 
      3:  
      4: insert into @A
      5: select distinct a.UserName from suatest.dbo.UR a 
      6: insert into @B
      7: select distinct b.UserAlias from sua.dbo.suauser b 
      8:  
      9: select a.username, b.username from @A a
     10: left join @B b on a.username = b.username
  

• Find out version number of SQL

      1: select @@version
  

• Convert datatime into mm/dd/yyyy format

      1: CONVERT(datetime,'2007-03-03',101) 
  

• Display definition of SP using command

      1: exec sp_helptext AddCategory
  

• Truncating load test results tables

    1: truncate table LoadTestBrowsers
    2: truncate table LoadTestCase
    3: truncate table LoadTestMessage
    4: truncate table LoadTestNetworks
    5: truncate table LoadTestPageDetail
    6: truncate table LoadTestPageSummaryByNetwork
    7: truncate table LoadTestPageSummaryData
    8: truncate table LoadTestPerformanceCounter
    9: truncate table LoadTestPerformanceCounterCategory
   10: truncate table LoadTestPerformanceCounterInstance
   11: truncate table LoadTestPerformanceCounterSample
   12: truncate table LoadTestRun
   13: truncate table LoadTestRunInterval
   14: truncate table LoadTestScenario
   15: truncate table LoadTestSqlTrace
   16: truncate table LoadTestTestDetail
   17: truncate table LoadTestTestSummaryData
   18: truncate table LoadTestThresholdMessage
   19: truncate table LoadTestTransactionDetail
   20: truncate table LoadTestTransactionSummaryData
   21: truncate table WebLoadTestErrorDetail
   22: truncate table WebLoadTestRequestMap
   23: truncate table WebLoadTestTransaction
   24: truncate table LoadTestRunAgent 

Modify the above script parameters appropriately to use it successfully!

- Syed