## Relational division |
||

Let`s consider task: Determine makers which produce models of all types (in Computer firm schema). The keyword here is “all” which means that maker in Product table must have models of every type: PC, Laptop, and Printer. There is special operation for solving this type of tasks in the relational algebra. This operation is relational division (DIVIDE BY). This operation makes solution of considering task very simple [1]:
The hooks determine projection operation to the corresponding attributes. The relational division operation is superfluous. It can be expressed by the other operations of the relational algebra. Perhaps, that`s the reason why it absents in the SQL. Let`s introduce some methods of implementation of relational division in 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 on considering example. ## GroupingIf we use fact that there is only three types of product corresponding to description of the knowledge domain, we may group data by maker and count quantity of unique types. Then we select only makers with quantity equal three. Èòàê, But if the type`s count is arbitrary this solution will be right only in current state of database, not in every possible. So we need to replace hard number by “variable”, i.e. to use subquery:
## SubtractionIf we subtract model types of every maker from all types then the resultant set must have no rows for maker with all types of product.
This query may be rewritten in shorter form if we take into account fact that the ALL predicate returns TRUE if the subquery have no rows:
The ALL predicate`s list will be empty for target makers, and in all other cases it would contain types which are absent in the maker product list and the operation “=” for all models returns FALSE. ## ExistenceThere is no such type of product which is absent in the maker`s product list.
All solutions, except first one, are using correlation subquery for determining the maker`s product types set. We should also note that the solution with grouping is not applicable for cases in which we need to divide by not full set of types, but its subset. For instance, if we need to find all makers with product`s set include (or equal to) set of types determined by some criteria. Other methods may be adapted for solving such tasks. Suggested exercises: 71 |

- CHOOSE function
- Data type conversion and CAST function page 2
- COUNT DISTINCT and window functions
- Explicit join operations
- Exercise #28
- Exercise #8 (tips and solutions) page 2
- Exercise #8 (tips and solutions)
- Exercise #7 (tips and solutions)
- Exercise #6 (tips and solutions)
- Exercise #2 (tips and solutions)

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

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
money
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
UNPIVOT
UPDATE
varchar
WHERE
window functions
WITH
XML
XPath
XQuery

The book was updated

*several days ago*