## Exercise #53 |
||

To within two decimal digits, define the average amount of guns for the battleship classes. I supposed there is only one problem in this task, and that is rounding. But recently I received the following solution:
It's very rich for mistakes analysis J . Let's start from the rounding. The number of guns is an integer (according to the column type, not to the logic!). That's why the sum itself will be a whole number. When dividing integers in A database management system (DBMS) by Microsoft Corporation. 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 Server we always get an integer. And the result is achieved not by rounding but by DISCARDING the fractional part. For example, execute the following query: The result will be 0, and that confirms what we've said. So, to make cosmetic improvements of this query, at least one operand should be converted to real type. As I've written in the Help at www.sql-ex.ru site, you can use implicit type conversion:
Now, as we need to count the average by classes, at first, we shouldn't take ships into account and, second, we don't need to remember ships from the Outcomes table. But for analyzing the mistakes let's consider the solution in its author's interpretation, that is, we'll define the average value by all linear ships from the database, and this turns out to be exercise #54. I've discussed that task but with another mistake. So, the number of guns and amount are counted separately by ships from the Ships table and by the head ships from the Outcomes table. Then, in the main query we summarize the number of guns and the amount of ships by every table and divide the first by second to get the average value. Let's discuss an example. Let there be 2 ships with 11 and 8 guns in the Ships table, and 1 ship with 11 guns in the Outcomes table. So we get 3 ships with 30 guns. The average value is 30/3=10. Is this correct? No, that is, it's correct only for certain cases, while we need to write a query that will be correct for any data. I see several examples to prove this. First. What if there is no head ship that corresponds to the terms of the task in the Outcomes table? Then the second query will return: 0 ships, the number of guns is NULL. As a result of calculating the average we'll get
Second. Let the head ship of ‘bb' class be both in the Ships and the Outcomes table, that means, it's the same ship. Then we should get 19/2 but not 30/3 as the solution presents. Third. But what if in the previous situation on the ships the head vessel took part in battles twice? Then we'll get (19 + 22) /( 2 + 2) = 41/4 instead of our 19/2. Fourth… Think of it yourself. That's how the checking base on the site is formed. :-) |

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
edge
equi-join
EXCEPT
exercise (-2)
More tags

exercise 19
exercise 23
exercise 32
exercise 37
exercise 39
exercise 46
exercise 54
exercise 55
exercise 56
exercise 57
exercise 7
exercise 70
exercise 8
exercises
EXISTS
FLOAT
FOREIGN KEY
FROM
FULL JOIN
GROUP BY
grouping
Guadalcanal
HAVING
head ships
IDENTITY
IN
income
INFORMATION_SCHEMA
inner join
INSERT
INTERSECT
IS NOT NULL
IS NULL
ISNULL
join operations
laptop
launched year
LEFT
LEFT OUTER JOIN
LEN
LIKE
LTRIM
MAX
MIN
mistakes
MySQL
NATURAL JOIN
node
NOT
NOT IN
NULL
NULLIF
number sequences
number-sequence generation
numbering
ON DELETE CASCADE
OR
Oracle
ORDER BY
outcome
Outcomes
outer joins
OVER
paging
Painting
PARTITION BY
Pass_in_trip
PATINDEX
PC
PIVOT
PostgreSQL
predicates
primary key
printer
Product
Ranking functions
recursive CTE
renaming columns
REPLACE
RIGHT
RIGHT JOIN
ROUND
rounding
ROW_NUMBER
ships
sorting
SQL Server
SQL Server 2012
SQL-92
sql-ex.ru
string functions
subquery
SUBSTRING
SUM
tables join
tips and solutions
Torus
Transact-SQL
Trip
TRUNCATE TABLE
type conversion
UNION
UNION ALL
UNKNOWN
UPDATE
varchar
WHERE
window functions
WITH
XML
XPath
XQuery

The book was updated

*several days ago*