Page 49 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes The result of these SQL statements is a following relation.
Result using DISTINCT Keyword

Ename Esal
John 35000
Henry 22000
David 30000
Sam 25000

It is quite interesting to note that this is same as using selection and projection operations in
relational algebra.
( (Employee))
ename, esal esal 20000
Hence, an SQL query is similar to the expression containing selection, projection and cross
product in relational algebra.

Notes Table does not contain all the tuples whose salary is greater than 20,000. The tuple
with ename “Henry” is repeated twice but is displayed only once. This is due to the use of
DISTINCT keyword. If this keyword is not used then one more tuple must be added to
table i.e., if we write,
SELECT E.ename, E.esal
FROM Employee E

WHERE E.esal > 20000.
The resulting relation will be

Result without using ‘DISTINCT’ Keyword

Ename Esal
John 35000
Henry 22000
David 30000
Sam 25000
Henry 22000

The omission of distinct keyword resulted in a vow (Henry, 22000) which is repeated
twice. This is called as multiset of rows. It can be defined as a set consisting of unordered
elements which may consist of many copies of same element but the total number of
copies of the element is important. For example the set (1,2,1,1,) and (2,1,1,1) are same
multiset. Both contains three copies of ‘1’ and only one copy of ‘2’. But the multiset (1,2,2,1)
is different. Number of copies of each element is an important criteria to differentiate
between two multisets.

42 LOVELY PROFESSIONAL UNIVERSITY