Database set operations

Database set operations

Database set operations – As we have ᴖ, ᴗ, – operations in algebra, same we have Union, Intersect and except operations in SQL. Like Union, Intersection and set difference operation in relational-algebra, the tables participating in these operations must be compatible that is, that they must be of the same arity and must have similar attributes. There are similar restrictions on the uses of these operators, as certain products do not support these operations. Consider the queries that would involve THE UNION, INTERSECT and EXCEPT OPERATIONS on two sets.

The union database set operations

This operation is executed using reserve word union. When a union of two compatible tables is taken, the resultant table contains the values, which are present in both the tables.
“Find all the customers who have both an account and loan at the bank”
To select the customers who have both an account at the bank, we write the query as follows.
Select cust_name from depositor
Similarly to select the customers who have a loan at the bank, we write the query as follows.
Select cust_name from borrower

Therefore in order to select all the customers who have both loan and account at the bank, we will take union of the above two queries, which would fetch the required result.

(select cust_name from depositor) union (Select cust_name from borrower)
When this query is executed, it automatically eliminates duplicate values; if we want to retain duplicate values we can do the same by using the word “all” along with the union construct as follows:

(select cust_name from depositor) union all  (Select cust_name from borrower)
The number of duplicates appearing in the resultant relation is equal to the total number of times the value appears in the two individual relations for which the union is taken.

The intersect database set operations

This operation when executed, the resultant table contains only those values, which are common in both the tables.
“Find all the customers who have both an account and a loan”
(select cust_name from depositor) intersect  (Select cust_name from borrower)
This also when executed automatically eliminates duplicate values, if we want to retain all the duplicate values, we can use “all” along with the intersect construct.

The except database set operations

This operation when select only those values which are present in only those values which are present in only in the first table, this functions similar to set difference operation of relation algebra.
“Find all the customers who have only account”

(select cust_name from depositor) except  (Select cust_name from borrower)

Database set comparisons

This is highly useful to compare one relational set with another relational set and sometimes when the same relation has to be compared with itself SQL allows <, >,-, >=, <=, <>. The SQL also has a provision for using all these relational operators with SOME construct. SOME construct is used to represent the conditions greater then, less then, equal to at least one. Thus we can write >some, <some, = some, >=some, <=some, <>some.
“Find the names of all branches that have assets greater than those of at least one branch located in MYSORE”
Select br-name from branch
Where assets > some (select assets from branch br_city = “MYSORE”)
Similarly one can use ALL to compare one value with the rest of the tuples in the relation. The ALL construct is used in the same way as SOME.
As another example of set comparisons, consider the query “Find that has the highest average balance”
Select br-name from account
Group by br_name
Having avg(balance) >= all (select avg 9 balance) from account Group by br_name)

Leave a Reply

Your email address will not be published.