## Exercise #16 |
||||||||||||||||||

Find the pairs of PC models having similar speeds and RAM. As a result, each resulting pair is shown only once, i.e. (i, j) but not (j, i). Result set: model with high number, model with low number, speed, and RAM. Here is the frequently proposed solution: Solution 1.12.1
I really don't know, what was the cause to decide outputting only models with maximum and minimum numbers for each coinciding pair of values {speed, ram}. It is possible that the result of inaccurate query on available database is misleading. In this task, it is needed to order all the models, but not only maximum and minimum of these. Extremal characteristics are mentioned for uniqueness, i.e. to get the pairs of models once , for example:
So, if three models 1122, 1121, and 1135 have alike features, the result will be as follows:
Below is presented almost correct solution, though also the too bulky. Solution 1.12.2
Here in subrequest S unique pairs of characteristics (speed, memory) are selected, which coincide for two computers (SUM (speed)/speed = 2) - the sum of identical values divided by this value gives us quantity of the PCs. Though with the same success it was possible to write the following HAVING clause:
The subquery joined with the table PC on this characteristics pair twice. In so doing, the second join is only carried out to order the models (L.model <P.model). The mistake of the given decision consists that the number of the personal computers with identical characteristics may be more than two. In this situation any of such models will not get in result set of the presented solution. In spite of the fact that the solution is easy to correct, better to rewrite it in not a such superfluous form. There is one more mistake which is often made in this exercise. It is due to the fact that the PC table may include some computers with same model value. Therefore it is necessary to exclude possible duplicates of required pairs. |

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*