## Exercise #17 |
||

Find the laptops having speeds less than all PCs. Result set: type, model, speed. Following errors are associated with overuse of join operations. The most egregious example, the author believes, is the following:
In the FROM clause it is using of the Cartesian product of three tables! If the presence of the Product table can still be somehow justified because is needed to specify also the type of product, then the PC table can be safely removed without affect on the result. Obviously, the decision would not be optimal for speed of execution. In addition, you may have problems with memory, since the power of an intermediate result can be huge even for relatively small tables. Recall that the power of the Cartesian product is the product of power operands. For example, for tables with the number of rows 100, 500 and 1000 of the Cartesian product contains 50000000 row! And, nevertheless, the decision was correct, because the DISTINCT statement eliminates all duplicates, resulting from the Cartesian product. |

- ROW_NUMBER function page 2
- Data type conversion and CAST function page 2
- Adding rows to a table with identity column page 4
- Product of column values
- Intersect and Except page 4
- HAVING clause
- Summarizing data using ROLLUP
- Common table expressions (CTE) page 3
- EOMONTH function
- Aggregate function to aggregate function

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
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
EXCEPT
exercise (-2)
exercise 19
exercise 23
exercise 32
More tags

exercise 37
exercise 39
exercise 46
exercise 54
exercise 55
exercise 56
exercise 57
exercise 7
exercise 70
exercise 8
exercises
EXISTS
FROM
FULL JOIN
GROUP BY
grouping
Guadalcanal
HAVING
head ships
IDENTITY
IN
income
inner join
INSERT
INTERSECT
IS NOT NULL
ISNULL
join operations
laptop
launched year
LEFT
LEFT OUTER JOIN
LEN
LTRIM
MAX
MIN
mistakes
MySQL
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
óïðàæíåíèå 23

The book was updated

*month ago*