loading..
Ðóññêèé    English
06:37
go

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:

Console
Execute
  1. SELECT MAX(model1), MIN(model2), MAX(speed), MAX(ram) 
  2. FROM (SELECT pc1.model AS model1, pc2.model AS model2, pc1.speed, pc2.ram, 
  3.              CASE WHEN CAST(pc1.model AS NUMERIC(6,2)) > 
  4.                        CAST(pc2.model AS NUMERIC(6,2)) 
  5.                   THEN pc1.model+pc2.model  
  6.                   ELSE pc2.model+pc1.model  
  7.              END AS sm 
  8.       FROM PC pc1, PC pc2 
  9.       WHERE pc1.speed = pc2.speed AND 
  10.             pc1.ram = pc2.ram AND 
  11.             pc1.model <> pc2.model
  12.       ) a 
  13. 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

  1. 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
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
îáìåíÿòü ñ epayments . Òà÷êè â ïåñî÷íèöå: Ñòðîéêà
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.