 Hello everyone, I am Mrs. Sunita Doh. The topic covered here is Structured Query Language nested subquery. What is subquery? A subquery or inner query or nested query is a query within another SQL query and embedded within VAR clause. SQL has an ability to nest queries within one another. Learning outcome At the end of the session, students will be able to write the query for given problems statement using SetMembershipOperator and SetComparisonOperator. SQL provides a mechanism for nesting of subqueries. A subquery is a select from VAR expression that is nested within another query. Subqueries provide an easy and efficient way to handle the queries that depend on the result from another query. It returns the data that will use in the main query as a condition to further restrict the data to be retrieved. SQL executes the innermost subquery first then the next level. A common use of subqueries is to perform the test for SetMembership, SetComparison, TestForEmptyRelation and TestForTheAbsenceOfDuplicateTouple. In this video, we are going to cover the SetMembership and SetComparison. There are few rules for, there are few rules that subquery must follow. Subqueries must be enclosed within parenthesis. A subquery can have only one column in the select clause unless the multiple columns are in the main query for the subquery to compare its selected column. An order by command cannot be used in the subquery although the main query can use an order by clause. The group by command can be used to perform the same function as the order by in a subquery. Subqueries that return more than one row can only be used with the multiple value operator such as in operator. The between operator cannot be used with a subquery. However, the between operator can be used within the subquery. SetMembership, the in connective TestForSetMembership where the set is a collection of values produced by a select clause. The not-in connectives test for the absence of set membership. SQL, in and not-in operators are used to specify multiple values in the where clause. The in and the not-in operator can also be used on the enumerated sets. Syntax for the set membership is select column names from table name where column name in or not in in bracket select statement. Syntax for the set membership on the enumerated set is select column name from table name where column name in or not in in bracket value 1 comma value 2 and so on. Consider the example of set membership on the enumerated set. Find all the customers that are lived in Brooklyn, Harrison and Ray. So the relation required is customer which consists of customer name, customer street and the customer city. Tupper shown in blue box indicate all the customers that are lived in Brooklyn, Harrison and Ray. Result shows that 5 customers are lived in the Brooklyn, Harrison and the Ray. The query for this example is select star from customer where customer city in in bracket Brooklyn comma Harrison comma Ray. If we replace a capital B in the customer city Brooklyn by small b in the where clause as shown in the query then the result contains only 4 tuples. This result does not contain the tuple where the customer city is Brooklyn that is capital B that is the second tuple having the name Brooks shown in the red box. So the names used in the enumerated set is case sensitive. Consider an example of set membership not in operator. Find all the customers that are not lived in the Brooklyn, Harrison and the Ray. Tuples shown in the blue box indicate all the customers that are not lived in the Brooklyn, Harrison and the Ray. Results show that 7 customers are not lived in the Brooklyn, Harrison and the Ray. So the query can be written as select star from the customer where customer city not in in bracket Brooklyn comma Harrison comma Ray. Consider one more example find all the customers who have both an account and a loan at the bank using set membership. So the relation required are depositor and borrower. In this example customer Hayes Jones and Smith have an account as well as loan at the bank. Hence the result of this query consists of these 3 tuples. The query using set membership operator in can be written as select distinct customer name from borrower where customer name not in sorry in bracket select customer name from depositor. Now consider the example find all customers who have and loan but not an account at the bank. Here we have to use the set membership operator not in the relation required are depositor and the borrower. 3 customers Adams Curry and Williams have and loan in the bank but not the account. So the result consists of these 3 customers. Query using set membership operator not in is written as select distinct customer name from borrower where customer name not in in bracket select customer name from depositor. Set comparison SQL allows the 2 set comparison operator sum and all that is less than sum less than equals to sum greater than sum greater than equals to sum equals to sum and not equals to sum comparison and less than all less than equals to all greater than all greater than equals to all equals to all and not all comparison. Syntax is select column name from table name where column name comparison operator sum or all select column name from table name where condition. F comparison operator sum R this is equivalent to their exist T belongs to R such that F comparison operator T where the comparison operators can be less than less than equals to greater than greater than equals to equals to and not is not equals to. Here some examples using the set comparison operator sum are given equals to sum is identical to in whereas not equals to sum is not the same as not in. Example find all the branches that have greater assets than some branch located in Brooklyn. The relation branch consist of branch name, branch city and the asset so we have to find all the branches that have greater assets than some branches located in Brooklyn. So first we find the asset value at the branch city Brooklyn. So there are 2 such asset value 71 lakh and 90 lakh and the branch name are Brinton and downtown respectively. Now we have to find all the branches that have greater assets than some branch located in Brooklyn. So 2 such tuples are second and 8th where the asset value is greater than the asset value 71 lakh and 90 or 90 lakh at Brooklyn branch. In second tuple for the branch name downtown asset value is 90 lakh which is greater than the asset value 71 lakh at branch name Brinton located in the branch city Brooklyn and in 8th tuple for the branch name round hill asset value is 80 lakh which is greater than asset value 71 lakh at the branch name Brinton located in the branch city Brooklyn. Hence the result contain these 2 tuples downtown and round hill with asset value greater than some branch located in the Brooklyn. The query for this example using set comparison operator sum is written as select branch name from branch where asset greater than sum in brackets select asset from branch where branch city is equal to Brooklyn. Till now we consider various set membership using in and not in operator and the set comparison using operator sum. 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. Now question is what is the output of following query? I hope all of you have completed this activity. The question is what is the output of the following query? From the SQL query we need to find all the branches that have greater asset than or equal asset to some branch located in the Brooklyn. So the result consist of these 3 tuples set comparison f comparison operator all r this is equivalent to for all t belongs to r in bracket f comparison operator t where comparison operators can be less than less than equals to greater than greater than equals to equals to and not equals to. Here the sum examples using set comparison operator all are given. Example find the name of all the branches that have greater asset than all the branches located in Brooklyn. So the relation required is branch and the set comparison operator use is greater than all. Consider the relation branch consisting of branch name, branch city and the asset. As we have to find all the branches that have greater asset than all the branches located in Brooklyn. So first we find the asset at the branch city Brooklyn. So there are 2 such asset value that is 71 lakh and 90 lakh and the branch name are Brinton and downtown respectively. Now we have to find all the branches that have greater asset than all the branches located in Brooklyn. So there is only one tuple with the branch name round hill and the asset value is 91 lakh which is greater asset than the asset value 71 lakh and 90 lakh located at the Brooklyn branch. Hence the result contains only one tuple round hill with asset value greater than all the branches located in Brooklyn. And the query using set comparison operator all is select branch name from branch where asset greater than all in bracket select asset from branch where branch city is equal to Brooklyn. These are some references. Thank you.