 Hello everyone, I am Mrs. Sunita Dole. Topic covered here is Structured Query Language nested subquery. As considered in the previous video, a subquery is a query within another SQL query and embed within the where clause. Learning outcome At the end of the session, students will be able to write the query for given problem statement using exist, not exist, unique and not unique operator. A subquery is a select from where expression that is nested within another query. A subquery return data that will be used in the main query as a condition to further restrict the data to be retrieved. A common use of subqueries is to perform the test for set membership, set comparison, test for empty relation and test for absence of duplicate tuples. In the previous video, we considered set membership and set comparison, while in this video we are going to cover test for empty relations and test for the absence of duplicate tuples. Test for empty relation SQL includes the feature for testing whether a subquery has any tuple in its result, that is the exist operator is used to test for the existence of any record in a subquery. The result of the exist is a Boolean value true or false. The exist construct returns the value true if the argument subquery is non-empty. Exist R, this is equivalent to R is not equals to phi and not exist R, this is equivalent to R equals to phi. Syntax for test for the empty relation is select column name from table name where exist or not exist in brackets subquery that is select column name from table name where condition. Consider an example of test for empty relation, find all customers who have both an account and a loan at the bank. So, relation required are borrower and depositor as shown below. As we have to find all the customers who have both an account and a loan at the bank. So, for this example we require all the customer names which are common in both the relation. In relation borrower first tuple contains the customer name Adams, so we check the depositor relation for this name. Now this name is not there in the depositor relation, so now consider the second tuple from the borrower relation having the customer name Curry and we check the depositor relation for this name. This name Curry is not there in the depositor relation, so now we consider the third tuple from the relation borrower having customer name Hayes and we check the depositor relation for this name. Now this name is in both relation, so this name will be in the result. In this way we check both the relation and find all the customer names which are common in both relation. So the customer names shown in the blue box will be in the result which are common in both relation. Query for this example can be written as select customer name from borrower where exists. In brackets subquery select star from depositor where depositor dot customer name is equal to borrower dot customer name. Here the second example find all the customers who have a loan but not an account at the bank relation required borrower and depositor. As we have to find all the customers who have a loan but not an account at the bank, so we are going to consider those customer name that are in the relation borrower but not in the relation depositor. Such customer names are Adams, Curry and Williams. These names are not in the relation depositor, hence the result contain these three names that is Adams, Curry and Williams. So query using the operator not exists can be written as select customer name from borrower where not exists. In brackets subquery select star from depositor where depositor dot customer name is equal to borrower dot customer name. Till now we consider the topic that is test for empty relation using exist and not exist. 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. What is the output of the following query for the given relation depositor and the borrower? I hope all of you have completed this activity. The question is what is the output of the following query for the relations depositor and the borrower? And the query given for these two relation is select customer name from the borrower where exist. In brackets select star from depositor where depositor dot customer name is equal to borrower dot customer name. Here the SQL query indicate the name of customer having both loan as well as account at the bank since all customer names that are in the relation borrower are also there in the relation depositor. Hence the result contained four tuples that is Hayes, Jones and two tuples for Smith. Second query is select customer name from borrower where not exist. Select star from depositor where depositor dot customer name is equal to borrower dot customer name. This query indicate the name of customers having loan but not an account at the bank. All the customer names that are in the borrower relation are also in the relation depositor hence the result does not contain any customer name. Test for the absence of the duplicate tuples. The unique construct test whether a sub query has any duplicate tuples in its result. It returns a Boolean value indicating the presence or the absence of the duplicate tuple. Unique construct returns true only if the sub query has no duplicate tuples as it returns false. Syntax for the test for absence of duplicate tuples using unique operator is select table1 dot column name from table1 where unit or not unique in bracket select table2 dot column name from table2 where table1 dot column name is equal to table2 dot column name. Syntax for test for the absence of duplicate tuples without unique operator is select table1 dot column name from table1 where one less than equals to or one greater than in bracket select table2 dot column name from table2 where table1 dot column name equals to table2 dot column name. Consider an example for test for the absence of duplicate tuples. Find all the customers who have at most one account at the peri-rage branch. So the relations required are depositor and account. Common attribute between both the relation is account number. So this common attribute is used to combine these two relation while writing the query. After considering both the relations there is only one customer haze having only one account at the peri-rage branch. Hence the result contains only one customer name haze. Query using test for absence of duplicate tuples using unique is written as select t dot customer name from depositor as t where unique in bracket. The sub query is select r dot customer name from account comma depositor as r where t dot customer name equals to r dot customer name and r dot account number equals to account dot account number and account dot branch name is equal to peri-rage. And the query using one less than equals to for the same example is given below that is select t dot customer name from depositor as t where one is less than equals to in bracket sub query select r dot customer name from account comma depositor as r where t dot customer name is equal to r dot customer name and r dot account number is equal to account dot account number and account dot branch name is equal to peri-rage. Here simply we have to write less than equals to instead of the operator unique as in the query of the previous example. Now consider the second example using not exist find all the customers who have at least two account at the peri-rage branch. So obviously the relations required are account and the depositor. So the common attribute is account number after considering both the relation there is no customer who have at least two account at the peri-rage branch. Hence the result does not contain any customer name. So we can write the query for this example as select t dot customer name from depositor as t where not unique in bracket sub query select r dot customer name from account comma depositor as r where t dot customer name is equal to r dot customer name and r dot account number is equal to account dot account number and account dot branch name is equal to peri-rage. Till now we consider the topic test for the absence of duplicate tuples using the unique and not unique. 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 this question then you can restart playing this video. The question is what is the output of the following query for the given relation instructor? I hope all of you have completed this activity. So the question is what is the output of the following query for the given relation instructor and the query is select i dot instructor underscore id comma i dot instructor underscore name from the instructor as i where unique in bracket the sub query select i n s t dot instructor underscore id from instructor as i n s t where i dot instructor underscore id is equal to i n s t dot instructor underscore id and i n s t dot year is equal to 2 0 1 9. Here the query indicate the name of all the instructor that taught at most one course in the year 2019. There are two such instructor Hays and Jones having the id 1 and 2. Instructor Smith also taught the course in 2019 but the Smith taught two courses hence this instructor name will not be there in the result. Hence the result contain these two instructor name Hays and Jones. These are some references. Thank you.