Common mistakes in solving SELECT statement exercises

Computer Firm Database

Find the model number, speed and hard drive capacity for all the PCs with prices below $500. Result set: model, speed, hd.

First exercise, level of difficulty 1. Even beginners have no problems solving it. Indeed, there is only one table, one condition for fetching records having the specified price, and the output limitation to three columns:

Console

Execute

SELECT model, speed, hd
FROM PC
WHERE price < 500;

SELECT model, speed, hd FROM PC WHERE price < 500;

    Scheme

One might wonder what the use of analyzing such a trivial task is. To answer this question, may we suggest considering a different solution to the same exercise:

Console

Execute

SELECT Product.model, PC.speed, PC.hd
FROM Product, PC
WHERE Product.model = PC.model AND price < 500;

SELECT Product.model, PC.speed, PC.hd FROM Product, PC WHERE Product.model = PC.model AND price < 500;

    Scheme

Both queries yield the same result because referential integrity is maintained between the PC and Product tables linked to each other by the model column. In particular, it means the PC table can’t contain a model not present in the Product table. However, the second query didn’t pass the system check on the site for a while, which resulted in an angry letter sent to the author of this book by the user who tried to solve the exercise this way.

As it turned out when the databases were being migrated from one server to another, some of the references were lost, so that a model with suitable characteristics but an ID absent from the Product table cropped up in the PC table. Naturally, the second query didn’t display this record, hence not passing the check of the verification system.

The data inconsistency has been fixed, and the second solution finally passes the system check successfully. The moral of this story is, don’t join tables if you don’t really need to. The task doesn’t require information from the Product table, thus it shouldn’t be referred to by the query. It’s not a justification for the lost reference, albeit, as you have seen, the first solution still returned the correct result, displaying all PCs costing less than $500.

In addition to join operations being quite resource-consuming by themselves, they cause unwanted locks on tables (the Product table in our case), which, in their turn, will suspend execution of simultaneously processed queries referring to the locked tables (e.g. data modification queries). As a result the performance of the whole system will be impaired.