Exercise #1

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:

  1. SELECT model, speed, hd
  2. FROM PC
  3. WHERE price < 500;

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:

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

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 cant contain a model not present in the Product table. However, the second query didnt 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 didnt 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, dont join tables if you dont really need to. The task doesnt require information from the Product table, thus it shouldnt be referred to by the query. Its 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.


If just to learn writing queries somehow isnt your sole purpose but you also want them to be as efficient as possible, you definitely should avoid unnecessary table joins.

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.

To solve this task at SQL-EX.RU

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.