Русский    English

Exercise #6

For each maker producing laptops with a hard drive capacity of 10 Gb or higher, find the speed of such laptops. Result set: maker, speed.

Here is the problem, which for the first time requires use of information in several tables: maker name is in Product table, speed and hd volume are in Laptop table.


I beg the pardon of those of my readers who are advanced  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL programmers, that we are analyzing the mistakes made by the beginners, but there can be no other kinds of mistakes in the problems of this level of difficulty. The level of difficulty of the problem is indicated in  "Level" column in the list of exercises. Thus we can get down to the problems of the 2-nd and the 3-rd level of difficulty.

Thus, the solution is the following:

  1. SELECT DISTINCT Product.maker, Laptop.speed
  2. FROM Product, Laptop
  3. WHERE Laptop.hd >= 10 AND
  4. type IN(SELECT type
  5. FROM Product
  6. WHERE type = 'laptop'
  7. );

Thereby the author of this solution claims that this query outputs 5 strings more than in the correct answer, and the query that he considers more adequate:

  1. SELECT DISTINCT Product.maker, Laptop.speed
  2. FROM Product, Laptop
  3. WHERE Laptop.hd >= 10;
outputs the result that contains all the types of products.

The mistake lies in the fact that listing of the tables through comma without indication of the method of their connection is nothing but Cartesian product, about which one can read in Chapter 5 (Item 5.6).

We agree with the opinion that the second solution is more correct. It lacks only joining of the tables. At the same time the first one is a mere attempt to fudge, limiting the output of the second «correct» solution using the models of the notebooks. It should be noted that this attempt appears to be a rather clumsy one, as, in case we have not misinterpreted the idea of the author, it was enough to write instead of the predicate

  1. type IN (SELECT type
  2. FROM Product
  3. WHERE type = 'laptop'
  4. )

the following

  1. type = 'laptop'


To solve the problem on 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
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.