Database Testing Example

I have explained database testing with examples in my video, Database Testing and SQL Tutorial for beginners.

Now, I received several emails from the my viewers asking for advice on how to practice database testing. This post contains one example to practice the same. I have explained SQL queries and SQL joins in my SQL Tutorial for Beginners | SQL Queries Tutorial using multiple examples.

Create a practice database with three tables, Products, Orders and OrderDetails or use the database that I showed in my SQL Queries tutorial above. It does not matter which database management software you use. It is simple to anticipate the relationships here. Products table is related to OrderDetails table, via the ProductId field. Orders table is related to OrderDetails table via the OrderId field.

Now, write and execute queries to check the following:

1. Is there any product which exists in the OrderDetails table but is missing in the Products table?

2. Does any OrderDetails row have an OrderId that is missing in the Orders table?

3. Is any ProductName, Serialnumber or ProductDescription duplicated in the Products table?

4. Does any product have a negative UnitsInStock?

5. Does any order have a ShipDate which is earlier than the OrderDate?

6. Does any order have a future OrderDate?

7. Does any OrderDetails row have a Quantity value less than 1? Or a Quantity value which is fractional?

8. Does any OrderDetails row have a zero or negative SalePrice or UnitPrice value?

9. Does any OrderDetails row have a UnitPrice for a product, different from that product's UnitPrice in the Products table?

10. Can you enter Price values (UnitPrice in Products table and SalePrice and UnitPrice in OrderDetails table) in decimal (up to 2 digits after the decimal point)?