Books and articles about SQL Rambler's Top100 Сменить язык на: Русский 23 October 2017 07:05:25


www.sql-ex.ru
Skip Navigation Links  

 

Print  Print version

Main page

Typical errors at solution of exercises. Exercise 14

S. Moiseenko

For Product table, receive result set in the form of a table with columns: maker, pc, laptop, and printer. For each maker, this table must include "yes" if a maker has products of corresponding type or "no" otherwise. In the first case (yes), specify in brackets (without spaces) the quantity of available distinct models of corresponding type (i.e. being in PC, Laptop, and Printer tables).

If the manufacturer has models of some type in the Product table, but none of them is available, then according to the formulation the result should be

yes(0)

Instead of

no

Many find a mistake in the test solution. Claims are reduced to the following query:

SELECT COUNT(*)
   FROM Product
   WHERE Maker = 'E' AND type='PC'

which gives 3 models of PC for manufacturer Е while "right answer" gives only one model of a computer for this manufacturer. We need return to the formulation that tells us:

“… specify in brackets (without spaces) the quantity of available distinct models of corresponding type (i.e. being in PC, Laptop, and Printer tables).”

In our case, this means that we need specify in brackets a quantity of distinct models of the personal computer of manufacturer Е in PC table. In terms of SQL it means:

SELECT COUNT(DISTINCT pc.model)
   FROM Product pr
   JOIN PC ON pr.model=pc.model
   WHERE Maker = 'E'

Main page

Print  Print version


Usage of any materials of this site is possible
only under condition of mandatory allocation of the direct link to a site
http://www.sqlbooks.ru
on each page where used materials are placed.

 Main   Articles    Books 
Рейтинг@Mail.ru Rambler's Top100 Alt Упражнения по SQL: обучение, тестирование, сертификация по языку SQL Copyright c 2002-2006. All rights reserved.