loading..
Русский    English
12:58
go

Exercise #32 page 2

Here’s another query posted on the forum.

Solution 3.14.2

Console
Execute
  1. SELECT Country, AVG(Wght) Wght, ISNULL(AVG(Wght),0) 
  2. FROM (SELECT DISTINCT Country, name, ship, 
  3.  CASE WHEN (Outcomes.Ship IS NULL AND 
  4.  Ships.Class IS NULL) 
  5.  THEN NULL
  6.  ELSE POWER(Bore,3)/2 
  7.  END Wght
  8.  FROM Outcomes FULL OUTER JOIN 
  9.  Ships ON Outcomes.Ship = Ships.Name RIGHT OUTER JOIN 
  10.  Classes ON Outcomes.Ship = Classes.Class OR 
  11.  Ships.Class = Classes.Class
  12.  ) s
  13. GROUP BY Country;

You can see another solution. Here we have grouping by countries, full outer join and application of
non-standard functions (SQL Server):

ISNULL(var, sub) — – returns var unless it’s NULL, otherwise returns sub;

POWER(var, N) — returns var raised to the power of N.

Despite the obvious difference, this query contains the same mistake as the previous one. One ship could be counted multiple times if it was involved in several battles. Try to fix this mistake without changing the solution logic.

Below you can find three solutions containing different errors. Namely, there is some data for each solution that produce a non-recurrent result in other solutions. So, let’s take a closer look only on “unique” mistakes.


Bookmark and Share
Pages 1 2 3 4
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.