loading..
Ðóññêèé    English
09:32

Exercise 71

Find the PC makers with all personal computer models produced by them being present in the PC table.

Here is the typical incorrect query

Console
Execute
  1. SELECT DISTINCT maker
  2. FROM Product
  3. WHERE model IN (SELECT model FROM PC);
that arrived at the help desk accompanied by the following question: «Producer E has model 1260 in the PC table, yet the correct result set doesn’t contain him. Why?»

The key point of the task formulation is the word «ALL». Let’s have a look at the models by maker E. PC models produced by maker E are obtained by 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 check which of these models can be found in the PC table:

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

As it turns out only one model out of three - 1260 – is present in PC table. According to the task, however, ALL three models should be there for maker E to be displayed.

Basically, the task solution boils down to relational division; however, each maker has his own divisor (the set of models by him). The simplified representation of relational division looks as follows:

  1. A(a, b) DIVIDEBY B(b)

Here, the dividend (À) is a binary relation (with two attributes), and the divisor (B) is a unary one. The quotient contains the values of the first attribute in the dividend for which the second attribute has ALL of the values in the divisor.

Relational division doesn’t belong to primitive operations. This means it can be expressed through other (primitive) relational operations. The redundancy of relational algebra introduced by Codd is induced by its focus on practical application. SQL  itself is redundant too, which is proved by every exercise at the site that can be solved in different ways. In spite of that, there is no relational division counterpart in SQL. :-)

Finally, here's an example how relational division can be performed using other operations.

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

Here, A[a] is the projection of the relation A onto attribute a; TIMES is the Cartesian product operation. An “interlinear” translation of the right part of the equation into  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 could look like this:

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;

It’s hardly advisable to use this word-for-word translation as an action guide; there are easier ways to solve this task. I don’t insist though. :-)

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.