loading..
Ðóññêèé    English
15:09
go

Exercise #16 (tips and solutions)

Redundancy decision 1.12.2 can be eliminated, if to remove the subquery and create join between tables P and L. In this case, the request will not only compact, but easily readable and, most importantly, more effective.

Here is an example of a hard to read, although the correct solution, which is published on the site's forum:

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;

I think this is a good example of not good queries.

To return to discussion of exercise #16

To solve a 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 CONSTRAINT 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 equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100