loading..
Русский    English
02:58

Exercise 71

Find all the makers who have all their models of PC type in the PC table

Here is the typical fallacious query

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product
  3. WHERE model IN (SELECT model FROM PC);
which calls for the following question: «Producer E with model 1260 is present in PC table, and the correct resulting set does not contain this information. Why so?»

The key point of the claim is the word «ALL». Let’s analyze the models of Producer E. For PC models, issued by Producer E, we can generate the following query:

Console
Execute
  1. SELECT model
  2. FROM Product
  3. WHERE maker='E' AND type='PC';

Result:

model
1260
2111
2112

And now let’s see, which of the models are provided in PC table:

Console
Execute
  1. SELECT DISTINCT model
  2. FROM PC
  3. WHERE model IN(1260, 2111, 2112);

It appears that out of the three models only one – 1260 – is present in PC table. In accordance with the terms of the problem there should be ALL the three models of producer E.

Namely, solution of this problem boils down to operation of relational division, but for each producer there should be his own factor (set of the models). In the simplified way the operation of relational division can be recorded in the following way:

  1. A(a, b) DIVIDEBY B(b)
in which dividend (А) is binary (two attribute) relation, and factor (B)  is unary. In the result set there will be such values of the first attribute of relation A, for each of which the values of the second attribute contain ALL the values of the factor.

Operation of relational division is rather primitive. It means that this operation can be represented using other (primitive) relational operations. Redundancy of relational algebra, suggested by Codd, is predetermined by its focus on practical application.  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 language is also redundant, which is proved by every exercise, which can be solved in different ways. However it does not provide any analogue for relational division. :-)

To conclude with I would like to present relational division by means of other operations.

  1. A DIVIDEBY B :=
  2. A[a] EXCEPT ((A[a] TIMES B) EXCEPT A) [a]

In this respect A[a] means projection of relation A on attribute a; TIMES is Cartesian product. «Verbal» translation of the right part of the equation into SQL language can be represented in the following way:

Console
Execute
  1. SELECT a FROM A
  2. EXCEPT
  3. SELECT a FROM (
  4. SELECT A.a, B.b FROM A, B
  5. EXCEPT
  6. SELECT a,b FROM A) X;

One should not use this translation as guide to operations; there are some easier ways to solve this problem. However I am not very persistent in this respect. :-)

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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100