 Hello everyone. I am Mrs. Sunita Do. In this video, the topic covered is Structured Query Language Set Operations. All the set operations like Union, Intersect and Accept are covered. Learning outcome. At the end of the session, students will be able to write the query using set operations like Union, Union All, Intersect, Intersect All, Accept and Accept All. The SQ set operation is used to combine the two or more SQ select statements. The set operations on the relations are given below. The set operations Union, Intersect and Accept operate on the relations and corresponds to the relational algebra operation Union, Intersection and Minus. To retain all duplicates, we use the corresponding multiset version Union All, Intersect All and Accept All. The relation participating in the set operation must be compatible that is they must have the same set of attributes. Set operation Union and Union All. The Union operator is used to combine the result set of two or more select statement. Each select statement within the Union must have the same number of columns. The column must also have the similar data type. The columns in each select statement must also be in the same order. The Union clause produces distinct values in the result set and to face the duplicate values to Union All must be used instead of just Union. Syntax for Union is select column names from table 1, Union select column names from table 2. The syntax for Union All is select column name from table 1, Union All select column name from table 2. The column names in the result set are usually equal to the column names in the first select statement in the Union and Union All. For all set operation we are going to consider the two relation depositor which denote the association between the customer and the bank account that the customer have and the borrower which denote the association between the customer and the bank loan that the customer have taken. The relation depositor consists of two filled customer name and the account number while the relation borrower consists of customer name and the loan number. For each example on set operation we need to find the set of all the customers who have account at the bank and set of all the customers who have a loan at the bank. Consider the example of Union. Find all the customer who have a loan and account or both at the bank. In these two tables customer name shown in blue box have a loan and account or both at the bank. So the result contains nine tuples and we write the query as select customer name from depositor Union select customer name from borrower. Here Union clause produces distinct values in the result set. Hence the nine tuples will be in the result instead of all the tuples of depositor and the borrower that is 14 tuples. Set operation Union all. Suppose a tuple occurs m times in the relation r and n times in the relation s then it occurs m plus n times in our Union all s. If Jones has four accounts and three loans at the bank then there will be seven tuples with the name Jones in the result. Example of Union all. Find all the customers who have a loan and account or both at the bank. The relation required are depositor and the borrower. As we have to use Union all operator it fetches all the tuples of the depositor and the borrower. Hence the result contain all the tuples of depositor as well as the borrower that is 14 tuples. The query for this example can be written as select customer name from depositor Union all select customer name from the borrower. Set operation intersect and intersect all. The intersect operator is used to find the intersection of the data sets of the two select statement. The number and the type of the field present in the two data set must be same and the similar. The intersect clause produces distinct values in the result set and to face the duplicate values to intersect all must be used instead of the intersect. The syntax for the intersect is select column name from table one intersect select column name from the table two. And the syntax for the intersect all is select column name from table one intersect all select column name from table two. Example find all customers who have a loan and an account at the bank. So here we have to find out the customer name which are common in depositor as well as the borrower relation. In both table customer name Hayes, Jones and Smith are common customer name. But the intersect operator produces distinct values in the result set. Hence the result contain three customer name Hayes, Jones and Smith. Even if the depositor relation contain two tuples for the customer name Hayes and the borrower relation contain two tuples for the customer name Smith. Hence the query using intersect operator can be written as select customer name from depositor intersect select customer name from the borrower and the result contain the distinct tuples from both the relation. Set operation intersect all. Suppose a tuple occurs m times in the relation r and n times in the relation s then it occurs minimum of m comma n times in the r intersect all s. If Jones has four account and three loans at the bank then there will be three tuples with the name Jones in the result that is the minimum of four accounts and three loans equals to three tuples. Example of intersect all operator find all customers who have a loan and account at the bank. The relation required are depositor and the borrower. Common customer name are Hayes, Jones and Smith. Even if in the relation depositor there are three tuples for the Hayes and in the relation borrower two tuples for the Hayes. The operator intersect all will contain the minimum of three accounts and two loans that is only two tuples for the Hayes in the result and same for the customer name Smith. Hence the result contains the four tuples. We can write the query for this example as select customer name from depositor intersect all select customer name from borrower. Set operation accept and accept all. The accept operator is used to combine the two select statement and return the row from the first select statement that are not returned by the select second select statement. The accept clause produce distinct values in the result set and to face the duplicate values to accept all must be used instead of just accept. The syntax for the accept operator is given here and the syntax for the accept all operator is select column name from table one except all select column name from table two. Example of the accept operator find all the customers who have an account but no loan at the bank. So here we have to consider those tuples from the relation depositor which are not there in the relation borrower. In the depositor relation two rows for the Johnson, Lindsey and the Turner shown in the blue box are there in the relation depositor but not in the relation borrower. The customer name Hayes, Jones and Smith shown in the red box are common to both the relation. Hence these names will not be there in the result of this example. Hence the result of this example contains three customer name Johnson, Lindsey and the Turner. Even if the relation depositor contains two tuples for the Johnson but because of the accept operator the result contains only one tuple for the Johnson. The query for this example using accept is select customer name from depositor except select customer name from the borrower. Set operation accept all. Suppose a tuple occurs m times in the relation r and n times in the relation s then it occurs max of 0 comma m minus n times in the r except all s. If the Jones has four account and the three loans at the bank then there will be max of 0 comma 4 accounts minus 3 loans equals to 1 that is max of 0 comma 1 is 1. So, only one tuple will be in the result of accept all. If the Jones has three accounts and four loans at the bank then there will be max of 0 comma 3 accounts minus 4 loans equals to minus 1 that is max of 0 comma minus 1 is 0. So, there will not be any tuple in the result of accept all. Consider the example of accept all operator find all the customers who have account but no loan at the bank. In depositor relation two tuples for the Johnson one tuple for the Lindsey and the Turner shown in the blue box are there in the relation depositor but not in the relation borrower. Hence accept all operator consider both the tuple for the Johnson as max of 0 comma 2 account for the Johnson minus 0 loan for the Johnson equals to 2. Hence there will be four tuples in the result of this example. The query for this example is select customer name from depositor accept all select customer name from the borrower. Till now we consider the various set operation. Now pause this video and reflect on this question for a minute or two minute and write your response. Once you return the answer to these questions then you can restart playing this video. Question is if the customer has two accounts and three loans at the bank then how many tuples will be there in the union all intersect all and the acceptor. I hope all of you have completed this activity. Suppose a tuple occurs m times in the relation r and n times in the relation s then it occurs m plus n times in the r union all s minimum of m comma n times in the r intersect all s max of 0 comma m minus 1 times in the r except all s. If the customer has two accounts and three loans at the bank then the number of tuples will be in union all that is five intersect all except all 0. These are some references. Thank you.