Can you think of ways to validate the information from SQL queries or reports, to assure a level of accuracy in the results? When, if at all, is it better to extract raw data instead of summary information, and what are the best ways to present any retrieved information (e.g., tables, diagrams, narrative)?
To ensure the accuracy of SQL reports, database (DB) administrators and developers need to validate the queries. Guagliardo and Libkin (2017) ran the queries on different sets of data as a way to validate the reports. However, the method may seem overly labor-consuming for small DBs. I think that the easiest way to confirm the results is to run the query on a set of simplified data, where the results are predictable.
This method allows avoiding numerous attempts at verifying the outcomes. Additionally, it may be suitable to validate queries manually by acquiring raw data. For instance, if it is needed to get a sum of all the salaries from an enterprise’s DB, an administrator can use SELECT * FROM and count the wages by hand to validate the previously run query. Therefore, sometimes acquiring raw data may more useful than receiving summary information, as it helps to ensure the accuracy of the SQL query.
The best way to present acquired information can vary depending on the type of data extracted. Numerical data, for instance, can be easier comprehended through tables. However, in most cases, it is best to use diagrams to represent retrieved information, as apart from data they also depict interactions among it in an explicit graphical manner.
Guagliardo, P., & Libkin, L. (2017). Formal semantics of SQL queries, its validation, and applications. Proceedings of The VLDB Endowment, 11(1), 27-39. Web.