QA must learn SQL practical guide: query syntax and application skills for 20 test scenarios

1. Why must QA learn SQL?

• Testing Efficiency: No Need To Wait For The Backend To Help Check The Data, You Can Verify It Yourself In 30 Seconds
• Bug Location: When You See A Front-End Exception, Directly Check The Database To Determine Whether It Is A Front-End Bug Or A Back-End Bug.
• Test Data Preparation: Create And Clean Test Data By Yourself, Do Not Rely On Others
• Regression Verification: After Fixing The Bug, Directly Check The Database To Confirm That The Data Has Been Corrected
• Career Competitiveness: Qas Who Know Sql Have Obvious Advantages In Interviews And Salary Negotiations

2. Environment preparation: Start practicing in 5 minutes

• Online Exercises: Sqlfiddle (Sqlfiddle.Com) Or Db Fiddle (Db-Fiddle.Com) - Open The Browser To Write Sql
• Local Tool: Dbeaver (Free, Supports All Major Databases) - The Most Recommended Database Management Tool By Qa
• Company Environment: Please Ask The Dba Or Backend To Enable Read Only Permissions For The Test Environment. Emphasize That You Only Need Select Permissions And Will Not Change The Data
• Practice Database: Use Sqlite To Build A Local Practice Library, Or Use The Company'S Staging Environment

3. Scenario 1-5: Basic data verification

• Scenario 1 - Verify Whether The Data Is Written: Select * From Orders Where User_Id = 123 Order By Created_At Desc Limit 5; → Confirm Whether There Are New Orders In The Database After Placing The Order
• Scenario 2 - Verify The Correctness Of Field Values: Select Status, Total_Amount, Payment_Method From Orders Where Order_Id = 'Ord-456'; → Check Whether The Order Status, Amount And Payment Method Are Correct
• Scenario 3 - Verify Data Deletion: Select Count(*) From Users Where Email = '[email protected]'; → After Deleting The Account, Confirm That The Data Has Been Removed From The Database (The Result Should Be 0)
• Scenario 4 - Verify Data Update: Select Name, Email, Updated_At From Users Where User_Id = 789; → Confirm That The Value In The Database Has Been Updated After Modifying Personal Data
• Scenario 5 - Validate Unique Constraints: Select Email, Count(*) From Users Group By Email Having Count(*) > 1; → Check For Duplicate Emails (Result Should Be Empty)

4. Scenario 6-10: Test data preparation and cleaning

• Scenario 6 - Query Available Test Accounts: Select User_Id, Email, Status From Users Where Email Like 'Test%' And Status = 'Active' Limit 10; → Find Available Test Accounts
• Scenario 7 - Check The Test Data Status: Select Status, Count(*) From Orders Where Created_At > '2026-05-01' Group By Status; → View The Distribution Of Test Orders This Month
• Scenario 8 - Find Test Data With Specific Conditions: Select * From Products Where Price Between 100 And 500 And Stock > 0 And Category = 'Electronics' Limit 5; → Find Products That Meet The Test Conditions
• Scenario 9 - Confirm The Integrity Of The Associated Data: Select O.Order_Id, O.Status, P.Payment_Status From Orders O Left Join Payments P On O.Order_Id = P.Order_Id Where O.Order_Id = 'Ord-456'; → Confirm Whether The Order And Payment Data Correspond
• Scenario 10 - Calculate The Amount Of Data Covered By The Test: Select Count(Distinct User_Id) As Tested_Users, Count(Distinct Product_Id) As Tested_Products From Orders Where Created_At > '2026-05-20'; → Count How Many Users And Products The Test Touched

5. Scenario 11-15: Bug root cause troubleshooting

• Scenario 11 - Front-End Vs Back-End Bug: Select Actual_Field From Table Where Id =
• Scenario 12 - Sorting Exception Troubleshooting: Select * From Products Order By Price Asc Limit 20; → When The Front-End Sorting Is Incorrect, Confirm Whether The Sorting Logic Of The Database Is Correct.
• Scenario 13 - Troubleshooting Paging Anomalies: Select Count(*) From Products Where Category = 'Shoes'; → When The Total Number Of Pages Displayed On The Front End Is Incorrect, Confirm The Actual Number Of Transactions In The Database
• Scenario 14 - Time Zone Related Bug: Select Created_At, Created_At At Time Zone 'Asia/Taipei' From Orders Where Order_Id = 'Ord-456'; → When The Time Display Is Incorrect, Check The Time Zone Stored In The Database
• Scenario 15 - Amount Calculation Bug: Select Unit_Price, Quantity, Unit_Price * Quantity As Expected_Total, Actual_Total From Order_Items Where Order_Id = 'Ord-456'; → When The Amount Is Incorrect, Check Item By Item

6. Scenario 16-20: Advanced Analysis and Reporting

• Scenario 16 - Statistics Of Data Distribution In Each Status: Select Status, Count(*) As Count, Round(Count(*) * 100.0 / Sum(Count(*)) Over(), 2) As Percentage From Orders Group By Status; → Output Order Status Distribution Table
• Scenario 17 - Find Abnormal Data: Select * From Orders Where Total_Amount < 0 Or Total_Amount > 1000000; → Find Orders With Abnormal Amounts (Possible Bug Or Attack)
• Scenario 18 - Time Trend Analysis: Select Date(Created_At) As Date, Count(*) As Order_Count From Orders Where Created_At > Current_Date - Interval '7 Days' Group By Date(Created_At) Order By Date; → Analyze The Order Volume Trend In The Last 7 Days
• Scenario 19 - Null Value Check: Select Column_Name, Count(*) As Null_Count From Orders Where Column_Name Is Null; → Check Whether The Required Fields Are Missing (Common Back-End Bug)
• Scenario 20 - Performance Related Query: Select Count(*) From Large_Table Where Indexed_Column = 'Value'; → Pay Attention To Query Time When Testing, Too Slow May Indicate Lack Of Index

7. QA commonly used SQL cheat sheet

8. Learning routes and recommended resources

• Week 1: Select, Where, Order By, Limit — Being Able To Look Up Data Is Enough To Handle 70% Of Scenarios
• Week 2: Join, Group By, Count/Sum/Avg - Can Do Related Queries And Statistical Analysis
• Week 3-4: Subqueries, Case When, Date Functions — Can Handle Advanced Analysis
• Recommended Exercises: Sqlzoo (Sqlzoo.Net) Free Interactive Teaching, Leetcode Sql Questions (From Easy To Difficult)
• Recommended Tools: Dbeaver (Free Gui Tool), Datagrip (Jetbrains Paid But Very Powerful)