Record the scenario of a SQL statement accessibility test in conjunction with a real test instance (including multi-table search and subqueries)

This article is a case study of SQL statements through database-assisted functional testing. I would like to read this article of the small partner, through this scenario example to understand the SQL statement in the real functional testing of the role played

The SQL knowledge points involved in this functional test are as follows:

1 SELECT Query

2 WHERE clause

3 count ()

4 To_char ()

5 GROUP BY

6 ORDER BY

72 Table Joint Check (this involves left join)

8 Sub-query nesting

Requirements document in the instance:

System Test Interface: (sorry, write the article, want to cut a better picture, test environment can not log in)

Prerequisites explained:

This report shows the statistical results of the number of delays and approvals for all processes.

Because the process data has no way through the foreground report statistical verification, in the test, the use of the database for query validation.

Ideas:

1 Checking out the number of approvals for grouping statistics by 4 column fields

2 query out the number of delay times for grouping statistics by 4 column fields

3 Verify that the interface display and query results are consistent separately

4 Validation descending order (sort by the order BY statement verifies the result sequence and the interface is consistent)

Extended:

The number of approvals is combined into one result (left JOIN usage and sub-query nesting) through two tables

(In fact, the completion of a single query can also complete the validation of this function, time permitting also to strengthen the use of two table query and sub-query nesting function, increase the statement extension)

Statement and running Result:

Number of Approvals:

Delay Times:

Two-table search and subquery (left join)

The reason why the left join is selected because the right table delay number of the statistical range is narrower than the open table approval number, the process of the total table of data each will be grouped to count the number of approval, but only is_timeout this field is true will be counted

For example, a group row in which the field is all false or empty, so I see the delay data is null

Record the scenario of a SQL statement accessibility test in conjunction with a real test instance (including multi-table search and subqueries)