loading..
Русский    English
17:45

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 the beginners easily cope with solving of this problem. Actually there is only one table, one condition of choice in accordance with price level and the restriction that the data should be displayed in three columns:

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

One might wonder what’s the use of analysis of such a problem? Answering this question we suggest considering another method of solving of this problem:

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

The solutions of this problem have the same result as the reference identity is maintained for PC and Product tables in accordance with the model number (model column). Actually it means that in PC table there should not be any model, which was not found in Product table. However the second query is not accepted by the system, due to which the author got an indignant letter from the person, who suggested this solution.

It appeared that when the databases were transferred to another server some of the references got lost, as a result of which in PC table there appeared a model meeting the requirements of the problem in terms of characteristics and number, which was not found in Product table. Quite naturally, the second solution did not have this line in the result, and the verification system did not accept such decision.

Inconsistency of data was eliminated, the connection was restored and the second solution was accepted in the course of verification procedure. The main idea of this reasoning is that one should not combine two tables if it is not absolutely needed. According to the terms of the problem we do not need the data from Product table; therefore it should not be used in the query. It is not an excuse for the mistake made when eliminating the reference, although as it has been shown, even if the data sets are not in conformity the first solution continued to give the result meeting the requirement, namely, it output all the PCs costing less than 500 dollars.

Advise:

If our purpose is not just to learn how to write queries, but also to create them efficiently, than one should avoid unneeded joining of the tables.

Besides, the operation of joining in itself consumes a lot of resources, unneeded blockings will be imposed on Product table, and it will suspend the execution of the simultaneously processed queries (i.e. on modification of data), addressed to this table. As a result the efficiency of the whole system is reduced.

To solve the problem on SQL-EX.RU

Bookmark and Share
Tags
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 CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
yesterday
billige fly
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100