Exercise #16 (tips and solutions)

The redundancy of the solution 1.12.2 can be eliminated by removing the subquery completely and performing the join between the tables P and L. This way, the query will not just become shorter, but also easy to read and, which is no less important, more efficient.

Here is another example of a query difficult to read; this solution would be correct if the model column was numeric:

SELECT MAX(model1), MIN(model2), MAX(speed), MAX(ram)
FROM (SELECT pc1.model AS model1, pc2.model AS model2, pc1.speed, pc2.ram,
             CASE WHEN CAST(pc1.model AS NUMERIC(6,2)) >
                       CAST(pc2.model AS NUMERIC(6,2))
                  THEN pc1.model+pc2.model
                  ELSE pc2.model+pc1.model
             END AS sm
      FROM PC pc1, PC pc2
      WHERE pc1.speed = pc2.speed AND
            pc1.ram = pc2.ram AND
            pc1.model <> pc2.model
      ) a
GROUP BY a.sm;

However, the data type VARCHAR(50) implies the presence of arbitrary characters, which is the case for the checking database (say, model T-64). For such data, the type conversion operation

CAST(pc1.model AS NUMERIC(6,2))

will cause an error.

To me, this seems like a good example of how not to write queries. But how to write them? Have a look at the exercise forum after solving it to find the best examples.

Return to discussion of exercise #16

Solve this task at SQL-EX.RU