Of the DB operations I came to is the Division operation. I really tried to understand this operation from different sources but didn't get the idea of this operation.
Can someone thankfully describe it or at least direct me to a source that explains this operation clearly?
Thanks.
Hi SWEngineer
Consider these two tables:
table student:
Name
-------
Ada
Benita
Cecile
Fatma
table subjects_passed:
Name subject
---------------------------
Ada Relational Algebra
Fatma Relational Algebra
Ada Databanks
Fatma Databanks
select name as "No exam passed" from students division select name from subjects_passed;
Result:
No exam passed
--------------
Benita
Cecile
So divison (except) operator gives all students which have not passed any exam.
That is, the result set contains all rows which are in left table but not in right table of division operator.
Same result is given by left join:
[B]select students.name as "No exam passed" from students left join subjects_passed on students.name = subjects_passed.name where subject is null;[/B]Another name for this relational operation is EXCEPT what is the official standard SQL/1999 name. There are also other names: difference, minus (Oracle).
Further relational operations are union and intercept (can be computed by division, so not necessary).
-- tesu
Thanks tesuji. I'm asking about the division operator, and in the resources it is something different than what you showed here. What do you think?
Ah sorry, I misread you.
Obviously you meant plain division operator such as 13/3 makes 4 (if you were to stumble about archaic transact sql).
Simply forget my previous posting.
-- tesu
Ah sorry, I misread you.
Obviously you meant plain division operator such as 13/3 makes 4 (if you were to stumble about archaic transact sql).
Simply forget my previous posting.
-- tesu
It is not actually the division you mentioned. Division is one of the extraction operations (i.e; Union, difference, join, select, ...etc).
This is what I mean.
It is not actually the division you mentioned. Division is one of the extraction operations (i.e; Union, difference, join, select, ...etc).
This is what I mean.
Gracious SWEngineer!
DIVISION is synonym for
1. difference --> read your posting !
2. minus (oracle)
3. EXCEPT what is its official name by sql 1999 standard
So you may go back and try to understand my FIRST posting !
-- tesu
Gracious SWEngineer!
DIVISION is synonym for
1. difference --> read your posting !
2. minus (oracle)
3. EXCEPT what is its official name by sql 1999 standard
So you may go back and try to understand my FIRST posting !
-- tesu
As the manga guide to databases it states that there are two groups of data extraction operations:
1- Set operations:
* Union
* Difference
* Intersection
* Cartesian product
2- Relational operations:
* Projection
* Selection
* Join
* Division
So, division and difference are different.
Yes, Division is of course an entirely different opperation than difference. It is most often used when you want to find tuples in on table that relate to all tuples in another table.
For instance:
We have three tables: Boyz with data on severel boys, like name adress et cetera, and boyzID as PK. Girlz with data on a lot of girls with girlzID as PK and Love that connects boyz with girlz by and having boyzID and girlzID as a compound pk.
if we would like to write a query that answers which boys who have loved all girls, we could use a division.
(something like Boyz รท Girlz if we would write it in relational algebra.)
The idea behind a division is to conduct a double negation: We will searh for any boy who has not loved a certain girl and then not show those boys in the result.
To achieve this with SQL, we need to identify three parts: 1) What do we want to show? (Here data from the table Boyz ) 2) what constitutes all? (Here data from the table Girlz) and 3) how are 1 & 2 joined (Here data from the table Love)
It would look like this (just replace the boys and girls parts with the tables (or joined tables) that you need to use. Each of the tree parts can be found after the three select statements below
SELECT name
FROM Boyz
WHERE NOT EXISTS (SELECT *
FROM Girlz
WHERE Girlz.girlzID NOT IN (SELECT Love.girlzID
FROM Love
WHERE Boyz.boyzID = Love.boyzID))We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.