loading..
Русский    English
13:58
go

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.

This is the first exercise that requires obtaining data from more than one table: the name of the maker is in the Product table, while the speed and hard drive capacity (hd) are in the Laptop table.

Notes:

I apologize to readers experienced in  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 for analyzing beginners’ mistakes; however, such easy tasks hardly leave room for other kind of mistakes. The difficulty level is specified in the Level column in the list of exercises. Thus, you can proceed with more difficult tasks.

But let's get back to the solution:

Console
Execute
  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.  );

The author of this solution claims that this query returns 5 extra rows compared to the correct answer, while the query he considers more accurate:

Console
Execute
  1. SELECT DISTINCT Product.maker, Laptop.speed
  2. FROM Product, Laptop 
  3. WHERE Laptop.hd >= 10;
displays all product types in the result set.

The mistake lies in the fact just listing the tables separated by commas without specifying any criterion how to combine them yields nothing else than the Cartesian product of them (see Chapter 5 section 5.6 for details).

We agree that the second solution is more accurate. The only thing it lacks is an appropriate table join operation. Whereas the first solution represents an attempt to fiddle with the output of the “more accurate” second query by confining it to laptop models. It should be noted this attempt was a rather clumsy one, since, if we understood the author’s idea correctly, the predicate

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

the following

  1. type = 'laptop'

T&S

Solve this task at 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 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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.