Exercise #2

List all printer makers. Result set: maker.

Here, a mistake typical for a number of exercises (e.g., 20, 27, 28) occurs for the first time. Its cause lies in the database schema not being studied careful enough. Heres one of the incorrect solutions:

  2. FROM Product
  3. WHERE model IN (SELECT model
  4. FROM Printer
  5. );

Thus, for each row in the Product table it is checked whether there is a corresponding model in the Printer table. The relationship between these two tables (one-to-many) allows the existence of a model in the Product table absent from the Printer table.

Lets consider, say, a printer repair company. Moreover, the Product table contains data for all known printer models, while the Printer table restricts itself to models our company provides service for. E.g., if the company doesnt repair printers by Sharp, models made by Sharp may be found in the Product table but will be missing from the Printer table.

As a result well miss printer makers not having models provided service for (in the Printer table). As already pointed out when discussing the database schema, the product type in the Product table is determined by the type column, which has been overlooked in our case.

If youre still wondering how to solve this exercise check out Chapter 4 Tips and solutions.


If additional comments are provided or the fallacious decisIf additional comments or a correct solution are provided for an exercise, a link to the corresponding section of the aforementioned chapter can be found at the end of the exercise analysis  T&S.

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