 Hello everyone, I am Mrs. Sunita Doh, the topic covered here is complex queries. Complex queries are often hard or impossible to write as a single SQL block or union, intersection or difference of the SQL blocks. What is SQL block? An SQL block consists of a single select from where statement possibly with the group by and having clause. Learning outcome. At the end of the session, students will be able to write the complex queries using the derive relation and the with clause. Two ways of composing multiple SQL blocks to express complex queries are derive relation and the with clause. Consider the derive relation. SQL allows a sub query expression to be used in the from clause. We use such expression then we must give the result relation a name and we can rename the attributes. We do this renaming by using the as clause. Consider the example. Consider the sub query which generates a relation consisting of name of all the branches and their corresponding average account balance. So input to this sub query is the relation account which consists of the attributes account number, branch name and balance. Query is select branch name, average of balance from account grouped by branch name as branch average in parenthesis branch name comma average balance. The sub query force find the average of balance branch wise. The sub query result is name branch average with the attribute branch name and the average balance. In the account relation there are two tuples for the branch name Brinton hence the average of balance is 825 in the resulting table and the remaining tuples are same as that of the original relation. To illustrate the use of a sub query expression in the from clause consider the example. Find the average account balance of those branches where the average account balance is greater than dollar 500. The relation required to write the query is account the query is select branch name comma average balance from select branch name comma average balance from account grouped by branch name as branch average in parenthesis branch name comma average balance where average balance is greater than 500. The relation branch average is the result of the sub query returned within from clause which find the average of balance branch wise. Final result contains only those tuple from the relation branch average where the average account balance is greater than dollar 500. In the relation branch average third, fourth and fifth tuple contains the average balance greater than 500 hence these three tuples are in the final result. Here while writing the query we do not need to use the having clause since the sub query in the from clause compute the average balance and its result is name as branch average and we can use the attribute of branch average directly in the where clause. To illustrate the use of the sub query expression in the from clause consider one more example. Find the maximum across all branches of the total balance at each branch. Relation required is account query is select max of total balance from select branch name comma sum of balance from account grouped by branch name as branch total in parenthesis branch name comma total balance. Here also the having clause does not help us in this task but we can write this query easily by using a sub query in the from clause. The relation branch total is the result of the sub query return in the from clause which find the total balance at each branch. The result of this query is the maximum across all the branches of total balance at each branch. In the relation branch total third tuple contains the maximum balance 1650 for the branch name brinton hence this tuple is in final result. Till now we consider how to write the query using the derived relation. 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. Question is find the minimum across all the branches of total amount at each branch and the branch name for given relation using derived relation. I hope all of you have completed this activity. So the question is given on this slide query is select minimum total amount comma branch name from select branch name comma sum of amount from loan grouped by branch name as branch total in parenthesis branch name comma total amount. The relation branch total is the result of the sub query return in the from clause which find the total balance at each branch. The result of this query is the minimum across all the branches of the total balance at each branch. In the relation branch total fifth tuple contains the minimum balance 500 for the branch name meanness. Hence this tuple is in the final result. Complex queries are much easier to write and to understand if we structure them by breaking them into the smaller views that we then combine just as we structure the programs by breaking their tasks into the procedures. Now consider the width clause. However, unlike a procedure definition a create view clause create a view definition in the database and the view definition stays in the database until a command drop view view name is executed. The width clause provides a way of defining a temporary view whose definition is available only to the query in which width clause occurs. Example, find all accounts with the maximum balance. Relation required is account query is given on this slide. This query select account with the maximum balance. If there are many account with the same maximum balance all of them are selected. We could have written the above query by using the nested subquery in either the from clause or the where clause. However, using the nested subqueries would have made the query harder to read and understand. The width clause make the query logic clearer. It also permit a view definition to be used in the multiple places within a query. The query return using width clause returns the maximum balance 900 of the account relation. The query select from where clause returns the account number with the maximum balance. Consider one more example of the width clause. Find all branches where the total account deposit is greater than the average of total account deposit at all the branches. Relation required is account. The query is width branch total in parenthesis branch name comma value as select branch name comma sum of balance from account grouped by branch name. Now, second width clause that is width branch total average in parenthesis value as select average of value from branch total and now main query that is select branch name from branch total comma branch total average where the branch total dot value is greater than equals to branch total average dot value. The relation branch total is the result of the first width clause which find the total balance at each branch. The branch total average relation is the result of the second width clause which find the average of the total account deposit at all branches. The resulting relation contains only one tuple corresponding to the branch name Brinton in which the branch total 1650 is greater than the average of total account deposit at all the branches that is 790. Till now we consider how to write the query using width clause. Now, pause this video and reflect on this question for a minute or two minute and write your response. Once you have written the answer to this question then you can start playing this video. The question is find all loans with the minimum amount for given relation using the width clause. I hope all of you have completed this activity. The query is with minimum amount of values as select minimum of amount from loan main query that is select loan number from loan minimum amount where loan dot amount equals to minimum amount dot value. The minimum amount relation is the result of the sub query written using width clause which find the minimum loan amount value. The resulting relation is the result of select from where clause which keeps the loan number L11 and L93 with the minimum amount for the given relation loan. These are the references used for preparing this presentation. Thank you.